写点什么

一文带你搞懂如何优化慢 SQL

  • 2023-03-29
    四川
  • 本文字数:4192 字

    阅读完需:约 14 分钟

一文带你搞懂如何优化慢SQL

作者:京东科技 宋慧超


一、前言


最近通过 SGM 监控发现有两个 SQL 的执行时间占该任务总执行时间的 90%,通过对该 SQL 进行分析和优化的过程中,又重新对 SQL 语句的执行顺序和 SQL 语句的执行计划进行了系统性的学习,整理的相关学习和总结如下;


二、SQL 语句执行顺序



要想优化慢 SQL 语句首先需要了解 SQL 语句的执行顺序,SQL 语句中的各关键词执行顺序如下:


◦首先执行 fromjoin 来确定表之间的连接关系,得到初步的数据。


◦然后利用 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 byhaving:先分组再删除不满足 having 条件的数据。(该两种几乎没有区别)


比如举例如下:100/2=50,此时我们把 100 拆分(10+10+10+10+10…)/2=5+5+5+…+5=50,只要筛选条件没变,即便是分组了也得满足筛选条件,所以 where group bygroup by having 是不影响结果的!


不同的是,having 语法支持聚合函数,其实 having 的意思就是针对每组的条件进行筛选。我们之前看到了普通的筛选条件是不影响的,但是 having 还支持聚合函数,这是 where 无法实现的。


当前的数据分组情况



执行 having 的筛选条件,可以使用聚合函数。筛选掉工资小于各组平均工资的 having salary<avg(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 列



例如:查询学习人数大于 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 > 3000
union
SELECT 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 语句从哪个表中获取数据



EXPLAINSELECTcourse 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
union
SELECT 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 ='MySOL'
复制代码



type 列


注意: 在 MySQL 中不一定是使用 JOIN 才算是关联查询,实际上 MySQL 会认为每一个查询都是连接查询,就算是查询一个表,对 MySQL 来说也是关联查询。


type 的取值是体现了 MySQL 访问数据的一种方式。type 列的值按照性能高到低排列 system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL




如果 where like “MySQL%”,type 类型为?


虽然 class_name 加了索引 ,但是使用 where like% 右统配, 所以会走索引范围扫描。


EXPLAINSELECTcourse 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 namelike'MySQL%'
复制代码



如果 where like “%MySQL%”,type 类型为?


虽然 class_name 加了索引 ,但是使用 where 的 %like% 左右统配, 所以会走全索引扫描,如果不加索引的话,左右统配会走全表扫描。


EXPLAINSELECTcourse 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 namelike'%MySQL%'
复制代码



possible_key、key 列


possible_keys说明表可能用到了哪些索引,而key是指实际上使用到的索引。基于查询列和过滤条件进行判断。查询出来都会被列出来,但是不一定会是使用到。


如果在表中没有可用的索引,那么 key 列 展示 NULL,possible_keys 是 NULL,这说明查询到覆盖索引。


key_len 列


实际用的的索引使用的字节数。


注意,在联合索引中,如果有 3 列,那么总字节是长度是 100 个字节的话,那么key_len值数据可能少于 100 字节,比如 30 个字节,这就说明了查询中并没有使用联合索引的所有列。而只是利用到某一些列或者 2 列


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 语句

发布于: 刚刚阅读数: 4
用户头像

拥抱技术,与开发者携手创造未来! 2018-11-20 加入

我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者,提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】,欢迎大家来玩

评论

发布
暂无评论
一文带你搞懂如何优化慢SQL_数据库_京东科技开发者_InfoQ写作社区