复合索引顺序写反导致索引失效的经典案例分析

mysql复合索引严格遵循最左前缀原则：条件列顺序须与索引定义一致，跳过最左列或顺序错乱将导致全表扫描；范围查询后右侧列失效；ORM动态SQL、隐式类型转换易引发索引失效，需用EXPLaiN验证。

WHERE 条件列顺序和复合索引定义顺序不一致

MySQL 的复合索引不是“任意列都能用”，而是严格依赖 B+ 树的存储结构：先按第一列排序，相同值再按第二列排序，以此类推。一旦查询条件跳过最左列，或顺序混乱（比如索引是 (user_id, status)，却写成 WHERE status = 'paid'），优化器就无法定位数据起始位置，只能全表扫描。

• 错误示例：CREATE INDEX idx_status_user ON orders(status, user_id)，但业务常查 WHERE user_id = 123 —— 这个查询完全用不上该索引
• 正确做法：根据高频查询模式定顺序。如果 user_id 是主过滤字段，status 是次要筛选，索引应建为 idx_user_status(user_id, status)
• 注意：MySQL 8.0+ 对 WHERE c=4 AND b=6 AND a=3 这类无序条件会自动重排，但前提是所有列都在同一复合索引中且无范围查询；一旦出现 WHERE user_id = 123 AND status > 'created'，status 后的列（如有）就失效了

范围查询后右侧索引列自动“失能”

复合索引中，只要某列用了范围操作（、>、BETWEEN、LIKE 'abc%'），它之后的所有列就不再参与索引查找——不是“没用上”，而是根本没法用，因为 B+ 树在该列已失去有序性保障。

• 典型失效：CREATE INDEX idx_age_created(age, created_at)，执行 WHERE age > 18 AND created_at > '2025-01-01' —— 只有 age 走索引，created_at 条件实际是回表后逐行判断
• 验证方法：用 EXPLAIN 看 key_len，若远小于索引总长度（如 key_len: 4 而索引含两个 int 字段共 8 字节），说明右侧列被截断
• 替代思路：把高选择性、等值匹配的列放左边；范围列尽量靠右；必要时拆成两个单列索引，让优化器按需选择

ORM 框架里拼接条件时顺序失控

spring Boot + mybatis/JPA 场景下，动态 SQL 或 Criteria API 容易导致 WHERE 子句列顺序与索引定义错位。尤其当多个可选参数组合查询时，框架生成的 SQL 可能先拼 status 再拼 user_id，而索引却是 (user_id, status)。

• 常见坑：@Query("select * FROM orders WHERE status = :status AND user_id = :uid") —— 即便参数传了，SQL 字面顺序仍违反最左原则
• 实操建议：在 Mapper xml 中显式控制条件顺序，或改用 @Query 带固定顺序的写法；JPA 中避免 findByStatusAndUserId() 这类方法名推导，手动指定 JPQL
• 调试技巧：开启 spring.jpa.show-sql=true 和 Logging.level.org.hibernate.SQL=DEBUG，拿到真实 SQL 后立刻用 EXPLAIN 验证

字符串字段隐式转换放大顺序问题

当复合索引包含字符串列（如 email）和数字列（如 tenant_id），而应用层传参类型不一致时，MySQL 先做隐式转换，再判断是否走索引——此时不仅类型不匹配，连“最左列是否被真正使用”都变得不可控。

• 例子：CREATE INDEX idx_email_tenant(email, tenant_id)，java 传参 tenant_id = 1001L（Long），但数据库字段是 INT，MySQL 可能将整列 email 转成数字比较，直接废掉整个索引
• 检查方式：看 EXPLAIN 的 type 是否为 ALL，以及 Extra 是否出现 using where; Using index（后者说明走了覆盖索引，前者大概率失效）
• 底线操作：所有查询参数类型必须与 DDL 定义完全一致；MyBatis 中用 #{tenantId} 而非 ${tenantId}，避免字符串拼接引发类型错乱

最危险的情况不是“没建索引”，而是“建了索引却以为它在工作”。只要 WHERE 条件里任意一列没对齐最左前缀，或者范围查询出现在中间位置，索引就只剩一半效力——而监控和日志通常不会报错，只会默默变慢。