为什么大表会导致MySQL变慢

虽然技术已经发展成熟，但仍有一些人认为MySQL仅适用于小型项目，或者它不能很好地处理大型表。

一些初创公司在早期就采用了MySQL，如Facebook、Uber、Pinterest等，这些公司现在都是成功的大公司，证明MySQL可以在大型数据库和大量使用的网站上运行。

随着磁盘速度越来越快，CPU和内存资源越来越便宜，我们可以很容易地说MySQL可以以良好的性能处理tb级的数据。

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

1.主键

这是在MySQL中创建新表时要考虑的最重要的事情之一，我们应该总是显式的创建一个主键(PK)。InnoDB将按主键顺序对数据进行排序，这将用于引用磁盘上的实际数据页。如果我们没有指定主键，MySQL将检查其他唯一索引作为PK的候选索引，如果没有，它将创建一个内部聚集索引作为主键，这不是最优的。

当没有应用程序逻辑或可选择的候选主键时，可以使用auto_increment列作为主键。

注意:在MySQL 8.0.30中，引入了不可见主键来在没有明确定义PK时添加一个不可见主键。

此外，请记住，主键将被添加到每个辅助索引的末尾，因此尽量避免选择字符串作为主键，因为这会使辅助索引更大，并且性能不会达到最佳。

2.冗余索引

众所周知，在大多数情况下，通过获取索引访问行比通过表扫描更有效。但是，在某些情况下，为了提供不同的查询模式，在多个索引上定义了相同的列，有时为同一列创建的某些索引是冗余的，这导致在插入或删除数据时(当索引更新时)增加了更多的开销，并增加了用于存储表索引的磁盘空间。

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

示例：

> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `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 emp ADD INDEX idx_last_name_hire_date (last_name,hire_date);

表定义就变成：

> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `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过滤查询，保留last_name索引变的冗余。

可以使用pt-duplicate-key-checker来证实这一点:

$ pt-duplicate-key-checker -d emp
# ########################################################################
# emp.emp                                                     
# ########################################################################
​
# 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 `emp`.`emp` DROP INDEX `idx_last_name`;
​
​
# ########################################################################
# Summary of indexes                                                      
# ########################################################################
​
​
# Size Duplicate Indexes   350357634
# Total Duplicate Indexes  1
# Total Indexes            17

3.数据类型

发现数据类型不正确的数据库并不罕见。在很多情况下，可以存储在smallint字段中用int，或者固定大小的char字段存储在可变大小的varchar字段中。对于小表来说，这可能不是一个大问题，但对于有数百万条记录的表来说，过度配置数据类型只会使表的大小和性能更大，而不是最优的。

确保在规划表的未来增长时正确地设计了数据类型。

例子:创建四个简单的表来存储字符串，但使用不同的数据类型

> CREATE TABLE tb1 (id int auto_increment primary key, test_text char(200)); 
Query OK, 0 rows affected (0.11 sec)
​
> CREATE TABLE tb2 (id int auto_increment primary key, test_text varchar(200)); 
Query OK, 0 rows affected (0.05 sec)
​
> CREATE TABLE tb3 (id int auto_increment primary key, test_text tinytext); 
Query OK, 0 rows affected (0.13 sec)
​
> CREATE TABLE tb4 (id int auto_increment primary key, test_text text); 
Query OK, 0 rows affected (0.11 sec)

分别插入2000行记录：

$ 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

每个表都是2000条记录：

$ 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 |
+----------+

来看看每个表的大小：

$ 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个字符)。

4.压缩

压缩是通过改变编码来重新构造数据的过程，以便将其存储在更少的字节中。目前有许多数据压缩工具和算法。

MySQL支持使用Zlib库和LZ77压缩算法对InnoDB表进行本地压缩。它节省磁盘空间和内存中的数据，但以压缩和解压缩数据的CPU占用为代价。如果CPU使用不是你的瓶颈，可以利用压缩，因为它可以提高性能，这意味着需要从磁盘读取和写入内存的数据更少，索引也会被压缩。它可以帮助我们节省存储成本和备份时间。

