随着电商的发展，使用数据库的业务越来越复杂，除了掌握哪些场景可以使用索引，哪些场景适合使用索引，还需要掌握索引在运行过程中的一些使用规则，特别是组合索引的使用。比如索引的优先级，索引失效场景等等，掌握这些规则，可以帮助分析并解决或优化在数据库运行过程中遇到的索引问题。
索引都有效的情况下，如果查询语句中同时有主键索引和辅助索引或多个索引，MySQL会如何执行查找？此种情况下两种索引会如何使用，顺序如何？索引在使用的优先级上，遵循以下规则
所以，查询语句中有多个索引的情况下，如果等值比较的主键索引优先；如果主键是范围比较，辅助索引是等值比较，则辅助索引优先，否则主键索引优先；多个辅助索引，比较类型相同时，按照出现在语句最左边的顺序优先。
优先的索引将被用于检索数据，剩下的索引，如果是有效索引，将作为索引下推的索引过滤条件(index filter)对数据进行过滤，否则将作为一般的WHERE条件和其它WHERE条件一起在Server层对数据进行过滤
组合索引是由多个字段组成的索引，MySQL会按照创建的顺序使用或匹配索引。在一棵索引树上，多个字段形成索引，效率高、省空间、容易形成覆盖索引，即查询结果均在索引上，直接作为结果返回，不需要再回表查询一次。使用组合索引，遵循最左前缀原则：
前缀索引
比如格式[索引字段 like 常量%]，前面的索引字段和like本身的索引字段有效，但like后面的索引字段将失效。
比如格式[索引字段 like %常量]，将不使用对应的组合索引。在结果字段、排序或分组字段形成覆盖索引的情况下，使用[索引字段 like %常量]，执行计划会显示使用index索引，但其实和like无关，显示index是因为覆盖索引的原因，注意区分。
最左前缀
即按照创建顺序使用，从左向右匹配直到遇到范围查询(like、>、<、<>或!=、between) 组合索引失效，即该字段后面的字段即使是组合索引中的字段也同样使用不到索引。
在InnoDB引擎中，如果使用辅助索引使用范围查询且结果大于总数据记录三分之一时，如果没有其它的有效索引，MySQL将丢弃辅助索引而使用全表扫描。在查看执行计划信息时，要注意。
如果mysql认为使用全表扫描要比使用索引快时，则不会使用到索引，索引也会失效。
除了以上两种失效情况，下面通过使用EXPLAIN命令分析SQL语句执行计划，总结了大概10条使用规则应用及其失效的情况。下面先创建用于示例说明数据表、索引、数据等。
# 用户表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
# 部门表
create table tdep(
id int primary key,
name varchar(100)
);
# 创建普通索引
mysql> alter table tuser add index idx_dep(dep);
# 创建唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
# 创建组合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
# 非唯一单列索引
create index index_name on tdep (name);
# 分别新增1条部门和用户数据
mysql> insert into tdep values (1,'技术部');
mysql> insert into tuser (id,loginname,name,age,sex,dep,address) values(1,'fdcoffee','蕃豆咖啡',36,'M',1,'广东广州市环市中路371号');
即索引列使用=进行比较。等值匹配，优先级高于其它操作符。主键索引使用范围匹配，辅助索引使用等值匹配，则优先使用辅助索引；如果都是等值匹配时，则主键索引优先辅助索引。
# 主键索引列全值匹配
mysql> explain select * from tuser where id = 1 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
# 唯一辅助索引列全值匹配
mysql> explain select * from tuser where loginname = 'fdcoffee' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: const
possible_keys: idx_loginname
key: idx_loginname
key_len: 303
ref: const
rows: 1
filtered: 100.00
Extra: NULL
# 辅助索引列全值匹配，遵循最左前缀原则
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ref
possible_keys: idx_name_age_sex
key: idx_name_age_sex
key_len: 312
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
组合索引中，需要按照最左前缀原则使用索引字段才会有效：带头索引不能丢，中间索引不能断
# 正确示例
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 G;
mysql> explain select * from tuser where name = 'fdcoffee' G;
# 错误示例
# 缺少带头索引name，剩下的age和sex字段都无法使用索引
mysql> explain select * from tuser where age = 36 and sex = 'M' G;
# 同上，没有前面的name和age字段一起，sex字段无法使用到索引
mysql> explain select * from tuser where sex = 'M' G;
# 缺少中间索引age,只能使用到部分索引：name字段有效，但sex字段无法用到索引
mysql> explain select * from tuser where name = 'fdcoffee' and sex = 'M' G;
不要在索引列上进行计算操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描
# 使用left函数对loginname长度截取，索引失效
mysql> explain select * from tuser where left(loginname,4) = 'fdco' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
# 上面的示例可以这样优化：使用 like% 或在程序上先对loginname做处理在传入MySQL数据库查询
mysql> explain select * from tuser where loginname like 'fdco%' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: range
possible_keys: idx_loginname
key: idx_loginname
key_len: 303
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
不能继续使用索引中范围条件(between、<、>、in等)右边的列
# 由于age使用范围操作符，后面的sex字段索引失效
mysql> explain select * from tuser where name = 'fdcoffee' and age > 20 and sex = 'M' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: range
possible_keys: idx_name_age_sex
key: idx_name_age_sex
key_len: 308
ref: NULL
rows: 1
filtered: 14.29
Extra: Using index condition
尽量使用覆盖索引(select 索引列)，也就是查询列和索引列一致，减少使用select *
# 使用select *全表扫描
mysql> explain select * from tuser G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: NULL
# 使用select 索引列时，扫描索引，不需要回表
mysql> explain select name,age,sex from tuser G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: index
possible_keys: NULL
key: idx_name_age_sex
key_len: 312
ref: NULL
rows: 7
filtered: 100.00
Extra: Using index
索引字段上使用不等(!=或者<>)判断时，会导致索引失效而转向全表扫描
mysql> explain select * from tuser where name != 'fdcoffee' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL # 全表扫描
possible_keys: idx_name_age_sex
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
索引字段上使用is null/is not null判断会走全表扫描。允许为null的索引字段呢？看下面分析。
# 主键id
mysql> explain select * from tuser where id is not null G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 85.71
Extra: Using where
# 由于主键不允许为空，使用is null，执行计划信息Extra列将提示Impossible WHERE信息
mysql> explain select * from tuser where id is null G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Impossible WHERE
# 允许为空的索引
# 使用is not null将走全表扫描
mysql> explain select * from tuser where loginname is not null G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: idx_loginname
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
# 而is null则用到了索引
mysql> explain select * from tuser where loginname is null G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ref
possible_keys: idx_loginname
key: idx_loginname
key_len: 303
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
索引字段使用like以通配符开头(%字符串)时，会导致索引失效而转向全表扫描 。尽量以(字符串%)方式写语句。如果一定要使用%开头，业务满足的话，可以配合覆盖索引进行一定优化
# 以%开头，索引失效，变成全表扫描
mysql> explain select * from tuser where name like '%fdco' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where
# 以%结尾，可以使用到索引
mysql> explain select * from tuser where name like 'fdco%' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: range
possible_keys: idx_name_age_sex
key: idx_name_age_sex
key_len: 303
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
# 如果业务上只需要返回索引中的字段，则可以将*改为索引字段，形成覆盖索引，可以提升性能
mysql> explain select name,age,sex from tuser where name like '%fdcofd' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: index
possible_keys: NULL
key: idx_name_age_sex
key_len: 312
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where; Using index
索引字段是字符串，但查询时不加单引号，形成隐式类型转换，相当于使用了函数进行计算，会导致索引失效而转向全表扫描
# 字符串类型的字段，传入一个数字类型，导致索引失效全表扫描
mysql> explain select * from tuser where name = 7 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: idx_name_age_sex
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where
# 数字类型的age可以传入字符串的数字，索引有效，但不建议这样去使用
mysql> explain select * from tuser where name = 'ccc' and age = '7' and sex = 'M' G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ref
possible_keys: idx_name_age_sex
key: idx_name_age_sex
key_len: 312
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
索引字段使用or时，会导致索引失效（这个似乎MySQL有做优化，5.7版本测试可以使用索引，后面再详细测试）
# 5.7版本测试下，同一个索引字段用or时可以使用到索引的
mysql> explain select * from tuser where loginname = 'fd' or loginname = 'coffee' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: range
possible_keys: idx_loginname
key: idx_loginname
key_len: 303
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
#5.7版本测试下，不同索引字段用or则变成全表扫描
mysql> explain select * from tuser where name='fdcoffee' or age = 7 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: idx_name_age_sex
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 26.53
Extra: Using where
# 同上
mysql> explain select * from tuser where name='fdcoffee' and age = 7 or sex = 'M' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: ALL
possible_keys: idx_name_age_sex
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 16.03
Extra: Using where
优化索引，其实就是要遵守索引的使用规则，然后避免出现导致索引失效的情况。通过EXPLAIN可以发现索引失效的情况，然后按照使用规则纠正即可。下面是流传的优化口诀，简单实用易记。
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。
京公网安备 11010802035086号