Mysql-索引失效 order by优化
Mysql-索引失效 order by优化
索引失效
-
最佳左前缀法则
如果索引了多列 要遵循最佳左前缀法则 指从查询索引的最左前列开始并且不跳过索引中的列
-
不在索引列上做任何操作 会导致索引失效而转向全表扫描
-
不能使用索引中范围条件右边的列
-
尽量使用覆盖索引
-
在使用不等于(!=或者<>)的时候 无法使用索引会导致全表扫描
-
is null 和is not null也无法使用索引
-
lile以通配符开头 会造成索引失效变成全表扫描
百分号开头的情况下 会造成索引失效
如果必须得用百分开头 如%abc% 这种情况 则可以利用覆盖索引解决索引失效的问题
-
字符串不加单引号 会导致索引失效
-
小结
-
练习1
-
练习2
只用c1 一个字段索引 但是c2 c3 用于排序
-
练习3
第二个sql c1 c2 用于索引 c2 和 c3 用于排序
-
练习4
order by关键字优化
-
上手案例
-
1.where条件限制,order by 2 字段(2字段为已建立组合索引字段,并按照组合索引的顺序排序),索引生效
-
2.where条件限制,order by 2 字段(2字段为已建立组合索引字段,但排序的顺序和组合索引的顺序不一致),出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY name ,deptId;
-
3.where条件限制,order by 2 字段(其中某一字段为非组合索引字段),出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId ,empno;
-
4.where条件限制,where and条件的值确定,排序条件中有该定值字段,即使order by后字段顺序和组合索引的顺序不一致(排序字段去除定值字段后剩余字段后组合索引顺序一致),此时不会出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId,age;
-
5.order by后跟的排序字段是desc和asc 组合,不论排序顺序是否和组合索引顺序一致,必然会出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId DESC,name ASC;
-
order by索引对比
1)无过滤条件(无where和limit)的order by 必然会出现 Using filesort
2)过滤条件中的字段和order by 后跟的字段的顺序不一致,必然会出现 Using filesort
3)order by后跟的字段排序即有DESC也有ASC,必然会出现Using filesort
4)where条件的值确定,且order by后跟了跟了where条件的排序字段(order by 字段去除定值字段后剩余单字段),即使order by后跟的字段和组合索引字段顺序不一致,也不会出现Usi -
order by索引建议
1)尽量在索引列上完成排序操作,
遵循最佳做前缀法则
2)order by子句,尽量使用index方式排序,避免使用filesort方式 -
无索引 order by的索引建议
双路排序:扫描2次磁盘获取最终数据,第一次扫描读取行指针和order by字段列的值进行排序,刷选出需要的排完序的行指针,第二次扫描读取所需的全部数据
单路排序:从磁盘中读取查询所需的全部列,在buff中进行排序,排序后进行输出,只需要扫描一次磁盘
问题:双路排序相比单路排序会减少I/O次数,但会消耗更多的内存,如果取出的数据总大小超出sort_buffer的容量,会创建temp文件进行多路合并,反而会增加I/O次数,同理双路排序也会出现同样的问题,但单路排序的相对几率要高很多
优化:
1)增大sort_buffer_size
2)增大max_length_for_sort_data
3)减少select后跟的查询字段 -
小结
key a b c(a,b,c) order by能使用索引最左前缀 - order by a - order by a,b - order by a,b,c - order by a DESC,b DESC,c DESC 如果where使用了索引的最左前缀定义为常量 则order by能使用索引 - WHERE a=const order by b,c - WHERE a=const AND b=const ORDRE BY c - WHERE a=const AND b>const ORDER BY b,c 不能使用索引进行排序 - ORDERE BY a ASC,b DESC,c DESC 排序不一致 - WHERE g=const ORDER BY b,c 丢失a索引 - WHERE a=const ORDER BY C 丢失b索引 - WHERE a=const ORDER BY d d不是索引的一部分