order by 是怎么工作的？读后总结

àì夳堔傛蜴生んèń 2021-10-30 04:08 380阅读 0赞

order by 排序的两种模式

1.  全字段排序  
    我们一般用explain命令查看sql是否使用排序。一般查看extra字段的描述。  
    mysql会为每个线程分配一块内存用于排序 称为sort\_buffer  
    我们以这条sql查询为例  
    select city,name,age from t where city=‘sz’ order by name limit 1000  
    首先将满足条件的记录中的city，name，age存入sort\_buffer中，之后根据name进行快排。  
    由于是将所有记录字段city,name,age进行排序。所以是全字段，所有需要的字段都包含。  
    sort\_buffer足够大的话就在内存中排序，sort\_buffer不够大的话，就利用磁盘进行外部排序。  
    涉及参数 sort\_buffer\_size。所需内存大于sort\_buffer\_size则利用临时文件，小于sort\_buffer\_size则在内存中排序。  
    你可以用下面的语句判断一个排序是否使用了临时文件  
    /\* 打开 optimizer\_trace，只对本线程有效 \*/  
    SET optimizer\_trace=‘enabled=on’;

/\* @a 保存 Innodb\_rows\_read 的初始值 \*/  
select VARIABLE\_VALUE into @a from performance\_schema.session\_status where variable\_name = ‘Innodb\_rows\_read’;

/\* 执行语句 \*/  
select city, name,age from t where city=‘杭州’ order by name limit 1000;

/\* 查看 OPTIMIZER\_TRACE 输出 \*/  
SELECT \* FROM `information_schema`.`OPTIMIZER_TRACE`\\G

/\* @b 保存 Innodb\_rows\_read 的当前值 \*/  
select VARIABLE\_VALUE into @b from performance\_schema.session\_status where variable\_name = ‘Innodb\_rows\_read’;

/\* 计算 Innodb\_rows\_read 差值 \*/  
select @b-@a;  
optimizer\_trace的结果，从number\_of\_tmp\_files来查看是否使用了临时文件。  
2.rowid 排序  
全字段排序在记录数非常大的情况下，会使用非常多的临时文件做归并。涉及到磁盘读写的排序性能一般都不是很好。如果一行记录非常大的情况下。排序效率会变得异常的慢。  
这里先介绍一个参数是用于控制排序行数据长度的参数，max\_length\_for\_sort\_data.当行数据超过这个参数时，排序就从全字段排序转换为rowid排序。  
rowid排序很简单就是将放入sort\_buffer的字段减少为主键与待排序字段。这样sort\_buffer中就可以放入更多的行记录。但是当我们在返回数据的时候，需要进行一次回表操作，从主键索引树上将所需的数据读出来。  
由于最后的结果集需要回表 ，所以总的扫描行数也会增加。

mysql设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。  
所以rowid排序会有回表操作所以不会被优先选择。  
但是内存不够利用临时文件进行外排的情况，排序效率可能更低。

对于order by 并不是每次都需要排序的，对于无序数据需要进行排序，但是如果我们在排序字段上创建了索引，那么查询出来的数据就是有序的，那么就可以省下这个排序的过程。  
还是以上面的sql为例，我们建一个city和name的联合索引，这样查询出来的记录就是有序的。  
如果我们建一个 city，name，age的覆盖索引，那么就可以省下去主键索引树回表读的操作，进一步提高了效率。