MySql教程

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

本文主要是介绍MySQL优化三:order by原理及优化实践,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

目录

前言

建表及准备数据

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

什么是filesort

优化filesort

总结


前言

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条件中已经使用了一个索引,它就只能使用这个索引树的排序规则去排序,索引要想走Index排序必须使用这个索引排序,因为这个索引树就是按照这个索引顺序排序而建立的。否则他就会使用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优化三:order by原理及优化实践的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!