mysql 之order by

cyyking 2020-05-03

sql 里面的order by 在mysql 里面是如何实现的呢?

为了说明这问题,首先先创建一个表

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL, 
 `age` int(11) NOT NULL, 
  PRIMARY KEY (`id`), 
 KEY `city` (`city`)) ENGINE=InnoDB;
explain select city,name,age from user where city=‘hz‘ order by name limit 1000;

执行上面这个语句你会发现 exra 返回 Using where; Using filesort
其中Using filesort 表示需要用到排序。
mysql会为排序分配单独的内存 叫做sort buffer 。
上面语句的排序过程如下:
1.查询city索引,取出满足条件的id。
2.根据id查询主键索引,取出字段city name age 字段放到 sort buffer
3.继续取出 id 不走1 2来回弄,直到city不满足
4.对sort buffer进行排序
5.返回结果
上面流程是在sort buffer内存充足的情况下。其实这个内存大小是可控的 通过参数sort_buffer_size 来控制,如果sort buffer的内存不够 ,那么就会利用外部的磁盘文件来进行排序,如果是这样效率可能就会下降咯。

还有一种排序的方式是rowid 排序。mysql在8.0.20之前,还有个参数 max_length_for_sort_data,这个参数的意思是一行可以允许放下的容量,当sort buffer一行放不来这么多的时候,就会采用另外一种排序 就是row id排序。
排序过程如下:
1.查询city索引,取出满足条件的id。
2.根据id查询主键索引, name id字段放到 sort buffer
3.继续取出 id 不走1 2来回弄,直到city不满足
4.对solr buffer 排序
5.回表取city name age 字段
6.返回结果。

对比:可以看到虽然rowid的排序方式中 sort buffer能够放入更多的字段,但是它的缺点也很明显,就是会多回表。所以优先还是选择 全字段排序的方式。

那么既然排序这么消耗性能可能会用到外部磁盘,有没有可以避免排序呢?
答案是肯定的,就是用索引,如果有个组合索引(citye,name) 那么这个时候就不需要再排序咯。你可以试验一下,你会发现extra用会没有 Using filesort

到了这里排序就差不多说完了,如果explain select city,name,age from user where city=‘hz‘ order by name limit 1000;这个语句 想要更快,一次回表都没有,应该怎么做呢?

相关推荐

whats / 0评论 2011-04-10