前言
排序和分组也是在SQL 中经常用到的知识点,那么这两个家伙该怎么优化呢?
其实它们和 where 后面的查询是一样的,本文进行简单描述。
-
order by 为排序使用索引
1、MySQL两种排序方式:文件排序(filesort)或扫描有序索引(index)排序
2、MySQL 能为排序与查询使用相同的索引举例:
创建的索引为: create index idx_a_b_c on test_A(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 order by c -- where a = const and b > const order by b,c 注意:b>const 范围后的索引失效了,但是 order by 排序的时候 ,是根据 b,c 来排序的,前面的 a 为一个常量,无须排序,所以,依然可以使用索引
反例:
# 不能使用索引进行排序 -- order by a asc,b desc,c desc (排序顺序不一样,就和跳楼机一样,一会上一会下,将索引的顺序打乱了) -- where x = const order by b,c (丢失 a 索引,带头大哥没了) -- where a = const by c (丢失 b 索引,中间兄弟断了) -- where a = const order by a,d (d并不是索引的一部分) -- where a in (...) order by b,c (对于排序来说,多个相等条件也是范围查询) ```
-
group by 关键字优化
group by 实质是先排序后进行分组,遵照索引建的最佳左前缀,同样可以使用索引 当无法使用索引列,增大max_length_for_sort_data 参数的设置、增大sort_buffer_size 参数的设置 where高于 having,能写在 where 限定的条件就不要去 having 限定
-
优化口诀
记牢下面的优化口诀,让自己写出一手性能高效的 SQL 语句!!全值匹配我最爱,最左前缀要遵守; 带头大哥不能死,中间兄弟不能断; 索引列上少计算,范围之后全失效; Like百分写最右,覆盖索引不写星; 不等空值还有or,索引失效要少用; VAR引号不可丢,SQL高级也不难!