MySQL数据库性能优化

MySQL是目前最流行和广泛使用的开源关系型数据库之一，随着数据量的增长和访问负载的提高，优化数据库性能变得至关重要，以确保系统能够高效地处理大量的并发请求。本文将记录一些MySQL数据库性能优化的技巧，提高数据库的运行效率，提升系统性能。

衡量查询开销的指标

对于MySQL，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

响应时间是两个部分之和：服务时间和排队时间。

• 服务时间是指数据库处理这个查询真正花了多长时间。
• 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁等。一般最常见和重要的等待是I/O和锁等待。

存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。

一般来说，数据表行数越少访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

理想情况下扫描的行数和返回的行数应该是相同的，而实际情况中，通常需要扫描多行才能生成结果集中的一行。

扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。——《高性能MySQL》

MySQL访问类型

不同的访问方式下，需要扫描的行数可能会不同。访问类型有很多种，EXPLAIN 语句中的type列显示了当前查询的访问类型：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE first_name = 'Zvonko';
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |    10.00 | Using where |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
1 row in set, 1 warning (0.04 sec)

主要包括以下几种类型（速度从慢到快）：

• ALL：全表扫描
• index：索引扫描
• range：范围扫描
• ref：非主键非唯一索引等值扫描
• eq_ref：主键索引或者非空唯一索引等值扫描
• const：常数引用，使用主键或唯一索引进行等值条件查询时会用 const。

下面介绍一些MySQL性能优化方法。

查询性能优化

MySQL基础架构：SQL查询语句执行过程 中介绍了查询语句的执行路径，

执行一条查询语句时，主要执行流程为：

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

查询的每个操作都会花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

执行查询包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。查询性能低的最基本原因是访问的数据太多，因此可以通过减少访问的数据量的方式进行查询性能优化。

使用LIMIT

如果只需要获取前N条记录，可以在查询后面加上LIMIT，不需要查询所有数据，然后再过滤。

如果没有添加索引，并且知道查询结果只有一个，可以使用 LIMIT 1 来提高查询效率。因为找到这条记录后就不会继续扫描了，如果不使用LIMIT，会进行全表扫描。

SELECT * FROM t_user WHERE email = 'xxxxxxx@谷歌.com' LIMIT 1;

如果在email字段上添加了索引就不需要使用LIMIT了。

注意: EXPLAIN 方法在估计行数时不考虑LIMIT语句，比如:

mysql> EXPLAIN SELECT * FROM employees.employees where gender='M';
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299556 | Using where |
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM employees.employees where gender='M' LIMIT 2;
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299556 | Using where |
 ---- ------------- ----------- ------ --------------- ------ --------- ------ -------- ------------- 
1 row in set (0.00 sec)

mysql>

减少返回的列

查询时，如果不需要所有数据列，可以只取需要的列。如果看到SELECT * 语句时，检查一下是否需要返回全部列。

取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA是严格禁止SELECT * 的写法的，这样做有时候还能避免某些列被修改带来的问题。——《高性能MySQL》

添加索引

添加合适的索引是改善性能的最优手段，尤其是当表中的数据量很大时，索引对性能的影响非常大。

在MySQL中，索引是在存储引擎层实现的，所以，不同存储引擎的索引的工作方式可能不一样。此外索引有很多种类型，比如B-Tree索引、哈希索引、空间数据索引（R-Tree）等，它们在不同场景下有性能差异，这里不做过多介绍，大多存储引擎使用的类型是B Tree。

比如下面的查询中 birth_date 字段没有添加索引，采用的是全表扫描：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date = '1965-01-20';
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |    10.00 | Using where |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
1 row in set, 1 warning (0.00 sec)

接下来给 birth_date 字段添加一个索引：

mysql> ALTER TABLE `employees`.`employees` ADD INDEX `birth_date` (`birth_date` ASC) VISIBLE;

执行查询：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date = '1965-01-20';
 ---- ------------- ----------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------- 
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
 ---- ------------- ----------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | employees | NULL       | ref  | birth_date    | birth_date | 3       | const |   50 |   100.00 | NULL  |
 ---- ------------- ----------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------- 
1 row in set, 1 warning (0.00 sec)

