SQL优化 硬控面试官半小时

SQL优化可以串联 MySQL 90%的知识点,讲的好的话,可以让面试官眼前一亮,是很加分的,特别是对于那些本身就很懂MySQL的面试官,基本这次面试就稳了

这里我总结了一些SQL优化的思路,供大家参考,基本覆盖99%的场景了。

PS: 如果对你有用的话,请不要吝啬你的花花,这是我分享的动力

如何定位慢SQL(场景)

  • 业务场景执行时间非常久,触发网关超时
  • 使用以下命令分析慢SQL日志:`mysqldumpslow /path/to/your/slow-query.log`

优化思路

增删查改

  • 对于插入可以把多个语句合并成一个语句批量处理。
  • 对于删除,因为删除是假删除(减少B+树合并访问磁盘的开销),如果有太多地方没有使用,B+树层数虚高,增加了访问磁盘的速度,并且全表扫描也会扫描到很多无用数据,可以在数据库空闲的时候通过alter table来重建表使数据排列更紧凑
  • 对于频繁修改的数据
  • 利用最左匹配原则减少索引数量
  • 如果有唯一索引可以考虑改成普通索引,避免修改时为了维护唯一性导致change buffer失效。
  • 对于查询:
  • 使用缓存, 早期mysql开启查询缓存,mysql8.0没有查询缓存,业务层可以使用缓存例如redis;或者可以把一些特殊的语句定期执行然后保存,后面查保存的数据,不用执行sql,比如我之前做的一个预测成本的功能,每月只需要预测一次,那就可以写一个定时任务每个月定时预测一次保存到另一张表,后续查询直接查这张表就可以了,就不用执行复杂SQL了。
  • 对于大数据量的场景,可以读写分离,分库分表

分析执行计划

EXPLAIN分析SQL语句的执行计划,主要关注以下几个字段

  • 如果typeALL,说明进行了全表扫描,考虑是否可以通过增加索引来优化。
  • 分析possible_keys (可能使用到的索引)key(实际使用的索引),确保相关的列上建立适当的索引并且正确选取索引。
  • 使用覆盖索引来避免回表
  • 使用复合索引来提高多条件查询的性能(索引下推)。
  • 利用最左匹配原则尽可能的建立更少的索引
  • 分析有没有没有正确选取索引:
  • 可能会错误的使用全表扫描的场景:
  • 对字段使用了函数:将函数写在判断条件上面,避免对字段使用函数。
  • 字段隐式类型转换( str->int, 字段使用了utf8但是字符是utf8mb4),需要保证查询目标与字段类型一致
  • 如果没有出现上述问题,还有一种解决思路:使用force index强制使用索引 | order by .. limit 1。
  • 因为选取索引是优化器的工作,优化器会分析选取索引的扫描行数加上回表的代价是否比主键全表扫描少,这里采用采样分析,因为全表分析代价太大,在多个事务的时候,因为是假删除而且多个事务的时候MVCC多版本数据在undo-log里面,这个时候采样分析会把已经删除的数据也考虑到总量里面去,比如实际上总量是1000行,考虑成了2000行导致考虑的扫描行数翻倍。所以采样分析针对高并发和大数量的场景是非常不准的
  • MySQL临时表, CTE会破坏索引结构(例如group后使用了临时表):想办法优化掉临时表,或者减少临时表的查询、JOIN操作
  • rows字段,表示查询的结果集行数。我们要尽可能的减少rows的数量,以下是一些思路
  • 确保查询条件尽可能具体, 例如在WHERE子句中使用更严格的条件。
  • 对于确定的数量(例如只需要查询一个结果)使用limit
  • in 替换成 exists,in 是 双重匹配,exists匹配到了一个后就会提前返回
  • count字段看可不可以替换成count( * ), count(1)
  • Extra字段,记录一些额外信息
  • 如果有Using filesort,表示使用了文件排序。
  • 可以考虑给需要排序的字段加上索引,因为索引使用的B+树本来就是排序好的,可以减少排序时间。 或者可以把单次排序的内存sort_buffer_size设置大一点,因为排序是取磁盘里的部分数据到内存进行排序最后合并, 把单次排序内存设置大一点这样减少IO次数
  • 如果有Using Join Buffer, 说明Join没有使用索引。没有索引join会用到Block Nested-Loop Join算法,时间复杂度很高,可以看作两层遍历,实际上更复杂一点,考虑到数据很多不能全读到内存里,mysql使用了join_buffer来存一部分数据,可能会因为 join_buffer 不够大,需要对被驱动表做多次全表扫描。
  • 在需要Join的字段加上索引

