最新要闻
- 楼市回暖背后:多城二手房在售量持续增加
- 环球新消息丨vivo将整合旗下iQOO手机:开启降本增效
- 王传福:比亚迪目标年底成为中国最大汽车制造商
- 全球热讯:全球最大游戏展危险了!世嘉、腾讯均宣布不参加本届E3
- 绿巨人前女友15年后回归漫威!《美国队长4》新剧照曝光:黑人美队现身
- 环球即时看!联名高达!ROG游戏手机7系列来了:二代骁龙8+6000mAh电池
- 全球视讯!中国代表敦促个别国家立即解除对叙利亚单边制裁
- 天天简讯:四方达(300179)3月28日主力资金净卖出861.62万元
- 微信QQ出现功能异常 官方紧急修复:微信支付等功能已恢复
- 天天速看:男子陵园祭祖车雷达显示全是人 网友:啥车?我想试试
- PS主机神作好评仅有32%!顽皮狗回应《最后生还者》PC优化翻车
- 全球速读:专家谈ChatGPT:或让更多人享受生活 一周只工作一天
- 全球热讯:电池级碳酸锂价格腰斩 电动车会降价吗?专家给出结论
- 环球速看:魔法氛围拉满!Redmi Note 12 Turbo哈利波特版图赏
- 不用带手机 抬手就能付!微信支付尝试推出微信刷掌服务
- 天天要闻:开发者危机!微软GitHub启动裁员:印度工程师团队几乎整体裁撤
手机
iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
- 警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- 男子被关545天申国赔:获赔18万多 驳回精神抚慰金
- 3天内26名本土感染者,辽宁确诊人数已超安徽
- 广西柳州一男子因纠纷杀害三人后自首
- 洱海坠机4名机组人员被批准为烈士 数千干部群众悼念
家电
全球实时:一文带你搞懂如何优化慢SQL
作者:京东科技宋慧超
一、前言
(资料图片仅供参考)
最近通过SGM监控发现有两个SQL的执行时间占该任务总执行时间的90%,通过对该SQL进行分析和优化的过程中,又重新对SQL语句的执行顺序和SQL语句的执行计划进行了系统性的学习,整理的相关学习和总结如下;
二、SQL语句执行顺序
要想优化慢SQL语句首先需要了解SQL语句的执行顺序,SQL语句中的各关键词执行顺序如下:
◦首先执行from、join来确定表之间的连接关系,得到初步的数据。
◦然后利用where关键字后面的条件对符合条件的语句进行筛选。
from&join&where:用于确定要查询的表的范围,涉及到哪些表。
选择一张表,然后用join连接:
from table1 join table2 on table1.id=table2.id
选择多张表,用where做关联条件:
from table1,table2 where table1.id=table2.id
最终会得到满足关联条件的两张表的数据,不加关联条件会出现笛卡尔积。
◦然后利用group by对数据进行分组。
按照SQL语句中的分组条件对数据进行分组,但是不会筛选数据。
下面用按照id的奇偶进行分组:
◦然后分组后的数据分别执行having中的普通筛选或者聚合函数筛选。
having&where
having中可以是普通条件的筛选,也能是聚合函数,而where中只能是普通函数;一般情况下,有having可以不写where,把where的筛选放在having里,SQL语句看上去更丝滑。
使用where再group by: 先把不满足where条件的数据删除,再去分组。
使用group by在having:先分组再删除不满足having条件的数据。(该两种几乎没有区别)
比如举例如下:100/2=50,此时我们把100拆分(10+10+10+10+10…)/2=5+5+5+…+5=50,只要筛选条件没变,即便是分组了也得满足筛选条件,所以where后group by和group by再having是不影响结果的!
不同的是,having语法支持聚合函数,其实having的意思就是针对每组的条件进行筛选。我们之前看到了普通的筛选条件是不影响的,但是having还支持聚合函数,这是where无法实现的。
当前的数据分组情况
执行having的筛选条件,可以使用聚合函数。筛选掉工资小于各组平均工资的having salary
然后再根据我们要的数据进行select,普通字段查询或者聚合函数查询,如果是聚合函数,select的查询结果会增加一条字段。
分组结束之后,我们再执行select语句,因为聚合函数是依赖于分组的,聚合函数会单独新增一个查询出来的字段,这里我们两个id重复了,我们就保留一个id,重复字段名需要指向来自哪张表,否则会出现唯一性问题。最后按照用户名去重。
select employee.id,distinct name,salary, avg(salary)
将各组having之后的数据再合并数据。
◦然后将查询到的数据结果利用distinct关键字去重。
◦然后合并各个分组的查询结果,按照order by的条件进行排序。
比如这里按照id排序。如果此时有limit那么查询到相应的我们需要的记录数时,就不继续往下查了。
◦最后使用limit做分页。
记住limit是最后查询的,为什么呢?假如我们要查询薪资最低的三个数据,如果在排序之前就截取到3个数据。实际上查询出来的不是最低的三个数据而是前三个数据了,记住这一点。
假如SQL语句执行顺序是先做limit再执行order by,执行结果为3500,5500,7000了(正确SQL执行的最低工资的是3500,5500,5500)。
SQL查询时需要遵循的两个顺序:
1、关键字的顺序是不能颠倒的。
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
2、select语句的执行顺序(在MySQL和Oracle中,select执行顺序基本相同)。
FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT
以SQL语句举例,那么该语句的关键字顺序和执行顺序如下:
SELECT DISTINCT player_id, player_name, count(*) as num #顺序5FROM player JOIN team ON player.team_id = team.team_id #顺序1WHERE height > 1.80 #顺序2GROUP BY player.team_id #顺序3HAVING num > 2 #顺序4ORDER BY num DESC #顺序6LIMIT 2 #顺序7
三、SQL执行计划
• 为什么要学习SQL的执行计划?
因为一个sql的执行计划可以告诉我们很多关于如何优化sql的信息 。通过一个sql计划,如何访问表中的数据 (是使用全表扫描还是索引查找?)一个表中可能存在多个不同的索引,表中的类型是什么、是否子查询、关联查询等…
• 如何获取SQL的执行计划?
在SQL语句前加上explain关键词皆可以得到相应的执行计划。其中:在MySQL8.0中是支持对select/delete/inster/replace/update语句来分析执行计划,而MySQL5.6前只支持对select语句分析执行计划。 replace语句是跟instert语句非常类似,只是插入的数据和表中存在的数据(存在主键或者唯一索引)冲突的时候**,****replace**语句会把原来的数据替换新插入的数据,表中不存在唯一的索引或主键,则直接插入新的数据。
•如何分析SQL语句的执行计划?
下面对SQL语句执行计划中的各个字段的含义进行介绍并举例说明。
◦id列
id标识查询执行的顺序,当id相同时,由上到下分析执行,当id不同时,由大到小分析执行。
id列中的值只有两种情况,一组数字(说明查询的SQL语句对数据对象的操作顺序)或者NULL(代表数据由另外两个查询的union操作后所产生的结果集)。
explainselect course_id,class_name,level_name,title,study_cntfrom imc_course ajoin imc_class b on b.class_id=a.class_idjoin imc_level c on c.level_id =a.level_idwhere study_cnt > 3000
返回3行结果,并且ID值是一样的。由上往下读取sql的执行计划,第一行是table c表作为驱动表 ,等于是以C表为基础来进行循环嵌套的一个关联查询。 (4 *100*1 =400 总共扫描400行等到数据)
◦select_type列
值 | 含义 |
---|---|
SIMPLE | 不包含子查询或者UNION操作的查询(简单查询) |
PRIMARY | 查询中如果包含任何子查询,那么最外层的查询则被标记为PRIMARY |
SUBQUERY | select列表中的子查询 |
DEPENDENT SUBQUERY | 依赖外部结果的子查询 |
UNION | union操作的第二个或者之后的查询值为union |
DEPENDENT UNION | 当union作为子查询时,第二或是第二个后的查询的值为select_type |
UNION RESULT | union产生的结果集 |
DERIVED | 出现在from子句中的子查询(派生表) |
例如:查询学习人数大于3000, 合并 课程是MySQL的记录。
EXPLAINSELECT course_id,class_name,level_name,title,study_cntFROM imc_course ajoin imc_class b on b.class_id =a.class_idjoin imc_level c on c.level_id = a.level_idWHERE study_cnt > 3000unionSELECT course_id,class_name,level_name,title,study_cntFROM imc_course ajoin imc_class b on b.class_id = a.class_idjoin imc_level c on c.level_id = a.level_idWHERE class_name ="MySQL"
分析数据表:先看id等于2
id=2 则是查询mysql课程的sql信息,分别是b,a,c 3个表,是union操作,selecttype为是UNION。
id=1 为是查询学习人数3000人的sql信息,是primary操作的结果集,分别是c,a,b3个表,select_type为PRIMARY。
最后一行是NULL, select_type是UNION RESULT 代表是2个sql 组合的结果集。
◦table列
指明是该SQL语句从哪个表中获取数据
值 | 含义 | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
◦type列 注意: 在MySQL中不一定是使用JOIN才算是关联查询,实际上MySQL会认为每一个查询都是连接查询,就算是查询一个表,对MySQL来说也是关联查询。 type的取值是体现了MySQL访问数据的一种方式。type列的值按照性能高到低排列
•如果where like “MySQL%”,type类型为? 虽然class_name加了索引 ,但是使用where的like% 右统配,所以会走索引范围扫描。
•如果where like “%MySQL%”,type类型为? 虽然class_name加了索引 ,但是使用where的%like% 左右统配,所以会走全索引扫描,如果不加索引的话,左右统配会走全表扫描。
◦possible_key、key列
如果在表中没有可用的索引,那么key列 展示NULL,possible_keys是NULL,这说明查询到覆盖索引。 ◦key_len列 实际用的的索引使用的字节数。 注意,在联合索引中,如果有3列,那么总字节是长度是100个字节的话,那么 key_len的长度是由表中的定义的字段长度来计算的,并不是存储的实际长度,所以满足数据最短的实际字段存储,因为会直接影响到生成执行计划的生成 。 ◦ref列 指出那些列或常量被用于索引查找 ◦rows列 ( 有2个含义)1、根据统计信息预估的扫描行数。 2、另一方面是关联查询内嵌的次数,每获取匹配一个值都要对目标表查询,所以循环次数越多性能越差。 因为扫描行数的值是预估的,所以并不准确。 ◦filtered列 表示返回结果的行数占需读取行数的百分比。 filtered列跟rows列是有关联的,是返回预估符合条件的数据集,再去取的行的百分比。也是预估的值。数值越高查询性能越好。 ◦Extra列 包括了不适合在其他列中所显示的额外信息。
四、SQL索引失效 ◦最左前缀原则:要求建立索引的一个列都不能缺失,否则会出现索引失效。 ◦索引列上的计算,函数、类型转换(列类型是字符串在条件中需要使用引号,否则不走索引)、均会导致索引失效。 ◦索引列中使用is not null会导致索引列失效。 ◦索引列中使用like查询的前以%开头会导致索引列失效。 ◦索引列用or连接时会导致索引失效。 五、实际优化慢SQL中遇到问题 下面是在慢SQL优化过程中所遇到的一些问题。 •MySQL查询到的数据排序是稳定的么? •force_index的使用方式? •为什么有时候order by id会导致索引失效? •........未完整理中...... 六、总结 通过本次对慢SQL的优化的需求进而发现有关SQL语句执行顺序、执行计划、索引失效场景、底层SQL语句执行原理相关知识还存在盲区,得益于此次需求的开发,有深入的对相关知识进行学习和总结。接下来会对SQL底层是如何执行SQL语句 |
关键词:
全球实时:一文带你搞懂如何优化慢SQL
焦点快报!Dijkstar-And-Astar算法
用上ChatGPT的这几个功能,你的开发效率不高都难
联想y400什么时候上市的?联想y400笔记本配置
地下城与勇士龙年套装哪个好?地下城与勇士龙年套装有几个宝珠?
HTCG28什么时候上市的?HTCG28手机参数
华为C8813Q如何装sim卡?华为C8813Q手机参数
gprs套餐费是什么意思?怎么关闭GPRS套餐?
楼市回暖背后:多城二手房在售量持续增加
环球关注:编写高质量c#代码的10个建议
环球即时看!插入排序
【世界速看料】【Visual Leak Detector】配置项 StackWalkMethod
环球新消息丨vivo将整合旗下iQOO手机:开启降本增效
王传福:比亚迪目标年底成为中国最大汽车制造商
全球热讯:全球最大游戏展危险了!世嘉、腾讯均宣布不参加本届E3
绿巨人前女友15年后回归漫威!《美国队长4》新剧照曝光:黑人美队现身
环球即时看!联名高达!ROG游戏手机7系列来了:二代骁龙8+6000mAh电池
全球视讯!中国代表敦促个别国家立即解除对叙利亚单边制裁
天天微动态丨KubeVela 1.7 版本解读:接管你的已有工作负载
焦点报道:接通率维持 66% 以上,为什么火山引擎 VeDI 能让企业智能外呼不再难?
【全球快播报】《Python编程快速上手—让繁琐工作自动化》实践项目答案:第四章
天天简讯:四方达(300179)3月28日主力资金净卖出861.62万元
微信QQ出现功能异常 官方紧急修复:微信支付等功能已恢复
天天速看:男子陵园祭祖车雷达显示全是人 网友:啥车?我想试试
PS主机神作好评仅有32%!顽皮狗回应《最后生还者》PC优化翻车
全球速读:专家谈ChatGPT:或让更多人享受生活 一周只工作一天
干货分享|袋鼠云数栈离线开发平台在小文件治理上的探索实践之路
全球热讯:电池级碳酸锂价格腰斩 电动车会降价吗?专家给出结论
环球速看:魔法氛围拉满!Redmi Note 12 Turbo哈利波特版图赏
不用带手机 抬手就能付!微信支付尝试推出微信刷掌服务
天天要闻:开发者危机!微软GitHub启动裁员:印度工程师团队几乎整体裁撤
全球微头条丨同款商超6元/瓶!特仑苏牛奶大促:2.93元/盒相当于半价
讯息:2023华大新高考联盟3月联考各科试题及答案!_老高考老教材
如何隐藏Apache版本号和其它敏感信息
容器安全之 Dockerfile 安全扫描
zookeeper的Leader选举源码解析
焦点要闻:前端设计模式——享元模式
每日观察!使用NSIS打包超大型软件的几个注意事项
环球头条:【手慢无】速来占便宜!三星128GB存储卡仅需49.8元
全球热头条丨汽车雷达在无人陵园内显示全是人影:吓坏车主
985硕士男子失业半年 应聘道士35岁已超龄!做实习生都被拒:现送外卖
没污染!国内首款氢内燃机飞机成功首飞:中国自研
环球新资讯:内地特供的网游性价比神U!酷睿i7-13790F评测:游戏性能强于锐龙9 7950X
环球播报:海豚模拟器登陆Steam
全球实时:半场战报:踢疯了!梅西第38分钟戴帽 阿根廷5-0暂领先库拉索
环球热推荐:Epic独占结束!中国功夫游戏《师父》登陆Steam:139元起
【天天时快讯】Native开发过程中容易忽略的注意事项
全球快播:【国际大宗商品早报】美豆反弹收涨近2% 伊拉克库尔德地区原油出口停顿支撑油价续涨
全固态电池空间电荷层微观机理揭示
环球要闻:核心、显存砍得没法看!RTX 4060 Ti/4060要5月上市:3999元起超值?
天天播报:大作《生化危机4重制版》发售 采用Denuvo正版加密 黑客发声:准备出手破解
天天快看点丨任天堂《塞尔达传说:王国之泪》限定版Switch发布!金黄手柄太酷炫
短讯!《最后生还者》PC版多半差评
每日消息!读SQL进阶教程笔记01_CASE表达式
快讯:成年子女不能与父母在酒店住同一标间引热议 太没道理?酒店回应
Spring整合Mybatis遇到的问题(一)
有关Mongodb 在windows上安装的问题
今头条!JNI知识点总结
天天日报丨【Visual Leak Detector】配置项 ReportTo
【Visual Leak Detector】配置项 SelfTest
环球观察:4个多月第一次!Intel Arc Pro专业显卡终于有了新驱动
当前最新:小米“退钱”了:27万小米空气净化器初代用户 每人899元
全球快报:小金刚手机杀到1999元 卢伟冰喊话友商:欢迎光明磊落竞争
全球播报:人体工学椅从没想过:自己真正的对手会是汽车
天天微资讯!日本海滩惊现大量乌贼尸体 绵延200米
通讯!青石板路图片(青石板规格)
微头条丨喊老公过时了!赵丽颖唐嫣孙俪都这样称呼另一半,网友:甜炸了
项目中如何对XSS统一处理
面向对象设计原则
instanceof的使用
GPT-4救了我狗的命
【全球快播报】你敢坐吗?滴滴能打到自动驾驶汽车了
环球看点!三爱健康集团(01889)发盈喜 预计2022年度股东应占溢利同比增加约223.6%至2922万元
世界头条:用gpt4训练一个简易真人代理
全球热头条丨【Visual Leak Detector】配置项 ReportFile
环球通讯!融创百亿美元境外债务重组成功在即 持债金额超30%的债权人小组已签订重组支持协议
环球新消息丨OpenAI创始人:AI可能毁灭人类 必须开发新技术来解决
焦点速递!美亚柏科:公司将对各类 AIGC 内容的检测、AI 生成文本的检测技术及产品进行布局
聚焦:免费Midjourney AI绘画Prompt提示词平台合集
NCNN 模型推理详解及实战
基于中断的字符串动态显示
全球热推荐:[NOI1999] 生日蛋糕
绿牌将会取消?网友:走好不送
一图看懂Note 12 Turbo:性价比进行到底 16+1TB仅售2599元
一加李杰:用户不会被蒙蔽 将旗舰体验普及到底
视讯!哈迷必备!Redmi Buds 4哈利波特版图赏
首发第二代骁龙7+!Redmi Note 12 Turbo图赏
设备树的概念(四):平台设备驱动和设备树
天天最资讯丨Apache iotdb-web-workbench 认证绕过漏洞(CVE-2023-24829)
Halcon学习教程(一) 之提取十字线中心 图像分割
甲流吃退烧药不退烧怎么办_吃了一粒退烧药多久可以喂奶
环球今日报丨卢伟冰:Note系列全球销量破3.2亿 进入全球单品十强
头条:马斯克为何没做出ChatGPT?揭秘OpenAI创始人的权力斗争
日本推出佛祖版ChatGPT:已经为20多万人解决烦恼
观天下!合资车还咋玩!奇瑞艾瑞泽5 GT上市:起售价仅7.99万
每日快报!Redmi Note 12 Turbo搭载超细四窄边直屏:边框窄至1.42mm!
热消息:一篇文章带你了解面积图
京沪杭等地近期明确将有序放开设摊、允许外摆
全球资讯:德创环保:宁波甬德拟以1.61亿元收购飞乐环保100%股权
每日讯息!12306回应免费坐高铁:积分存在有效期