大型表会让MySQL变慢吗？（译文）

oracleace 2023-02-03

573

摘要：本文分析了一个很多人都关心的问题，在MySQL数据库中如果表中的数据量过大，性能会下降吗？并给出了提高MySQL中大型表性能的方法。

作者：Eduardo Krieg，来自Percona公司

原文网址：https://www.percona.com/blog/why-mysql-could-be-slow-large-tables

关于译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一位Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证。
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

16年前，我们的创始人Peter Zaitsev曾就这个话题写过文章，其中的一些观点至今仍然有效，我们将在这里介绍更多内容。现在的技术已经进化地足够成熟，但仍有一些人认为MySQL仅适用于小型项目，它不能很好地处理大型表。

一些初创公司在早期就采用了MySQL，例如Facebook，Uber，Pinterest等等，这些公司现在是大型而成功的公司，这证明了MySQL可以在大型数据库和高负载的站点上稳定地运行。随着现在磁盘的速度更快，CPU和内存资源更便宜，我们可以更有把握地说MySQL可以以良好的性能处理TB级的数据。例如，在Percona托管服务中，我们有许多客户有TB级数据，并且性能良好。

在这篇博文中，我们将回顾在 MySQL 中更有效地管理大型数据集需要考虑的关键问题。

—

主键

主键是在 MySQL 中创建新表时要考虑的最重要的事情之一，我们应该为每一个表显式地创建一个主键。InnoDB将按主键顺序对数据进行排序，主键将用于引用磁盘上的实际数据页。如果我们不指定主键，MySQL 将指定其它唯一索引作为主键，如果找不到其它唯一索引，它将创建一个内部聚集索引作为主键，这种情况不是最理想的。

当从应用的逻辑中找不到主键时，我们可以使用自增（auto_increment）列作为主键。

注意：从MySQL 8.0.30开始，新增了生成不可见主键的特性，可以在用户没有显式定义主键时自动添加不可见的主键。

另外，请记住，主键的一部分将添加到每个二级索引的末尾，因此应尽量避免选择字符串作为主键，因为这会使二级索引更大，并且性能不会达到最佳状态。

—

冗余索引

众所周知，在大多数情况下，通过索引访问记录比通过表扫描更有效。有时需要在多个索引上包含同一个字段以适应不同的查询模式，有时为同一列创建的某些索引是冗余的，从而导致插入或删除数据时（随着索引的更新）产生更多开销，并增加用于存储索引的磁盘空间。

您可以使用我们的工具之一 pt-duplicate-key-checker 来检测重复索引。

示例（使用employee sample DB）：

假设我们有以下架构：

db1 employees> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_last_name` (`last_name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

现在，假设我们需要按last_name和hire_date进行过滤，我们将创建以下索引：

ALTER TABLE employees ADD INDEX idx_last_name_hire_date (last_name,hire_date);

我们最终会得到以下架构：

