添加图片注释，不超过 140 字（可选）
在 MySQL 的生产环境中，性能问题是一个常见的挑战。当数据库查询响应时间变慢，或者系统资源消耗异常时，我们需要快速定位问题并进行优化。MySQL 提供了一个强大的工具——EXPLAIN，它可以帮助我们分析查询语句的执行计划，从而找到性能瓶颈。
本文模拟之前生产环境mysql性能问题的优化过程，对生产环境的复盘。系统上线后正常使用，大概1年后就出现业务慢，运维反馈某些业务操作会让服务器cpu飙升到100%以上。
添加图片注释，不超过 140 字（可选）
准备
理解 EXPLAIN 输出
EXPLAIN 命令可以附加在任何 SQL 查询语句之前，用于显示 MySQL 如何执行该查询。输出结果包含多个字段，每个字段都提供了关于查询执行的重要信息。
以下是 EXPLAIN 输出中的一些关键字段：
- id：查询标识符，表示查询中操作的顺序。
- select_type：查询的类型，如 SIMPLE、SUBQUERY、DERIVED 等。
- table：查询涉及的表。
- type：访问类型，如 ALL、index、range、ref 等，反映了 MySQL 查找行的方式。
- possible_keys：可能用到的索引。
- key：实际使用的索引。
- key_len：使用的索引长度。
- ref：显示索引的哪一列被使用了。
- rows：MySQL 认为必须检查的用来返回请求数据的行数。
- Extra：包含不适合在其他列中显示但十分重要的额外信息。
- filtered：表示通过查询条件过滤后，返回的行数与rows列估计的行数的比例。这个值越高，表示查询的过滤效果越好。
MySQL开启慢查询
在使用EXPLAIN之前，确实需要确保MySQL的慢查询日志功能已经开启，因为慢查询日志记录了执行时间超过阈值的SQL语句，这些语句往往是性能调优的重点。
以下是配置MySQL慢查询日志的步骤：
1. 配置慢查询日志参数
在配置文件中my.cnf或my.ini，找到或添加以下参数来配置慢查询日志：
vim /etc/mysql/my.cnf
[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2 log_queries_not_using_indexes = 1
- slow_query_log = 1：启用慢查询日志。
- slow_query_log_file = /var/log/mysql/mysql-slow.log：指定慢查询日志文件的路径。请确保MySQL服务有权限写入这个文件。
- long_query_time = 2：设置慢查询的阈值，单位是秒。这里设置为2秒，执行时间超过2秒的查询会被记录到慢查询日志中。
- log_queries_not_using_indexes = 1：如果设置为1，MySQL会记录那些没有使用索引的查询，即使它们的执行时间没有超过long_query_time的阈值。
2. 重启MySQL服务
保存配置文件后，你需要重启MySQL服务以使更改生效。在Linux系统中，可以使用以下命令重启MySQL服务：
service mysql restart
3. 验证慢查询日志配置
重启服务后，你可以通过以下SQL语句来验证慢查询日志是否已经正确配置：
mysql -uroot -p SHOW VARIABLES LIKE 'slow_query_log%';
添加图片注释，不超过 140 字（可选）
确保slow_query_log的值为ON，slow_query_log_file指向正确的日志文件路径。
DBA不让开慢查询
如果说公司DBA不让开启慢查询。
理由是：
慢查询日志会记录执行时间超过指定阈值的SQL语句，这会导致日志文件的增长。
每次记录慢查询日志时，都会涉及到磁盘I/O操作，这可能会对数据库的写入性能产生影响。尤其是在高负载环境下，频繁的磁盘写入可能会略微降低数据库的整体性能。
记录慢查询的过程可能会增加查询执行的额外开销。这种开销通常很小，但在极端情况下，对于那些非常接近慢查询阈值的查询，可能会因为记录日志而导致它们被错误地标记为慢查询。
这时可以通过重现业务操作，触发慢查询，然后执行
select * from information_schema.`PROCESSLIST` where info is not null;
查询当前有哪些脚本正在执行及占用时间
添加图片注释，不超过 140 字（可选）
准备阶段最核心的作用就是找出性能比较差的脚本是哪些。找到了就可以进行下一步的explain。
性能调优
使用 EXPLAIN 的步骤
步骤 1：识别慢查询
首先，我们需要识别出哪些查询是慢查询。可以通过 MySQL 的慢查询日志或者监控工具来获取这些信息。
调取慢查询日志文件
tail -f /var/log/mysql/mysql-slow.log
添加图片注释，不超过 140 字（可选）
步骤 2：使用 EXPLAIN 分析查询
把慢查询SQL脚本，使用 EXPLAIN 来分析这些查询
添加图片注释，不超过 140 字（可选）
步骤 3：解读 EXPLAIN 输出
调整前：
添加图片注释，不超过 140 字（可选）
explain结果
添加图片注释，不超过 140 字（可选）
分析 EXPLAIN 的输出，特别关注以下几个方面：
- type：如果 type 是 ALL，表示进行了全表扫描，这通常是性能问题的标志。如果 type 是 ALL，考虑创建索引。
添加图片注释，不超过 140 字（可选）
- ALL：全表扫描（Full Table Scan）。
- index：索引扫描（Index Scan）。与全表扫描类似，但数据库扫描的是整个索引，而不是表。
- range：范围扫描（Range Scan）。数据库使用索引来检索特定范围内的行。
- ref：索引查找（Index Lookup）。数据库使用非唯一索引来查找与单个列上的等值条件匹配的行。
- eq_ref：唯一索引查找（Unique Index Lookup）。
- NULL：表示查询不需要访问表或索引，数据库可以直接从索引中获取结果，而不需要访问表。
- key：实际使用的索引名称，如果 key 是 NULL，表示没有使用索引，需要创建或优化索引。
- rows：如果 rows 的值很大，表示 MySQL 需要检查很多行，这可能是性能瓶颈。尝试优化查询条件，减少需要检查的行数。
- Extra：如果出现了 Using filesort 或 Using temporary，这些都是性能问题的信号。
步骤 4：验证优化效果
在实施优化措施后，重新使用 EXPLAIN 分析查询，并监控查询的实际执行时间，以验证优化效果。
添加图片注释，不超过 140 字（可选）
此次生产上的问题主要是没有增加索引，这里还没对SQL本身进行大量优化。比如
- like使用左右%时，索引会失效
- 尽量不要使用三表或三表以上的联合查询
- 尽量减少In的使用
- .....
添加图片注释，不超过 140 字（可选）
此次复盘的主要目的是了解explain的基本概念及使用步骤。
另外还有非常重要的一点就是项目成员重视索引的重要性，建表时索引就一条语句而已。项目初期数据量小或许体现不出来，但当数据量大了的情况下索引的能力就尤为突出。