可以看到访问类型变成了ref(非唯一索引等值扫描)，EXPLAIN估计的扫描行数大大减少，变成了50。

范围索引扫描

范围扫描（range）类型是一个有范围限制的索引扫描，比全索引扫描（index）更高效。

以下情况都会使用到范围扫描：

1. 范围条件查询： WHERE 子句中使用 BETWEEN、>、<、>=、<= 的查询。注意 != 或者 <> 无法使用索引。
2. 多个等值条件查询：
   • 使用 IN() 和 OR 。NOT IN条件运算符会执行全表扫描，不会使用范围扫描。
   • 使用 like 进行前缀匹配模糊查询，注意必须是前缀匹配：xxx% （这是由MySQL索引的存储结构决定的，因为MySQL的索引是使用B树(B-Tree)存储的，每个B树节点中存储索引值和对应行的地址。B树的搜索是基于前缀进行的，所以只有前缀匹配可以利用到B树索引）。

IN 条件运算符注意事项：

• 如果IN列表包含了太多的值，MySQL可能不会使用索引而使用全表扫描。
• 在IN中使用子查询会使用到索引。

如果 WHERE 子句使用了Mysql函数，会导致索引失效。比如搜索出生年份为1965年的职员(birth_date字段添加了索引)：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE (LEFT(`birth_date`, 4) = '1965');
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |   100.00 | Using where |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ------------- 
1 row in set, 1 warning (0.00 sec)

根据结果可以发现上面的语句采用的是全表扫描，没有使用索引，原因是 WHERE 子句使用了Mysql函数，导致索引失效。要搜索出生年份为1965年的职员，且使用到索引，可使用如下查询语句：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date >= '1965-01-01' AND birth_date <= '1965-12-31';
 ---- ------------- ----------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ----------------------- 
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
 ---- ------------- ----------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ----------------------- 
|  1 | SIMPLE      | employees | NULL       | range | birth_date    | birth_date | 3       | NULL | 1940 |   100.00 | Using index condition |
 ---- ------------- ----------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ----------------------- 
1 row in set, 1 warning (0.00 sec)

可发现采用了范围扫描，扫描行数显著减小。

在前面使用 WHERE 条件的例子中，Extra 列显示了 Using index 或者 Using Where，一般MySQL能够使用如下三种 WHERE 条件（性能从好到坏）：

• 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

排序优化

MySQL有两种方式获取有序的结果，一种是通过索引进行排序，另一种是文件排序（filesort）。

索引排序

索引排序是对存储在数据库索引中的数据进行排序的过程。如果 EXPLAIN 返回的 type 列的值为 index ，则说明 MySQL 使用了索引扫描来排序，比如

mysql> EXPLAIN SELECT * FROM employees.dept_emp ORDER BY emp_no;
 ---- ------------- ---------- ------------ ------- --------------- --------- --------- ------ -------- ---------- ------- 
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra |
 ---- ------------- ---------- ------------ ------- --------------- --------- --------- ------ -------- ---------- ------- 
|  1 | SIMPLE      | dept_emp | NULL       | index | NULL          | PRIMARY | 20      | NULL | 331143 |   100.00 | NULL  |
 ---- ------------- ---------- ------------ ------- --------------- --------- --------- ------ -------- ---------- ------- 
1 row in set, 1 warning (0.00 sec)

文件排序

当不能使用索引排序时，MySQL需要自己进行排序，如果数据量小于“排序缓冲区”，则在内存中进行“快速排序”操作，如果数据量大则需要使用磁盘。MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。MySQL将内存和在磁盘的这个排序过程统一称为文件排序（filesort）。

使用文件排序时，EXPLAIN 返回的 Extra 列显示的是 Using filesort:

mysql> EXPLAIN SELECT * FROM employees.employees order by first_name;
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------- 
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------- 
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299556 |   100.00 | Using filesort |
 ---- ------------- ----------- ------------ ------ --------------- ------ --------- ------ -------- ---------- ---------------- 
1 row in set, 1 warning (0.00 sec)

文件排序有两种排序算法：

• 两次传输排序（旧版本使用）：需要从数据表中读取两次数据，读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
• 单次传输排序（新版本使用）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

可以通过调整 max_length_for_sort_data 这个参数来影响MySQL排序算法的选择，当查询需要所有列的总长度小于这个参数值时，MySQL会使用“单次传输排序”。