其它思路

  • 对于某些语句正常执行的时候很快,偶尔执行时间会很长,很难复现
  • 这是因为触发了flush操作,我们应该尽可能的去减少flush操作触发的次数。如果频繁触发flush操作,可能是脏页比例过高,可以查看脏页比例,脏页比率控制策略主要与redo-log大小和写盘速度(innodb_io_capacity)有关,我们需要设置合理的redo_log大小以及符合实际的预期写盘速度(可以通过 fio 这个工具来测试)。我们也可以想办法减小单次flush操作的时间,InnoDB有个策略是刷某一页时发现这一页旁边的页也是脏页也会去刷旁边的页,这样连带着一片都刷进去,导致一次flush操作可能会耗费很长的时间,我们可以通过innodb_flush_neighbors来控制这个行为,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。
  • 设计表的时候, 使索引的长度尽可能小,页的大小是固定的,根据索引长度计算B+树阶数,阶数越小,每页的节点数越多,B+数深度越小,访问磁盘次数越少;
  • 对于事务,使修改的顺序尽可能靠后,因为修改会持有行锁,会等事务提交后再释放锁,这样可能会阻塞其他事务,修改靠后就可以更晚的持有这些锁,这样就可以减少阻塞,也可以减少死锁发生的概率。
  • 大数据量分页查询的优化,改成使用id:分页的原理是查询到第一个满足条件的数据行后往后一页一页遍历,如果改成id的话,就可以使用索引进行优化,直接定位到需要查询的那一页
  • CTE物化问题,部分数据库(如MySQL)将CTE处理为临时表时不会保留原表索引, 解决方法:修改逻辑避免使用CTE/使用临时表替换CTE, 并在临时表上加索引(空间换时间)

CTE(Common Table Expression 公共表表达式) 是一种在 SQL 查询中定义临时结果集的方法。它通过 WITH 子句创建,可以:

  • 简化复杂查询的可读性
  • 支持递归查询
  • 多次引用同一结果集 基础语法
WITH cte_name (column1, column2, ...) AS (
  -- 子查询
  SELECT ...
)
SELECT * FROM cte_name;

我的面试思路

一般结合一两个点来讲

比如 Join 没有使用索引,耗时很长,但是后续分析发现Join字段上建立了索引,为什么Join没有选取索引呢?最终分析发现了以下原因:

  • 临时表破坏了原表的索引结构
  • CTE物化问题破坏了原表的索引结构

再针对上述问题讲解一些优化思路。

PS: 此文章适合有一定SQL优化基础的人进阶使用,对于小白可能不是很友好,如果你有什么疑问的话,可以在评论区我们相互交流一下。(因为每个知识点都要展开讲的话基本覆盖了90%MySQL知识点了,篇幅太长)

最后,如果对你有用的话,一定不要忘记送个花花呀,这么高质量又免费的帖子很少了

全部评论
“对于删除,因为删除是假删除”啥意思,我们删除难道不是真删除吗
3 回复 分享
发布于 03-14 14:41 香港
佬可以试试贝壳呢,主页有~
2 回复 分享
发布于 03-18 23:53 北京
mark
1 回复 分享
发布于 04-09 10:35 重庆
mark
点赞 回复 分享
发布于 05-04 04:22 江西
mark
点赞 回复 分享
发布于 04-29 18:43 广西
mark
点赞 回复 分享
发布于 04-28 16:33 山西
mark
点赞 回复 分享
发布于 04-19 14:57 黑龙江
mark
点赞 回复 分享
发布于 04-11 20:41 澳大利亚
mark
点赞 回复 分享
发布于 04-11 07:10 广东
mark
点赞 回复 分享
发布于 04-09 18:44 江苏
mark
点赞 回复 分享
发布于 04-08 15:53 安徽
mark
点赞 回复 分享
发布于 04-08 11:54 江西
mark
点赞 回复 分享
发布于 04-07 21:02 河北
mark
点赞 回复 分享
发布于 04-07 17:51 江苏
mark
点赞 回复 分享
发布于 04-05 13:12 江西
mark
点赞 回复 分享
发布于 04-04 17:57 安徽
mark
点赞 回复 分享
发布于 04-04 01:02 陕西
能看看我帖子的简历吗,数据分析方向的,不求大厂只求一个普通中小厂的数据运营可以吗
点赞 回复 分享
发布于 04-03 08:20 湖南
mark
点赞 回复 分享
发布于 04-02 19:09 陕西
mark
点赞 回复 分享
发布于 04-02 09:45 四川

