目录
一、前言
排序缓存区(sort buffer)
概念:
- mysql会为每个查询线程分配一块内存作为排序缓冲区(sort buffer)。
内部排序VS外部排序:
- 当待排序数据不超过 sort buffer 的容量(
sort_buffer_size
)时,mysql 将会在内存中使用快速排序算法进行排序。 - 当待排序数据量超过 sort buffer 的容量(
sort_buffer_size
)时,mysql 将会借助临时磁盘文件使用归并排序算法进行排序(通常会将待排序数据分成多个“小文件”,对各个“小文件”进行快速排序,再汇总成一个有序的“大文件”)。
参数:
- sort buffer的大小由
sort_buffer_size
参数控制,默认为 256 kb。- 查看:show variables like 'sort_buffer_size'
- 结果:sort_buffer_size 8388608 单位是字节,8388608即16M
回表
- 概念:存储引擎(存储引擎层)通过二级索引拿到主键id之后,去遍历主键索引,然后从文件系统(文件系统层)来获取数据的过程就叫做回表。
- 特点:回表的操作更多的是随机io,随机io在性能上还是比较低下的。
rowId
- 概念:mysql对每行数据的唯一标识符,当数据表有主键时,rowId 就是表主键;当数据表没有主键或者主键被删除时,mysql会自动生成一个长度为 6 字节的rowId。
二、排序算法
2.1全字段排序
概念:
- 将最终结果集中所有的字段都放进 sort buffer中,然后在sort buffer中针对排序字段进行快速排序。
例子:
SELECT nick_name, age, phone
FROM t_user
WHERE city = "深圳"
ORDER BY nick_name;
假设 city 字段上有索引,全字段排序的过程:
- 从 city 索引树上找到第一条值为深圳的数据,取得 id 之后回表取得 nick_name、age、phone 三个字段放入 sort buffer。
- 从 city 索引树取下一条值为深圳的数据,重复 1 过程,直到下一条数据不满足值为深圳条件。
- 到这一步,所有 city = 深圳 的数据都在 sort buffer 了,然后对 nick_name 进行快速排序。
- 将排序结果返回
- 可以看到当查询条件本身有索引可用的话,全字段排序的排序过程都在sort buffer进行,回表次数为符合条件的数据个数。
- 若是由city、nick_name建立的联合索引,则可以实现“索引覆盖”,即在一棵索引树上取得全部所需数据,减少回表(随机读)次数。
- 从 (city,nick_name)索引中获取到第一条 city='深圳' 的数据,取得 id 之后回表取得 nick_name、age、phone 三个字段。
- 从 (city,nick_name)索引中获取下一条 city='深圳' 的数据,取得 id 之后回表取得 nick_name、age、phone 三个字段。
- 1和2中获取的数据天然是有序的,故mysql不需要再进行排序了,这样就避掉了对 name 字段的排序。
缺点:
- 当待排序的数据行很大(即查询的字段有很多)时,使用全字段排序必然会导致“外部排序”,为了尽可能地避免“外部排序”,mysql提供了rowId排序的算法。
2.2rowId排序
概念:
-
只将与排序相关的字段和 rowId 放入 sort buffer,其余结果集需要用到的数据在排序完成后,通过 rowId 回表取得。
特点:
- rowId 排序的好处是在 sort buffer 大小固定的情况下,sort buffer 能够容纳更多的数据行,能够避免使用或者少使用“外部排序文件”。
- 最终返回结果集的时候,需要再次进行回表。
例子:
SELECT nick_name, age, phone
FROM t_user
WHERE city = "深圳"
ORDER BY nick_name;
rowId 排序全过程:
- 从 city 索引树上找到第一条值为深圳的数据,取得 id 之后回表取得 nick_name 这个与排序相关的字段和主键 id 一起放入 sort buffer。
- 从 city 索引树取下一条值为深圳的数据,重复 1 过程,直到下一条数据不满足值为深圳条件。
- 这时候,所有 city = 深圳 的数据都在 sort buffer 了(sort buffer 里面的数据包含两个字段: id 和 nick_name),然后对 nick_name 执行快速排序。利用排序好的数据,使用主键 id 再次回表取其他字段,将结果返回。
- 注意:在步骤 4 中不会等所有排序好的 id 回表完再返回,而是每个 id 回表一次,取得该行数据之后立即返回,所以不会消耗额外的内存。
全字段排序还是rowId排序?
如果单行数据的长度不大于设置的最大值(max_length_for_sort_data
),则使用全字段排序,否则使用rowId排序。
参数:max_length_for_sort_data
- 表示mysql对于排序的行数据支持的最大长度,默认值为 1024 字节。
- 测试:
SET max_length_for_sort_data=8; # 小于要排序字段长度的和即可。
2.3优先队列排序
分页查询时的排序:
- 分页查询时我们往往会先对数据进行排序,然后再获取指定页码的数据。
- order by + limit n 查询时,如果仍然使用全字段排序或rowId排序,虽然我们只需要n条数据有序,但是仍会将所有满足查询条件的数据都载入sort buffer中进行排序,大大降低了sort buffer的利用率。
概念:
- 在排序字段无索引的情况下,mysql使用优先队列进行排序(即堆排序)对 order by + limit n 排序语句进行优化。
过程:
- 在所有待排序的数据,取数量为
LIMIT
的数据,构建一个堆。 - 不断的取下一行数据,更新堆节点。
- 当所有行的扫描完,得到最终的排序结果
三、借助临时表进行排序
- 通常对于一个执行较慢的排序语句,在使用
EXPLAIN
进行执行过程分析的时候除了能看到Using filesort
以外,还能看到Using temporary
,代表在排序过程中使用到了临时表。
内存临时表排序
- MySQL 优先使用内存临时表。当 MySQL 使用内存临时表时,临时表存储引擎为 memory 。
- 如果当前 MySQL 使用的是内存临时表的话,将会直接使用 rowId 排序,这时的回表(回临时表取数据)只是在内存表中读数据,操作不涉及硬盘IO 。
磁盘临时表排序
- 如果系统中很多需要使用临时表的排序语句执行,而又不加以限制,全都使用临时表的话,内存很快就会被打满。
- MySQL 提供了
tmp_table_size
参数限制了内存临时表的大小,默认值是 16M,如果临时表大小超过了tmp_table_size
,那么内存临时表就会转成磁盘临时表。这时的回表意味着磁盘随机读,所以会搭配全字段排序方式。 - 这时候 MySQL 会根据单行大小是否超过
max_length_for_sort_data
决定采用全字段排序还是 rowId 排序。