MySQL优化三：order by原理及优化实践

前言

order by排序是我们在MySQL中查询数据时非常常用的语句，本篇文章我们将了解order by的执行过程以及如何优化，在此之前，大家需要对索引有所了解，不了解的朋友可以参考以前写的文章

MySQL优化一：索引原理 
 MySQL优化二：索引B+树能放多少索引指针？能放多少数据

建表及准备数据

CREATE TABLE `t1`  (
  `id` int(11) NOT NULL,
  `c1` int(11) NULL DEFAULT NULL,
  `c2` int(11) NULL DEFAULT NULL,
  `c3` int(11) NULL DEFAULT NULL,
  `c4` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_c1c2c3`(`c1`, `c2`, `c3`) USING BTREE,
  UNIQUE INDEX `uk_c4`(`c4`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (1, 1, 1, 1, 4);
INSERT INTO `t1` VALUES (2, 2, 1, 1, 5);
INSERT INTO `t1` VALUES (3, 2, 1, 2, 6);
INSERT INTO `t1` VALUES (4, 2, 2, 2, 7);
INSERT INTO `t1` VALUES (5, 2, 2, 3, 8);
INSERT INTO `t1` VALUES (6, 2, 3, 3, 9);
INSERT INTO `t1` VALUES (7, 4, 4, 3, 10);
INSERT INTO `t1` VALUES (8, 4, 4, 4, 11);
INSERT INTO `t1` VALUES (9, 5, 3, 4, 12);
INSERT INTO `t1` VALUES (10, 6, 4, 4, 13);

根据查询case分析order by的使用

这边先放一个idx_c1c2c3二级索引的一张图，方便对下面的查询的理解有所帮助

case1：  

这里出现了一个问题，我们发现这个SQL查询走的是全表扫描，没有走idx_c1c2c3这个二级索引树，这个是什么原因呢？

这里通过上面二级索引的图解释一下，按照c>=1条件查询会得到叶子节点中除了1 1 1数据外的所有数据，然后拿到主键key去聚簇索引回表拿所有的数据，这个过程其实比全表扫描所要的I/O成本更多，所以MySQL这里就走了全表扫描。既然走了全表扫描那么排序规则就成了Using filesort。按照这个说法，若是减少查询的范围或者直接使用覆盖索引不去回表那么MySQL还是会走idx_c1c2c3这个二级索引树的，请看下面的case演示。

case2：           

               

这个case中将c>1改成c>2减少了查询范围之后就走了二级所引树，使用了覆盖索引之后也依然走了二级索引树，同时排序走了Index排序。所谓二级索引就是上面图中叶子节点存的idx_c1c2c3索引值和主键值。这两个case只是解释了当where条件是二级索引时会走全表扫描的情况，算是一个意外收获吧。下面的case我们将演示order by将会在什么情况下走Index，什么情况下走filesort。

case3：

             

当使用一个二级索引查询或者聚簇索引查询时，排序仍使用这个索引排序时就会使用Index排序，注意当查询和排序不是一个索引时就会使用filesort，因为在一次查询中只能使用走一个索引，where条件中已经使用了一个索引，MySQL会在这个索引的B+树上查询，要想走Index排序order by条件必须为这个索引，因为这个索引树就是按照这个索引顺序排序而建立的。否则他就会使用filesort，拿到数据后然后再比较排序。

case4：

              

              

              

oder by后面的顺序要按照idx_c1c2c3索引建立的字段顺序出现，要符合最左前缀原则，并且排序的降序或升序要一致才会使用Index排序。按照上面索引图的理解，索引的建立就是按照c1 c2 c3大小顺序排的，先比较c1大小，c1相同然后比较c2,c2相同然后比较c3,若是少了c1的话MySQL就不能根据索引的建立顺序直接输出了而是要重新排序再输出。

case5：

                                   

 当添加了where条件时，where条件的字段和order by的字段也要符合最左前缀才能走Index排序。注意当where条件是范围查询时，就破坏了最左前缀，范围条件后的order by字段就会失效就比如case5的图二，此时order by若是依然按照idx_c1c2c3索引的最左前缀还是会使用Index排序的如图一。这个可以使用上图的索引树来理解，就比如c1>2的查询条件那就会拿到4 4 3 ，4 4 4， 5 3 4， 6 4 4，若此时使用c1,c2排序此时直接可以输出，因为索引树就是这样排序的嘛，如果使用c2排序的话4 4 3和5 3 4根本就不符合这个索引树的顺序所以还是会再排序然后输出。同样比如c1=2那就会拿出2 1 1， 2 1 2， 2 2 2， 2 2 3， 2 3 3那么可以直接order by c2就可以直接输出了。

什么是filesort

这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。

文件排序是通过相应的排序算法,将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在sort buffer进行排序，再从磁盘取其他字段。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：是一次性取出满足条件行的所有字段，按照order by列在sort buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

.单路排序出现的问题

当读取数据超过sort_buffer的容量时，就会创建tmp文件进行合并排序，反而增加更多的I/O。

优化filesort

当无法避免排序操作时，又该如何来优化呢？很显然，应该尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作，很大幅度地提高排序工作的效率。

1.增大max_length_for_sort_data参数的设置 （默认4k）。

  这个参数是决定使用多路排序算法还是单路排序算法的，当select的一行字段长度小于这个字段的长度时而且不是text|blob时就会使用单路排序，反之使用多路排序，虽然增大这个参数会增加使用改进算法的概率，但是如果设置的太高那么放进sort_buffer_size的字段就会增多，这样sort_buffer_size超出的概率就会增大反而会创建出更多的tmp文件排序，进而造成更多的I/O

2.增大 sort_buffer_size 参数设置

不管哪一种排序算法，提高这个参数设置都会提高效率，但是要根据系统能力去设置，因为这个buffer区是每个线程都有的

3..在使用order by时，尽量不要用select *，只查询所需的字段。

3.1 因为当查询字段过多时，会导致sort_buffer不够，从而创建tmp文件排序，造成更多的I/O。

3.2 select的字段长度小于max_length_for_sort_data的话就会增加使用优化算法的概率。

总结

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序即查询到数据直接输出。index效率高，filesort效率低。
• ilesort 并不是说通过磁盘文件进行排序，而是在设置的sort_buffer_size内存中排序，有两种排序算法：双路排序和单路排序。
• order by满足下面情况会使用Using index。
  1.order by语句使用索引最左前列。
  2.使用where子句与order by子句条件列组合满足索引最左前列。遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
  具体解释：当where出现范围查询时范围条件后的order by条件的最左前缀失效，此时若想还是Index排序，只能使order by条件符合索引最左前缀遵循索引的顺序从而直接输出。当where条件为等值条件时就可以和order by一起遵循最左前缀，因为这样也遵循索引的顺序。
         
• 在使用order by时，尽量不要用select *，只查询所需的字段，这样可以增加使用单路排序的概率，同时也会减少导致sort_buffer不够，从而创建tmp文件排序，造成更多的I/O的概率。
• 能用覆盖索引尽量用覆盖索引，这样可以避免回表，也可以避免全表扫描，可以较少I/O。