MySQL慢查询定位方法
MySQL慢查询日志（Slow Query Log）记录执行时间超过long_query_time阈值的所有SQL语句。生产环境中，少数慢查询即可拖垮整个数据库实例，导致连接池耗尽和应用超时。通过慢查询日志分析、EXPLAIN执行计划解读和索引优化，可将90%以上的慢查询响应时间降低一个数量级。
慢查询日志配置与采集
开启慢查询日志需要配置以下参数。建议long_query_time设为0.1秒（100ms），捕获足够多的慢查询样本用于分析：
-- 查看当前慢查询配置
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 = 0.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 = 0.1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100log_queries_not_using_indexes开启后，未使用索引的查询即使执行时间未超阈值也会记录。min_examined_row_limit=100排除扫描行数少于100的查询，减少日志噪声。生产环境高负载时，慢查询日志写入会影响性能，建议通过Filebeat采集后关闭文件直写。
使用pt-query-digest分析慢查询日志，按总耗时排序找出影响最大的SQL：
# 分析慢查询日志，输出TOP SQL
pt-query-digest /var/log/mysql/slow.log --report --limit 10
# 按查询次数排序
pt-query-digest /var/log/mysql/slow.log --order-by Query_time:sum --limit 10
# 分析特定时间段
pt-query-digest /var/log/mysql/slow.log --since "2026-08-06 00:00:00" --until "2026-08-07 00:00:00"EXPLAIN执行计划关键字段解读
EXPLAIN输出包含12个字段，以下5个是判断查询效率的核心：
| 字段 | 含义 | 关注点 |
|---|---|---|
| type | 访问类型 | ALL（全表扫描）需优化，ref/range/eq_ref为佳 |
| key | 实际使用的索引 | NULL表示未走索引 |
| rows | 预估扫描行数 | 越小越好，与实际行数对比判断选择性 |
| Extra | 附加信息 | Using filesort、Using temporary需重点优化 |
| key_len | 索引使用长度 | 判断联合索引用了几列 |
-- 分析慢查询执行计划
EXPLAIN SELECT order_id, user_id, amount, status
FROM orders
WHERE user_id = 10086
AND status = 1
AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;
-- 查看实际执行成本（MySQL 8.0+）
EXPLAIN ANALYZE SELECT order_id, user_id, amount, status
FROM orders
WHERE user_id = 10086 AND status = 1 AND created_at >= '2026-07-01'
ORDER BY created_at DESC LIMIT 20;EXPLAIN ANALYZE输出实际执行时间和行数，比普通EXPLAIN的预估值更准确。type=ref且rows接近LIMIT值时说明索引设计合理。若出现Using filesort，说明排序操作未使用索引，需要调整索引顺序。
联合索引与最左前缀原则
联合索引遵循最左前缀原则，查询条件必须从索引最左列开始连续匹配。以上面的查询为例，分析三种索引设计的效率差异：
-- 索引方案A: (user_id, status, created_at) - 最优
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);
-- 索引方案B: (user_id, created_at) - 次优，status需要回表过滤
ALTER TABLE orders ADD INDEX idx_user_time (user_id, created_at);
-- 索引方案C: (created_at, user_id, status) - 最差，created_at选择性低
ALTER TABLE orders ADD INDEX idx_time_user (created_at, user_id, status);方案A中，user_id作为等值查询条件定位到索引范围，status进一步缩小范围，created_at用于排序，三者完全覆盖查询条件，无需回表和额外排序。方案C把created_at放在最前面，created_at范围查询无法精确定位，索引利用效率低。
判断索引列顺序的经验规则：等值查询列在前，范围查询列在后，排序列与范围列一致。选择性高的列优先，即 cardinality/总行数 比值大的列放前面。
覆盖索引消除回表操作
InnoDB的二级索引存储主键值，查询非索引列需要回表到聚簇索引获取完整数据行。覆盖索引指查询所需的所有列都包含在索引中，无需回表。EXPLAIN结果中Extra显示Using index即为覆盖索引：
-- 查询只需要order_id（主键）和user_id
SELECT order_id, user_id FROM orders WHERE user_id = 10086;
-- 索引方案: (user_id)
-- 执行计划: Using index（因为order_id是主键，已包含在二级索引中）
-- 查询需要order_id, user_id, status
SELECT order_id, user_id, status FROM orders WHERE user_id = 10086;
-- 索引方案: (user_id, status) - 覆盖索引
-- Extra: Using index
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);覆盖索引trade-off：索引列增多会增大索引体积和写入开销。写多读少的表不适合添加大量覆盖索引，读多写少的表可适当放宽。通过以下查询统计读写比例，辅助索引决策：
-- 统计表的读写比例（InnoDB缓冲池统计）
SELECT
OBJECT_SCHEMA AS db,
OBJECT_NAME AS table_name,
COUNT_READ, COUNT_WRITE,
ROUND(COUNT_READ / (COUNT_READ + COUNT_WRITE) * 100, 2) AS read_pct
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'your_db'
ORDER BY COUNT_READ + COUNT_WRITE DESC;分页查询优化方案
LIMIT offset分页在offset较大时性能急剧下降，MySQL需要扫描offset+N行然后丢弃前offset行。LIMIT 1000000, 20的查询即使有索引也需扫描百万行数据。
延迟关联（Deferred Join）通过子查询先获取主键，再关联查询减少回表次数：
-- 差: 深度分页，扫描1000020行
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC
LIMIT 1000000, 20;
-- 好: 延迟关联，子查询走覆盖索引
SELECT t.* FROM orders t
INNER JOIN (
SELECT order_id FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC
LIMIT 1000000, 20
) tmp ON t.order_id = tmp.order_id;游标分页（Cursor Pagination）利用上一页最后一条记录的值定位，避免offset扫描：
-- 第一页
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
-- 第二页（基于第一页最后一条记录的created_at和order_id）
SELECT * FROM orders
WHERE user_id = 10086
AND (created_at < '2026-07-15 10:30:00'
OR (created_at = '2026-07-15 10:30:00' AND order_id < 12345))
ORDER BY created_at DESC, order_id DESC
LIMIT 20;游标分页需要 (user_id, created_at, order_id) 联合索引，order_id作为唯一并列条件避免同一created_at多条记录时漏翻。这套方案在不支持跳页的场景下（如无限滚动）性能最优，固定时间复杂度。
Online DDL与索引变更
生产环境添加索引需要考虑锁表影响。MySQL 8.0的Online DDL支持 inplace + concurrent 模式，添加二级索引时不阻塞DML操作：
-- 在线添加索引（MySQL 8.0+，ALGORITHM=INPLACE不锁表）
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- 查看DDL进度
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb%';大表（亿级数据）添加索引仍可能耗时数小时，推荐使用pt-online-schema-change或gh-ost工具在影子表上操作，减少主库负载和数据一致性风险。操作前评估索引大小增加比例，预留磁盘空间：
-- 预估索引大小
SELECT
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS current_size_mb,
ROUND(SUM(data_length) * 0.3 / 1024 / 1024, 2) AS estimated_new_index_mb
FROM information_schema.tables
WHERE table_name = 'orders';原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-you-hua-shi-zhan-zhi-xing-ji-hua-fen-xi/
