mysql的排序

Posted on | Edited on | In ,

mysql排序 order by的原理

原理

下面的语句用按照姓名排序，我们看下执行计划，Extra有一段‘Using filesort’，说明Mysql用的用到了排序

1
2
3
4
explain select city,name,age from t where city='杭州' order by name limit 1000  ;

#执行计划
1 SIMPLE t NULL ref city city 66 const 1 100.00 Using index condition; Using filesort

排序的过程

mysql在排序操作中会用到sort_buffer和临时文件，sort_buffer的大小由sort_buffer_size参数决定

  • sort_buffer:是Mysql为排序操作开辟的一块内存空间，会将需要查询的字段以及排序字段放在内存空间中，进行排序操作。
  • 临时文件：如果字段太多sort_buffer容量不够了会借用到临时文件进行存储，会将需要排序的文件分成N份，同时排序排序完成后在进行合并。

全字段排序

全字段排序需要的流程

  1. 初始化sort_buffer,确定要放进去的字段
  2. 根据索引等条件取出满足条件的主键ID
  3. 回表查询出需要显示的字段，将需要的字段放到sort_buffer中，如果用到临时文件，加载到临时文件中
  4. 内存中排序
  5. 返回结果

rowid排序

如果Mysql判断单行占用的空间很大，会采用rowid方式进行排序,可以用如下方式进行设置。受max_length_for_sort_data指标来控制

1
SET max_length_for_sort_data = 16;
  1. 初始化sort_buffer,确定排序字段和主键ID
  2. 根据索引等条件取出满足条件的主键ID
  3. 将需要排序和主键ID放到sort_buffer中，如果用到临时文件，加载到临时文件中
  4. 内存中排序，得到排好序的排序字段值–主键ID
  5. 回表查询数据
  6. 返回结果

全字段排序 vs rowid 排序

rowid比全字段排序多了一次回表操作，理论上来说mysql能用内存尽量用内存，减少磁盘操作，所以理论上来说rowid排序性能会低于全字段排序

如何优化排序

使用索引：因为索引本身是有序的，合理利用索引，这样排序就可以用索引避免使用临时文件和sort_buffer进行排序。如例子中的语句可以创建（city,name）的索引，可以用执行计划看出来。

1
2
3
4
5
#创建索引（city,name）注意顺序
explain select city,name,age from t where city='杭州' order by name limit 1000  ;

#执行计划
1 SIMPLE t NULL ref idx idx 66 const 1 100.00 Using index condition

附录

判断排序语句是否用了临时文件，是那种排序方式的语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/* 打开 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;

如何判断排序类型

  1. SELECT * FROM information_schema.OPTIMIZER_TRACE\G，之后的json对象看filesort_summary段
    1. sort_mode是sort_key, rowid是全字段排序
    2. sort_mode是sort_key, packed_additional_fields采用全字段排序
1
2
3
4
5
6
7
8
9
10
11



            "filesort_summary": {
              "rows": 1001, #取的字段
              "examined_rows": 110501, #扫描行数
              "number_of_tmp_files": 0, #历史文件个数
              "sort_buffer_size": 17024,
              "sort_mode": "<sort_key, rowid>"
            }
#如果是rowid排序实际扫描行数是examined_rows+rows（因为回了一次表）