MySQL开发技巧索引

上周帮同事排查一个慢查询，发技表里才三万行数据，巧索SELECT 却跑了 1.2 秒。发技看一眼执行计划，巧索type=ALL，发技全表扫描。巧索问题出在 WHERE 里对索引列用了 DATE(create_time) = '2024-01-01'。发技函数一包，巧索索引直接废掉。发技这种事我见过太多次，巧索开发写 SQL 时顺手一个函数、发技一个隐式转换，巧索DBA 就得跟着擦屁股。发技

索引不是巧索建了就完事。B+树的发技结构决定了它只能从最左列开始匹配。联合索引 (a, b, c)，查询条件只有 b 和 c，索引用不上。条件有 a 和 c，只有 a 走索引，c 得回表过滤。范围查询更讲究——a > 1 AND b = 2，b 用不上索引，因为 a 的范围扫描已经把有序性打乱了。这些规则写在手册里，但真正踩过坑才记得住。

覆盖索引能省掉回表

有个订单列表接口，查询只需要 order_id、user_id、status 三个字段。原索引建在 (user_id, status) 上，每次都要回表取 order_id。后来改成 (user_id, status, order_id)，执行计划里 Extra 出现 Using index，QPS 从 800 涨到 2400。回表是随机 I/O，代价比顺序扫描大得多。把查询涉及的字段都塞进索引，哪怕索引宽一点，只要不触发页分裂，通常划算。

但别滥用。有人把 SELECT * 需要的列全建进索引，一个索引十几个字段，写入性能掉得厉害。每次 INSERT 都要维护所有索引树，更新频繁的表上，索引数量超过五个就得警惕。我见过一张日志表建了九个索引，写入延迟从 3ms 飙到 40ms，删掉四个冗余索引后恢复正常。

前缀索引和索引下推

字符串字段建索引，VARCHAR(255) 全列索引占空间。用 ALTER TABLE t ADD INDEX idx_email (email(10))，只取前十个字符。选择性够用就行——COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) 超过 0.9 基本可以接受。缺点是 ORDER BY email 用不了这个索引，GROUP BY 也不行。

MySQL 5.6 之后有索引下推（ICP）。联合索引 (name, age)，查询 name LIKE '张%' AND age = 25。没有 ICP 时，存储引擎按 name 过滤出记录，回表后 Server 层再筛 age。有了 ICP，age 的判断直接下推到存储引擎，在索引里就完成过滤。减少回表次数，效果立竿见影。不过 EXPLAIN 里看到 Using index condition 才说明生效了。

隐式转换是隐形杀手

字段 user_id 是 VARCHAR(32)，查询写 WHERE user_id = 12345。MySQL 把字符串转成数字比较，索引失效。反过来，字段是 INT，查询写 WHERE id = '123'，同样出问题。字符集不一致也会导致隐式转换，utf8mb4 和 utf8 联表查询，被驱动表的索引直接报废。这种坑排查起来费劲，EXPLAIN 里 key 显示 NULL，但 SQL 看起来完全正常。

索引统计信息会骗人

SHOW INDEX FROM t 里的 Cardinality 是个估算值，不是精确去重计数。ANALYZE TABLE 可以手动更新，但大表上跑一次很慢。有时候优化器选错索引，就是因为统计信息过期。我习惯在批量导入数据后执行 ANALYZE TABLE，或者调整 innodb_stats_auto_recalc 参数让它在数据变化超过 10% 时自动触发。

还有 FORCE INDEX 这种核武器。不到万不得已别用，优化器比人聪明的时候居多。真要用，记得在注释里写清楚原因和日期，否则半年后没人敢删。

索引优化没有银弹。理解 B+树怎么找数据，比背一百条规则管用。每次写完 SQL，顺手 EXPLAIN 一下，成本几秒钟，省下的可能是几小时的排查时间。