索引的本质是空间换时间
InnoDB 使用 B+ 树作为索引结构。聚簇索引以主键组织数据,叶子节点存放完整行记录;二级索引的叶子节点存放索引列与主键值,查询时需要「回表」再到聚簇索引取完整数据。理解这一点,就能明白为什么覆盖索引能显著提升性能——要查的列都在索引里,无需回表。
B+ 树的高度通常只有 2 到 4 层,因此即便数据量巨大,单次查找的磁盘 I/O 次数也很有限。索引失效的根源,往往是查询条件没有利用到树结构的有序性。
最左前缀法则
联合索引 (a, b, c) 实际建立了 (a)、(a,b)、(a,c) 的组合有序结构。查询条件必须从最左列开始,且中间不能跳过,才能用上索引。例如 WHERE a=1 AND c=3 只能用到 a 部分,c 无法利用索引过滤。
范围查询会让其后的列失去索引能力:WHERE a>1 AND b=2 中,b 不能用于索引查找。把等值条件放前面、范围条件放后面,是设计联合索引的一般原则。
索引失效的常见场景
对索引列做函数运算或隐式类型转换(如字符串列与数字比较)会导致索引失效;LIKE '%prefix' 的前置通配符无法利用 B+ 树的有序性;OR 连接不同列时,可能放弃索引。用 EXPLAIN 观察 type 与 key 字段,能直接看出索引是否被使用。
索引不是越多越好。每个索引都会增加写入成本与存储空间,写入密集的表尤其要克制。定期用 sys.schema_unused_indexes 排查长期未使用的冗余索引。
实践建议
为高频查询建立合适的联合索引,尽量做到覆盖索引;用 EXPLAIN 分析慢查询并验证优化效果;结合业务特点迭代,而不是一次性设计完美。索引优化是持续的过程,不是一劳永逸的工作。