排序优化注意事项

MySQL在进行文件排序的时候需要分配临时存储空间，如果需要返回的列非常多、非常大，会额外占用大量的空间，所以应尽可能避免排序或者尽可能避免对大量数据进行排序。如果一定要排序，可以使用索引排序来进行排序优化。

要使用索引排序需要注意以下情况：

1. 对单列排序（无论升序或降序），都会使用索引排序。

2. 如果是组合索引，排序规则要和组合索引的顺序匹配，顺序须满足索引最左前缀规则。如果 WHERE 子句或者 JOIN 子句中对左侧的索引列指定了常量，可以不满足索引的最左前缀的要求。

3. ORDER BY多个字段时，如果其中一个字段没有添加索引，将会走文件排序。

4. 如果排序使用了函数或表达式，不是直接引用索引列，无法使用索引排序。比如：SELECT column1, column2 FROM table_name ORDER BY ABS(column1);

JOIN关联查询

实际应用中，业务通常比较复杂，需要进行关联查询。下面是一些 JOIN 关联查询的优化方法：

1. 尽量减少关联表的数量。JOIN 每增加一个表，查询性能就会下降。
2. 确保关联字段的数据类型相同或兼容。如果数据类型不匹配，MySQL 会进行数据转换，查询效率会降低。
3. 使用主键或唯一索引作为关联键。
4. 尽量使用内连接(INNER JOIN)，避免外连接(LEFT JOIN)。外连接会导致中间表中的每个数据都会返回一次。
5. 使用小表做驱动表，这样可以减少中间表的大小。
6. 使用索引。在关联字段上创建索引可以显著提高查询速度。
7. 只返回需要的列，不要返回所有（*）

其它优化技巧

存储引擎

这里介绍MyISAM和InnoDB这两种最常用的MySQL存储引擎的差异：

1. 事务支持：InnoDB支持事务，MyISAM不支持。所以在需要事务支持的应用场景下，InnoDB的性能会更高。
2. 并发性能：MyISAM采用表锁，更新时会锁定整张表。InnoDB采用行锁，更新时只锁定当前行，可以实现更细粒度的并发控制，不同行之间的写操作可以并发进行，性能更高。
3. 外键约束：MyISAM不支持外键约束，Join时需要在应用层确保数据完整性，性能较差。InnoDB支持外键约束，在多表Join时性能会更高。
4. 索引：InnoDB 支持聚簇索引，MyISAM 则只支持非聚簇索引，聚簇索引的查找效率要比非聚簇索引快，因为聚簇索引查找到索引就查找到了数据位置，而非聚簇索引查找到索引之后，根据记录的数据地址，再去查找数据。（详细介绍可参考MySQL索引介绍）

使用缓存

对于要重复执行的查询，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能会更好。比如可以利用Redis缓存查询结果来提升MySQL的效率，将频繁查询的结果缓存到Redis中，当下次有相同的查询请求时，首先在Redis中查找结果，如果存在则直接返回，避免了对MySQL的查询操作，从而提高响应速度和降低数据库的负载。

重构查询

可以将一个大查询分解为多个小查询。比如删除旧的数据，定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

总结

本文介绍的MySQL数据库性能优化技巧主要有:

1. 选择合适的存储引擎。
2. 使用LIMIT减少返回数据。
3. 减少返回的列，不要返回所有（*）。
4. 索引优化：合理添加索引，正确使用索引。
5. JOIN关联查询中使用小表做驱动表。

MySQL数据库性能优化是一门比较广泛和深入的学科,优化的方法和技巧较多,本文对其做了比较简单的总结和概括。在实际应用和开发中,需要综合考虑实际业务场景来有针对性地进行优化，以获得最佳的性能提升效果。

MySQL优化方法很多，本文仅做简单介绍。在实际应用和开发中，需要根据具体的业务场景和需求进行深入分析和优化，选择合适的优化方法。

参考文档

1. https://mode.com/sql-tutorial/sql-performance-tuning/

2. https://dev.mysql.com/doc/sakila/en/sakila-installation.html

3. https://dev.mysql.com/doc/employee/en/employees-installation.html

4. https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

--THE END--

这篇好文章是转载于：学新通技术网