你真的了解MySQL的order by吗

最新推荐文章于 2024-03-26 08:50:44 发布

码事通

最新推荐文章于 2024-03-26 08:50:44 发布

阅读量114

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/m0_64355285/article/details/122411953

版权

本文探讨了MySQL中关于`ORDER BY`的使用，特别是当`ORDER BY`字段没有索引时的情况。文章通过一个实际场景，解释了`USING FILESORT`的原理，包括内存排序和磁盘临时文件排序的过程。同时，提出了优化方法，如减少回表操作和使用合适大小的`sort_buffer`，以及创建联合索引以避免额外排序。最后，强调了在处理大量数据时，理解`ORDER BY`的执行计划和合理设置参数的重要性。

摘要由CSDN通过智能技术生成

排序这个词，我的第一感觉是几乎所有App都有排序的地方，淘宝商品有按照购买时间的排序、B站的评论有按照热度排序的...，当然我们今天说的并不是大数据下该如何优雅的排序，如何提升排序性能的问题，我们说一说MySQL中的排序。

对于MySQL，一说到排序，你第一时间想到的是什么？关键字order by？order by的字段最好有索引？叶子结点已经是顺序的？还是说尽量不要在MySQL内部排序？

事情的起因

现在假设有一张用户的朋友表：

CREATE TABLE `user` (
  `id` int(10) AUTO_INCREMENT,
  `user_id` int(10),
  `friend_addr` varchar(1000),
  `friend_name` varchar(100),  
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;
复制代码

表中目前有两个点需要关注下：

用户的 user_id ，朋友的姓名 friend_name、朋友的地址 friend_addr
user_id 是有索引的

有一天，有个初级开发工程师小猿，收到了来自初级产品经理小汪的需求：
小汪：小猿同志，现在需要在后台加个功能，这个功能要支持根据用户 id 能查到他所有的朋友姓名和地址，并且要求朋友的姓名是按照字典排序的。
小猿：好的，这个功能简单，我马上就上线。

于是小猿书写了这样的sql：

select friend_name，friend_addr from user where user_id=? order by name
复制代码

在电光石火的瞬间，小猿趾高气昂的上线了，这一切都很顺利，直到有一天有个运营同学导致了这样的查询：

select friend_name，friend_addr from user where user_id=10086 order by name
复制代码

然而，这个查询竟然比平时慢很多，数据库报了慢查询，小猿此时慌的一b：这是怎么回事？user_id 明明有索引啊，而且机智地我还只用了 select friend_name,friend_addr，并没有用 select *呀。小猿此时不停地安慰自己，要淡定要淡定，然后突然想到有个explain命令，用explain来查看下那条sql的执行计划吧，当小猿用了explain之后，发现extra字段里面有个看起来很危险的字眼：using filesort。

“这个查询竟然用到了传说中的文件排序，但是如果一个人朋友不是很多