MySQL联合索引（abc）命中规则

1.建表

mysql创建一张表，表名：‘test_models’

• id列为 主键，int类型 ，自增
• a,b,c,d,e 全部是int（11）
• 为（a,b,c）添加一个联合索引 index_abc

执行语句：

CREATE TABLE `test_models` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
 `a` INT(11) DEFAULT NULL,
 `b` INT(11) DEFAULT NULL,
 `c` INT(11) DEFAULT NULL,
 `d` INT(11) DEFAULT NULL,
 `e` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_abc` (`a`,`b`,`c`)
);

往表中写入100万条数据
其中每行数据取值

• a 范围 0 - 1000 随机整数
• b 范围 0 - 10000 随机整数
• c 范围 0 - 100000 随机整数
• d 范围 0 - 1000 随机整数
• e 范围 0 - 1000 随机整数

2.测试 联合索引（a,b,c）

使用 ‘EXPLAIN’ sql语句查看执行详情

EXPLAIN SELECT * FROM test_models WHERE a = 100 AND b = 1000 AND c = 10000;

3.结果（下面结论全部都实际测试过）

1、AND AND 只要用到了最左侧a列，和顺序无关 都会使用 索引

a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引

c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

2、OR 不使用索引

这个条件中也包含了AND和OR逻辑操作符。在大多数数据库系统中，OR操作符往往难以使用联合索引，因为它需要组合多个条件的结果。在这种情况下，数据库可能会选择使用最适合的单列索引，或者执行多个单列索引的合并操作，或者在没有适用的单列索引的情况下执行全表扫描

a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引

这个条件中只包含了OR逻辑操作符，没有AND。在某些数据库系统中，当查询条件中只包含OR操作符时，可以考虑使用联合索引。但是具体是否使用索引还会受到索引的选择性（selectivity）等因素的影响。如果a、b和c的取值范围广泛，那么联合索引可能对查询性能提升不大

a = 1 OR b = 2 OR c = 3 未使用索引

3、最左侧的‘a’列 被大于，小于比较的 ，使用range索引

a > 1 AND b = 2 AND c = 3  使用range索引
a < 1 AND b =  2 AND c = 3  使用range索引
a > 1 ; 使用range索引
a <> 1 AND b = 2 AND c = 3 不走索引，因为使用了<>

最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and and ）

a = 1 AND b < 2 AND c = 3 使用索引，索引走到b
a = 1 AND c = 2 AND b < 3 使用索引，索引走到b
a = 1 AND b < 2 使用索引，索引走到b
a = 1 AND b <> 2 AND c = 3 使用索引，索引走到b
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 未使用索引

4、ORDER BY
a = 某，后面order 无所谓 都 使用索引 （和最上面的最左匹配一样）
看

// 或者 ORDER BY b ， ORDER BY c, 使用索引，走到abc
(1)a = 1 AND b = 2 AND c = 3 ORDER BY a;

// 使用abc索引，走到a
(2)a = 1 ORDER BY a; 

// 使用abc索引，走到b
(3)a = 1 ORDER BY b; 

// 使用abc索引，走到a, a用到了索引，但是这个地方c没有使用索引，因为中间断点了，使用 explain 可以看到 filesort
(4)a = 1 ORDER BY c; 

// b没有用到索引，排序中a也没有使用索引
(5)b=1 order by a;

ORDER BY 任何影响，去掉 ORDER BY 确实和上面的测试重复

最终总结：

1) MySQL联合索引遵循最左前缀匹配规则，即从联合索引的最左列开始向右匹配，直到遇到匹配终止条件。例如联合索引(col1, col2, col3), where条件为col1=a AND col2=b可命中该联合索引的(col1,col2)前缀部分, where条件为col2=b AND col3=c不符合最左前缀匹配，不能命中该联合索引。

2) 匹配终止条件为范围操作符(如>, <, between, like等)或函数等不能应用索引的情况。例如联合索引(col1, col2, col3), where条件为col1=a AND col2>1 AND col3=c, 在col2列上为范围查询，匹配即终止，只会匹配到(col1, col2)，不能匹配到(col1, col2, col3).

3) where条件中的顺序不影响索引命中。例如联合索引(col1, col2, col3), where条件为col3=c AND col2=b AND col1=a, MySQL优化器会自行进行优化，可命中联合索引(col1, col2, col3).