#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 若至少有一列的索引不是等值查询,此时将全表扫描