MySQL慢查询日志分析与复合索引优化实战指南

小编 • • 数据库

MySQL慢查询日志记录执行时间超过阈值的SQL语句，是定位数据库性能瓶颈的首要工具。大多数生产环境的数据库性能问题源于缺索引、索引失效和低效SQL写法。本文从慢查询日志配置、分析工具使用，到复合索引设计原则和执行计划解读，给出完整的排查优化流程。

慢查询日志配置与采集

MySQL通过参数控制慢查询日志的开启和阈值。以下配置适用于MySQL 8.0：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;

-- 持久化配置到my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1

long_query_time设为1秒是线上环境的常用值。开发测试环境可设为0.1秒捕获更多潜在问题SQL。log_queries_not_using_indexes开启后，即使SQL执行时间未超过阈值，只要全表扫描也会记录，能提前发现将要变慢的查询。

mysqldumpslow工具分析慢查询

慢查询日志文件可能很大，手动阅读效率低。MySQL自带mysqldumpslow工具可聚合分析相似SQL：

# 按总耗时排序，显示Top 10慢SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按返回行数排序（找出扫描大量数据的SQL）
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 按出现次数排序（找出高频慢SQL）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按平均耗时排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 输出结果示例
Count: 156  Time=3.21s (501s)  Lock=0.01s (2s)  Rows=15000 (2340000)
  SELECT * FROM orders WHERE user_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N

各字段含义：Count表示该SQL模式出现的次数，Time表示平均/总执行时间，Lock表示平均/总锁等待时间，Rows表示平均/总返回行数。-s参数指定排序方式：t=总时间、at=平均时间、c=次数、r=返回行数、ar=平均返回行数。

pt-query-digest是Percona Toolkit中的高级分析工具，提供比mysqldumpslow更详细的分析报告：

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 只分析指定时间段的慢查询
pt-query-digest --since '2026-08-05 09:00:00' --until '2026-08-05 12:00:00' \
  /var/log/mysql/slow.log

# 分析结果中的关键信息
# Rank: 慢查询排名
# Query ID: SQL指纹唯一标识
# Response: 总响应时间及占比
# R/Call: 平均每次调用的响应时间
# V/M: 方差均值比，值越大说明执行时间波动越大，需重点排查
# EXPLAIN: 自动为SQL生成执行计划

EXPLAIN执行计划解读与索引诊断

找到慢SQL后，用EXPLAIN分析执行计划，判断索引使用情况：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'paid' 
ORDER BY created_at DESC LIMIT 20;

-- 结果
+----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL  | 892341 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+-------+--------+-------------+

关键字段解读：

-- type=ALL: 全表扫描，未使用任何索引，扫描了89万行
-- possible_keys=NULL: 没有可用索引
-- key=NULL: 实际未使用索引
-- Extra=Using filesort: 需要额外排序操作（ORDER BY未走索引）
-- rows=892341: 预估扫描行数

-- 理想状态应为：
-- type=ref 或 range（使用了索引查找或范围扫描）
-- key=实际使用的索引名
-- rows=远小于全表行数
-- Extra不含 Using filesort 或 Using temporary

复合索引设计与最左前缀原则

上面的慢查询涉及三个条件：user_id等值查询、status等值查询、created_at排序。单列索引无法覆盖，需要设计复合索引：

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 验证索引使用
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'paid' 
ORDER BY created_at DESC LIMIT 20;

-- 结果
+----+-------------+--------+-------+----------------------------+----------------------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys              | key                        | key_len | ref     | rows | Extra       |
+----+-------------+--------+-------+----------------------------+----------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ref   | idx_user_status_created    | idx_user_status_created    | 12      | const,const | 20   | Using index condition |
+----+-------------+--------+-------+----------------------------+----------------------------+---------+---------+------+------+-------------+

复合索引(user_id, status, created_at)遵循最左前缀原则。索引列的顺序决定了哪些查询能命中索引：

-- 能命中索引的查询模式：
-- 1. user_id + status + created_at（三列全用）
SELECT * FROM orders WHERE user_id = 10086 AND status = 'paid' ORDER BY created_at DESC;

-- 2. user_id + status（前两列）
SELECT * FROM orders WHERE user_id = 10086 AND status = 'paid';

-- 3. user_id（第一列）
SELECT * FROM orders WHERE user_id = 10086;

-- 不能完整命中索引的查询模式：
-- 4. status + created_at（跳过user_id，无法走索引）
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;
-- 此查询只能全表扫描，因为跳过了索引最左列user_id

