关于 MySQL 慢查询优化的一些思路与知识点
1 慢查询优化思路
当发生慢查询的时候，优化的思路为：
- 利用慢查询日志定位慢查询 SQL
- 通过 explain 分析慢查询 SQL
- 修改 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)
复制代码这里主要关注三个变量：
- long_query_time，慢查询的时间阈值，单位秒，如果一个 SQL 语句的执行时间超过这个值，那么 MySQL 就认定其为慢查询
- slow_query_log，慢查询日志功能是否开启，默认关闭，开启后记录慢查询
- 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 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：
- 通过慢查询日志定位 SQL
- 使用 explain 分析 SQL
- 修改 SQL，令其走合适的索引
在使用 explain 时，我们主要关注这些字段：
- type
- key
- Extra
在编写 SQL 使用索引的时候，我们尽量注意一下规则：
- 模糊查询不要使用通配符 % 开头，例如 like '%abc'
- 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or
- 使用复合索引遵循最左原则
- 索引字段不要参加表达式运算、函数运算
作者：码指星斗
链接：
https://juejin.cn/post/7147941447029751822
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。