联合索引的最左前缀原则是什么？哪些情况会导致索引失效？

详细解析#

联合索引是怎么排序的#

(a, b, c) 索引中的记录顺序：
(1, 1, 3) (1, 2, 1) (1, 2, 5) (2, 1, 2) (2, 3, 1) (3, 1, 1)

a 整体有序；a 相同时 b 才有序；a、b 都相同时 c 才有序
单看 b：1 2 2 1 3 1，是无序的，没法在索引上二分查找

像查字典：先按第一个字母排，再按第二个字母排。知道第一个字母能很快翻到，只知道第二个字母就只能从头翻到尾。

范围条件包括 >、<、>=、<=、BETWEEN 和 LIKE 'x%'。范围之后的列为什么不能定位：b > 2 匹配的是 b = 3、4、5 等多个 b 值，每个 b 值下面的 c 各自有序，合在一起就无序了。不过 c 的条件也不是完全没用，索引下推 可以在回表之前用它过滤。

索引失效的常见情况#

还有两种情况常被说成"索引失效"，其实是优化器的选择：

• 优化器认为全表扫描更快：比如 status = 1 匹配了表里大部分行，走二级索引要回表几十万次随机读，不如直接顺序扫描聚簇索引。没有固定的比例阈值，取决于统计信息和成本估算
• !=、NOT IN、IS NOT NULL：它们可以转成范围扫描，并不是一定不能用索引，只是匹配的行通常很多，回表代价高

字符集不一致的关联也容易踩坑：关联字段一边是 utf8mb3、一边是 utf8mb4，MySQL 会把 utf8mb3 那一侧转换成 utf8mb4 再比较，被转换的一侧上的索引就用不上了。

索引设计原则#

1. 从查询出发：为 WHERE、JOIN、ORDER BY、GROUP BY 中高频出现的列建索引，而不是每列各建一个
2. 等值在前，范围在后：WHERE user_id = ? AND status = ? AND create_time > ? 建 (user_id, status, create_time)；多个等值列时，优先放能被更多查询复用的列，再考虑区分度
3. 看区分度：COUNT(DISTINCT col) / COUNT(*) 越接近 1 越好。状态、性别这类区分度低的列单独建索引意义不大，但放在联合索引里配合其他列仍然有用
4. 兼顾排序和覆盖：WHERE user_id = ? ORDER BY create_time DESC LIMIT 20 建 (user_id, create_time)，按索引顺序取到 20 条就停；常查的少量列加进索引可以避免回表
5. 控制数量和长度：每个索引都会拖慢写入、占用空间；有了 (a, b) 就不需要再单独建 (a)；长字符串可以用前缀索引 INDEX (email(10))，但前缀索引用不了覆盖索引，也不能用于排序

代码示例#

-- 对列用了函数，用不上 create_time 上的索引
EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2024-06-01';
-- type: ALL

-- 改写成范围条件
EXPLAIN SELECT * FROM orders
WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02';
-- type: range，key: idx_create_time

-- MySQL 8.0.13 起支持函数索引，查询中的表达式要和索引定义一致
ALTER TABLE orders ADD INDEX idx_create_date ((DATE(create_time)));
EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2024-06-01';
-- type: ref，key: idx_create_date

面试官可能追问#

易错点#

• WHERE 里条件的书写顺序不影响最左前缀，b = 2 AND a = 1 照样能用 (a, b)
• 隐式类型转换只在字符串列和数字比较时导致失效；INT 列和字符串常量比较，转换的是常量，索引仍然可用
• LIKE 'abc%' 可以用索引，但它属于范围条件，联合索引中后面的列不能再用于定位
• !=、IS NULL、IS NOT NULL 不是一定不走索引，要以 EXPLAIN 为准