-- 5. user_id + created_at（跳过中间列status）
SELECT * FROM orders WHERE user_id = 10086 AND created_at > '2026-08-01';
-- user_id走索引，但created_at无法使用索引范围扫描
-- MySQL 8.0+的Index Condition Pushdown能部分优化，但效率不如完整匹配

索引列顺序设计原则：等值查询条件列在前，范围查询条件列在后，排序列在最后。原因是范围查询会中断后续列的索引使用。上述案例中user_id和status都是等值查询，放在前面；created_at用于排序，放在最后，使ORDER BY操作直接利用索引有序性，消除filesort。

索引失效的常见场景规避

即使建立了正确的索引，某些SQL写法仍会导致索引失效。排查以下常见问题：

-- 1. 函数操作导致索引失效
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-05';
-- EXPLAIN结果: type=ALL, key=NULL（全表扫描）

-- 正确：改用范围查询
SELECT * FROM orders WHERE created_at >= '2026-08-05' AND created_at < '2026-08-06';
-- EXPLAIN结果: type=range, key=idx_user_status_created

-- 2. 隐式类型转换导致索引失效
-- 错误：user_id是BIGINT，传入字符串
SELECT * FROM orders WHERE user_id = '10086';
-- MySQL会将user_id转为字符串比较，索引失效

-- 正确：传入正确类型
SELECT * FROM orders WHERE user_id = 10086;

-- 3. LIKE通配符前置导致索引失效
-- 错误：前置通配符
SELECT * FROM products WHERE name LIKE '%手机%';
-- 无法利用B-Tree索引的有序性

-- 正确：后置通配符可走索引范围扫描
SELECT * FROM products WHERE name LIKE '手机%';
-- 或使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

-- 4. OR条件部分无索引导致全表扫描
-- 错误：order_no有索引但user_name无索引
SELECT * FROM orders WHERE order_no = 'ORD20260805001' OR user_name = '张三';
-- 整个查询退化为全表扫描

-- 正确：为user_name添加索引，或拆分为UNION
SELECT * FROM orders WHERE order_no = 'ORD20260805001'
UNION
SELECT * FROM orders WHERE user_name = '张三';

-- 5. !=和NOT IN导致索引失效
SELECT * FROM orders WHERE status != 'cancelled';
-- 通常无法走索引，考虑改为IN列举需要的值
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

索引选择性评估与冗余索引清理

不是所有查询都需要建索引。索引选择性（Cardinality / Total Rows）衡量索引列值的离散程度，选择性越高索引效果越好：

-- 评估列的选择性
SELECT 
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
  COUNT(DISTINCT created_at) / COUNT(*) AS created_at_selectivity
FROM orders;

-- 结果示例
-- status_selectivity: 0.000003 (5种状态/150万行) → 极低，单独建索引无意义
-- user_id_selectivity: 0.65 (97万用户/150万行) → 高，适合作为索引前导列
-- created_at_selectivity: 0.98 (147万不同时间/150万行) → 很高，适合范围查询

-- 查找冗余索引
SELECT 
  s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME,
  s.COLUMN_NAME, s.SEQ_IN_INDEX,
  s2.INDEX_NAME AS redundant_index
FROM information_schema.STATISTICS s
JOIN information_schema.STATISTICS s2 
  ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA 
  AND s.TABLE_NAME = s2.TABLE_NAME
  AND s.SEQ_IN_INDEX = s2.SEQ_IN_INDEX
  AND s.COLUMN_NAME = s2.COLUMN_NAME
  AND s.INDEX_NAME != s2.INDEX_NAME
WHERE s.TABLE_SCHEMA = 'your_database'
GROUP BY s.TABLE_NAME, s.INDEX_NAME, s2.INDEX_NAME
HAVING COUNT(*) = (
  SELECT MAX(SEQ_IN_INDEX) 
  FROM information_schema.STATISTICS s3 
  WHERE s3.TABLE_SCHEMA = s.TABLE_SCHEMA 
  AND s3.TABLE_NAME = s.TABLE_NAME 
  AND s3.INDEX_NAME = s.INDEX_NAME
);

选择性的经验阈值：单列选择性低于0.1不建议单独建索引。但作为复合索引的组成部分，低选择性列仍可放在等值查询条件中（如status列在复合索引中间位置），因为前导列user_id已大幅缩小了扫描范围。

冗余索引浪费写入性能和存储空间。如果已有索引(user_id, status, created_at)，再建单列索引(user_id)就是冗余的——复合索引的最左前缀已覆盖单列索引的查询场景。定期执行冗余索引检测脚本，删除无用索引。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-ri-zhi-fen-xi-yu-fu-he-suo-yin-you-hua/

赞 (0)
小编小编
0
上一篇 20小时前
下一篇 20小时前

相关推荐