Mysql数据库优化—5（索引补充）

1、>  < =导致的索引失效

还是使用刚才的book表

删除其已经存在的所有索引，然后添加新的复合索引

alter table book add index idx_book_at(authorid,typeid);

使用=执行查询

explain select * from book where authorid = 1 and typeid = 2;

两个索引都使用到了。

 

使用>进行查询分析

explain select * from book where authorid > 1 and typeid = 2;

可以看到自身索引和右侧的索引都失效了。

而在最后面使用>，索引都没有失效。

概率问题：

 

2、小结

①、范围查询< > in 等，之后的索引会失效（补救：进行使用索引覆盖）

创建（a,b,c）复合索引，select a,b,c from xxx where a=.. and b=..;尽量将使用的索引覆盖

②、like尽量以常量开头，不要以%开头，否则索引失效

select * from xx where name like "%x%";  --name索引会失效

如果必须使用这种模糊匹配，可以使用索引覆盖挽救一部分：select name from xx where name like "%x%"；name被索引覆盖

③、尽量不要使用类型转换（显示、隐式），否则索引失效

explain select * from teacher where tname = 'abc';

explain select * from teacher where tname = 123;--123在底层会隐式转换为'123'，会导致索引失效

④、尽量不要使用or，否则索引失效

explain select * from teacher where tname='xx' or tcid>1; or会导致tname索引失效

 

3、一些其他的优化方法

①、exist和in

如果主查询的数据集大，使用in，效率高。如果子查询的数据集大，使用exist，效率高。（exist：将主查询的结果放到子查询结果中进行条件校验（如果子查询中包含该数据，校验成功，保留数据））

②、order by

order by使用很容易出现using filesort，其右两种算法：双路排序、单路排序。

对其优化主要是：选择使用单路、双路；调整buffer的容量；避免select * 。。。。;复合索引不要跨列使用，避免using filesort；保证全部的排序字段，排序的一致性（都是升序或者都是降序）