对orderby的理解

　　前言
　　日常开发中，我们经常会使用到orderby，亲爱的小伙伴，你是否知道orderby的工作原理呢？orderby的优化思路是怎样的呢？使用orderby有哪些注意的问题呢？本文将跟大家一起来学习，攻克orderby
　　一个使用orderby的简单例子
　　假设用一张员工表，表结构如下：CREATETABLEstaff（idBIGINT（11）AUTOINCREMENTCOMMENT主键id，idcardVARCHAR（20）NOTNULLCOMMENT身份证号码，nameVARCHAR（64）NOTNULLCOMMENT姓名，ageINT（4）NOTNULLCOMMENT年龄，cityVARCHAR（64）NOTNULLCOMMENT城市，PRIMARYKEY（id），INDEXidxcity（city））ENGINEINNODBCOMMENT员工表；
　　表数据如下：
　　我们现在有这么一个需求：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。对应的SQL语句就可以这么写：selectname，age，cityfromstaffwherecity深圳orderbyagelimit10；
　　这条语句的逻辑很清楚，但是它的底层执行流程是怎样的呢？
　　orderby工作原理
　　explain执行计划
　　我们先用Explain关键字查看一下执行计划
　　执行计划的key这个字段，表示使用到索引idxcityExtra这个字段的Usingindexcondition表示索引条件Extra这个字段的Usingfilesort表示用到排序
　　我们可以发现，这条SQL使用到了索引，并且也用到排序。那么它是怎么排序的呢？
　　全字段排序
　　MySQL会给每个查询线程分配一块小内存，用于排序的，称为sortbuffer。什么时候把字段放进去排序呢，其实是通过idxcity索引找到对应的数据，才把数据放进去啦。
　　我们回顾下索引是怎么找到匹配的数据的，现在先把索引树画出来吧，idxcity索引树如下：
　　idxcity索引树，叶子节点存储的是主键id。还有一棵id主键聚族索引树，我们再画出聚族索引树图吧：
　　我们的查询语句是怎么找到匹配数据的呢？先通过idxcity索引树，找到对应的主键id，然后再通过拿到的主键id，搜索id主键索引树，找到对应的行数据。
　　加上orderby之后，整体的执行流程就是：MySQL为对应的线程初始化sortbuffer，放入需要查询的name、age、city字段；从索引树idxcity，找到第一个满足city深圳’条件的主键id，也就是图中的id9；到主键id索引树拿到id9的这一行数据，取name、age、city三个字段的值，存到sortbuffer；从索引树idxcity拿到下一个记录的主键id，即图中的id13；重复步骤3、4直到city的值不等于深圳为止；前面5步已经查找到了所有city为深圳的数据，在sortbuffer中，将所有数据根据age进行排序；按照排序结果取前10行返回给客户端。
　　执行示意图如下：
　　将查询所需的字段全部读取到sortbuffer中，就是全字段排序。这里面，有些小伙伴可能会有个疑问，把查询的所有字段都放到sortbuffer，而sortbuffer是一块内存来的，如果数据量太大，sortbuffer放不下怎么办呢？
　　磁盘临时文件辅助排序
　　实际上，sortbuffer的大小是由一个参数控制的：sortbuffersize。如果要排序的数据小于sortbuffersize，排序在sortbuffer内存中完成，如果要排序的数据大于sortbuffersize，则借助磁盘文件来进行排序
　　如何确定是否使用了磁盘文件来进行排序呢？可以使用以下这几个命令打开optimizertrace，开启统计setoptimizertraceenabledon；执行SQL语句selectname，age，cityfromstaffwherecity深圳orderbyagelimit10；查询输出的统计信息selectfrominformationschema。optimizertrace
　　可以从numberoftmpfiles中看出，是否使用了临时文件。
　　numberoftmpfiles表示使用来排序的磁盘临时文件数。如果numberoftmpfiles0，则表示使用了磁盘文件来进行排序。
　　使用了磁盘临时文件，整个排序过程又是怎样的呢？从主键Id索引树，拿到需要的数据，并放到sortbuffer内存块中。当sortbuffer快要满时，就对sortbuffer中的数据排序，排完后，把数据临时放到磁盘一个小文件中。继续回到主键id索引树取数据，继续放到sortbuffer内存中，排序后，也把这些数据写入到磁盘临时小文件中。继续循环，直到取出所有满足条件的数据。最后把磁盘的临时排好序的小文件，合并成一个有序的大文件。
　　TPS：借助磁盘临时小文件排序，实际上使用的是归并排序算法。
　　小伙伴们可能会有个疑问，既然sortbuffer放不下，就需要用到临时磁盘文件，这会影响排序效率。那为什么还要把排序不相关的字段（name，city）放到sortbuffer中呢？只放排序相关的age字段，它不香吗？可以了解下rowid排序。
　　rowid排序
　　rowid排序就是，只把查询SQL需要用于排序的字段和主键id，放到sortbuffer中。那怎么确定走的是全字段排序还是rowid排序排序呢？
　　实际上有个参数控制的。这个参数就是maxlengthforsortdata，它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL就认为单行太大，就换rowid排序。我们可以通过命令看下这个参数取值。showvariableslikemaxlengthforsortdata；
　　maxlengthforsortdata默认值是1024。因为本文示例中name，age，city长度644641321024，所以走的是全字段排序。我们来改下这个参数，改小一点。修改排序数据最大单行长度为32setmaxlengthforsortdata32；执行查询SQLselectname，age，cityfromstaffwherecity深圳orderbyagelimit10；
　　使用rowid排序的话，整个SQL执行流程又是怎样的呢？MySQL为对应的线程初始化sortbuffer，放入需要排序的age字段，以及主键id；从索引树idxcity，找到第一个满足city深圳’条件的主键id，也就是图中的id9；到主键id索引树拿到id9的这一行数据，取age和主键id的值，存到sortbuffer；从索引树idxcity拿到下一个记录的主键id，即图中的id13；重复步骤3、4直到city的值不等于深圳为止；前面5步已经查找到了所有city为深圳的数据，在sortbuffer中，将所有数据根据age进行排序；遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name和age三个字段返回给客户端。
　　执行示意图如下：
　　对比一下全字段排序的流程，rowid排序多了一次回表。
　　什么是回表？拿到主键再回到主键索引查询的过程，就叫做回表
　　我们通过optimizertrace，可以看到是否使用了rowid排序的：打开optimizertrace，开启统计setoptimizertraceenabledon；执行SQL语句selectname，age，cityfromstaffwherecity深圳orderbyagelimit10；查询输出的统计信息selectfrominformationschema。optimizertrace
　　全字段排序与rowid排序对比全字段排序：sortbuffer内存不够的话，就需要用到磁盘临时文件，造成磁盘访问。rowid排序：sortbuffer可以放更多数据，但是需要再回到原表去取数据，比全字段排序多一次回表。
　　一般情况下，对于InnoDB存储引擎，会优先使用全字段排序。可以发现maxlengthforsortdata参数设置为1024，这个数比较大的。一般情况下，排序字段不会超过这个值，也就是都会走全字段排序。
　　orderby的一些优化思路
　　我们如何优化orderby语句呢？因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化orderby语句。我们还可以通过调整maxlengthforsortdata等参数优化；
　　联合索引优化
　　再回顾下示例SQL的查询计划explainselectname，age，cityfromstaffwherecity深圳orderbyagelimit10；
　　我们给查询条件city和排序字段age，加个联合索引idxcityage。再去查看执行计划：altertablestaffaddindexidxcityage（city，age）；explainselectname，age，cityfromstaffwherecity深圳orderbyagelimit10；
　　可以发现，加上idxcityage联合索引，就不需要Usingfilesort排序了。为什么呢？因为索引本身是有序的，我们可以看下idxcityage联合索引示意图，如下：
　　整个SQL执行流程变成酱紫：从索引idxcityage找到满足city深圳’的主键id到主键id索引取出整行，拿到name、city、age三个字段的值，作为结果集的一部分直接返回从索引idxcityage取下一个记录主键id重复步骤2、3，直到查到第10条记录，或者是不满足city深圳’条件时循环结束。
　　流程示意图如下：
　　从示意图看来，还是有一次回表操作。针对本次示例，有没有更高效的方案呢？有的，可以使用覆盖索引：
　　覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。
　　我们给city，name，age组成一个联合索引，即可用到了覆盖索引，这时候SQL执行时，连回表操作都可以省去啦。
　　调整参数优化
　　我们还可以通过调整参数，去优化orderby的执行。比如可以调整sortbuffersize的值。因为sortbuffer值太小，数据量大的话，会借助磁盘临时文件排序。如果MySQL服务器配置高的话，可以使用稍微调整大点。
　　我们还可以调整maxlengthforsortdata的值，这个值太小的话，orderby会走rowid排序，会回表，降低查询性能。所以maxlengthforsortdata可以适当大一点。
　　当然，很多时候，这些MySQL参数值，我们直接采用默认值就可以了。
　　使用orderby的一些注意点：没有where条件，orderby字段需要加索引吗
　　日常开发过程中，我们可能会遇到没有where条件的orderby，那么，这时候orderby后面的字段是否需要加索引呢。如有这么一个SQL，createtime是否需要加索引：selectfromAorderbycreatetime；
　　无条件查询的话，即使createtime上有索引，也不会使用到。因为MySQL优化器认为走普通二级索引，再去回表成本比全表扫描排序更高。所以选择走全表扫描，然后根据全字段排序或者rowid排序来进行。
　　如果查询SQL修改一下：selectfromAorderbycreatetimelimitm；无条件查询，如果m值较小，是可以走索引的。因为MySQL优化器认为，根据索引有序性去回表查数据，然后得到m条数据，就可以终止循环，那么成本比全表扫描小，则选择走二级索引。
　　分页limit过大时，会导致大量排序怎么办？
　　假设SQL如下：selectfromAorderbyalimit100000，10可以记录上一页最后的id，下一页查询时，查询条件带上id，如：whereid上一页最后idlimit10。也可以在业务允许的情况下，限制页数。
　　索引存储顺序与orderby不一致，如何优化？
　　假设有联合索引idxagename，我们需求修改为这样：查询前10个员工的姓名、年龄，并且按照年龄小到大排序，如果年龄相同，则按姓名降序排。对应的SQL语句就可以这么写：selectname，agefromstafforderbyage，namedesclimit10；
　　我们看下执行计划，发现使用到Usingfilesort
　　这是因为，idxagename索引树中，age从小到大排序，如果age相同，再按name从小到大排序。而orderby中，是按age从小到大排序，如果age相同，再按name从大到小排序。也就是说，索引存储顺序与orderby不一致。
　　我们怎么优化呢？如果MySQL是8。0版本，支持DescendingIndexes，可以这样修改索引：CREATETABLEstaff（idbigint（11）NOTNULLAUTOINCREMENTCOMMENT主键id，idcardvarchar（20）NOTNULLCOMMENT身份证号码，namevarchar（64）NOTNULLCOMMENT姓名，ageint（4）NOTNULLCOMMENT年龄，cityvarchar（64）NOTNULLCOMMENT城市，PRIMARYKEY（id），KEYidxagename（age，namedesc）USINGBTREE）ENGINEInnoDBAUTOINCREMENT15DEFAULTCHARSETutf8COMMENT员工表；
　　使用了in条件多个属性时，SQL执行是否有排序过程
　　如果我们有联合索引idxcityname，执行这个SQL的话，是不会走排序过程的，如下：selectfromstaffwherecityin（深圳）orderbyagelimit10；
　　但是，如果使用in条件，并且有多个条件时，就会有排序过程。explainselectfromstaffwherecityin（深圳，上海）orderbyagelimit10；
　　这是因为：in有两个条件，在满足深圳时，age是排好序的，但是把满足上海的age也加进来，就不能保证满足所有的age都是排好序的。因此需要Usingfilesort。
　　对orderby的理解《Linux就该这么学》（linuxprobe。com）