接着上篇《深聊MySQL之：让orderby、Groupby查询速度飞起来(上)》

我们今天继续讨论如何让orderby,groupby 的查询速度起飞 

3、order by 优化

我们了解了order by的原理，

那么我们就来看看，优化order by 有什么技巧。

3.1 添加合适索引

3.1.1 排序字段添加索引

①首先我们看下对 d 字段（没有索引）进行排序的执行计划：

explain select d,id from t1 order by d;

执行结果如下：

发现使用的是 filesort（关注 Extra 字段）。

②再看些对 c 字段（有索引）进行排序的执行计划：

explain select c,id from t1 order by c;

执行结果如下：

可以看到，根据有索引的字段排序，在 Extra 中显示的就为 Using index，表示使用的是索引排序。

注：

如果数据量比较大，显然通过有序索引直接返回有序数据效率更高

3.1.2 多个字段排序优化

问：有时面对的需求是要对多个字段进行排序，而这种情况应该怎么优化或者设计索引呢？

答：如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句

举个例子

①

对 a、c 两个字段进行排序的执行计划

explain select id,a,c from t1 order by a,c;

执行结果如下： 

观察 Extra 字段，发现使用的是 filesort。

②

再看对 a、b（a、b 两个字段有联合索引）两个字段进行排序。

explain select id,a,b from t1 order by a,b;

执行结果如下：

发现使用的是索引排序。 

注：

     多个字段排序的情况，如果要通过添加索引优化，得注意排序字段的顺序与联合索引中列的顺序要一致 

3.1.3 先等值查询再排序的优化

问：我们更多的情况是会先根据某个字段条件查出一部分数据，然后再排序，而这类 SQL 应该如果优化呢？

答：可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。

举个例子

①

表 d1中，根据 a=1000 过滤数据再根据 d 字段排序的执行计划如下：

explain select id,a,d from t1 where a=1000 order by d;

执行结果如下： 

可以在 Extra 字段中看到 “Using filesort”，说明使用的是 filesort 排序。

②

再看下根据 a=1000 过滤数据在根据 b 字段排序的执行计划（a、b 两个字段有联合索引）：

explain select id,a,b from t1 where a=1000 order by b;

执行结果如下：

可以在 Extra 字段中看到“Using index”，说明使用的是索引排序。

3.2 去掉不必要的返回字段

有时，我们其实并不需要查询出所有字段，但是，有的时候，我们习惯性的写出 “select * from table_name”。 

举个例子

①

select * from t1 order by a,b;           /* 根据a和b字段排序查出所有字段的值 */

执行结果如下：

②

select id,a,b from t1 order by a,b;      /* 根据a和b字段排序查出id,a,b字段的值 */

执行结果如下：

通过这两个例子，我们可以看到，

查询所有字段的这条 SQL 是 filesort 排序，

而只查 id、a、b 三个字段的 SQL 是 index 排序。

那么，

为什么查询所有字段会不走索引？

是因为：

扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

3.3 修改参数

一、修改哪些参数？

这里就用到了在前面提到的两个参数：

    max_length_for_sort_data
    sort_buffer_size。

二、如何修改这两个参数的值？

・max_length_for_sort_data：

如果觉得排序效率比较低，可以适当加大 max_length_for_sort_data 的值，让优化器优先选择全字段排序。当然不能设置过大，可能会导致 CPU 利用率过低或者磁盘 I/O 过高；

・sort_buffer_size：

适当加大 sort_buffer_size 的值，尽可能让排序在内存中完成。但不能设置过大，可能导致数据库服务器 SWAP。

4、 group by 优化

默认情况，会对 group by 字段排序，

因此优化方式与 order by 基本一致，
如果目的只是分组而不用排序，可以指定 order by null 禁止排序

5、总结

今天，整理的是 order by 的 一些优化技巧，

因为在写sql中，这两个还是比较常用的，

所以，记住还是有必要的。

关于order by的优化，主要就这么几点知识：

・通过添加合适索引

・去掉不必要的返回字段

・调整参数：主要是 max_length_for_sort_data 和 sort_buffer_size

我是奕然

    51Testing认证讲师

    CSDN博客专家

关注我，带你学习更多更专业的测试开发知识。