江苏龙网

  1. 主页 > 生活百科 > >

MySQL索引失效分析与优化

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: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: constpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL# 唯一辅助索引列全值匹配mysql> explain select * from tuser where loginname = 'fdcoffee' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: constpossible_keys: idx_loginnamekey: idx_loginnamekey_len: 303ref: constrows: 1filtered: 100.00Extra: NULL# 辅助索引列全值匹配，遵循最左前缀原则mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: refpossible_keys: idx_name_age_sexkey: idx_name_age_sexkey_len: 312ref: const,const,constrows: 1filtered: 100.00Extra: NULL


推荐阅读


上一篇：库存管理系统有哪些功能 淘宝卖家仓库管理软件哪个好

下一篇：淘宝店铺拉新 淘宝拉新怎么做