前言在日常开发中，我们经常会用到orderby，各位小伙伴，你知道orderby是如何工作的吗？orderby的优化思路是什么？使用orderby应该注意哪些问题？本文将和大家一起学习攻克orderby~一个使用orderby的简单例子，假设使用的是employee表。表结构如下：CREATETABLE`staff`(`id`BIGINT(11)AUTO_INCREMENTCOMMENT'主键id',`id_card`VARCHAR(20)NOTNULLCOMMENT'ID号',`name`VARCHAR(64)NOTNULLCOMMENT'name',`age`INT(4)NOTNULLCOMMENT'age',`city`VARCHAR(64)NOTNULLCOMMENT'city',PRIMARYKEY(`id`),INDEXidx_city(`city`))ENGINE=INNODBCOMMENT'员工表';表数据如下：我们现在有这样一个需求：查询深圳员工的前10名姓名，年龄，城市，按年龄升序排列。对应的SQL语句可以这样写：selectname,age,cityfromstaffwherecity='Shenzhen'orderbyagelimit10;这条语句的逻辑很清晰，但是它的底层执行过程是怎样的呢？orderbyworkingprincipleexplainexecutionplan，我们先用ExplainKeywords检查执行计划的关键字段，也就是说使用了索引idx_cityExtra。该字段的Usingindex条件表示索引条件。Extra这个字段的Usingfilesort表示使用排序。我们可以发现，这条SQL使用了索引，并且还用于排序。那么它是如何排序的呢？全域排序MySQL会为每个查询线程分配一小块内存用于排序，称为sort_buffer。什么时候放入字段进行排序，其实就是通过idx_city索引找到对应的数据，然后放入数据。我们再回顾一下索引是如何找到匹配数据的。我们先画索引树。idx_city索引树如下：idx_city索引树，叶子节点存放主键id。还有一个id主键聚簇索引树，我们画一个聚簇索引树图：我们的查询语句是如何找到匹配数据的？先通过idx_city索引树找到对应的主键id，然后得到主键id，搜索id主键索引树，找到对应的行数据。添加orderby后，整体执行流程为：MySQL为对应线程初始化sort_buffer，放入需要查询的name、age、city字段；从索引树idx_city中，找到满足city='Shenzhen'id条件的第一个主键，即图中id=9；到主键id索引树中得到id=9的那一行数据，取name、age、city这三个字段的值，存入sort_buffer；从索引树中获取下一条idx_city记录的主键id，即图中id=13；重复步骤3和4，直到city的值不等于Shenzhen；前面5步已经找到所有城市为深圳的数据，并在sort_buffer中，将所有数据按照年龄排序；根据排序结果，取前10行返回给客户端。执行图如下：将查询需要的所有字段读入sort_buffer，即按所有字段排序。说到这里，可能有朋友会有疑问，把query的所有字段都放到sort_buffer中，而sort_buffer是一块内存，如果数据量太大，sort_buffer放不下怎么办？磁盘临时文件辅助排序其实就是，sort_buffer的大小由一个参数控制：sort_buffer_size。如果要排序的数据小于sort_buffer_size，则在sort_buffer内存中进行排序。如果待排序的数据大于sort_buffer_size，则借助磁盘文件进行排序。如何判断是否使用磁盘文件进行排序？可以使用如下命令##开启optimizer_trace，开启统计setoptimizer_trace="enabled=on";##执行SQL语句selectname,age,cityfromstaffwherecity='Shenzhen'orderbyagelimit10;##查询输出统计select*frominformation_schema.optimizer_trace可以看到来自number_of_tmp_files，是否使用了临时文件。number_of_tmp_files表示用于排序的磁盘临时文件数。如果number_of_tmp_files>0，表示使用磁盘文件进行排序。使用临时磁盘文件时，整个排序过程是怎样的？从主键Id索引树中获取需要的数据，放入sort_buffer内存块中。当sort_buffer快满时，对sort_buffer中的数据进行排序，排序后数据暂时放在磁盘上的一个小文件中。继续返回主键id索引树取数据，继续放入sort_buffer内存。排序后，将这些数据写到磁盘上的临时小文件中。不断循环，直到取出所有符合条件的数据。最后将磁盘上临时排序好的小文件合并成一个有序的大文件。TPS：借助磁盘临时小文件排序，实际使用的是归并排序算法。朋友们可能会有疑问，既然sort_buffer放不下，就需要使用临时磁盘文件，这样会影响排序效率。那为什么要把与排序无关的字段（name，city）放到sort_buffer中呢？只放排序相关的年龄字段，不好吗？您可以了解rowid排序。rowid排序rowid排序就是只把查询SQL排序需要的字段和主键id放到sort_buffer中。那么如何判断是按所有字段排序还是按rowid排序呢？其实是有一个参数来控制的。这个参数是max_length_for_sort_data，代表MySQL用来对行数据长度进行排序的一个参数。如果单行长度超过这个值，MySQL会认为单行过大，会按照rowid进行排序。我们可以通过命令看到这个参数的值。显示像“max_length_for_sort_data”这样的变量；max_length_for_sort_data的默认值为1024，因为本文例子中name、age、city的长度=64+4+64=132<1024，所以采用全字段排序。我们把这个参数改小一点，##修改排序数据的最大单行长度为32setmax_length_for_sort_data=32;##执行查询SQLselectname,age,cityfromstaffwherecity='Shenzhen'orderbyagelimit10;如果使用rowid排序，整个SQL执行过程是怎样的？MySQL为对应线程初始化sort_buffer，放入待排序的age字段，以及主键id；从索引树idx_city中，找到第一个满足city='Shenzhen'条件的主键id，也就是图中的id=9；到主键id索引树中获取id=9的数据行，取age和主键id的值，保存到sort_buffer中；从索引树idx_city中获取下一条记录的主键id，即图中id=13；重复步骤3和4，直到city的值不等于Shenzhen；前面5步已经找到所有城市为深圳的数据，在sort_buffer中，将所有数据按照年龄排序；遍历排序结果，取前10行，并根据id的值返回原表，取出city、name、age三个字段返回给客户端。执行图如下：对比整个字段排序过程，rowid排序加一回表。什么是返表？获取主键然后返回主键索引查询的过程称为表返回。”我们可以通过optimizer_trace查看是否使用了rowid排序：执行SQL语句selectname,age,cityfromstaffwherecity='Shenzhen'orderbyagelimit10;##查询输出统计select*frominformation_schema.optimizer_trace全字段排序和rowid排序对比全字段排序：如果sort_buffer内存不够，需要使用disk临时文件，导致磁盘访问rowid排序：sort_buffer可以存储更多的数据，但是需要回原表取数据，比全字段排序多一个回表。一般情况下，对于InnoDB存储引擎，全Field排序。可以发现max_length_for_sort_data参数设置为1024，这是一个比较大的数字。一般情况下，排序field不会超过这个值，即使用全字段排序。orderby的一些优化思路我们如何优化orderby语句呢？？因为数据是无序的，所以需要排序。如果数据本身是有序的，那么就没有必要对其进行排序。并且索引数据本身是有序的。我们通过建立联合索引来优化orderbystatement。我们还可以通过调整max_length_for_sort_data等参数进行优化；联合索引优化再查看样例SQL的查询计划,age);explainselectname,age,cityfromstaffwherecity='深圳'orderbyagelimit10;可以发现加入idx_city_age联合索引不需要使用filesort排序。为什么？因为索引本身是有序的，我们可以看一下idx_city_age联合索引的示意图，如下：整个SQL执行过程变成红色：从索引idx_city_age中找到满足city='Shenzhen'的主键id到主键id索引检索整行，得到name、city、age三个字段的值，直接从索引idx_city_age返回下一条记录的主键id作为结果集的一部分重复步骤2and3直到找到第10条记录，或者不满足city=满足'深圳'条件时循环结束。流程示意图如下：从示意图来看，还是有一个返表操作。对于这个例子，有没有更高效的解决方案？是的，可以使用覆盖索引：覆盖索引：在查询数据列时，不需要回表查看，直接从索引列中得到想要的结果即可。也就是说，你的SQL使用的索引列数据覆盖了查询结果的列，就被认为是覆盖索引。”我们对city,name,age形成一个联合索引，然后我们可以使用覆盖索引。这时候执行SQL的时候，连回表的操作都可以省略。调整参数优化我们也可以通过调整参数Exec??ute来优化排序，比如可以调整sort_buffer_size的值，因为sort_buffer的值太小，如果数据量大，会借助磁盘上的临时文件进行排序。如果MySQL服务器配置高，可以使用稍大的值，我们也可以调整max_length_for_sort_data的值，这个值太小，orderby会使用rowid排序回表，会降低查询性能所以，max_length_for_sort_data可以适当大一些，当然很多情况下，我们可以直接对这些MySQL参数值使用默认值。重新条件？SQL，create_time是否需要加索引：select*fromAorderbycreate_time;如果是无条件查询，即使create_time上有索引，也不会被使用。因为MySQL优化器认为使用普通的二级索引，然后再回表比扫描全表排序成本高。所以选择扫描全表，然后按照全字段或者rowid排序。如果查询SQL，修改：select*fromAorderbycreate_timelimitm;无条件查询，如果m的值较小，可以使用索引。因为MySQL优化器认为，按照索引的顺序，回表查数据，然后得到m条数据，就可以终止循环，成本比全表扫描小，并选择二级索引。当分页限制过大时，会导致大量排序。假设SQL如下：select*fromAorderbylimit100000,10可以记录上一页的最后一个id。查询下一页的时候，查询条件会带上id，比如：whereid>previous页面的最后一个id限制为10。如果业务允许，也可以限制页数。索引存储顺序与orderby不一致，如何优化？假设有一个联合索引idx_age_name，我们需要修改如下：查询前10名员工的姓名和年龄，按年龄从小到大排序。如果年龄相同，则按姓名降序排列。对应的SQL语句可以这样写：selectname,agefromstafforderbyage,namedesclimit10;我们查看执行计划，发现使用了Usingfilesort。这是因为，在idx_age_name索引树中，age是从小到大排序的，如果age相同，则按name从小到大排序。orderby，按照年龄从小到大排序。如果年龄相同，则按姓名从大到小排序。即索引存储顺序与orderby不一致。我们如何优化它？如果MySQL是8.0版本，支持降序索引，可以这样修改索引：CREATETABLE`staff`(`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'主键id',`id_card`varchar(20)NOTNULLCOMMENT'身份证号',`name`varchar(64)NOTNULLCOMMENT'name',`age`int(4)NOTNULLCOMMENT'age',`city`varchar(64)NOTNULLCOMMENT'city',PRIMARYKEY(`id`),KEY`idx_age_name`(`age`,`name`desc)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8COMMENT='员工表';当使用in条件的多个属性时，SQL执行时是否有排序过程如果我们有一个联合索引idx_city_name，执行这条SQL，不会经过排序过程，如下：select*fromstaffwherecityin('Shenzhen')orderbyagelimit10;但是如果使用了in条件，并且有多个条件，就会有一个排序的过程。explainselect*fromstaffwherecityin('深圳','上海')orderbyagelimit10;这是因为：in有两个条件，当满足深圳的时候，年龄就排序了，但是如果也加上上海的年龄，就不能保证满足所有年龄的排序。因此需要使用文件排序。本文转载自微信公众号“捡蜗牛的小男孩”，可通过以下二维码关注。转载请联系捡蜗牛的小男孩公众号。
