#emp_no PRIMARY
#first_name
#last_name
show index from employees#等值查询 =
#范围查询 in
#前缀匹配 like 'xx%'
#不等值查询 !=、>、>=、<、<=、not like、not in、like '%xx'、is not null
聚集索引(叶子节点存放行记录数据)
#const
explain select * from employees where emp_no = 10001
#range #Using where
explain select * from employees where emp_no != 10001#const
explain select * from employees where birth_date = '1953-09-02' and emp_no = 10001#range
#Using where
explain select * from employees where birth_date = '1953-09-02' and emp_no != 10001#range
#Using where
explain select * from employees where birth_date is not null and emp_no != 10001#无论存在多少个其他非索引的列的条件，即使其他列使用了非等值查询、is not NULL、not Like、not in等,存在聚集索引时，都是先根据主键进行查询出相关记录，再根据条件过滤#
#1.即使存在多个索引索引，当主键匹配为等值匹配,则优先使用聚集索引#
explain select * from employees where first_name = 'Georgi' and last_name = 'Facello' and emp_no = 10001#2.若主键匹配不为等值匹配，将优先使用其他等值匹配的索引索引.
#2.1.此时，优先使用first_name辅助索引
explain select * from employees where first_name = 'Parto1' and emp_no != 10001#2.2.此时，优先使用last_name辅助索引
explain select * from employees where first_name != 'Georgi' and last_name = 'Facello' and emp_no != 10001#3.若不存在其他等值匹配的辅助索引，则将使用聚集索引
explain select * from employees where first_name != 'Georgi' and last_name != 'Facello' and birth_date = '1953-09-02' and emp_no != 10001
辅助索引(叶子节点不存放行记录数据)
单个辅助索引：
#ref
explain select * from employees where first_name = 'Parto1'#前缀字符串匹配 range
#Using index condition
explain select * from employees where first_name like 'Parto1%'#非等值查询均为：
#all
#Using where
explain select * from employees where first_name != 'Parto1'
explain select * from employees where first_name like '%Parto1'
explain select * from employees where first_name like '%Parto1%'
explain select * from employees where first_name not like 'Parto1%'
explain select * from employees where first_name is not null#ref
#Using where
explain select * from employees where birth_date = '1953-09-02' and first_name = 'Parto1'#all
#Using where
explain select * from employees where birth_date = '1953-09-02' and first_name != 'Parto1'#单个辅助索引,只要不是等值查询或前缀字符串匹配,均将进行全表扫描，此时该列和不是索引的列查询结果,并无二异.#
#单个辅助索引和聚合索引一起时:
#1.若聚合索引为等值查询,则优先使用聚合索引;
#2.若聚合索引为不等值查询,当前辅助索引为等值查询,则使用当前辅助索引;
#3.若当前辅助索引也为不等值查询,则仍使用聚合索引.
多个辅助索引：
#index_merge(使用了idx_last_name,idx_first_name)
#Using intersect(idx_last_name,idx_first_name); Using where
explain select * from employees where first_name = 'Saniya' and last_name = 'Bamford'#range
#Using index condition; Using where; Using MRR
explain select * from employees where first_name = 'Saniya' and last_name like 'Bamfo%'#range
#Using index condition; Using where; Using MRR
explain select * from employees where first_name like 'Sani%' and last_name like 'Bamfo%'#index_merge
#Using intersect(idx_last_name,idx_first_name); Using where
explain select * from employees where first_name = 'Saniya' and last_name = 'Bamford' and birth_date = '1953-09-02'#对于非等值查询的辅助索引,此时该列和不是索引的列查询结果,并无二异.
#ref (使用了idx_first_name)
#Using where
explain select * from employees where first_name = 'Saniya' and last_name != 'Bamford'#all
#Using where
explain select * from employees where first_name != 'Patricio' and last_name != 'Bamford'#all
#Using where
explain select * from employees where first_name != 'Patricio' and last_name is not null#使用OR连接时：
#1.1 当所有的列均为索引,且为等值查询,仍将采用index_merge 进行查询
#1.2 若有一列不是索引,此时将全表扫描;
#1.3 若至少有一列的索引不是等值查询,此时将全表扫描
#index_merge
#Using union(idx_first_name,idx_last_name); Using where
explain select * from employees where first_name = 'Saniya' OR last_name = 'Bamford'#ALL
#Using where
explain select * from employees where first_name = 'Saniya' OR last_name != 'Bamford'#ALL
#Using where
explain select * from employees where first_name = 'Saniya' OR last_name = 'Bamford' OR birth_date = '1953-09-02'
重点总结：
即使在使用了不等值查询、使用了 is null、is not null 、OR， 也不能说该查询一定不走索引(见红色标注部分).
#使用OR连接时：
#1.1 当所有的列均为索引,且为等值查询,仍将采用index_merge 进行查询
#1.2 若有一列不是索引,此时将全表扫描;
#1.3 若至少有一列的索引不是等值查询,此时将全表扫描
#对于非等值查询的辅助索引,此时该列和不是索引的列查询结果,并无二异.
#对于聚集索引：
若聚集索引为等值匹配，只要是AND 连接，那么一定是使用聚集索引，如果是 OR 连接，则按照上述OR 连接规则处理；
若聚集索引不为等值匹配，且其他列均不是索引列或者是非等值查询的辅助索引列，此时将使用聚集索引；
若聚集索引不为等值匹配，且至少存在一个等值查询的辅助索引列，此时将使用该辅助索引。
