关于 MySQL 慢查询优化的一些思路与知识点

1 慢查询优化思路

当发生慢查询的时候，优化的思路为：

1. 利用慢查询日志定位慢查询 SQL
2. 通过 explain 分析慢查询 SQL
3. 修改 SQL，尽量让 SQL 走索引

2 慢查询日志

MySQL 提供了一个功能——慢查询日志，会记录查询时间超过指定时间阈值的 SQL 到日志中，便于我们定位慢查询并且优化对应的 SQL 语句。

首先查看 MySQL 中关于慢查询相关的全局变量：

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |		【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |		【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |		【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

🍅 这里主要关注三个变量：

1. long_query_time，慢查询的时间阈值，单位秒，如果一个 SQL 语句的执行时间超过这个值，那么 MySQL 就认定其为慢查询
2. slow_query_log，慢查询日志功能是否开启，默认关闭，开启后记录慢查询
3. slow_query_log_file，慢查询日志文件的存储位置

默认慢查询日志功能是关闭的，因此我们需要启动该功能

# 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 设置慢查询时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

这样子设置后，MySQL 重启会丢失这些配置，需要在配置文件中修改才会永久有效。

3 explain

我们可以使用 explain 分析 SQL 语句的执行情况，例如：

mysql> explain select sum(1+2);

执行结果如下，可以看到有很多字段

我们主要看看一些重要的字段：

• select_type 表示查询语句的查询类型，包括简单查询、子查询等等
• table 表示查询的表，不一定是存在表，可能是本次查询中得到的临时表
• type 表示检索类型，使用全表扫描、还是索引扫描等
• possible_keys表示可能使用的索引列
• keys表示查询中实际使用的索引列，由查询优化器决定

3.1 select_type 字段

值	解释
SIMPLE	简单查询，没有使用 UNION / 子查询
PRIMARY	包含子查询/UNION，最外层的查询被标记为 PRIMAY
UNION	UNION 中的第二个或之后的查询
DEPENDENT UNION	UNION 中的第二个或之后的查询取决于外面的查询
UNION RESULT	UNION 的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，取决于外面的查询
DERIVED	用到派生表的查询，即 from 子句中的子查询
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION 中的第二个或者后面的语句属于不可缓存的子查询

3.2 type 字段

对于 InnoDB 存储引擎，type列通常都是all或者index。

关于 type 字段的值，其从上到下对应的 SQL 的执行性能逐渐变差。

值	解释
system	查询对象表只有一行数据，只用于 MyISAM 和 Memory 引擎的表，最坏的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索，只对 MyISAM 引擎有效
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

3.3 extra 字段

值	解释
Using filesort	使用外部排序而不是索引排序，数据量小时从内存排序，否则需要在磁盘完成排序
Using temporary	创建一个临时表存储结构，通常发生在对没有索引的列进行 group by
Using index	使用覆盖索引
Using where	使用 where 语句处理结果
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没有索引
Using index condition	先条件过滤索引，在查数据
Select tables optimized away	使用某些聚合函数访问存在索引的某个字段

4 慢查询例子

准备数据，数据表结构：

create table user_info_large (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
	`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
	`name` VARCHAR(20) NOT NULL COMMENT '用户名',
	`password` VARCHAR(20) not null COMMENT '用户密码',
	`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
	`signature` VARCHAR(50) not null COMMENT '个性签名',
	PRIMARY KEY (`id`) COMMENT '主键',
	UNIQUE (`account`) COMMENT '唯一索引',
	KEY `index_area_signture` (`area`,  `signature`)  COMMENT '组合索引'
);

随机生成 200w 条数据

mysql> select count(id) from user_info_large;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.38 sec)

截取部分数据：

执行以下 SQL 语句，没有使用任何索引字段：

SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Navicat 工具显示的查询时间如下，这并不是 MySQL 真正执行 SQL 的时间，这里面包含了网络传输等时间：

🍅 SQL 具体的查询时间可以查看慢查询日志：

# Time: 2022-09-26T13:44:18.405459Z
# User@Host: root[root] @  [ip]  Id:  1893
# Query_time: 10.162999  Lock_time: 0.000113 Rows_sent: 100000  Rows_examined: 2100000
SET timestamp=1664199858;
SELECT name from user_info_large ORDER BY name desc limit 0,100000;

关于其中一些信息的说明：

• Time：SQL 执行的开始时间
• Query_time：SQL 语句查询花费的时间，可以看到花费了 10 秒钟
• Lock_time：等待锁表的时间
• Rows_sent：语句返回的记录数
• Rows_examined：从存储引擎中返回的记录数

正在执行的慢查询是不会被记录到慢查询日志的，只有等待其执行完毕才会记录到日志中。

我们可以使用 show processlist 查看正在执行 SQL 的线程。

再执行以下语句，使用索引 account 字段：

SELECT account from user_info_large ORDER BY account desc limit 0,100000;

查看慢查询日志，并没有被记录下来。

现在分别使用 explain 查看 SQL 语句的执行情况：

explain SELECT name from user_info_large ORDER BY name desc limit 0,100000;

分析情况如下：

可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra 也是外部排序。

再看看这条 SQL 语句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情况如下：

type 为 index，使用了索引，使用的索引字段为 account，Extra 显示为使用索引排序。

因此，在实际开发中，我们可以针对慢查询的 SQL，使用 explain 分析语句，根据分析情况以及索引的设计，重新设计 SQL 语句，让 SQL 语句尽量走索引，走合适的索引。

5 优化器与索引

在执行 SQL 时，MySQL 的优化器会根据情况选择索引，但并不能保证其执行时间一定最短，我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。

例如，以下语句执行后，key 字段为 account，并没有走主键索引。

explain SELECT count(id) from user_info_large;

如果使用 force key，就可以强制令语句走主键索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);

6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

1. 通过慢查询日志定位 SQL
2. 使用 explain 分析 SQL
3. 修改 SQL，令其走合适的索引

🍅 在使用 explain 时，我们主要关注这些字段：

• type
• key
• Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

• 模糊查询不要使用通配符 % 开头，例如 like '%abc'
• 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or
• 使用复合索引遵循最左原则
• 索引字段不要参加表达式运算、函数运算