线上出现数据库慢查询该如何排查？

## 作用 线上数据库慢查询排查的核心是：先定位真正耗时的 SQL，再分析执行计划，最后针对 SQL 写法或索引问题修复，并用执行耗时、扫描行数等指标验证效果，避免凭感觉乱加索引。 ## 原理与排查步骤 ### 1. 开启并确认慢查询日志 - 通过以下变量检查慢日志配置： - `slow_query_log`：是否开启慢查询日志。 - `long_query_time`：超过多少秒记录为慢查询。 - `log_queries_not_using_indexes`：是否记录未使用索引的 SQL。 - 可临时动态开启： - `SET GLOBAL slow_query_log = 'ON';` - `SET GLOBAL long_query_time = 1;` - `SET GLOBAL log_queries_not_using_indexes = 'ON';` - 注意事项： - 动态配置重启后失效，持久化需要写入 `my.cnf`。 - `long_query_time` 对新建连接生效，已有连接需要重连。 - `log_queries_not_using_indexes` 在小表上可能产生大量噪音，排查结束后建议关闭。 ### 2. 从慢日志中找出重点 SQL 不要直接逐行看慢日志，应先做汇总排序： - 使用 `mysqldumpslow -s t -t 10 slow.log` 按总耗时排序，查看前 10 条。 - 也可以使用 `pt-query-digest` 获取更完整的统计信息。 重点关注： - 总耗时占比。 - 执行次数。 - `Rows_examined / Rows_sent`，即扫描行数与返回行数的比值。 - 如果扫描行数远大于返回行数，通常说明 SQL 扫描了大量无用数据。 ### 3. 使用 EXPLAIN 分析执行计划 对可疑 SQL 执行： ```sql EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID' ORDER BY created_at DESC LIMIT 20; ``` 重点查看以下字段： - `type`：访问类型，常见性能从好到差大致为 `const`、`eq_ref`、`ref`、`range`、`index`、`ALL`。 - 出现 `ALL` 表示全表扫描。 - `key`：实际使用的索引。 - 为 `NULL` 表示没有走索引。 - `rows`：预估扫描行数。 - `Extra`： - `Using filesort`：可能存在额外排序，需要关注。 - `Using temporary`：使用临时表，可能影响性能。 - `Using index`：使用覆盖索引，是较好的情况。 MySQL 8.0.18 及以上版本可使用 `EXPLAIN ANALYZE` 查看真实执行耗时，但它会实际执行 SQL，生产环境不要对写入类语句使用。 ### 4. 排查常见索引失效场景 - 对索引列使用函数或计算： - 如 `DATE(created_at) = '2026-10-01'` 可能无法使用索引。 - 可改写为范围查询： ```sql WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02' ``` - 隐式类型转换： - 如果 `phone` 是 `varchar`，条件写成 `phone = 13800000000` 会触发类型转换，导致索引失效。 - 应写成字符串：`phone = '13800000000'`。 - 联合索引不满足最左前缀： - 如联合索引是 `(user_id, status, created_at)`，只按 `status` 查询无法命中该索引。 - `LIKE` 以通配符开头： - `LIKE '%abc'` 无法使用普通 B+ 树索引。 - 深分页： - `LIMIT 1000000, 20` 会先扫描并丢弃大量记录。 - 可改为基于游标或主键的翻页： ```sql SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20; ``` ### 5. 建索引并验证效果 根据过滤、排序字段设计联合索引，例如： ```sql ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at); ``` 建立后再次执行 `EXPLAIN`，确认： - `key` 命中目标索引。 - `rows` 扫描行数下降。 - `Using filesort` 等额外操作消失。 - SQL 实际耗时明显降低。 大表加索引时应注意： - 尽量在业务低峰执行。 - 先在测试环境用相近数据量验证收益。 - Online DDL 虽然通常不阻塞读写，但仍会占用 IO，并可能带来复制延迟。 - 不要为每条 SQL 单独建索引，索引会增加写入开销和存储成本。 ### 6. 标准排查清单 1. 开启慢查询日志，设置合理的 `long_query_time`。 2. 使用 `mysqldumpslow` 或 `pt-query-digest` 找出总耗时最高的 SQL。 3. 使用 `EXPLAIN` 检查 `type`、`key`、`rows`、`Extra`。 4. 对照函数计算、隐式转换、最左前缀、前导通配符、深分页等场景分析问题。 5. 通过改写 SQL 或增加合适索引修复。 6. 修改后重新对比执行耗时和扫描行数，确认优化收益。