一、结论先给
慢查询优化有固定顺序，跳步就是白干：
1. 找到慢 SQL（慢日志 / performance_schema）
2. EXPLAIN 看它走没走索引、扫了多少行
3. 改写 SQL 或补索引（优先改写，其次补索引）
4. 验证（rows 降下来、耗时降下来）
5. 观察一段时间，确认没有引入新的慢 SQL
绝大多数慢查询不是没索引，而是索引写了但没用上。判断依据就一行：EXPLAIN 里的 key 是不是你以为的那个，rows 是不是接近真实返回行数。
👉 把慢日志片段粘进 日志分析工具，能直接统计出 Top 慢 SQL 和错误高发时段，不用自己 awk 半天。
二、先打开慢日志
-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
-- 在线开启（重启失效，要永久写进 my.cnf）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';
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
log_slow_admin_statements = 1
min_examined_row_limit = 100 # 扫描少于 100 行的不记，降噪
long_query_time 建议从 1 秒开始，跑几天再调到 0.5 秒。一上来设 0.1 秒会被日志淹掉。
2.1 用 mysqldumpslow 汇总
# 最慢的 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 出现次数最多的 10 条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 扫描行数最多的
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log
# 更现代的替代品（Percona Toolkit）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
pt-query-digest 会把 SQL 归一化（WHERE id = 5 和 WHERE id = 9 合并成一条），报告里有总耗时占比，排优先级必备。
2.2 不开慢日志也能查（performance_schema）
生产库不敢开慢日志时，用这个：
SELECT
DIGEST_TEXT AS 归一化SQL,
COUNT_STAR AS 执行次数,
ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS 总耗时秒,
ROUND(AVG_TIMER_WAIT/1000000000000, 4) AS 平均秒,
SUM_ROWS_EXAMINED AS 总扫描行数,
SUM_ROWS_SENT AS 总返回行数
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10\G
SUM_ROWS_EXAMINED 远大于 SUM_ROWS_SENT 就是典型的"扫了很多、返回很少"——优先优化这类。
三、EXPLAIN 逐列读懂
EXPLAIN SELECT * FROM `user` WHERE mobile = '13800138000'\G
| 列 | 看什么 | 好/坏 |
|---|---|---|
type |
访问类型 | system>const>eq_ref>ref>range>index>ALL；出现 ALL 就是全表扫 |
possible_keys |
理论上能用的索引 | 有但 key 为空 = 优化器放弃了 |
key |
实际用的索引 | NULL = 没走索引 |
key_len |
实际用上的索引字节数 | 判断联合索引用了几列 |
rows |
预计扫描行数 | 越接近返回行数越好 |
Extra |
补充信息 | 见下表 |
Extra 里的关键值：
| 值 | 含义 | 处置 |
|---|---|---|
Using index |
覆盖索引，不用回表 | 好，最优 |
Using where |
在 server 层再过滤 | 正常 |
Using filesort |
需要额外排序 | 考虑给排序字段建索引 |
Using temporary |
用了临时表（GROUP BY 常见） | 较重，考虑改写或调大 tmp_table_size |
Using index condition |
索引下推（ICP） | 好，5.6+ 特性 |
Using join buffer |
JOIN 没走索引 | 检查关联字段是否有索引 |
更精确的写法（MySQL 8）：
EXPLAIN ANALYZE SELECT ...; -- 真实执行并给出实际耗时和行数
EXPLAIN FORMAT=JSON SELECT ...; -- 详细信息，含 cost 估算
EXPLAIN 是估算，EXPLAIN ANALYZE 是真值。估算和实际差很多时，通常是统计信息过期：
ANALYZE TABLE `user`; -- 刷新统计信息
SHOW INDEX FROM `user`; -- 看 Cardinality，太低说明区分度差
四、索引核心概念（用最短的话讲清）
4.1 最左前缀原则
联合索引 (a, b, c) 相当于建了 (a)、(a,b)、(a,b,c) 三个索引。
INDEX idx_abc (a, b, c)
WHERE a = 1 -- ✅ 用上 a
WHERE a = 1 AND b = 2 -- ✅ 用上 a,b
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 全用上
WHERE a = 1 AND c = 3 -- ⚠️ 只用到 a（b 断了，c 用不上）
WHERE b = 2 -- ❌ 用不上（没有最左列 a）
WHERE a > 1 AND b = 2 -- ⚠️ 范围之后失效，b 用不上
范围查询（>、<、BETWEEN、LIKE 'x%' 之后）后面的列用不上索引，这是设计联合索引时最容易忽略的。
4.2 回表与覆盖索引
InnoDB 二级索引的叶子节点存的是主键值，通过二级索引找到主键后还要再查一次聚簇索引拿整行，这叫回表。
-- 需要回表
SELECT * FROM `user` WHERE mobile = '138...';
-- 覆盖索引：只查索引里已有的列，Extra 出现 Using index
SELECT id, mobile FROM `user` WHERE mobile = '138...';
优化手段：把高频查询的字段加进联合索引，让它变成覆盖索引。
-- 高频：SELECT id, status FROM user WHERE mobile = ?
ALTER TABLE `user` ADD INDEX idx_mobile_status (mobile, status);
4.3 索引下推（ICP，5.6+）
INDEX (name, age)，WHERE name LIKE '张%' AND age = 20：
- 5.6 之前：先按
name LIKE '张%'回表取全部，再在 server 层过滤 age； - 5.6 之后：在索引层就过滤 age，减少回表次数，
Extra显示Using index condition。
4.4 区分度（Cardinality）
SHOW INDEX FROM `user`;
-- Cardinality 越接近总行数，区分度越高，索引价值越大
性别、状态这类低区分度字段单独建索引基本没用（优化器会直接放弃）。但作为联合索引的后缀配合高区分度列是有效的。
五、12 种索引失效的真实写法
-- 1. 隐式类型转换（mobile 是 varchar，传了数字）⭐最高频
WHERE mobile = 13800138000 -- ❌ 全表扫
WHERE mobile = '13800138000' -- ✅
-- 2. 对索引列用函数
WHERE DATE(created_at) = '2026-09-23' -- ❌
WHERE created_at >= '2026-09-23' AND created_at < '2026-09-24' -- ✅ 改成范围
-- 3. 隐式字符集/编码运算（左边加运算）
WHERE id + 1 = 5 -- ❌
WHERE id = 4 -- ✅
-- 4. LIKE 前导百分号
WHERE name LIKE '%张三%' -- ❌
WHERE name LIKE '张三%' -- ✅ 前缀匹配可以走索引
-- 5. OR 连接非索引列
WHERE mobile = '138...' OR remark = 'x' -- ❌ remark 没索引就全表
-- 改成 UNION ALL 或给 remark 建索引
-- 6. 不等于 / NOT IN / NOT EXISTS
WHERE status != 1 -- 通常不走索引（结果集大时优化器放弃）
-- 7. IS NULL / IS NOT NULL（取决于数据分布，优化器可能选全表）
-- 8. 联合索引不满足最左前缀（见 4.1）
-- 9. 两表 JOIN 字段字符集/排序规则不同 ⭐隐蔽
-- t1.mobile 是 utf8mb4，t2.mobile 是 utf8 → 索引失效
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4;
-- 10. 用了 SELECT * 导致无法覆盖索引
-- 11. ORDER BY 字段与 WHERE 索引不一致 → Using filesort
WHERE a = 1 ORDER BY b -- INDEX(a) 不够，需要 INDEX(a, b)
-- 12. 数据量太小时索引无效（优化器认为全表更快）
第 1 条和第 9 条最难查，因为 SQL 看着完全没问题。判断方法：EXPLAIN 之后看 key 为 NULL，且 type=ALL。
六、补索引的正确姿势
-- 建索引
ALTER TABLE `user` ADD INDEX idx_mobile (mobile);
CREATE INDEX idx_created ON `user` (created_at);
-- MySQL 8：不可见索引（先观察，不行再删）
ALTER TABLE `user` ALTER INDEX idx_old INVISIBLE;
ALTER TABLE `user` ALTER INDEX idx_old VISIBLE;
-- 冗余索引检查
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'app';
-- 从未使用过的索引（跑一段时间后再判断）
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0
AND OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema');
大表加索引（MySQL 8 支持在线 DDL，但仍要评估）：
ALTER TABLE big_table ADD INDEX idx_x (col), ALGORITHM=INPLACE, LOCK=NONE;
| ALGORITHM | 是否重建表 | 是否允许 DML |
|---|---|---|
INSTANT |
否 | 是（8.0.12+，仅加列等有限场景） |
INPLACE |
部分 | 通常允许（加索引属于此类） |
COPY |
是 | 否（锁表，最慢） |
超过千万行且不能接受抖动时，用 pt-online-schema-change 或 gh-ost（影子表 + 触发器/ binlog 同步）。
七、SQL 改写的四个高频套路
-- 1. 深分页：LIMIT 1000000, 20 会扫 100 万行
SELECT * FROM t ORDER BY id LIMIT 1000000, 20; -- ❌
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 20; -- ✅ 用游标（需连续 id）
SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) x USING (id); -- ✅ 覆盖索引 + 回表
-- 2. COUNT(*)：InnoDB 没有行数缓存，大表很慢
SELECT COUNT(*) FROM t WHERE status = 1; -- 加索引 + 用近似值/汇总表
-- 用 EXPLAIN 的 rows 做估算，或维护计数表
-- 3. 大 IN 列表：拆成多次小查询或临时表 JOIN
WHERE id IN (1,2,3,...,10000) -- 解析代价高
-- 4. 子查询改 JOIN（5.6+ 优化器已能自动优化部分，但复杂场景仍手改更稳）
SELECT * FROM a WHERE id IN (SELECT aid FROM b WHERE x=1); -- 旧写法
SELECT a.* FROM a JOIN b ON a.id = b.aid WHERE b.x = 1; -- 改写
八、优化先后次序（别一上来就加索引）
| 优先级 | 手段 | 收益 |
|---|---|---|
| 1 | 改写 SQL（消除函数、类型转换、深分页） | 最大，零成本 |
| 2 | 调整联合索引顺序 / 加覆盖列 | 大 |
| 3 | 拆查询（大 SQL 拆成几个小 SQL） | 中 |
| 4 | 加缓存 / 汇总表 | 中 |
| 5 | 调参数（buffer pool、tmp_table_size） | 小 |
| 6 | 分库分表 / 归档历史数据 | 大但代价高 |
先改 SQL 再加索引。加索引是有代价的：占空间、拖慢写入、增加优化器选择负担。一个表上七八个索引是维护噩梦。
九、常见误区
- 给每个字段都建单列索引：MySQL 一般只选一个索引，多了反而拖慢优化器判断和写入。
- 用
SELECT *还怪没走覆盖索引：只查需要的列。 EXPLAIN出来rows很小就放心：那是估算，EXPLAIN ANALYZE才准。LIKE '%x'硬要优化：前缀通配无解，改用全文索引（MATCH AGAINST）或 Elasticsearch。- 不看
Extra只看type：Using filesort/Using temporary往往是真凶。 - 加完索引不验证：必须再看一次
EXPLAIN，确认key变了。
十、几条纪律
- 慢日志
long_query_time从 1 秒起步，配合pt-query-digest看总耗时占比排序。 - 优化顺序：改写 SQL > 调索引 > 加缓存 > 调参数 > 分库分表。
- 联合索引设计时把等值列放前、范围列放后、排序列跟在等值列后面。
- 每个表索引不超过 5-6 个，定期用
sys.schema_redundant_indexes清冗余。 - 线上加索引走低峰期，优先
ALGORITHM=INPLACE, LOCK=NONE，大表用在线改表工具。 - 优化完必须回归验证：看
EXPLAIN、看耗时、看慢日志里这条是否消失。
十一、延伸阅读
👉 相关在线工具：日志分析 · Linux 命令速查，免登录、浏览器内处理。