sql调优（三）——索引失效

sql优化之索引失效

我们在自己建立数据库索引时，有时候索引会失效，导致数据库性能下降，那么，我们如何避免索引失效呢，就要先知道什么时候索引失效。并且避免它，对它进行优化，提高数据库性能。

1.不遵守最佳作前缀法导致索引失效

1.1全值匹配：
建立一个多值索引：

-- create index ind_x_name_department_age on employee (name,depart_id,age);

#全值匹配是最好的匹配效果 不会造成索引失效

explain select * from employee where name='孙尚香' and depart_id=1 and age=20;

1.2最佳左前缀法：
建立索引多列时，索引从左列开始，并且不跳过索引的列（顺序可以乱），若跳过第一个字段时，索引失效，来看例子 ：上面表建立了name,depart_id,age的多值索引。全值索引 key_len=73 只用到两个字段 key_len=68 只用到一个字段key_len=63
全值索引时key_len=73：

a.跳过中间一个字段depart_id时：只用到一个字段的索引值（key_len=63 ）：

b.跳过第一个时索引失效（key_len=0）：

c.跳过前俩个时索引失效（key_len=0）：

2.在索引列上做（计算，函数，类型转换）操作导致索引失效

1.使用函数

3.查询范围条件右边索引失效：

当索引出现条件范围时右边的索引列失效：

4.查询条件符号导致索引失效

1.使用（!= ,<,>）无法使用索引，导致全表扫描。

2.使用is not null 无法使用索引，导致全表扫描。

3.or连接时索引失效，导致全表扫描。

4.like 以通配符在最前面（%qq）索引失效，

5.属性字符串不用单引号 ，变成全表扫描。
a.使用了引号，使用到了索引：

b.未使用引号，导致索引失效，变成全表扫描：

那么，如何解决上诉问题呢。使用覆盖索引
覆盖索引：查询的字段与建立索引字段刚好符合,只需扫描索引而无须回表。
1.like 以通配符在最前面（%qq）索引失效，导致全表扫描

2.使用覆盖索引，扫描类型type为index索引扫描，解决like‘+%’导致索引失效问题

只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，极大地减少数据访问量。
2.索引是按照列值顺序存储，对于IO密集的范围查找会比随机读取每一行数据的IO少。