MySQL 调优笔记

1.如何定位慢查询?

定位慢查询主要依靠 MySQL 的慢查询日志配合工具如 pt-query-digest ，mysqldumpslow 进行分析，或者通过 performance_schema 进行实时监控，进一步可以使用 EXPLAIN 分析执行计划。

-> 开启慢查询日志

-- 查看慢查询日志是否开启：
SHOW VARIABLES LIKE 'slow_query_log%';
-- 开启慢查询日志（立即生效）
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询时间阈值为 1 秒：
SET GLOBAL long_query_time = 1;
-- 设置慢查询日志文件路径（可选）
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 查看慢查询文件位置：
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 查看慢查询总数：
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-> 收集慢查询日志（慢于 long_query_time 的语句）

-> 使用工具提取 Top N 慢 SQL（如 pt-query-digest）

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
pt-query-digest /var/log/mysql/slow.log

-> 对具体 SQL 使用 EXPLAIN 或 EXPLAIN ANALYZE 分析执行计划

EXPLAIN SELECT * FROM user WHERE name LIKE '%abc%';
EXPLAIN ANALYZE SELECT * FROM user WHERE id = 123;

-> 判断是否索引失效 / 全表扫描 / 关联顺序不佳等

-> 优化 SQL 或加索引，重复测试

2.一个 SQL 语句执行很慢,如何分析 ?

我用 EXPLAIN 分析 SQL 时，重点关注以下几个字段：

type 是访问类型，性能最好的是 const（主键查单条）、eq_ref/ref（使用索引查找），次一点的是 range（范围扫描），最差的是 ALL（全表扫描），index（全索引扫描）；

key 表示实际使用的索引，若为 NULL 则说明没有用到索引，通常性能较差，如果 possible_key 有值，而 key 为 null，证明有索引可能能用但是没命中，就需要考虑是什么原因导致了索引失效；

rows 代表预估扫描的行数，数字越小越好，过大说明扫描量大，效率低；

Extra 字段里如果出现 Using filesort 或 Using temporary，说明使用了额外的排序或临时表，通常是性能瓶颈；如果看到 Using index 则说明是覆盖索引，性能很好，避免了回表；

综上，通过这些字段可以判断 SQL 是否命中索引，扫描量是否合理，以及是否存在额外开销，从而指导优化方案。

3.什么情况下索引会失效 ?

1.违反最左前缀匹配原则：复合索引（多列索引）只有从最左边的列开始按顺序使用，跳过最左列会失效。

2.使用了函数：如 WHERE YEAR(create_time) = 2023，索引失效，因为函数包裹了列，MySQL 无法用索引直接匹配。

3. 使用了通配符 % 开头的 LIKE：LIKE '%abc' 无法使用索引，因为前缀不确定，但 LIKE 'abc%' 是可以走索引的。

4. 隐式类型转换：查询条件的数据类型与索引列类型不匹配，会导致索引失效。

5. OR 条件没有覆盖所有列的索引：如果只用部分索引，另一部分条件需要全表扫描，这样部分索引的使用反而可能加重了整体扫描成本。

6. 使用了 NOT 或 <> 等否定条件：例如 WHERE col <> 1，MySQL一般无法利用索引。

7. 不等式比较符号（<, >, !=）后面的列索引不能用：在复合索引中，使用不等式后，后续的列索引无法使用。复合索引中，遇到范围查询（如 b > 10）后，索引只能定位该范围，后续列（如 c）索引失效，需回表过滤；而对同一列的多范围条件（如 b > 10 AND b < 20）则仍能利用索引缩小扫描范围。