一、联合索引的使用
本文中联合索引的定义为(MySQL):
ALTER TABLE table_name
ADD INDEX (col1
,col2
,col3
);
二、联合索引的本质
当创建(col1,col2,col3)联合索引时,相当于创建了(col)单列索引,(clo1,clo2)联合索引以及(col1,col2,col3)联合索引想要索引生效,只能使用col1和col1,col2和col1,col2,col3三种组合;当然,col1,col3组合也可以,但实际上只用到了col1的索引,col3并没有用到!
三、SQL中联合索引和普通索引区别
区别:
联合索引中列的顺序非常重要,从左原则。a,ab,ba,abc。
单个索引:一个一个起作用,也就是说有三个单个索引,哪个条件查询在前哪个起作用,其他不起作用。
结论:
一般来说,列表搜索需要多个列查询,此时就可以使用联合索引,都是and的关系。
什么时候需要创建索引:
1、where条件会经常出现的,并且当前表的数量比较大。
2、where条件中是用and而非or的时候。
3、联合索引比单个索引更适合,因为索引占用一定磁盘空间,也就说明有一定的开销,如果多个单个索引比较多,那么多资源的浪费也比较多,联合索引相当于对多个列建索引,并且只建一次,and条件下非常适合。
说明:
-
MySQL联合索引遵循最左前缀匹配规则,即从联合索引的最左列开始向右匹配,直到遇到匹配终止条件。例如联合索引(col1, col2, col3), where条件为col1=
a
AND col2=b
可命中该联合索引的(col1,col2)前缀部分, where条件为col2=b
AND col3=c
不符合最左前缀匹配,不能命中该联合索引。 -
匹配终止条件为范围操作符(如>, <, between, like等)或函数等不能应用索引的情况。例如联合索引(col1, col2, col3), where条件为col1=
a
AND col2>1 AND col3=c
, 在col2列上为范围查询,匹配即终止,只会匹配到col1,不能匹配到(col1, col2, col3). -
where条件中的顺序不影响索引命中。例如联合索引(col1, col2, col3), where条件为col3=
c
AND col2=b AND col1=a
, MySQL优化器会自行进行优化,可命中联合索引(col1, col2, col3).