mysql 索引失效 笔记

记录一下索引在哪些情况下会失效

必要知识:

EXPlAIN :模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句,分析你的查询语句或是表结构的性能瓶颈。

  • 可以查看到的信息:
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

使用方式:Explain + Sql语句

explain select * from tbl_user;

字段解读:

  • id:select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
    • id相同从上倒下
    • id不同id值越大优先级越高
  • select_type:
    • simple:简单的select查询,查询中不包含子查询或者union
    • primary:查询中若包含任何复杂的字部分,最外层查询则被标记为primary
    • subquery:在select或where中包含了子查询
    • derived:在from列表中包含子查询被标记为derived,mysql会递归执行这些子查询,把结果放在临时表里。
    • union:若第二个select出现在union之后,则被标记为union。若union包含在from子句的子查询中,外层select将被标记为deriver
    • union result:从union表获取结果的select
  • partitions:
  • type:
    • all:全表遍历
    • index:index与all区别为index类型只遍历索引树,这通常比all快,因为索引文件通常比数据文件小
    • range:只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般出现在between 、<、>、in等查询这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,在到某一点结束,不用扫描全部索引。
    • ref:非唯一性所擒扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个复合条件的行,所以他应该属于查找和扫描的混合体。
    • er_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
    • system:system表只有一行记录(等于系统表),这事const类型的特例,平时不会出现,这个可以忽略
    • const:表示通过索引一次就找到了,const用于比较primary key或unique索引,因为知匹配一行数据,所以很快,如将主键作为where条件。
  • possible_keys:显示可能应用在此次查询的索引,一个或多个,查询涉及到的字段上若存在索引则索引被列出,但不一定被查询实际使用
  • key:实际使用的索引,如果为null则没有使用索引,查询中若使用了覆盖索引,则该索引仅出现在key列表中。
  • key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,它不损失精度的情况下,长度越短越好。显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
  • ref:显示索引的那一列被使用了,如果可能的话,是一个常数,哪些列被用于查找索引上的值。
  • rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录需要读取的行数
  • extra:包含不适合在其他列中宣誓但十分重要的额外信息
    • Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为文件排序
    • Using where:使用了where过滤
    • Using temporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by
    • Using join buffer:标明使用了连接缓存
    • Using index
      • 表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率较高
      • 如果同时出现了using where,标明索引被用来执行索引键值的查找
      • 如果没有同时出现using where 表明索引用来读取数据而非执行查找动作
      • 覆盖索引
        • select的数据列只从索引中就能够取得,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不比根据索引再次读取数据文件,换句话说查询列被所建的索引覆盖。
    • impossible where:where子句的值总是false,不能用来获取任何元组
    • select tables optimized away:在没有group by子句的情况下,基于索引优化min max操作或者对于myisam存储引擎优化count(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化。
    • distinct:优化distince操作,在找到第一匹配的元组后即停止找同样值的动作。

一.索引创建顺序与使用顺序

create table sql:

create table test1
(
    id        int primary key auto_increment,
    firstName varchar(20) not null,
    lastName  varchar(20) not null,
    age       int         not null,
    idNumber varchar(20) not null
);

未创建索引 select sql:

explain select *
from test1
where firstName = 'a'
  and lastName = 'b'
  and age = 1;

result:

主要字段:

type:all

创建索引:

create index idx_firstName_lastName_age on test1 (firstName, lastName, age)

再次查询:

explain select *
from test1
where firstName = 'a'
  and lastName = 'b'
  and age = 1;

result:

主要字段:

type:ref

possible_keys: idx_firstName_lastName_age

key:idx_firstName_lastName_age

key_len:128

reft:const,const,const

将firstName去掉(头部索引):

explain
select *
from test1
where lastName = 'b'
  and age = 1;

result(索引失效):

主要字段

type: all

总结:创建的联合索引失去第一个索引导致索引失效

将lastName去掉(中间索引):

explain
select *
from test1 where   firstName='1' and age=1;

 主要字段:

type:ref

possible_keys:idx_firstName_lastName_age

key:idx_firstName_lastName_age

key_len:62

总结:未使用全部索引 key_len 精度丢失

解决方案使用覆盖索引:

explain select lastName,age from test1 where lastName='' and age=1;

二.在索引列上做计算、函数、类型转换(索引失效)

explain select * from test1 where firstName='1';
explain  select * from test1 where length(firstName)='1';
explain  select * from test1 where firstName=1;
explain  select * from test1 where firstName+''='1';

 result:

 

三.存储引擎不能使用索引中范围条件右边的列(索引失效)

需要修改索引:

drop index idx_firstName_lastName_age on test1;

create index idx_firstName_lastName_age on test1 (firstName, age, lastName);
explain select * from test1 where firstName='1';


explain select * from test1 where firstName='1' and age=100;


explain select * from test1 where firstName='1' and age=100 and lastName='1';


explain select * from test1 where firstName='1' and age<100 and lastName='1';


结论:
select * from test1 where firstName='1' and age=100; 与 explain select * from test1 where firstName='1' and age<100 and lastName='1'; 使用了同样的key_len精度 而后面的 lastName 并没有用到。

四.!=  or <>

explain select * from test1 where firstName='1' and age=1 and lastName='1';

explain select * from test1 where firstName<>'1' and age=1 and lastName='1';

五.使用 or

explain select * from test1 where firstName='1' and lastName='1' and age=1;


explain select * from test1 where firstName ='1' or lastName='1' and age=1;

六.like 以通配符开头 '%abd' 会导致索引失效

explain select * from test1 where firstName='1';

explain select * from test1 where firstName like '%1';

explain select * from test1 where firstName like '1%';

解决方案:覆盖索引

explain select firstName from test1 where firstName like '%1';

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读