mysql 之order by工作流程-优快云博客

用下面这个表为列来看看MySQL order by 的工作方式

代码解读

复制代码

CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ) ENGINE=InnoDB; 现在有个需求查询城市为杭州的所有人的姓名并且按照姓名的排序返回前1000位的name age ？？？

SQL 如下：

代码解读

复制代码

select city,name,age from t where city = '杭州' order by name limit 1000;

贴出explain SQL的执行结果

Extra 中 Using index condition; 这个是之前文章中提到的索引下推 ICP Using filesort 这个表示需要排序 mysql会给每个线程分配一块内存叫做sort_buffer

需要注意的是 order by name 这个动作有可能在内存中完成也可能使用外部排序这取决于排序需要的内存和sort_buffer_size

sort_buffer_size 也就是MySQL为排序开辟的内存空间如果要排序的数据量小于sort_buffer_size 排序就在内存中完成如果大的话就需要利用磁盘临时辅助排序

我们可以利用 optimizer_trace 来确定一个排序语句是否使用了临时文件具体的分析看optimizer_trace 这篇文章我直接贴图了

number_of_tmp_files 表示排序过程中使用的临时文件如果number_of_tmp_files = 0 表示没有用到临时文件上图为4 表示使用了外部排序 sort_buffer_size越小需要分成的份数越多 number_of_tmp_files就越大
注意下 explain rows的扫描行数是1824 而我们在上面排序扫描的行数是1825 是因为建立外部排序临时表存储在磁盘上 internal_tmp_disk_storage_engine 的默认值是innoDB 查询optimizer_tarce表时把数据从临时表取出来会把扫描行数加+1

接下来看order by的另一种排序 =====>rowid排序

上面排序算法中存在一个问题如果查询的数据太多 sort_buffer 中放的数据太多这样临时文件就越多性能会很差

mysql 配置中 max_length_for_sort_data 用于控制排序的行数如果排序的单行超过这个值 mysql就认为单行过大采用rowid算法

来看下rowid算法的执行流程：

1.初始化sort_buffer 放入要排序的字段和主键ID 2.走索引找到满足条件的主键ID 再通过主键ID 取出排序字段和ID 放到sort_buffer中从索引找到下一个满足条件的主键ID 3.重复2 直到不满足条件 4.对sort_buffer中的值按照排序字段排序 5.回到原表取出所有查询的字段返回

注意一点最后的结果集是逻辑上的概念 mysql从server段排序后的sort_buffer中取到ID 然后回到原表查其余的字段不需要在服务端再存储的是直接返回客户端的

我直接贴结果

可以看到sort_mode变了<sort_key,rowid>表示参与排序只有要求排序的字段和主键ID number_of_tmp_rows 变成了3 表示临时文件变少了参与排序的行变小了因此参与排序总量变小

第一种排序 vs rowid排序

mysql 认为内存不够才会使用rowid排序这样排序可以读取更多行但需要回原表取一次数据所以mysql会优先采用第一种排序

体现了mysql 设计思想 : 如果内存够就要多利用内存尽量减少磁盘访问

排序是个成本较高的操作那么如果避免这个问题从上面的过程我们可以发现mysql之所以需要生成临时表并且在临时表上排序是因为原来的数据都是无序的

上面的列子我们可以通过在city 和name上建立联合索引来解决同时也可以用我们之前提到的覆盖索引来避免排序

现在分析一个问题开发中有时候会遇到随机选取的问题 SQL可能是这样的

代码解读

复制代码

mysql> CREATE TABLE `words` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; select `word` from words order by rand() limit 3; 随机取出3个单词

我贴出explain 结果

Using temporary; 表示需要用临时表上面意思就是需要的内存表上排序

问题来了对于临时内存表来说选择那种算法

对于innoDB来说对于内存表回表的过程只是简单的根据数据行的位置直接访问内存得到数据（可以理解为一个内存中的数据下标对找对应的元素）根本不会有磁盘的访问所以优先考虑的是用于排序的行越小越好 mysql会选择rowid排序

我们来看上面那条SQL的执行流程

scss

代码解读

复制代码

1.创建一个memory引擎的内存表表里两个字段一个是double字段另一个是varchar（64）类型 2.从Word表中按主键顺序取出所有word的值调用rand()函数生成一个随机小数存放到内存表中 3.接下来按值排序 4.初始化sort_buffer 里面有两个值一个double类型一个整型 6 在sort_buffer中排序 7.排序完成之后直接选择前三个位置的数据返回客户端

再来看个临时表不是内存表的情况 ===》转化为磁盘临时表

mysql中 'tmp_table_size' 这个配置限制了内存临时表的大小默认是32M (mysql5.6 ) 看下图