MySQL的索引本质上是基于B+树的有序结构，查询能否走索引取决于检索条件是否能利用这棵树的左前缀有序性。很多慢查询并不是没有索引，而是索引因为写法或结构问题没有被优化器选中。理解索引的物理存储方式和匹配规则，是避免性能陷阱的第一步。
最左前缀失效与联合索引字段顺序误区
联合索引遵循最左前缀原则，这意味着索引树先按第一个字段排序，再按第二个字段排序。如果查询条件跳过了联合索引的最左列，数据库通常无法利用该索引进行快速定位。例如建立了(city, age, name)的联合索引，当查询只过滤age=20时，由于B+树中age不是最左有序维度，只能进行全索引扫描或回退全表扫描。
另一个常见错误是把区分度低的字段放在联合索引前面。区分度指字段不同值的数量占比，像gender这种只有两三个值的字段若作为联合索引首列，过滤效果极差，优化器可能认为不如直接扫描。正确的做法是将高区分度、频繁出现在等值条件的字段放在左侧，范围查询字段尽量靠右，因为范围查询之后的索引列会失效。
以下示例展示了联合索引顺序对查询的影响。第一个索引把状态这种低区分度字段放前面，第二个把用户ID放前面：
-- 低效联合索引 CREATE INDEX idx_status_age ON user_log(status, age); -- 高效联合索引 CREATE INDEX idx_uid_age ON user_log(user_id, age); -- 以下查询能命中 idx_uid_age 的最左前缀 SELECT * FROM user_log WHERE user_id = 1001 AND age > 18;
隐式类型转换与函数操作导致的索引失效
当查询条件中对索引列使用了函数，或者传入值的类型与列定义不一致时，MySQL往往需要做隐式转换或表达式计算，这会使索引列不再是一个可以直接比较的常量，优化器只能放弃索引。比如列phone是字符串类型，但查询写成WHERE phone = 13800000000，数字和字符串比较会触发转换，索引失效。
在索引列上调用函数也是典型陷阱。WHERE DATE(create_time) = '2023-01-01'会对每行数据计算函数，无法利用create_time上的索引。改写方式是使用范围条件：create_time >= '2023-01-01' AND create_time < '2023-01-02'，这样仍能走索引范围扫描。同理，对字段做算术运算、字符截取也会导致同样问题。
我们可以通过EXPLAIN观察type列和key列判断是否失效。下面代码演示了错误与正确写法以及执行计划差异：
-- 错误：对索引列使用函数 EXPLAIN SELECT * FROM orders WHERE YEAR(paid_at) = 2023; -- 正确：范围查询保留索引 EXPLAIN SELECT * FROM orders WHERE paid_at >= '2023-01-01' AND paid_at < '2024-01-01'; -- 错误：隐式转换，phone为varchar EXPLAIN SELECT * FROM user WHERE phone = 13800000000; -- 正确：保持类型一致 EXPLAIN SELECT * FROM user WHERE phone = '13800000000';
回表开销与覆盖索引设计不足
使用非覆盖索引时，MySQL通过索引找到主键后还要回到聚簇索引取完整行数据，这个过程叫回表。如果查询返回字段多、命中索引行数大，大量随机IO会严重拖慢性能。尤其在二级索引区分度不高、命中几万行的情况下，回表成本可能超过全表扫描。
覆盖索引是指查询所需的所有字段都包含在索引中，这样不需要回表。设计时可把常用查询的 select 字段加入联合索引尾部，用空间换时间。但要注意索引不能无限宽，否则写操作变慢、缓冲池命中率下降。需要在读性能和写开销之间权衡，通常把高频查询的少量字段做成覆盖索引即可。
如下示例展示如何通过扩展索引避免回表。原始索引只有user_id，查询还要取status和amount，改成联合索引后实现覆盖：
-- 原始索引，查询需回表 CREATE INDEX idx_user ON bill(user_id); SELECT status, amount FROM bill WHERE user_id = 5; -- 覆盖索引，避免回表 CREATE INDEX idx_user_cover ON bill(user_id, status, amount); SELECT status, amount FROM bill WHERE user_id = 5;
除了上述三点，OR连接非索引列、不等值查询!=导致优化器放弃、ORDER BY与索引顺序不一致引发文件排序等，也都是实际项目中常见的性能陷阱。建议在每次上线下发复杂查询前，都用EXPLAIN分析执行计划，确认key不为空、rows明显小于表总量、Extra中没有Using filesort和Using temporary，才能认为索引设计是有效的。