正排倒排,并不是 MySQL 的排序的全部!

Java技术栈

共 1915字,需浏览 4分钟

 ·

2021-06-23 22:51

点击关注公众号,Java干货及时送达

作者:温安适
来源:https://my.oschina.net/floor/blog/4961581

引言

春节前一个悠闲的上午,小航送了我,一袋坚果,他看我吃的正香,慢慢问道:”温哥,mysql的排序,有什么要注意的吗,不就是正排倒排吗?”

我一听他问我的问题,顿感坚果不香了,但是为了技术(mainzi),我装作大师的说道:

“正排倒排,当然不是全部,你最少要知道,2个参数,1个优化,一种特殊情况”

注:东西不能乱吃啊

两个核心参数

sort_buffer_size 决定内排,外排。内排就是走内存,外排就是采用归并排序走磁盘。

max_length_for_sort_data 决定 全字段排序还是,rowid排序。

全字段排序

字段都放到 sort_buffer 中,排序后就会直接从内存里面返回查询结果了

Rowid排序

内存放rowid与排序字段,排序后,再从库中找数据,拼接返回。

优化手段覆盖索引

覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。另外,MySQL 系列面试题和答案全部整理好了,微信搜索Java技术栈,在后台发送:面试,可以在线阅读。

例子

explain
SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

用到了filesort,也就是需要排序。《MySQL 开发的 36 条军规推荐看下

调整索引

ALTER TABLE `orders_detail` DROP INDEX `order_id`,ADD INDEX `order_id` (`order_id``pay_date`);

之后

explain
SELECT order_id,pay_date FROM orders_detail WHERE order_id='1001' ORDER BY pay_date asc

没有用到filesort,因为复合索引,字段后是有序的

特殊情况 Order by+ Limit

Limit可能用到优先队列排序算法。

例子:

  1. 开启优化追踪
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=off;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
  1. 查看字段索引
SHOW INDEX FROM oc_order_online WHERE COLUMN_NAME='order_name';

结果显示没有索引

  1. 执行order by+limit 查询语句
select * from `oc_order_online`  order by  `order_name`  limit 20
  1. 查询优化追踪信息
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30

对应结果如下:

查询将红框中数据,粘贴到json.cn查看格式化数据,有如下片段

filesort_priority_queue_optimization 中的chosen:true表示使用了优先队列排序。另外,关注公众号Java技术栈,在后台回复:面试,可以获取我整理的 Java 系列面试题和答案,非常齐全。

总结

  1. sort_buffer_size 决定内排,外排
  2. max_length_for_sort_data 决定 全字段排序还是,rowid排序
  3. 覆盖索引是一种优化手段
  4. Limit可能涉及优先队列排序






关注Java技术栈看更多干货



获取 Spring Boot 实战笔记!
浏览 18
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报