聊聊優化sql中order By語句的方法

如何最佳化sql中的orderBy語句？以下這篇文章為大家介紹一下優化sql中orderBy語句的方法，具有很好的參考價值，希望對大家有幫助。

在使用資料庫進行資料查詢時，難免會遇到基於某些欄位對查詢的結果集進行排序的需求。在sql中通常使用orderby語句來實作。將需要排序的欄位放到 該關鍵字後，如果有多個欄位的話，就用","分割。

select * from table t order by t.column1,t.column2;

上面的sql表示查詢表table中數據，然後先按照column1排序，如果column1相同的話，在按照column2排序，排序的方式默認是降序。當然排序方式也是可以指定的。在被排序欄位後加上 DESC,ASE,分別表示降序和升序。

使用該orderby可以很方便的實作日常的排序操作。使用的多了，不知道你有沒有遇過這種場景：有時候使用orderby後，sql執行效率非常慢，有時候卻比較快，由於整天被curd纏身，也沒有時間研究，反正就是覺得很神奇。趁這個週末比較閒，就來研究下，mysql中orderby是怎麼實現的。

為了方便描述，我們先建立一個資料表t1，如下：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL not null auto_increment,
  `a` int(11)  DEFAULT NULL,
  `b` int(11)  DEFAULT NULL,
  `c` int(11)  DEFAULT NULL,
  PRIMARY KEY (`id`) ,
  KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB;

並插入資料：

insert into t1 (a,b,c) values (1,1,3);
insert into t1 (a,b,c) values (1,4,5);
insert into t1 (a,b,c) values (1,3,3);
insert into t1 (a,b,c) values (1,3,4);
insert into t1 (a,b,c) values (1,2,5);
insert into t1 (a,b,c) values (1,3,6);

為了使索引生效，插入10000行7,7, 7，無關數據，數據量少的情況下，會直接全表掃描

insert into t1 (a,b,c) values (7,7,7);

我們現在需要查找a=1的所有記錄，然後按照b字段進行排序。

查詢sql為

select a,b,c from t1 where a = 1 order by b limit 2;

為了防止在查詢過程中全表掃描，我們在欄位a上新增了索引。

首先我們先透過語句

explain select a,b,c from t1 where a = 1 order by b lmit 2;

查看sql的執行計劃，如下所示：

整個sql的執行的過程如下：

RowId 排序

在全字段排序实现中，排序的过程中，要把需要输出的字段全部放到sort_buffer中，当输出的字段比较多的时候，可以放到sort_buffer中的数据行就会变少。也就增大了sort_buffer无法容纳数据的风险，直至出现基于文件的排序。

rowId排序对全字段排序的优化手段，主要是减少了放到sort_buffer中字段个数。

在rowId排序中，只会将需要排序的字段和主键Id放到sort_buffer中。

select a,b,c from t1 where a = 1 order by b limit 2;

在rowId的排序中的执行流程如下：

1.初始化并创建sort_buffer，并确认要放入的的字段，id和b。

2.从索引树a中找到第一个满足a=1的主键id，也就是id=1。

3.回表主键索引id，取出整行数据，从整行数据中取出id和b，存入sort_buffer中。

4.从索引a中取出下一条满足a=1的 记录的主键id。

5.重复步骤3和4，直到最后一个满足a=1的主键id，也就是a=6。

6.对sort_buffer中的数据，按照字段b排序。

7.从sort_buffer中的有序数据集中，取出前2个，因为此时取出的数据只有id和b，要想获取a和c字段，需要根据id字段，回表到主键索引中取出整行数据，从整行数据中获取需要的数据。

根据rowId排序的执行步骤，可以发现：相比全字段排序，rowId排序的实现方式，减少了存放到sort_buffer中的数据量，降低了基于文件的外部排序的可能性。

那rowid排序有不足的地方吗？肯定有的，要不然全字段排序就没有存在的意义了。rowid排序不足之处在于，在最后的步骤7中，增加了回表的次数，不过这个回表的次数，取决于limit后的值，如果返回的结果集比较小的话，回表的次数还是比较小的。

mysql是如何在全字段排序和rowId排序的呢？其实是根据存放的sort_buffer中每行字段的长度决定的，如果mysql认为每次放到sort_buffer中的数据量很大的话，那么就用rowId排序实现，否则使用全字段排序。那么多大算大呢？这个大小的阈值有一个变量的值来决定，这个变量就是 max_length_for_sort_data。如果每次放到sort_buffer中的数据大小大于该字段值的话，就使用rowId排序，否则使用全字段排序。

orderby的优化

上面讲述了orderby的两种排序的方式，以及一些优化策略，优化的目的主要就是避免基于磁盘文件的外部排序。因为基于磁盘文件的排序效率要远低于基于sort_buffer的内存排序。

但是当数据量比较大的时候，即使sort_buffer比较大，所有数据全部放在内存中排序，sql的整体执行效率也不高，因为排序这个操作，本身就是比较消耗性能的。

试想，如果基于索引a获取到所有a=1的数据，按照字段b，天然就是有序的，那么就不用执行排序操作，直接取出来的数据，就是符合结果的数据集，那么sql的执行效率就会大幅度增长。

其实要实现整个sql执行过程中，避免排序操作也不难，只需要创建一个a和b的联合索引即可。

alter table t1 add index a_b (a,b);

添加a和b的联合索引后，sql执行流程就变成了：

1.从索引树(a,b)中找到第一个满足a=1的主键id，也就是id=1。

2.回表到主键索引树，取出整行数据，并从中取出a，b，c，直接作为结果集的一部分返回。

3.从索引树(a,b)上取出下一个满足a=1的主键id。

4.重复步骤2和3，直到找到第二个满足a=1的主键id,并回表获取字段a，b，c。

此时我们可以通过查看sql的执行计划，来判断sql的执行过程中是否执行了排序操作。

explain select a,b from t1 where a = 1 order by b lmit 2;

通过查看执行计划，我们发现extra中已经没有了using filesort了，也就是没有执行排序操作了。

其实还可以通过覆盖索引，对该sql进一步优化，通过在索引中覆盖字段c，来避免回表的操作。

alter table t1 add index a_b_c (a,b,c);

添加索引a_b_c后，sql的执行过程如下：

1.从索引树(a,b,c)中找到第一个满足a=1的索引，从中取出a，b，c。直接作为结果集的一部分直接返回。

2.从索引(a,b,c)中取出下一个，满足a=1的记录作为结果集的一部分。

3.重复执行步骤2，直到查到第二个a=1或者不满足a=1的记录。

此时通过查看执行sql的的还行计划可以发现 extra中只有 Using index。

explain select a,b from t1 where a = 1 order by b lmit 2;

總結

透過對該sql的多次最佳化，sql的最終執行效率和沒有排序的普通sql的查詢效率基本上是一樣的。之所以可以避免orderby的排序操作，就是利用了索引天然有序的特性。

但我們都知道，索引可以加快查詢的效率，但是索引的維護成本比較大，對資料表中資料的新增和修改都會涉及索引的變動，所以索引也不是越多越好，有時候，並不能因為一些不常用的查詢和排序，而增加了過多的索引，得不償失。

【相關推薦：mysql影片教學】

以上是聊聊優化sql中order By語句的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！