对MySQL中order by的理解_u012135155的博客
|
我们知道，在SQL语句中当对查询结果应用order by排序时，如果排序字段有索引，或者满足“最左前缀原则”，则InnoDB内部执行查询后就已经是有序的了，因为InnoDB的索引B+Tree的叶子节点就是按照顺序排列存储的。这个不难理解，那么对于没有走到索引排序的order by查询，MySQL是如何处理的呢？
比如上面的查询的执行计划中没有走到索引，extra的信息是Using filesort，这是什么意思呢？“Using filesort”表示的就是需要排序mysql排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。order by create_time这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。当内存放不下，需要外部排序时，MySQL会创建临时文件进行排序（注意这个排序的动作不是由InnoDB完成的，这个时候存储引擎已经返回了结果集，需要MySQL进行排序）。外部排序一般使用归并排序算法，即MySQL会把结果集放进若干个临时文件中分别对其进行排序，最后再把这若干个有序文件合并为一个大文件。 在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以MySQL有一种排序模式叫rowid 排序。 max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数，这个值默认为1024。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。即如果表中的一行总字节数超过了max_length_for_sort_data，则MySQL就会采用rowid排序模式，即在sort_buffer中不取整行的值，而只取待排序字段和主键id，取出所有满足条件的字段后，再进行排序，用这个结果集的有序id再进行回表操作拿到最终的结果集返回。 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。 对于常用的条件和order by排序的组合，可以对其建立联合索引，要注意联合索引的顺序，条件查询要满足最左前缀查询。如果不需要全字段查询，也尽可能地利用覆盖索引，减少回表操作造成的消耗。 下面说一说MySQL处理order by rand()操作的原理。
Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。下面介绍一下临时表的排序原理。MySQL中临时表的排序用的方法是上面提到的rowid排序，因为数据都在内存中（建立的临时表也是在内存中），因此不牵涉到到磁盘上进行回表操作，使用rowid模式排序更节省内存。 MySQL创建一个临时表，存储引擎用memory，有两个字段R和nickname，取出所有的nickname写进临时表，并调用rand()方法，为每行字段计算一个随机值写进R。初始化sort_buffer，在临时表中取出R值和位置信息（下面会解释）写进sort_buffer中，按照R的随机值进行排序，排序完成后取出前3个结果的位置信息，然后回到内存临时表中取出nickname字段，返回给客户端。 MEMORY 引擎不是索引组织表，上面提到的位置信息可以理解为内存中数组的下标。 如果直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要尽量避开这种写法。 以上内容参考了极客时间林晓斌的MySQL实战课程： （编辑：财气旺网 - 财气网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