db1 employees> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_last_name` (`last_name`),
  KEY `idx_last_name_hire_date` (`last_name`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

现在，索引idx_last_name和idx_last_name_hire_date具有相同的前缀（last_name）。

新的索引idx_last_name_hire_date可用于匹配仅按last_name字段进行过滤的查询，也可以匹配按last_name和hire_date字段进行过滤的查询，这时idx_last_name索引成了多余的。

我们可以通过使用pt-duplicate-key-checker来证实这一点：

[user1] percona@db1: ~ $ pt-duplicate-key-checker -d employees
# ########################################################################
# employees.employees                                                     
# ########################################################################




# idx_last_name is a left-prefix of idx_last_name_hire_date
# Key definitions:
#   KEY `idx_last_name` (`last_name`),
#   KEY `idx_last_name_hire_date` (`last_name`,`hire_date`)
# Column types:
#   `last_name` varchar(16) not null
#   `hire_date` date not null
# To remove this duplicate index, execute:
ALTER TABLE `employees`.`employees` DROP INDEX `idx_last_name`;




# ########################################################################
# Summary of indexes                                                      
# ########################################################################


# Size Duplicate Indexes   350357634
# Total Duplicate Indexes  1
# Total Indexes            17

—

数据类型

数据类型定义不恰当的情况并不少见。在很多例子中，本来定义smallint类型就够用的字段被定义成了int 类型，或者应该使用变长varchar类型的字符串被定义成了固定长度的字符串字段。对于小型表来说，这可能不是一个大问题，但对于具有数百万条记录的表，过度预分配数据类型会使表变大，性能不是最优。

当然，数据类型的定义需要确保数据类型正确并能满足未来增长的需求。

例子：创建4个简单的表，使用不同的数据类型存储字符串。

db1 test> CREATE TABLE tb1 (id int auto_increment primary key, test_text char(200)); 
Query OK, 0 rows affected (0.11 sec)


db1 test> CREATE TABLE tb2 (id int auto_increment primary key, test_text varchar(200)); 
Query OK, 0 rows affected (0.05 sec)


db1 test> CREATE TABLE tb3 (id int auto_increment primary key, test_text tinytext); 
Query OK, 0 rows affected (0.13 sec)


db1 test> CREATE TABLE tb4 (id int auto_increment primary key, test_text text); 
Query OK, 0 rows affected (0.11 sec)

插入包含字符串的2000条记录：

[user1] percona@db1: ~ $ for i in {1..2000}; do for tb in {1..4}; do mysql test -e "INSERT INTO tb$tb (test_text) VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse euismod, nulla sit amet rhoncus venenatis, massa dolor lobortis nisi, in.');"; done; done

现在所有的4个表都有2000条记录：

[user1] percona@db1: ~ $ mysql test -e "select count(*) from tb1; select count(*) from tb2; select count(*) from tb3; select count(*) from tb4;"
+----------+
| count(*) |
+----------+
|     2000 |
+----------+
+----------+
| count(*) |
+----------+
|     2000 |
+----------+
+----------+
| count(*) |
+----------+
|     2000 |
+----------+
+----------+
| count(*) |
+----------+
|     2000 |
+----------+

现在我们检查一下这4个表达磁盘空间占用情况：

[user1] percona@db1: ~ $ sudo ls -lh var/lib/mysql/test/|grep tb
-rw-r-----. 1 mysql mysql 592K Dec 30 02:48 tb1.ibd
-rw-r-----. 1 mysql mysql 464K Dec 30 02:48 tb2.ibd
-rw-r-----. 1 mysql mysql 464K Dec 30 02:48 tb3.ibd
-rw-r-----. 1 mysql mysql 464K Dec 30 02:48 tb4.ibd

我们可以看到tb1比其它表更大，因为它在固定大小的char (200)字段中存储文本，该字段将存储定义的200个字符，而不考虑实际插入的字符串长度，而varchar、tinytext和text字段是可变大小的字段，将只存储字符串的实际长度(在本例中，我们插入了143个字符)。

—

压缩

压缩是通过改变数据的编码来重构数据的过程，以减少数据占用的存储空间。目前业界有许多数据压缩工具和算法。MySQL支持使用Zlib库和LZ77压缩算法对InnoDB表进行本地压缩。它可以节省磁盘空间和内存中的数据，但压缩和解压缩数据会占用大量CPU资源。如果CPU使用率不是您的应用中的瓶颈，您可以利用压缩来提高性能，这意味着从磁盘读取和写入内存的数据更少，并且索引也得到压缩。压缩可以节省存储成本和备份时间。压缩率取决于多种因素，但与任何其他压缩方法一样，它对文本比对二进制文件更有效，因此包含文本字段的表具有更好的压缩率。

例子：创建一个新的压缩表employees_compressed：

mysql> CREATE TABLE employees_compressed LIKE employees;
Query OK, 0 rows affected (0.12 sec)


mysql> ALTER TABLE employees_compressed ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> INSERT INTO employees_compressed SELECT * FROM employees;

比较占用的空间：

[user1] percona@db1: ~ $ sudo ls -lh var/lib/mysql/employees/|grep employees
-rw-r-----. 1 mysql mysql 704M Dec 30 02:28 employees.ibd
-rw-r-----. 1 mysql mysql 392M Dec 30 17:19 employees_compressed.ibd

在这个简单的例子中，我们的压缩率大约是45%！

Yves有几篇博客文章描述了MySQL压缩并对其进行了基准测试：

《MySQL 中的压缩选项（第 1 部分）》

https://www.percona.com/blog/2018/11/23/compression-options-in-mysql-part-1/

《MySQL 中的压缩选项（第 2 部分）》

https://www.percona.com/blog/2018/11/23/compression-options-in-mysql-part-1/

—

存档数据

一些公司必须将数据保留多年，以保证合规性或业务需求。但是在很多数据仅在短时间内需要访问。例如，为什么要将应用程序会话信息保留多年？

虽然MySQL可以处理大型数据集，但建议仅将有用的数据保留在数据库中，因为这将使数据访问更加高效，并且还有助于节省存储和备份成本。Gaurav有一篇很好的博客文章，《MySQL数据归档与最小中断》

（https://www.percona.com/blog/mysql-data-archival-with-minimal-disruption/）展示了我们如何使用pt-archiver轻松归档旧数据。

—

分区（Partitioning）

分区是一项根据分区键将大型表划分为较小的子表的数据库功能，表分区的最常见用例是按日期划分分区。

例如：如果数据需要保存多年而且对数据的查询是按年份进行过滤，则按年份对表进行分区会有助于性能的提升。在这种情况下，只读取一个较小的分区比读取一个包含多年记录的大型表更高效。

根据查询模式设置分区键非常重要，因为如果查询并不总是使用分区键作为筛选条件，则需要扫描多个分区以获取所需的数据，这会导致巨大的性能损失。

分区是一个很酷的功能，但如上所述，它并不适合所有的场景，需要仔细规划，因为选择一个糟糕的分区键可能会导致巨大的性能损失。

—

分片（Sharding）

分片是将数据进行水平拆分，即将数据分发到多个服务器（分片），这意味着一个表的不同数据部分可以存储在多个不同的服务器上。这有助于将大型数据集拆分为存储在多个服务器中的较小数据集。

分片的数据拆分方式与分区类似，分片键决定着数据如何在分片之间拆分和分布的模式，在应用程序层要有一个协调器将查询分发到存储数据的特定分片。此外，重要的是要根据表的查询模式慎重选择适当的分片键，以便通过仅路由到一个分片来解决大多数查询，因为从多个分片中查找数据，然后对其进行过滤、处理和聚合是一项昂贵的操作。

综上所述，并非所有应用程序或工作负载都适合分片，并且，再加上需要正确处理应用程序，这可能会增加应用架构的复杂度。

MongoDB原生支持分片，但是MySQL不支持，但是MySQL业界中有一些工具可以实现分片，例如：

MySQL 集群

MySQL NDB Cluster是由Oracle为MySQL开发的内存数据库集群解决方案，它原生支持分片，并对应用程序透明，它以付费订阅方式提供。

ProxySQL

它是一个功能丰富的开源MySQL代理解决方案，它可以对最常见的MySQL架构（PXC Galera，Replication，Group Replication等）进行查询路由。它允许通过配置一组后端服务器（分片）和一组查询规则进行分片，将应用程序查询路由到指定的分片。

请注意，它需要对应用程序进行一些处理，因为它不支持从多个分片进行合并和数据检索。

如果需要了解更多的信息，可以参考Marco的博客《使用ProxySQL的MySQL分片》（https://www.percona.com/blog/2016/08/30/mysql-sharding-with-proxysql/）

Vitess

它是由PlanetScale创建的开源数据库集群解决方案，与MySQL引擎兼容，支持原生分片。您可以在我们的博客文章中找到有关 Vitess 的更多信息 Alkin：《关于 Kubernetes for MySQL 的 Vitess 简介 – 第 I 部分，共 III 部分》（https://www.percona.com/blog/2020/01/13/introduction-to-vitess-on-kubernetes-for-mysql-part-i-of-iii/）。

—

MyRocks

MyRocks是由Facebook开发的开源存储引擎，它是为优化大数据集的数据存储和访问而开发的。MyRocks在Percona Server for MySQL中发布。

Vadim有一篇很酷的博客文章，涵盖了MyRocks中的大数据集：《MyRocks 用例：大数据集》（https://www.percona.com/blog/myrocks-use-case-big-dataset/）

—

查询优化

经常会发现应用程序在开始时性能非常好，但随着数据的增长，性能开始下降。最常见的原因是编写不佳的查询或糟糕的架构设计在数据量少的时候性能良好，但是，随着数据的增长，所有这些问题都会暴露出来。您可以使用slow_query_log和 pt-query-digest 来找到有问题的查询。

—

管理

对大型表执行管理任务可能会很痛苦，特别是模式更改和备份。

对于模式更改，Percona有一个工具pt-online-schema-change，可以帮助我们执行模式更改，期间对数据库的干扰降至最低。它的工作原理是创建一个进行模式更改的新表，并将现有数据从原始表批量复制到新表，使用触发器将正在进行的更改从原始表复制到新表。这样，在大型表中，pt-online-schema-change 可以在后台运行而无需锁定，以最大程度地减少性能影响。

对于大型数据集的备份，Percona XtraBackup可以帮助减少备份和恢复的时间，它是一种热物理备份解决方案，可以复制表的数据文件，同时将正在进行的对数据库的更改保存为重做日志。它支持本机压缩和加密。

请记住，监视数据库对于帮助您发现问题或瓶颈始终很重要，您可以免费使用和安装Percona Monitoring and Management ，以更深入地了解服务器和数据库的运行状况。它提供 QAN（Query Analyzer查询分析器）来帮助您查找数据库中有问题的查询。

—

结论

MySQL无法处理大型数据集的古老传说只不过是一个传说。随着硬件功能更强大、更便宜，以及技术的发展，现在在 MySQL 中管理大型表比以往任何时候都更容易。

欢迎加我的微信，拉你进高手如云的微信群👇