2022 年记一次慢查询优化指南，MySQL 优化学习第 9 天

实战场景

有时候编写完的代码，上传到服务器，立马 CPU 与 负载跑满，查询原因之后，发现是 MySQL 慢查询导致，顾需要对慢查询进行调优。

准备工作

优化慢查询，那首先要做的就是配置慢查询日志。

MySQL 慢查询，全名是慢查询日志，用来记录 MySQL 的响应时间超过阈值的语句。具体环境中，就是超过 long_query_time 设置值的语句。

查询 long_query_time 是否开启，该值默认一般设置为 10.

show variables like 'long_query_time';

如果不是为了调优操作，该日志可以关闭，毕竟开启查询日志记录还是会带来性能影响。

🌸慢查询相关参数

• slow_query_log：1 是开启，0 是关闭；

• slow-query-log-file：MySQL 数据库慢查询日志存储路径；

• long_query_time：慢查询阈值，即超过多少分钟后记录日志；

• log_queries_not_using_indexes：是否记录未使用索引的查询；

• log_output：日志存储方式，默认值是 FILE，表示文件，也可以修改为 TABLE 表示存储到数据库。

查询上述参数值的语句如下所示：

show variables like 'slow_query_log';show variables like 'long_query_time';show variables like 'log_queries_not_using_indexes';show variables like 'log_output';show variables like 'slow-query-log-file';

除了直接在数据库中进行修改以外，也可以直接在 my.ini 文件中进行修改。

打开系统生成的慢日志文件，具体如下所示：

# Time: 2022-03-23T02:37:43.435138Z# User@Host: root[root] @ localhost [127.0.0.1]  Id: 6917272# Query_time: 60.214801  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 600413SET timestamp=1648003063;SELECT COUNT(*) AS ........;

其中具体的参数说明如下所示：

• Time：日志记录时间；

• User @Host：MySQL 登录的用户和登录的主机地址；

• Query_time：第一个参数值是查询时间，第二个是锁表时间，第三个是返回行数，第四个是扫描行数；

• SET timestamp：MySQL 查询时间戳；

• 最后是 SQL 语句。

⛳️ 慢日志优化

🌞第一步，优化索引接下来就可以使用 explain 关键字对查询进行优化了。首先将 where 条件中的部分参数增加索引，下面是优化前和优化后的对比图，可以明显看到扫描行数 rows 降低。

此时首先要注意的就是索引是否起作用，如果没有设置索引或者索引失效，及时清理和解决。

适当的时候，可以建立复合索引。

🌞第二步，优化数据库表结构

拆解大表，如果表中的字段特别多，并且某些字段使用频率低，可以将其拆解出去形成新表增加中间表，如果几个表经常被联合查询，可以建立中间表，将对应的数据进行插入，后续将联合查询修改为对中间表的查询。

🌞第三步，查看需要优化 LIMIT 分页这里唯一需要注意的就是 LIMIT M, N 中的偏移量问题，即 M 值过大的问题。

🌞第四步，设置小表驱动大表

🌞第五步，order by 关键字优化

排序尽量使用索引排序，避免出现 filesort 方式实现此逻辑的前提

• order by 语句使用索引左置；

• where 语句也使用索引左置。

🌞第六步，group by 关键字优化 group by 实质是先排序后分组，所以其也遵循索引左置原则。一般优化是增大 max_length_for_sort_data +增大 sort_buffer_size 。还有一点注意的是能 where 解决的筛选，就不要用 having。

🌞总结一下一般优化流程

1. 开启慢日志，至少运行一天，要生产慢日志，时间阈值设置好；

2. explain + 慢日志分析；

3. show profile 分析，查询 SQL 执行细节；

4. MySQL 服务器调优。

本篇博客主要优化 MySQL 中的插入操作，核心实现 insert 优化任务。

插入数据

load data infile 导入数据

使用上述命令，可以大幅度提高批量插入数据。如果希望使用该命令，可以用 show 命令进行测试

show variables like '%infile%'

使用批量插入如果希望一次性插入多条数据，可以使用一条 insert 语句，然后拼接多行数据。

insert into 表名 values(...),(...),(...)

禁用唯一检查在插入数据时，可以临时取消唯一性校验，具体办法是在插入前执行下述命令

set unique_checks = 0

插入完毕之后在打开

set unique_checks = 1

禁用外键检查与上述逻辑一致，相关命令如下所示。

set foreign_key_checks = 0set foreign_key_checks = 1

禁止自动提交禁止事务自动提交

set autocommit = 0set autocommit = 1

禁用索引临时性关闭索引

alter table 表名 disable keys

执行完毕打开索引

alter table 表名 enable keys

从配置的角度进行修改

bulk_insert_buffer_size 缓存大小，默认 8M，可以提高。

show variables like 'bulk_insert%'

这个参数只能对 MyISAM 使用， innodb 无效。

max_allowed_packet 接受的数据包大小，默认为 16M，可以提高。

show variables like 'max_all%'

net_buffer_length 通信时缓存数据的大小，最小 4k，最大 16M，默认是 1M。

show variables like 'net_buffer_length'

其它可学习内容

尽量在事务中进行插入操作 MySQL 默认每次进行 insert 操作时，都会创建一个事务，所以我们提前将批量插入操作放置到事务中，可以提高效率。

START TRANSACTION; insert into 表名 values(...),(...),(...);insert into 表名 values(...),(...),(...);insert into 表名 values(...),(...),(...);COMMIT;