最左匹配
讲到联合索引就一定会讲到最左匹配
原则就是如果你的SQL用到了联合索引中最左边的索引,那么这条SQL语句就可以利用这个索引去进行匹配,当遇到范围查找的时候停止匹配
例如
我们对(a,b)字段建立索引,那么当你的的where后的条件为a=1或者a=1 and b=2就可以匹配索引。
注意:
b=2 and a= 1当你的sql是这样的情况下依然可以匹配索引,原因是MySQL有优化器会自动调整a,b的顺序与索引一致,但是当你执行b=2的时候就匹配不到索引
当你对(a,b,c,d)字段建立索引,where后条件为a=1 and b=2 and c>3 and d=4 那么abc三个字段会被匹配索引,而d匹配不到,因为遇到范围查询
原理
实现按照a来排序,在在确定a的值之后再根据b进行排序
从全局来看,a的值是有序的,而b的值是1,2,1,4,1,2,是全局无序的,但却又是局部有序的,在a已确定的情况下,b又是有序的,这也是为什么当只有b字段的时候,无法使用联合索引,也是为什么当遇到范围查找的时候就无法使用索引
经典题型
题型一
SQL
SELECT * FROM table
WHERE a = 1 and b = 2 and c = 3;
如何建立索引?
如果回答是对(a,b,c)建立索引,那就可以回去等通知了
正确的回答是建立联合索引,但是怎么建是看情况的,要把字段区分度高的字段放在前面,区分度低的放在后面,当字段区分度从大到小为b,c,a,那我们就对(b,c,a)建立联合索引,但是我在写SQL的时候不必去在意where后面的字段顺序,MySQL的优化器会帮我们做这件事。
题型二
SQL
SELECT * FROM table
WHERE a > 1 and b = 2;
这个时候应该建立(b,a)索引
题型三
SELECT * FROM `table`
WHERE a > 1 and b = 2 and c > 3;
建立(b,a)或者(b,c)都行,具体看情况,比如区分度
题型四
SELECT * FROM `table`
WHERE a = 1 ORDER BY b;
对(a,b)建立索引,当a=1的时候,b相对有序,避免再排序
SELECT * FROM `table`
WHERE a > 1 ORDER BY b;
建立a的索引就行,a为范围,b无序,没必要建立(a,b)索引
题型五
SELECT * FROM `table`
WHERE a IN (1,2,3) and b > 1;
建立(a,b)索引,in在这视为等值引用,不会中断索引匹配