MySQL - order by 出现 using filesort根因分析及优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL - order by 出现 using filesort根因分析及优化


Pre

MySQL - order by和 group by 优化初探


Case

table

CREATE TABLE `user` (
  `id` int(10) AUTO_INCREMENT,
  `user_id` int(10),
  `gf_phone` varchar(1000),
  `gf_name` varchar(100),  
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;
  • 用户的 user_id ,女朋友的名字gf_name、女朋友的电话号码gf_phone
  • user_id 索引

模拟数据

# 清空表 
truncate table user;
# 删除存过
drop PROCEDURE  batchInsert 
# 创建存储过程 
delimiter ‘$’;
CREATE PROCEDURE batchInsert(in args int)
BEGIN
declare i int default 1;
start TRANSACTION;
while i <= args DO
insert into user(id,user_id, gf_name ,gf_phone) VALUES ( i, 8888, concat("art-" , i),i);
set i = i+1;
end while;
COMMIT;
end
 # 批量写入数据   100万
 call batchInsert(1000000);


故障复现

select gf_name,gf_phone from user where user_id=8888 order by gf_name ; 


还等啥, explain 呀



咋办?

看看这个SQL的数据结果集有多少???? ------100万

方案一 加limit ,少取一点?

当然了实际工作中是基本不会出现这种情况的, 假设真的取了100万数据, 无论是MySQL内存缓冲区的占用,还是网络带宽的消耗都是巨大的。

那加了limit 10呢?因为数据包整体变小了, 网络带宽的问题是解决了,但是 using filesort 的问题并没有解决,mysql还是要给你排序的呀。



using filesort 到底是个什么鬼????


让我大胆的猜一猜啊

在文件中排序的?在文件中到底是怎么排序的?

我们分析一下现在的索引和数据情况哈

  • 首先 user_id 是有索引的,所以会先在 user_id 索引树上检索目标数据,即 user_id=8888 的数据,但是我们要查询的是gf_name 和gf_phone 字段, 光靠 user_id 索引是找不到这两个字段值的
mmp  100万个女朋友。。。。


  • 所以啊,找不到怎么办????? 需要回表,通过 user_id 对应的主键去【主键索引树】上去查找,还真他娘的找到了第一条 user_id=8888 的gf_name 和 gf_phone 字段 . 可是我有100万个女朋友啊。。。。。

  • 怎么办,直接返回???? 可是你妹的还让我 order by gf_name ,按女朋友的名字给你排个序呀???? 数据都还没找全,那么就得把查到的数据先放在一个地方,这就是 sort_buffer

sort_buffer 是用于这种情况下排序用的缓冲区,这里需要注意的是每个线程都会有一个单独的 sort_buffer,这么做的目的主要是为了避免多个线程对同一块内存进行操作带来锁竞争的问题。


  • 当第一条数据的 gf_name 和 gf_phone 已经放入 sort_buffer 中,这肯定不能结束呀,会一直重复同步的步骤,直至把所有 user_id=8888 的 gf_name 和 gf_phone 都放入到 sort_buffer 中才结束.

  • sort_buffer 中的数据已经放入完毕,接下来就该排序了,这里 MySQL 会对 gf_name 进行快排,通过快排后,sort_buffer 中 gf_name 就是有序的了


  • 最后返回 sort_buffer 中的limit 条数,结束。


就是这么顺畅啊。。。。。

整个过程类似如下 :

sort_buffer 占用的是内存空间,它有上限的, 不能太小,又不能太大。


在 InnoDB 存储引擎中,这个值是默认是256K。

那如果要放进 sort_buffer 中的数据是大于256K的话,那么采用在 sort_buffer 中快排的方式肯定是不行的啊。

那MySQL难道不能根据数据大小自动扩充吗?


MySQL是多线程模型,如果每个线程都扩充,那么分给其他功能buffer就小了(比如change buffer等等等等呢个),就会影响其他功能了,所以,mysql采用了另外一种方式处理 。


filesort 步骤

此时就是真正的文件排序了,也就是磁盘的临时文件,MySQL会采用归并排序的思想,把要排序的数据分成若干份,每一份数据在内存中排序后会放入临时文件中,最终对这些已经排序好的临时文件的数据再做一次合并排序就ok了. 这不就是分治的思想嘛 。

具体步骤如下

  • 先将要排序的数据分割,分割成每块数据都可以放到 sort_buffer 中


  • 对每块数据在 sort_buffer 中进行排序,排序好后,写入某个临时文件中

  • 当所有的数据都写入临时文件后,这时对于每个临时文件而言,内部都是有序的,但是它们并不是一个整体,整体还不是有序的,所以接下来就得合并数据


  • 假设现在存在 tmpX 和 tmpY 两个临时文件,这时会从 tmpX 读取一部分数据进入内存,然后从 tmpY 中读取一部分数据进入内存

为什么是一部分而不是整个或者单个?因为首先磁盘是缓慢的,所以尽量每次多读点数据进入内存,但是不能读太多,因为还有 buffer 空间的限制。


  • 对于 tmpX 假设读进来了的是 tmpX[0-5] ,对于 tmpY 假设读进来了的是 tmpY[0-5],于是只需要这样比较:如果 tmpX[0] < tmpY[0],那么 tmpX[0] 肯定是最小的,然后 tmpX[1] 和 tmpY[0] 比如,如果 tmpX[1] > tmpY[0],那么 tmpY[0] 肯定是第二小的…,就这样两两比较最终就可以把 tmpX 和 tmpY 合并成一个有序的文件tmpZ,多个这样的tmpZ再次合并…,最终就可以把所有的数据合并成一个有序的大文件。


rowid 排序 ?

看完了上面的排序流程 , 如果要排序的数据很大,超过 sort_buffer 的大小,那么就需要文件排序,文件排序涉及到分批排序与合并,很耗时。


为什么呢? sort_buffer 不够用啊

那就两个思路

  1. 扩 --------------> mysql不支持,甭想了
  2. 缩 ---------------> 少向sort buffer中放一点,这样就能多存点了


我们分析下SQL

select gf_name,gf_phone from user where user_id=8888 order by gf_name

我们需要按照 gf_name 排序,但是却把 gf_phone 也塞进了 sort_buffer 中

这样 单行数据的大小就等于 gf_name 的长度 + gf_phone 的长度 ,能否让 sort_buffer 中只存 gf_name 字段,这样的话,整体的利用空间就大了,说不定不一定用得到到临时文件, 哈哈哈哈


是的,这就是另外一种排序优化 rowid排序

rowid 排序的思想就是把不需要的数据不要放到 sort_buffer 中,让 sort_buffer 中只保留必要的数据。


只放 gf_name 可以吗 ?


SQL: select gf_name,gf_phone

排序完了之后, 要获取gf_phone怎么办?


因此还要把主键id放进去,这样排完之后,通过 id 再回次表,拿到 gf_phone即可.


大致流程如下:

1. 根据 user_id 索引,查到目标数据,然后回表,只把 id 和 gf_name 放进 sort_buffer 中

2. 重复1步骤,直至全部的目标数据都在 sort_buffer 中

3. 对 sort_buffer 中的数据按照 gf_name 字段进行排序

4. 排序后根据 id 再次回表查到 gf_phone 返回,直至返回1000条数据,结束。


有几点需要注意的:

  1. 需要两次回表
  2. sort_buffer 虽然小了,但是如果数据量本身还是很大,应该还是要临时文件排序的\


MySQL 该如何选择?得根据某个条件来判断走哪种方式吧,这个条件就是进 sort_buffer 单行的长度,如果长度太大(gf_name+ gf_phone的长度),就会采用 rowid 这种方式,否则第一种,长度的标准是根据 max_length_for_sort_data 来的,这个值默认是1024字节:


不想回表? 也不想排序?

不管是上面哪种方法,我们发现其实是都需要 回表 + 排序的

回表是因为二级索引上没有目标字段

排序是因为数据不是有序的

那如果二级索引上有目标字段并且已经是排序好的了,那不就OK了嘛


联合索引 搞一搞嘛

只需要建立一个 (user_id,gf_name,gf_phone)的联合索引即可,这样就可以通过这个索引拿到目标数据,并且gf_name已经是排序好的,同时还有gf_phone字段, 不需要回表,不需要再次排序。

流程大致如下:

  1. 通过联合索引找到user_id=8888的数据,然后读取对应的 gf_name和 gf_phone字段直接返回,因为 gf_name已经是排序好的了,不需要额外处理
  2. 重复第一步骤,顺着叶子节点接着向后找,直至找到第一个不是8888的数据,结束。


温馨提示

小提示: 联合索引虽然可以解决这种问题,但是在实际应用中切不可盲目建立,要根据实际的业务逻辑来判断是否需要建立,如果不是经常有类似的查询,可以不用建立,因为联合索引会占用更多的存储空间和维护开销。



验证

ALTER TABLE `artisan`.`user` 
DROP INDEX `user_id`,
ADD INDEX `idx_name_phone`(`user_id`, `gf_name`, `gf_phone`);

业务系统,一般我们都得加limit呀。谁会取100万出来呢?

看看执行计划吧


回顾总结

  1. 对于 order by 没有用到索引的时候,这时 explain 中 Extra 字段大概是会出现 using filesort 字眼
  2. 出现 using filesort 的时候也不用太慌张,如果本身数据量不大,比如也就几十条数据,那么在 sort buffer 中使用快排也是很快的
  3. 如果数据量很大,超过了 sort buffer 的大小,那么是要进行临时文件排序的,也就是归并排序,这部分是由 MySQL 优化器决定的
  4. 如果查询的字段很多,想要尽量避免使用临时文件排序,可以尝试设置下 max_length_for_sort_data 字段的大小,让其小于所有查询字段长度的总和,这样放入或许可以避免,但是会多一次回表操作
  5. 实际业务中,我们也可以给经常要查询的字段组合建立个联合索引,这样既不用回表也不需要单独排序,但是联合索引会占用更多的存储和开销
  6. 大量数据查询的时候,尽量分批次,提前 explain 来观察 sql 的执行计划是个不错的选择。


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
14天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
52 3
|
4月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
161 0
|
14天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(上)
最终建议:当前系统是完美的读密集型负载模型,优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存,预期可降低30%的CPU负载,同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
73 6
|
14天前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(中)
使用MYSQL Report分析数据库性能
62 1
|
2月前
|
存储 关系型数据库 MySQL
深入理解MySQL索引类型及其应用场景分析。
通过以上介绍可以看出各类MySQL指标各自拥有明显利弊与最佳实践情墁,在实际业务处理过程中选择正确型号极其重要以确保系统运作流畅而稳健。
115 12
|
2月前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
92 6
|
3月前
|
存储 SQL 关系型数据库
MySQL的Redo Log与Binlog机制对照分析
通过合理的配置和细致的管理,这两种日志机制相互配合,能够有效地提升MySQL数据库的可靠性和稳定性。
120 10
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
3月前
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。(图解+秒懂+史上最全)
|
3月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
128 0