MySQL建立联合索引 失效原理讲解

前言

在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
mysql使用联合索引时，需要注意的是联合索引的最左匹配原则。

mysql联合索引原理及失效原理

MySQL 联合索引（也称为复合索引或组合索引）是指在数据库表中对多个列进行索引的索引方式。联合索引的优点是可以通过索引优化查询性能，特别是对于多列查询时可以避免全表扫描。

联合索引的原理是通过对多个列的值进行排序来创建一个B-Tree结构，这样可以在查询时使用这些列作为过滤条件来快速定位记录。

联合索引失效是指当查询条件不满足联合索引的顺序或使用了范围查询（如BETWEEN, >, <等）时，联合索引可能不会被使用，这时查询性能可能会下降。

没有遵守最左匹配原则，使用like、%和or 等情况下索引也会失效。

例如，如果有一个联合索引(col1, col2)，下面的查询条件会使用到这个索引：

SELECT * FROM table WHERE col1 = 'value1' AND col2 = 'value2';

但是，如果查询条件改变顺序或使用了范围查询，则可能不会使用这个索引： 

-- 顺序不匹配，不会使用索引
SELECT * FROM table WHERE col2 = 'value2' AND col1 = 'value1';

-- 使用了范围查询，可能不会使用索引
SELECT * FROM table WHERE col1 = 'value1' AND col2 > 'value2';

 为了确保联合索引的有效使用，可以通过调整查询条件的顺序来保持和索引一致，或者对于需要范围查询的列，确保查询能够使用索引的最左前缀特性。

联合索引的好处

• 减少索引建立的开销，建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引；
• 覆盖索引 无需回表，这减少了很多的随机io操作；所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
• 缩小筛选范围。