mysql有关《索引失效》的原因及解决办法 《最全总结》

我们先来聊聊什么是索引

索引

• 索引（Index）是帮助 MySQL 高效获取数据的数据结构。

可以得到索引的本质：

• 索引是数据结构。
• 可以简单理解为排好序的快速查找数据结构。

索引的优点：

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗

索引的缺点

• 虽然提高了检索的效率，但是降低了更新表的速度，如对表进行insert 、update、delete
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向索引表记录，所以索引列也是占用空间的

索引失效

口诀

• 全职匹配我最爱，最左前缀要遵守；
• 带头大哥不能死，中间兄弟不能断；
• 索引列上少计算，范围之后全失效；
• LIKE百分写最右，覆盖索引无所谓；
• 不等空值还有 OR，索引影响要注意；
• VAR 引号不可丢，SQL 优化有诀窍。

下面我举一个例子来演示索引失效的问题

首先创建表

create table staffs(
    -> id int primary key auto_increment,
    -> name varchar(24) not null default '' comment '姓名',
    -> age int not null default 0 comment '年龄',
    -> pos varchar(20) not null default ''comment '职位',
    -> add_time TIMESTAMP not null default CURRENT_TIMESTAMP comment'入职时间'
    -> )charset utf8 comment '员工记录表';

添加测试数据

 insert into staffs(name,age,pos,add_time)values ('z3',22,'manager',NOW());
 insert into staffs(name,age,pos,add_time)values ('July',23,'dev',NOW());
 insert into staffs(name,age,pos,add_time)values ('2000',23,'dev',NOW());

创建索引

mysql> create index idx_staffs_nap  on staffs(name,age.pos);

查看索引

mysql> show index from staffs;

type 是查询的访问类型。

结果值从最好到最坏依次是（缩略版）：
System>const>eq_ref>ref>range>index>ALL

全职匹配我最爱

带头大哥不能死
第二行sql中第一个索引没有使用，所以type 为最坏类型ALL 并且key为NULL
而第一行的sql中使用了头索引，type为ref

中间兄弟不能断
可以看到第二个sql中key为74 是因为索引中断造成的
而第一行的sql中包含name,age,pos

索引列上少计算

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

可以看到当使用left（name,4） 函数时索引失效

范围之后全失效
可以看到使用" > "后索引类型由ref ->>range
key_len由140–>78
是因为age>24导致索引失效
mysql在执行sql时底层会使用Optimizer（查询优化器）优化语句使之改成explain select * from staffs where name=‘July’ and pos =‘manager’ and age>24 ;
所以也就是为什么age>24在中间位置，而范围后全失效后，key_len是78 （两个索引）而不是一个索引的原因

like百分写最右
“ % ”要写在右面以免造成索引失效

覆盖索引无所谓
解决like’%字符串%’索引不被使用的方法
使用覆盖索引

不等空值还有 OR

1. 不等于
   
2. NULL值
   
   3.少用 or
   

var引号不要丢
因为在mysql底层，会把2000转换为字符类型，进行了一次类型转换

sql优化有诀窍
mysql在执行sql时底层会使用Optimizer（查询优化器）优化语句