最新要闻
- 投诉不断 极氪被爆出现大规模动力故障 官方回应:会升级
- 焦点讯息:昆虫学硕士因表演双节棍获得工作 2000万粉大V感慨:掌握一门手艺很重要
- 当前报道:手机QQ崩了:显示无网络 你消息接收正常吗?
- 【环球新要闻】Moment推出1.55X变形镜头:iPhone也能拍出电影级超宽照片
- 十八罗汉分别叫什么?十八罗汉排名及顺序
- 马前泼水指的是什么生肖?马前泼水这个故事是什么意思?
- 巴黎恋人的结局是什么?巴黎恋人演员表
- 手净欲摸杯是什么意思?手净欲摸杯的出处是哪里?
- 20088乐队现在怎么样?20088乐队现在怎么样了?
- 票房榜前10稳了!《流浪地球2》总票房突破35亿 官方公布月球美术设计
- 每日信息:丰田皇冠遭车主集体投诉:空调管短了 漏水漏风
- 腾讯严惩《和平精英》外挂 一大批玩家一觉醒来发现被封号10年
- 高速特斯拉自动驾驶 驾驶员座位上睡觉!网友:保险赔吗?
- 世界实时:女子应聘人事被要求身高1米63以上 追问HR被回怼:不自信就不用了解了
- 快喝不起了!农夫山泉涨价:纯净水、矿泉水啥区别、到底哪个好?
- 天天微头条丨在ChatGPT眼里 未来的汽车竟长这模样
手机
iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
- 警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- 男子被关545天申国赔:获赔18万多 驳回精神抚慰金
- 3天内26名本土感染者,辽宁确诊人数已超安徽
- 广西柳州一男子因纠纷杀害三人后自首
- 洱海坠机4名机组人员被批准为烈士 数千干部群众悼念
家电
滴滴一面:order by 调优10倍,思路是啥?
文章很长,而且持续更新,建议收藏起来,慢慢读!疯狂创客圈总目录 博客园版为您奉上珍贵的学习资源 :
【资料图】
免费赠送 :《尼恩Java面试宝典》持续更新+ 史上最全 + 面试必备 2000页+ 面试必备 + 大厂必备 +涨薪必备免费赠送 经典图书:《Java高并发核心编程(卷1)加强版》面试必备 + 大厂必备 +涨薪必备 加尼恩免费领免费赠送 经典图书:《Java高并发核心编程(卷2)加强版》面试必备 + 大厂必备 +涨薪必备 加尼恩免费领免费赠送 经典图书:《Java高并发核心编程(卷3)加强版》面试必备 + 大厂必备 +涨薪必备 加尼恩免费领免费赠送 经典图书:《尼恩Java面试宝典》 面试必备 + 大厂必备 +涨薪必备 加尼恩免费领免费赠送 资源宝库: Java 必备 百度网盘资源大合集 价值>10000元 加尼恩领取
背景说明:
Mysql调优,是大家日常常见的调优工作。
所以,Mysql调优是一个非常、非常核心的面试知识点。
在40岁老架构师 尼恩的读者交流群(50+)中,其相关面试题是一个非常、非常高频的交流话题。
近段时间,有小伙伴面试滴滴,说遇到一个order by 调优的面试题:
order by 线上的查询速度太慢, 需要优化10倍以上, 说说你的思路?
社群中,还遇到过大概的变种:
形式1:order by 是怎么实现排序的?
形式2:order by 是怎么实现优化的?
形式3: 后面的变种,应该有很多变种........,会收入 《尼恩Java面试宝典》。
这里尼恩给大家order by 调优,做一下系统化、体系化的梳理,使得大家可以充分展示一下大家雄厚的 “技术肌肉”,让面试官爱到 “不能自已、口水直流”。
也一并把这个 题目以及参考答案,收入咱们的《尼恩Java面试宝典》V44版本,供后面的小伙伴参考,提升大家的 3高 架构、设计、开发水平。
注:本文以 PDF 持续更新,最新尼恩 架构笔记、面试题 的PDF文件,请从这里获取:码云
回答这个order by的优化之前,首先要给面试官介绍一下order by的底层原理。
首先、什么是Order by 工作原理?
假设,有一个用户表为例,表结构如下:
create table `user` ( `id` int(11) not null auto_increment COMMENT "id", `city` varchar(16) not null COMMENT "城市", `name` varchar(16) not null COMMENT "姓名", `age` int(11) not null COMMENT "年龄", `sex` int(1) default 1 COMMENT "性别", primary key (`id`), key `city` (`city`)) engine = InnoDB comment "用户表";
表数据示例如下:
现在假定有个需求:查询前 5 个来自北京的用户姓名、年龄、城市,并且按照年龄升序排序。
那么相应的 SQL 如下:
select name, age, city from user where city = "北京" order by age limit 5;
这条 SQL 语句逻辑简单清晰,要点有3个:
- city = "北京" :有where 查询条件
- order by age :根据 age 排序,默认是asc
- limit 5 :取得 top5
那么 mysql 底层是如何执行的呢?
首先,给大家介绍一下宏观的思路。
Order by 执行的两步
总体来说,Order by 执行流程,分为两步,具体如下:
第1步:索引的查找
根据where 后面的字段,进行 二级索引的查找,找到后再回表 聚集索引,拿到需要的字段
第2步:原始数据的排序
原始数据的数据, 并不是按照 order by 有序的。
所以,需要按照 order by 字段,进行排序。
接下来,排序的地点在哪里呢?
- 优先选择内存。因为内存的性能高。
- 如果原始的数据实在规模太大,就借助磁盘进行排序。
用于排序的内存,称为 sort_buffer。其实 MySQL 会给每个线程分配一块内存用于排序的 sort_buffer。
了解了整个步骤后,开始来看看执行计划。
然后再看怎么优化。
Explain查看执行计划
我们先用Explain关键字查看一下执行计划。
那么相应的 SQL 如下:
select name, age, city from user where city = "北京" order by age limit 5;
可以看到:
- key 字段表示使用到 city 索引,
- Extra 字段中的
Using index condition
表示用到了索引条件, Using filesort
表示用到了文件排序 。
用到文件排序,说明第一次查出来的 原始数据,在内存放不下, 需要借助 磁盘空间进行排序,
磁盘IO的性能比较低的,所以,需要进行调优。
再调优之前,首先图解一下order
图解一下Order by 执行的两步
第1步:索引的查找
根据where 后面的字段,进行 二级索引的查找,找到后再回表 聚集索引,拿到需要的字段
回顾 SQL 如下:
select name, age, city from user where city = "北京" order by age limit 5;
首先从 二级索引city 索引树 的查找 city = "北京" 的索引叶子。
在 city 索引树中是非聚簇索引树,叶子节点存储的是主键 ID。city 索引树 如下:
聚簇索引树的叶子节点则存放的是每行数据,如下图:
查询语句的执行流程就是先通过 city 索引树,找到对应的主键 ID,然后再搜索主键索引树,找到对应的行数据。
这些数据是原始数据,放在内存 sort_buffer 中。
第2步:原始数据的排序
原始数据的数据, 并不是按照 order by 有序的。 所以,需要按照 order by 字段,进行排序。
加上order by
排序之后,整体的执行流程就是:
Order by 执行完整流程,如下:
- 当前线程首先初始化 sort_buffer 块,
- 然后从 city 索引树从查询一条满足
city="北京"
的主键 ID, 比如图中的id=2
, - 接着在聚簇索引树中查询
id=2
的一行数据,将name
、age
、city
三个字段的值,存到sort_buffer, - 继续重复前两个步骤,直到 city 索引树中找不到
city="北京"
的主键 ID。 - 最后在sort_buffer中,将所有数据根据
age
进行排序,取前 5 行返回给客户端。
全字段排序就是将查询所需的字段,如name
、age
、city
三个字段数据全部存到 sort_buffer 中。
3个核心概念
接下来开始给面试官介绍如何调优。
不过不急,调优涉及到3个核心概念
- 全字段排序
- 外部排序
- rowid 排序
全字段排序
sort_buffer是 MySQL 为每个任务线程维护的一块内存区域,用于进行排序。
sort_buffer的大小可以通过 sort_buffer_size来设置。
那这种处理方式会存在一个问题, sort_buffer 是一块固定大小的内存,如果数据量太大,sort_buffer 放不下怎么办呢?
sort_buffer_size是一个用于控制sort_buffer内存大小的参数。
外部排序
如果要排序的数据小于 sort_buffer_size,那在 sort_buffer 内存中就可以完成排序,如果要排序的数据大于 sort_buffer_size,则需要外部排序,借助磁盘文件来进行排序。
通过执行一下命令,可以查看 SQL 语句执行中是否采用了磁盘文件辅助排序。
set optimizer_trace = "enabled=on";select name,age,city from user where city = "北京" order by age limit 5;select * from information_schema.optimizer_trace;
可以从number_of_tmp_files中看出,是否使用了临时文件。
{ "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`user`", "field": "age" } ], "filesort_priority_queue_optimization": { "limit": 5, "rows_estimate": 992, "row_size": 112, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 28, "number_of_tmp_files": 0, "sort_buffer_size": 720, "sort_mode": "" } } ] }}
number_of_tmp_files 表示使用来排序的磁盘临时文件数。如果 number_of_tmp_files>0,则表示使用了磁盘文件来进行排序。
使用了磁盘临时文件后,当 sort_buffer 内存不足时,先进行排序,将排序后的数据存放到一个小磁盘文件中,清空 sort_buffer。
然后继续存放数据到 sort_buffer,重复以上步骤。最后将多个磁盘小文件合并成一个有序的大文件。
Tips: 磁盘小文件合并排序,使用的是归并排序算法
这样依然会存在问题,数据存放到临时磁盘小文件,然后还需要归并排序为大文件,再读入到内存中,返回结果集,整体排序效率低下。
rowid 排序
要解决上述问题,可以将只需要用于排序的字段和主键 ID 放入 sort_buffer 中,也就是 rowid 排序,这样就可以在 sort_buffer 中完成排序。
max_length_for_sort_data是一个用于表示 Mysql 用于排序行数据长度的一个参数,如果单行数据的长度超过了这个值,那么可能会导致采用临时文件排序,mysql 会换用 rowid 排序。
可以通过命令看下这个参数取值。
show variables like "max_length_for_sort_data";
max_length_for_sort_data默认值是 1024。本文中name,age,city 长度=64+4+64=132<1024
, 所以走的是全字段排序。
我们执行以下命令,改下参数值,再重新执行 SQL。
set max_length_for_sort_data = 32;select name,age,city from user where city = "北京" order by age limit 5;
使用 rowid 排序的后,执行示意图如下:
对比全字段排序,rowid 排序最后需要根据主键 ID 获取对应字段数据即多了回表查询。
当需要查询的数据在索引树中不存在的时候,需要再次到聚集索引中去获取,这个过程叫做回表
我们通过执行以下命令,可以看到是否使用了 rowid 排序的:
set optimizer_trace = "enabled=on";select name,age,city from user where city = "北京" order by age limit 5;select * from information_schema.optimizer_trace;
{ "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`user`", "field": "age" } ], "filesort_priority_queue_optimization": { "limit": 5, "rows_estimate": 992, "row_size": 8, "memory_available": 262144, "chosen": true }, "filesort_execution": [], "filesort_summary": { "rows": 3, "examined_rows": 28, "number_of_tmp_files": 0, "sort_buffer_size": 96, "sort_mode": "" } } ] }}
sort_mode表示排序模式为 rowid 排序。
order by 的优化思路
重点来了
如何调优呢?两大措施:
联合索引优化
如果数据本身是有序的,那就不需要排序,而索引数据本身是有序的,所以,我们可以通过建立联合索引,跳过排序步骤。
参数优化
可以通过调整max_length_for_sort_data等参数优化排序算法;
联合索引
基于查询条件和排序条件,给表加个联合索引idx_city_age
。然后再查看执行计划:
alter table user add index idx_city_age(city,age);explain select name,age,city from user where city = "北京" order by age limit 5;
从执行计划Extra字段中可以发现没有再使用Using filesort排序,keys使用了idx_city_age
索引。
idx_city_age
联合索引示意图,如下:
整个 SQL 执行流程图如下所示:
任务线程从idx_city_age
索引树中可以有序的获取满足条件的主键 ID, 然后根据主键 ID 查询对应字段数据,不需要在 sort_buffer 中排序了。
从示意图看来,还是有一次回表操作,那可以通过覆盖索引来解决,给city
,name
,age
组成一个联合索引,省去回表操作。
覆盖索引是 select 的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。
参数优化
通过调整参数,也可以优化order by
的执行。
- 调整 sort_buffer_size 参数的值。如果 sort_buffer 值太小而数据量大的话,MySQL 会采用磁盘临时文件辅助排序。MySQL 服务器配置高的情况下,可以将参数调大些。
- 调整 max_length_for_sort_data 的值,值太小的话 MySQL 会采用 rowid 排序,会多一次回表操作导致查询性能降低。同样可以适当调大些。
推荐阅读:
《网易二面:CPU狂飙900%,该怎么处理?》
《阿里二面:千万级、亿级数据,如何性能优化? 教科书级 答案来了》
《峰值21WQps、亿级DAU,小游戏《羊了个羊》是怎么架构的?》
《场景题:假设10W人突访,你的系统如何做到不 雪崩?》
《2个大厂 100亿级 超大流量 红包 架构方案》
《Nginx面试题(史上最全 + 持续更新)》
《K8S面试题(史上最全 + 持续更新)》
《操作系统面试题(史上最全、持续更新)》
《Docker面试题(史上最全 + 持续更新)》
《Springcloud gateway 底层原理、核心实战 (史上最全)》
《Flux、Mono、Reactor 实战(史上最全)》
《sentinel (史上最全)》
《Nacos (史上最全)》
《分库分表 Sharding-JDBC 底层原理、核心实战(史上最全)》
《clickhouse 超底层原理 + 高可用实操 (史上最全)》
《nacos高可用(图解+秒懂+史上最全)》
《队列之王: Disruptor 原理、架构、源码 一文穿透》
《环形队列、 条带环形队列 Striped-RingBuffer (史上最全)》
《一文搞定:SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系(史上最全)》
《红黑树( 图解 + 秒懂 + 史上最全)》
《分布式事务 (秒懂)》
《缓存之王:Caffeine 源码、架构、原理(史上最全,10W字 超级长文)》
《缓存之王:Caffeine 的使用(史上最全)》
《Docker原理(图解+秒懂+史上最全)》
《Redis分布式锁(图解 - 秒懂 - 史上最全)》
《Zookeeper 分布式锁 - 图解 - 秒懂》
《Zookeeper Curator 事件监听 - 10分钟看懂》
《Netty 粘包 拆包 | 史上最全解读》
《Netty 100万级高并发服务器配置》
滴滴一面:order by 调优10倍,思路是啥?
投诉不断 极氪被爆出现大规模动力故障 官方回应:会升级
焦点讯息:昆虫学硕士因表演双节棍获得工作 2000万粉大V感慨:掌握一门手艺很重要
当前报道:手机QQ崩了:显示无网络 你消息接收正常吗?
【环球新要闻】Moment推出1.55X变形镜头:iPhone也能拍出电影级超宽照片
十八罗汉分别叫什么?十八罗汉排名及顺序
马前泼水指的是什么生肖?马前泼水这个故事是什么意思?
巴黎恋人的结局是什么?巴黎恋人演员表
手净欲摸杯是什么意思?手净欲摸杯的出处是哪里?
环球观点:Linux 命令大全:2万字实现Linux自由
20088乐队现在怎么样?20088乐队现在怎么样了?
milo显示器是什么牌子?milo显示器怎么样?
ec文件是什么的简称?ec文件怎么打开?
神舟笔记本如何下载安装软件?神舟笔记本如何下载驱动?
tplink路由器怎么设置端口映射?tplink路由器怎么重新设置密码?
冰箱电磁阀怎么判断好坏?冰箱电磁阀的颜色代表什么?
票房榜前10稳了!《流浪地球2》总票房突破35亿 官方公布月球美术设计
每日信息:丰田皇冠遭车主集体投诉:空调管短了 漏水漏风
腾讯严惩《和平精英》外挂 一大批玩家一觉醒来发现被封号10年
高速特斯拉自动驾驶 驾驶员座位上睡觉!网友:保险赔吗?
世界实时:女子应聘人事被要求身高1米63以上 追问HR被回怼:不自信就不用了解了
最新资讯:keycloak~JWT没有被持久化_是因为你对方法论理解不到位
快喝不起了!农夫山泉涨价:纯净水、矿泉水啥区别、到底哪个好?
天天微头条丨在ChatGPT眼里 未来的汽车竟长这模样
天天快报!ChatGPT引发失业恐慌?这20种工作要避开:含医生、快递员
天天新动态:果然“超级奶爸”!李想晒六口全家福:期待五胎到来
订单充足不愁卖 国产特斯拉Model Y入门版涨价:贵了2000
环球滚动:学习笔记——redis持久化之RDB、AOF
BigDecimal加减乘除运算整合工具类
天天新资讯:4年还完20万欠款!夫妻回应剪掉名下所有信用卡 告别卡奴引网友感叹
天天热文:Win11又出严重bug:Intel用户程序崩溃 AMD躲过一劫
世界热点!1600人失业 老牌互联网巨头雅虎裁员20%:离职补偿未定
世界讯息:开窍!iPhone 15摄像头终于要升级:苹果设计让人抓狂 背部凸起更严重
环球视讯!3499元爆火!真我GT Neo5 1T版抢购一空:2023年旗舰射门员
世界最资讯丨日本国产大飞机失败:三菱重工不服输 自研新一代战机
天天看点:读Java实战(第二版)笔记06_新的日期和时间API
世界时讯:NVIDIA发布GeForce 528.49驱动:首发支持史上最强移动显卡
全球实时:网友50000元攒机 到底是不是大冤种?
今日热闻!基于高层次综合器(Vivado HLS)的硬件优化[原创www.cnblogs.com/helesheng]
你同意?张朝阳:《流浪地球2》跟好莱坞还是没法比
【天天时快讯】奇葩!哈尔滨机场一旅客为逃避安检把活蜗牛藏嘴里
环球热议:Google放大招对抗ChatGPT:结果低级答错题 市值蒸发1000亿
微头条丨单枪匹马也能拍大片!这次又让大疆给拿捏了
快资讯:史上最强AMD显卡!撼迅正式发布水冷RX 7900 XTX 还是单插槽
Spring配置类理解(Lite模式和Full模式)
环球速讯:JAVA中如何判断一个ResultSet结果集是否为空
【天天速看料】MYSQL脱敏 || 给开发人员限制权限,保证mysql数据库数据安全
重点聚焦!小白也能做应用(二)之fusion app增加B站视频页面
每日观点:法国调香师夸国产花露水清新还美丽 要卖450元 网友:六神YYDS
MATLAB 实现点云累计-坐标系转换-目标范围点云提取(附代码与代码注释)
【全球快播报】面向对象知识点汇总(小白必会)
(一)浅谈人工智能:ChatGPT
《蚁人3》女儿凯茜中文预告公布 首映礼美艳图赏
环球热消息:【一句话】@Configuration和@Component的区别
世界看热讯:《分布式技术原理与算法解析》学习笔记Day06
焦点消息!ASP.NET Core+Element+SQL Server开发校园图书管理系统(完)
全球看点:if else 代码优化实战
全球动态:网络协议-ssh基础
当前关注:重回中国!福建13岁女孩4.82秒打破鲁班锁世界纪录
今日讯!ROG新款幻14笔记本即将上市:锐龙9 7940HS 可选RTX 4060
免费用!中国信通院全球网测APP iOS版上线:支持5G/千兆接入测速
速读:71款任选 森马T恤33元清仓手慢无
动态:祝福!比尔盖茨有新女友了 身家超33亿女富婆:双方沉默 前妻也有新恋情
完美收官!《三体》电视剧最后5集被赞爆:老外满分刷屏
快看:80km续航 雅迪电动车DN2千元秒杀:2699元起还是新国标
Copula
今亮点!女子熬夜看《狂飙》:患高启强同款干眼症
世界热头条丨逆天 韩国学生用ChatGPT写论文“喜”提0分 校方:剽窃!
快资讯:再续一个月:《阿凡达:水之道》再次宣布延长上映
28款CPU性能实测:Intel实力屠榜 AMD游戏神U赢回面子
全球消息!全国首座高速公路重卡充换电站亮相:电池包350kWh 3分钟自动换电
基于图的下一代入侵检测系统
环球快资讯丨MySQL——性能优化的关注点
天天简讯:记录--Cesium+Vue实战教程——地图导航
世界关注:人工智能(Python)教程_编程入门自学教程_菜鸟教程-免费教程分享
天天要闻:安卓常用shell命令大全
环球视点!无损压缩鼻祖去世了 没有他就没有今天的Zip、PNG、PDF
当前观点:博主“科普”鲸鱼会假装搁浅吸引人来救 博物杂志辟谣:九死一生
天天快讯:行驶中会“熄火” 大众召回超2万辆ID.4电动车
世界报道:欧洲玩家吐槽《霍格沃茨之遗》捏脸系统:最浅的皮肤选项依然很深
当前速看:Stochastic Methods in Finance (1)
【独家】.Net6对AOP的多种支持之IAsyncResourceFilter
世界观天下!常见的python技术难点分享
当前速讯:Nodejs原型链污染
世界要闻:[threeJS]--- 外部导入的模型如何编程式实现帧动画以及调用模型自带的动画
情人节必备:德芙香浓黑巧12元/碗大促
逃离银河系!科学家在仙女星系中发现银河移民
世界快播:山东女子中淘宝彩票锦鲤:直播1小时刮594张彩票 中7170元
天天滚动:骁龙8+满血版、残血版差价非常大 一加揭秘:能差1个亿
防止技术垄断:昆仑万维宣布将在年内开源类ChatGPT代码
【全球播资讯】陕西旅游集团旗下景区春节期间累计接待超 200 万人次,这背后也有火山引擎 VeDI 的身影
当前热门:分享5个我不能没有的Vue.js库,不信你用不上
KingbaseES libstdc++.so.6 version 'CXXABI_1.3.8'问题处理
敏捷数据科学教程_编程入门自学教程_菜鸟教程-免费教程分享
MPI库并行Fortran程序:进程通讯
微头条丨女子带汉堡进星巴克被拦 称味道大会影响其他顾客 网友抵制
微信数据再多都够用 真我GT Neo5 1TB干到3499元:旗舰射门员
新资讯:14岁女孩连续玩手机81小时险猝死 专家提醒:家长一定要控制
【环球时快讯】1TB手机不到3500元!网友评价真我GT Neo5:这让友商很难做
世界新消息丨又多了一种摸鱼手段 小红书网页版上线:左图右文 沉浸大屏