压缩比取决于多个因素，但与任何其他压缩方法一样，它在文本上比在二进制文件上更有效，因此带有文本字段的表将具有更好的压缩比。

创建一个新表emp_compressed:

mysql> CREATE TABLE emp_compressed LIKE emp;
Query OK, 0 rows affected (0.12 sec)
​
mysql> ALTER TABLE emp_compressed ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> INSERT INTO emp_compressed SELECT * FROM emp;

大小比较：

​$ sudo ls -lh /var/lib/mysql/emp/|grep emp
-rw-r-----. 1 mysql mysql 704M Dec 30 02:28 emp.ibd
-rw-r-----. 1 mysql mysql 392M Dec 30 17:19 emp_compressed.ibd

这个例子中，压缩率是45%。

5.归档或清除旧的或不使用的数据

有些公司为了遵守法规或满足业务需求，不得不将数据保留多年。然而，在许多情况下，数据被存储只需要很短的时间;例如，为什么要将应用程序会话信息保存多年?

虽然MySQL可以处理大型数据集，但总是建议在数据库中只保留使用的数据，因为这将使数据访问更有效，也将有助于节省存储和备份成本。

6.分区

分区是一种允许根据分区键将一个大表划分为更小的子表的特性。表分区最常见的用例是按日期划分数据。

例如:如果拥有多年的数据，并且你的查询模式是按年过滤的，那么按年划分表是有益的。在这种情况下，只读取一个较小的分区比读取一个包含多年信息的大表更有效。

在基于查询模式进行分区之前分析分区键是非常重要的，因为如果查询并不总是使用分区键作为过滤条件，那么它们将需要扫描一个或多个分区来获得所需的数据，这将导致巨大的性能损失。

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

7.分片

分片是水平分割数据的概念，即将数据分布到多个服务器(分片)，这意味着给定表的数据的不同部分可能存储在许多不同的服务器上。这有助于将大数据集分割成存储在多个服务器上的小数据集。

数据以类似于分区的方式进行分割，使用一个分片键(sharding key)，这是数据如何在分片之间进行分割和分布的模式。这需要在应用程序层处理，并且有一个协调器来读取查询并将查询分发到存储数据的特定分片。

此外，根据对表的查询模式仔细选择适当的分片键是很重要的，以便通过只路由到一个分片来解决大多数查询，因为必须从许多分片中查找信息，然后对其进行过滤、处理和聚合是一项昂贵的操作。

综上所述，并不是所有的应用程序或工作负载都适合分片，并且需要对应用程序进行适当的处理，这可能会增加环境的复杂性。

MongoDB本身支持分片，但是MySQL不支持，但是MySQL世界中有一些实现分片的努力：

·MySQL集群

MySQL NDB集群是Oracle为MySQL开发的内存数据库集群解决方案。它支持对应用程序透明的本机分片。它可以付费订阅。

·ProxySQL

它是一个功能丰富的开源MySQL代理解决方案，允许对最常见的MySQL架构(PXC/Galera, Replication, Group Replication等)进行查询路由。

它允许通过配置一组后端服务器(分片)和一组查询规则进行分片，从而将应用程序查询路由到指定的分片。

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

你可以在Marco的博客中找到更多信息:MySQL Sharding with ProxySQL

·Vitess

它是由PlanetScale创建的开源数据库集群解决方案，与MySQL引擎兼容。

MyRocks

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

8.查询优化

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

9.管理

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

对于模式更改，Percona有一个工具pt-online-schema-change，它可以帮助我们在对数据库影响最小的情况下执行模式更改。它通过应用所需的模式更改创建一个新表，并将现有数据批量从原始表复制到新表。使用触发器将正在进行的更改从原始表复制到新表。

通过这种方式，在一个大型表中，pt-OSC可以在后台运行，而不需要对一个alter进行大量的阻塞操作，从而将性能影响最小化。

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

结论

关于MySQL不能处理大型数据集的旧说法只是一个谬论。随着硬件越来越强大和便宜，以及技术的发展，现在在MySQL中管理大型表比以往任何时候都更容易。