再观看下面这个案例:
# 删除单独的索引 drop index authorid_index on book; drop index typeid_index on 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 ;
结果如下:
结论:复合索引中如果有【>】,则自身和右侧索引全部失效。
在看看复合索引中有【<】的情况:
我们学习索引优化 ,是一个大部分情况适用的结论,但由于SQL优化器等原因 该结论不是100%正确。一般而言, 范围查询(> < in)之后的索引失效。
④ SQL优化,是一种概率层面的优化。至于是否实际使用了我们的优化,需要通过explain进行推测。
# 删除复合索引 drop index authorid_typeid_bid on book; # 为authorid和typeid,分别创建索引 create index authorid_index on book(authorid); create index typeid_index on book(typeid); # 查看执行计划 explain select * from book where authorid = 1 and typeid =2 ;
结果如下:
结果分析:我们创建了两个索引,但是实际上只使用了一个索引。因为对于两个单独的索引,程序觉得只用一个索引就够了,不需要使用两个。
当我们创建一个复合索引,再次执行上面的SQL:
# 查看执行计划 explain select * from book where authorid = 1 and typeid =2 ;
结果如下:
⑤ 索引覆盖,百分之百没问题
⑥ like尽量以“常量”开头,不要以’%'开头,否则索引失效
explain select * from teacher where tname like "%x%" ; explain select * from teacher where tname like 'x%'; explain select tname from teacher where tname like '%x%';
结果如下:
结论如下:like尽量不要使用类似"%x%"情况,但是可以使用"x%"情况。如果非使用 "%x%"情况,需要使用索引覆盖。
⑦ 尽量不要使用类型转换(显示、隐式),否则索引失效
explain select * from teacher where tname = 'abc' ; explain select * from teacher where tname = 123 ;
结果如下:
⑧ 尽量不要使用or,否则索引失效
explain select * from teacher where tname ='' and tcid >1 ; explain select * from teacher where tname ='' or tcid >1 ;
结果如下:
注意:or很猛,会让自身索引和左右两侧的索引都失效。
8、一些其他的优化方法
1)exists和in的优化
如果主查询的数据集大,则使用i关键字,效率高。
如果子查询的数据集大,则使用exist关键字,效率高。
select ..from table where exist (子查询) ; select ..from table where 字段 in (子查询) ;
2)order by优化
IO就是访问硬盘文件的次数。
using filesort 有两种算法:双路排序、单路排序(根据IO的次数)
MySQL4.1之前默认使用双路排序;双路:扫描2次磁盘(1:从磁盘读取排序字段
,对排序字段进行排序(在buffer中进行的排序)2:扫描其他字段)
MySQL4.1之后默认使用单路排序:只读取一次(全部字段),在buffer中进行排序。但种单路排序会有一定的隐患(不一定真的是“单路/1次IO”,有可能多次IO)。原因:如果数据量特别大,则无法将所有字段的数据一次性读取完毕,因此会进行“分片读取、多次读取”。
注意:单路排序 比双路排序 会占用更多的buffer。
单路排序在使用时,如果数据大,可以考虑调大buffer的容量大小:
# 不一定真的是“单路/1次IO”,有可能多次IO set max_length_for_sort_data = 1024
如果max_length_for_sort_data值太低,则mysql会自动从 单路->双路(太低:需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)
提高order by查询的策略:
a.选择使用单路、双路 ;调整buffer的容量大小;
b.避免使用select * …(select后面写所有字段,也比写*效率高)
c.复合索引,不要跨列使用 ,避免using filesort
d.保证全部的排序字段,排序的一致性(都是升序或降序)