MySQL进阶之索引失效分析与优化

概述

随着电商的发展，使用数据库的业务越来越复杂，除了掌握哪些场景可以使用索引，哪些场景适合使用索引，还需要掌握索引在运行过程中的一些使用规则，特别是组合索引的使用。比如索引的优先级，索引失效场景等等，掌握这些规则，可以帮助分析并解决或优化在数据库运行过程中遇到的索引问题。

索引优先级

索引都有效的情况下，如果查询语句中同时有主键索引和辅助索引或多个索引，MySQL会如何执行查找？此种情况下两种索引会如何使用，顺序如何？索引在使用的优先级上，遵循以下规则

索引字段使用等值或全值匹配(=)的索引优先于使用范围匹配(like <>between)的索引
比较类型相同时，主键索引优先于辅助索引
优先级别相同时，出现在语句中的最左边顺序优先

所以，查询语句中有多个索引的情况下，如果等值比较的主键索引优先；如果主键是范围比较，辅助索引是等值比较，则辅助索引优先，否则主键索引优先；多个辅助索引，比较类型相同时，按照出现在语句最左边的顺序优先。

优先的索引将被用于检索数据，剩下的索引，如果是有效索引，将作为索引下推的索引过滤条件(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: constpossible_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: constpossible_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: refpossible_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: ALLpossible_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: rangepossible_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: rangepossible_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: ALLpossible_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: indexpossible_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的索引字段呢？看下面分析。

# 主键idmysql> explain select * from tuser where id is not null \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tuser   partitions: NULL         type: ALLpossible_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: NULLpossible_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: ALLpossible_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: refpossible_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: ALLpossible_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: rangepossible_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: indexpossible_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: ALLpossible_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: refpossible_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

索引字段使用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: rangepossible_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: ALLpossible_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: ALLpossible_keys: idx_name_age_sex          key: NULL      key_len: NULL          ref: NULL         rows: 7     filtered: 16.03        Extra: Using where