那条跑了 4.7 秒的 SQL，我改了三个地方降到 23 毫秒
九月底，运营反馈"订单查询页面打不开"。我一看监控，那个列表接口的 TP99 从平时的 120ms 涨到了 4.7 秒，慢查询日志里刷了一屏同一条 SQL。
库是 MySQL 5.7.21，订单表 t_order 数据量 860 万。先说结论，最后优化效果：
| 阶段 | 耗时 | 扫描行数 |
|---|---|---|
| 原始 | 4720 ms | 约 860 万（全表） |
| 改索引后 | 245 ms | 3.2 万 |
| 改 SQL + 覆盖索引 | 23 ms | 20 |
原始 SQL 和表结构
CREATE TABLE `t_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`order_no` varchar(32) NOT NULL,
`user_id` bigint(20) NOT NULL,
`shop_id` int(11) NOT NULL,
`status` tinyint(4) NOT NULL,
`pay_type` tinyint(4) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_shop_status_time` (`shop_id`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
业务 SQL（MyBatis 里拼的）：
SELECT * FROM t_order
WHERE shop_id = 10086
AND DATE(created_at) >= '2018-09-01'
AND status IN (2, 3, 4)
AND user_id = 900120
ORDER BY created_at DESC
LIMIT 20;
explain 出来：
+----+-------------+---------+------+----------------------+------+---------+------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------------+------+---------+------+---------+-----------------------------+
| 1 | SIMPLE | t_order | ALL | idx_shop_status_time | NULL | NULL | NULL | 8267440 | Using where; Using filesort |
+----+-------------+---------+------+----------------------+------+---------+------+---------+-----------------------------+
type=ALL 全表扫描，key=NULL 索引一个没用上，rows 826 万。它明明 possible_keys 里认出了那个联合索引，为什么不用？
场景一：索引列上包了函数
第一个问题在 DATE(created_at) >= '2018-09-01'。MySQL 的规矩是：对索引字段做函数运算，索引直接失效。B+ 树里存的是 created_at 的原始值，不是 DATE(created_at) 的结果，优化器没法拿计算结果去树里定位。
改成范围查询：
AND created_at >= '2018-09-01 00:00:00'
AND created_at < '2018-10-01 00:00:00'
改完 explain：
| type | key | key_len | rows | Extra |
| range | idx_shop_status_time | 10 | 32416 | Using where; Using filesort |
用上索引了，rows 降到 3.2 万，耗时 4720ms → 1420ms。key_len=10 说明只用到了 shop_id(4) + status(1)... 等等，实际是 10，后面再说这个数字怎么读。
MySQL 5.7 支持函数索引的替代方案：加一个生成列再建索引。不过我当时没用，改 SQL 就够了：
ALTER TABLE t_order ADD COLUMN created_date date GENERATED ALWAYS AS (DATE(created_at));
ALTER TABLE t_order ADD INDEX idx_shop_date (shop_id, created_date);
场景二：最左前缀原则
联合索引 (shop_id, status, created_at) 在 B+ 树里是先按 shop_id 排序，shop_id 相同再按 status 排，status 也相同才按 created_at 排。所以：
WHERE shop_id = 10086 -- 用上 shop_id
WHERE shop_id = 10086 AND status = 2 -- 用上 shop_id, status
WHERE shop_id = 10086 AND status = 2 AND created_at > ? -- 三个都用上
WHERE status = 2 -- 用不上，跳过了最左列
WHERE shop_id = 10086 AND created_at > ? -- 只能用上 shop_id，created_at 被跳过
key_len 就是判断用了几个字段的尺子。按我们的字段类型算：
shop_idint，4 字节（非空）→ 4statustinyint，1 字节 → 1created_atdatetime，MySQL 5.7 是 5 字节 → 5
上面那次 key_len=10，等于 4 + 1 + 5，说明三个字段全用上了。如果只有 5，那就是只用了 shop_id(4)，加一个字节可能是 NULL 标志位。
这里还有个我踩过的点：范围查询之后的列用不上索引。比如 WHERE shop_id = 10086 AND status > 2 AND created_at > ?，status 是范围查询，那 created_at 就只能用于回表后过滤，key_len 会是 5 而不是 10。但我们的 status IN (2,3,4) 属于多个等值，MySQL 会当成多个范围，MySQL 5.6 之后有 index condition pushdown 优化，能把这个影响降到最低。
场景三：隐式类型转换
这是我遇到过最隐蔽的一个。order_no 是 varchar，有唯一索引，但下面这句是全表扫描：
SELECT * FROM t_order WHERE order_no = 20180901123456;
数字没加引号。MySQL 的规则是：字符串和数字比较时，会把字符串转成数字。等价于 CAST(order_no AS UNSIGNED) = 20180901123456，又变成对索引列做函数运算了。
反过来是安全的：如果字段是 int，你写 WHERE user_id = '900120'，MySQL 会把字符串常量转成数字，索引字段本身没被处理，索引照样能用。所以规律是——字段是什么类型，就传什么类型的值。
我们项目里这类问题基本都出在 MyBatis 上：
<!-- 错误：userId 是 Long，传了 String -->
<if test="userId != null and userId != ''">
AND user_id = #{userId}
</if>
那个 userId != '' 的写法是网上抄的，很多老项目的祖传代码里都有，它会导致值为 0 时被跳过，而且容易让类型混乱。我后来统一改成了只判 null。
场景四：like 前置通配符
WHERE order_no LIKE '20180901%' -- 能用索引，前缀匹配
WHERE order_no LIKE '%20180901' -- 用不了
WHERE order_no LIKE '%20180901%' -- 用不了
B+ 树是按索引值从左到右排的，前缀确定的话可以在树里定位起点；开头就是 % 的话，起点都找不到，只能全扫。
我们有个"订单号模糊搜索"的后台功能，上线时表只有 2 万行，秒开；半年后 800 万行，每次搜索 9 秒。最后的解决方案是加了个倒序冗余列：
ALTER TABLE t_order ADD COLUMN order_no_rev varchar(32);
UPDATE t_order SET order_no_rev = REVERSE(order_no);
ALTER TABLE t_order ADD INDEX idx_order_no_rev (order_no_rev);
-- 查后缀变成查前缀
WHERE order_no_rev LIKE REVERSE('%0901') -- 即 LIKE '1090%'
场景五：or 连接了非索引列
WHERE shop_id = 10086 OR remark LIKE '%投诉%'
remark 没索引，MySQL 会认为：既然有一边必须全表扫，那不如直接全表扫，索引白建。改成两个查询 union，或者给 remark 加全文索引：
SELECT * FROM t_order WHERE shop_id = 10086
UNION ALL
SELECT * FROM t_order WHERE MATCH(remark) AGAINST('投诉' IN BOOLEAN MODE);
注意用 UNION ALL 而不是 UNION，后者会去重，多一次临时表排序。当然前提是两边结果不会重复。
场景六、七、八：另外三个高频坑
- != 和 NOT IN：通常走不了索引。我一般改写成
IN正向枚举，或者加个is_deleted标记做逻辑删除。 - is null / is not null：MySQL 的索引是能存 NULL 的，
IS NULL在某些情况下能用索引，但优化器经常因为回表代价大而放弃。我们表里所有字段都设成 NOT NULL 加默认值，从根上避免。 - 优化器主动放弃索引：当预计要回表的行数超过全表的 20%~30% 时，优化器会判断全表扫描比"走索引 + 回表"更快。这个判断依赖统计信息，不准的时候用
ANALYZE TABLE t_order刷新一下，或者用FORCE INDEX强制（我只在确认过之后才用）。
最后的 23 毫秒是怎么来的
改完函数包裹之后是 1420ms 还是不够。剩下的开销在两个地方：一是 SELECT * 导致大量回表，二是 Using filesort。
先说 filesort。索引是 (shop_id, status, created_at)，而 SQL 里有 status IN (2,3,4)，多个 status 值对应的 created_at 在索引里不是全局有序的，所以 ORDER BY 还得排一次。我把 status 的多值查询改成单值优先（前端默认筛 "全部" 时走另一条不带 status 的 SQL），并建立了覆盖索引：
ALTER TABLE t_order ADD INDEX idx_cover (shop_id, status, created_at, user_id, order_no, amount);
覆盖索引的意思是，查询需要的列全在索引里，不用回表查主键。explain 里会出现 Using index：
| type | key | key_len | rows | Extra |
| range | idx_cover | 10 | 20 | Using where; Using index |
rows 只有 20，因为 LIMIT 20 配合有序索引，扫到 20 条就停了。Using filesort 也没了。耗时 1420ms → 23ms。
代价是这个索引有 6 个字段，占了 1.2G 磁盘，写操作会变慢。加之前我特意问过师傅，他说订单表读写比大概 20:1，值得。
我总结的检查顺序
EXPLAIN先看 type，出现 ALL 和 index 就要警惕；- 看
key是不是你以为的那个，key_len判断联合索引用了几列； - 看
Extra：Using filesort和Using temporary是性能杀手，Using index是好事； - 检查 WHERE 里的字段有没有被函数包、有没有类型不匹配；
- 确认联合索引顺序和最左前缀；
- 最后看能不能用覆盖索引干掉回表。
还有一条我觉得最重要的：不要在生产库上直接试 SQL。我第一次执行 ALTER TABLE 加索引的时候，是在主库上直接跑的，800 万行的表锁了大概 90 秒，期间所有下单失败。后来才知道 5.7 的 Online DDL 对加索引是支持的，但要显式写 ALGORITHM=INPLACE, LOCK=NONE：
ALTER TABLE t_order ADD INDEX idx_cover (...), ALGORITHM=INPLACE, LOCK=NONE;
那 90 秒的事故，师傅没骂我，只说了句"以后 DDL 记得找我 review"。这比骂一顿难受多了。