mysql教程联合索引_mysql 索引实践(二) 联合索引

#emp_no PRIMARY

#first_name,last_name,gender  idx_first_name_last_name

show index from employees

最左匹配：

不是指的顺序，而是where 中将根据联合索引的创建顺序，去检索当前SQL能用到该联合索引到哪一步。

first_name,last_name,gender ：可匹配 (first_name)、(first_name,last_name)、(first_name,last_name,gender)

#ref

explain select * from employees where first_name = 'Parto1'

#ref

#Using where

explain select * from employees where first_name = 'Parto1' and birth_date = '1953-09-02'

#ref

#Using where

explain select * from employees where first_name = 'Parto1' and birth_date != '1953-09-02'

#ALL

#Using where

explain select * from employees where first_name != 'Parto1'

若联合索引的第一个字段为不等值查询,则该SQL一定不走该联合索引。

此时，可以发现是否使用和SQL中字段的顺序无关

此时，当前SQL能用到该联合索引 first_name,last_name

#ref

explain select * from employees where first_name = 'Parto1' and last_name =  'Parto1'

#ref

explain select * from employees where last_name = 'Parto1' and first_name =  'Parto1'

#ref

#Using where

explain select * from employees where birth_date = '1953-09-02'  and first_name =  'Parto1' and last_name =  'Parto1'

explain select * from employees where first_name =  'Parto1' and birth_date = '1953-09-02' and last_name =  'Parto1'

explain select * from employees where first_name =  'Parto1' and last_name =  'Parto1' and birth_date = '1953-09-02'

explain select * from employees where first_name =  'Parto1' and last_name =  'Parto1' and birth_date != '1953-09-02'

explain select * from employees where first_name =  'Parto1' and last_name =  'Parto1' and birth_date is not null

此时，当前SQL能用到该联合索引 first_name

#range

#Using index condition

explain select * from employees where first_name =  'Parto1' and last_name !=  'Parto1'

explain select * from employees where first_name =  'Parto1' and last_name !=  'Parto1' and gender = 'M'

explain select * from employees where first_name =  'Parto1' and last_name !=  'Parto1' and gender != 'M'

explain select * from employees where first_name =  'Parto1' and last_name is not null and gender is not null

explain select * from employees where first_name =  'Parto1' and last_name !=  'Parto1' and birth_date = '1953-09-02'

explain select * from employees where first_name =  'Parto1' and last_name !=  'Parto1' and birth_date != '1953-09-02'

此时，当前SQL不能用到该联合索引

#ALL

#Using where

explain select * from employees where first_name !=  'Parto1' and last_name =  'Parto1'

explain select * from employees where first_name !=  'Parto1' and last_name =  'Parto1' and gender = 'M'

explain select * from employees where first_name !=  'Parto1' and last_name =  'Parto1' and birth_date = '1953-09-02'

总之, 对于联合索引，会将SQL中的字段按照索引的顺序去匹配，若某一字段未匹配上，或者该字段查询为不等值查询，该索引将只执行到该字段之前，在此之后的字段，即使匹配且为等值查询，也将不再继续进行匹配了。

因此，如果若第一个字段就匹配不上或者该字段为不等值查询，则一定为全表扫描。

例如，(A,B,C) 如果B 缺失或者B为不等值查询，则索引将只执行到A，此时B,C不执行。

不满足所有列均为索引

#ALL

#Using where

explain select * from employees where first_name = 'Parto1' or last_name =  'Parto1'

explain select * from employees where first_name = 'Parto1' or birth_date = '1953-09-02'

满足所有列均为索引

#index_merge

#Using union(idx_first_name_last_name,PRIMARY); Using where

explain select * from employees where first_name = 'Parto1' and last_name =  'Parto1' and gender = 'M' or emp_no = 10005

不满足所有列均为索引

#ALL

#Using where

explain select * from employees where first_name = 'Parto1' and last_name =  'Parto1' and gender = 'M' or birth_date = '1953-09-02'

#使用OR连接时：

#1.1 当所有的列均为索引,且为等值查询,仍将采用index_merge 进行查询

#1.2 若有一列不是索引,此时将全表扫描;

#1.3 若至少有一列的索引不是等值查询,此时将全表扫描