Ubuntu 上 MySQL 查询变慢的排查与优化清单
一 快速定位慢 SQL
- 开启并调整慢查询日志，先抓到“对哪些 SQL 慢”的事实：
- 查看状态：SHOW VARIABLES LIKE ‘slow_query_log%’; SHOW VARIABLES LIKE ‘long_query_time’;
- 动态开启与设阈值：SET GLOBAL slow_query_log=‘ON’; SET GLOBAL long_query_time=1;（生产常用 1s，高要求可 0.1s）
- 定位日志文件：SHOW VARIABLES LIKE ‘slow_query_log_file’;（常见路径如 /var/lib/mysql/hostname-slow.log）
- 可额外记录未走索引的语句：SET GLOBAL log_queries_not_using_indexes=ON;
- 实时看正在执行的慢操作：SHOW FULL PROCESSLIST;（发现长时间 Running 的语句优先处理）
- 用工具对慢日志做聚合分析：
- pt-query-digest（Percona Toolkit）：pt-query-digest /var/lib/mysql/hostname-slow.log > slow_report.txt
- 或 mysqldumpslow：mysqldumpslow -s t /var/lib/mysql/hostname-slow.log
- 对抓到的 SQL 用执行计划定位瓶颈：EXPLAIN FORMAT=JSON SELECT …；重点看 type（尽量避免 ALL 全表扫描）、key、rows、Extra 中的 Using filesort/Using temporary。
二 SQL 与索引优化要点
- 只查需要的列：避免 **SELECT ***；减少网络与内存开销。
- 为高频在 WHERE、JOIN、ORDER BY、GROUP BY 中出现的列建立索引；优先使用复合索引并遵循最左前缀；尽量让查询能被覆盖索引满足。
- 避免索引失效：不要在索引列上做函数或计算（如 YEAR(col)=2023）；避免隐式类型转换；尽量不要前导通配 LIKE ‘%abc’。
- 优化写法：能用 JOIN 替代低效子查询；分页深翻时用游标/键集分页替代 OFFSET 大偏移；必要时拆分复杂查询。
- 减少返回数据量：加 LIMIT；避免一次性拉取大 BLOB/TEXT。
- 检查并维护索引：SHOW INDEX FROM tbl; 创建缺失索引；删除长期未使用或重复的索引。
三 InnoDB 配置与资源优化
- 合理设置 InnoDB 缓冲池（建议为物理内存的70%–80%）：SET GLOBAL innodb_buffer_pool_size=2G;（示例值，按机器内存调整）
- 连接与并发：结合压测与业务峰值设置 max_connections，避免过高导致上下文切换与内存压力；必要时使用连接池。
- 查询缓存：在 MySQL 8.0 中查询缓存已移除；5.7 及更早版本如无强一致诉求可考虑关闭或谨慎使用。
- 存储与硬件：优先使用 SSD；保证足够内存以减少磁盘 I/O；CPU 核数不足时考虑扩容或读写分离分担读压力。
- 监控与压测：用 PMM、MySQL Workbench、Prometheus+Grafana 持续观测；变更前在测试环境验证并回放压测。
四 表结构与维护策略
- 规范化减少冗余，必要时做垂直拆分（大字段分离）与水平分区/分片（如按时间分区）以降低单表/单索引压力。
- 定期维护统计信息与碎片：ANALYZE TABLE tbl; 对高删除/更新表按需执行 OPTIMIZE TABLE（InnoDB 多为在线 DDL，但仍需评估窗口期与锁影响）。
- 事务与锁：避免长事务与过大的事务范围；减少锁等待与死锁，必要时缩小 SELECT … FOR UPDATE 的扫描范围。
- 缓存与架构：引入 Redis/Memcached 缓存热点数据；读多写少场景可用主从复制+读写分离；超大数据量考虑分库分表。
五 一条可执行的排查路径
- 第 1 步：开启慢查询并设置阈值（如 1s），确认日志落盘位置；同时打开 log_queries_not_using_indexes 便于发现无索引 SQL。
- 第 2 步：用 pt-query-digest 对慢日志做 Top SQL 排名，锁定“最耗时/最频繁”的 SQL 模板。
- 第 3 步：对 Top SQL 逐一执行 EXPLAIN，检查 type、key、rows、Extra；优先消除 ALL 扫描、Using filesort、Using temporary。
- 第 4 步：按“最左前缀”与覆盖索引原则补齐或改造索引；改写 SQL（避免索引列函数、前导通配、子查询改 JOIN 等）。
- 第 5 步：在测试环境压测验证（如平均响应从 200ms 降至 50ms 等可量化指标），再灰度上线。
- 第 6 步：若仍未达标，结合连接数、缓冲池命中率、磁盘 IOPS 等做资源与配置调优，并考虑缓存/读写分离/分区等架构手段。