相关推荐

不愿透露姓名的神秘牛友
今天 13:39
点赞 评论 收藏
分享
06-27 12:54
已编辑
门头沟学院 Java
累了,讲讲我的大学经历吧,目前在家待业。我是一个二本院校软件工程专业。最开始选专业是觉得计算机感兴趣,所以选择了他。本人学习计算机是从大二暑假结束开始的,也就是大三开始。当时每天学习,我个人认为Java以及是我生活的一部分了,就这样持续学习了一年半,来到了大四上学期末,大概是在12月中旬,我终于找的到了一家上海中厂的实习,但我发现实习生的工作很枯燥,公司分配的活也不多,大多时间也是自己在自学。就这样我秋招末才找到实习。时间来到了3月中旬,公司说我可以转正,但是转正工资只有7000,不过很稳定,不加班,双休,因为要回学校参加答辩了,同时当时也是心高气傲,认为可以找到更好的,所以放弃了转正机会,回学校准备论文。准备论文期间就也没有投递简历。然后时间来到了5月中旬,这时春招基本也结束了,然后我开始投递简历,期间只是约到了几家下场面试。工资也只有6-7k,到现在我不知道该怎么办了。已经没有当初学习的心劲了,好累呀,但是又不知道该干什么去。在家就是打游戏,boss简历投一投。每天日重一次。26秋招都说是针对26届的人,25怎么办。我好绝望。要不要参加考公、考研、央国企这些的。有没有大佬可以帮帮我。为什么感觉别人找工作都是顺其自然的事情,我感觉自己每一步都在艰难追赶。八股文背了又忘背了又忘,我每次都花很长时间去理解他,可是现在感觉八股、项目都忘完了。真的已经没有力气再去学习了。图片是我的简历,有没有大哥可以指正一下,或者说我应该走哪条路,有点不想在找工作了。
码客明:太累了就休息一下兄弟,人生不会完蛋的
如果实习可以转正,你会不...
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
06-21 11:33
昨天是学校最后一场招聘会,鼠鼠去参加了,全场只有一个招聘java的岗位,上来先做一份笔试题,做完后他拿张纸对答案,然后开始问简历上的问题,深圳小厂,6-8k(题目如下),后面还有两轮面试。然后我就在招聘现场逛呀逛,看到有公司招聘电商运营,给的比上年的小厂还多,鼠鼠就去了解了下,然后hr跟鼠鼠要了份简历,虽然我的简历上面全是求职Java开发相关的内容,但是hr还是鼓励我说没关系,她帮我把简历给老板看看,下周一会给我通知。招聘会结束后鼠鼠想了一段时间,也和朋友聊了聊,发现我可能是不太适合这个方向,然后就跟爸爸说回家了给我发条微信,我有些话想跟他说说。晚上爸爸到家了,跟我发了条微信,我立马跑出图书馆跟他打起了电话,这个通话长达一个小时,主要是跟爸爸坦白说我不想找这行了,是你的儿子太没用了,想试试其他行业。然后爸爸也跟我说了很多,说他从来没有希望我毕业后就赚大钱的想法,找不到就回家去,回家了再慢慢找,实在找不到就跟他干(帮别人装修房子,个体户),他也知道工作不好找,让我不要那么焦虑,然后就是聊一些家常琐事。对于后面的求职者呢我有点建议想提一下,就是如果招实习的时间或者秋招开始,而你的简历又很差的情况下,不要说等做好项目填充完简历之后再投,那样就太晚了,建议先把熟悉的项目写上简历,然后边投边面边完善,求职是一个人进步的过程,本来就比别人慢,等到一切都准备好后再投岂不是黄花菜都凉了。时间够的话还是建议敲一遍代码,因为那样能让你加深一下对项目的理解,上面那些说法只是针对时间不够的情况。当然,这些建议可能没啥用,因为我只是一个loser,这些全是建立在我理想的情况下,有没有用还需其他人现身说法。上篇帖子没想到学校被人认了出来,为了不丢脸只能匿名处理了。
KPLACE:找研发类或技术类,主要还是要1.多投 2.多做准备,很多方面都要做准备 3.要有心理准备,投累了就休息一两天,再继续,要相信自己能找到
投递58到家等公司7个岗位
点赞 评论 收藏
分享
评论
304
1567
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务