写点什么

如何优化慢 SQL,还是傻傻的只会加索引?

作者:采菊东篱下
  • 2024-12-31
    湖南
  • 本文字数:2158 字

    阅读完需:约 7 分钟

关于 SQL 优化,我们可以从几个方面来说:

直观 SQL

我们遇到 SQL 需要优化,第一印象应该是看一下这条 SQL 有没有什么致命的毛病,不需要借助任何工具。

第一条:避免使用 select *

很多人可能不太理解为什么要避免使用select *,因为select *会把所有字段都查出来,那这里有两个说服你的理由:

  • 我们只需要查询出我们需要的字段,减少数据量的传输,从而提高 IO 传输性能。

  • 在索引优化环节中,我们可以利用索引优化规则,来提升整体的查询效率。举个简单的场景:覆盖索引

第二条:避免使用子查询,使用 JOIN 代替

因为子查询都是嵌套查询,而嵌套查询就会创建一些临时表,这样就会增加性能的损耗。

况且使用 JOIN,数据库可以更有效的优化 JOIN 连接操作。

第三条:避免使用 OR 查询,使用 UNION 或者 UNION ALL 代替

在 MySQL5.0 之前的版本,要尽量避免使用 OR 查询,因为它可能会导致索引失效。

我们可以使用 UNION 或者 UNION ALL 代替,而 UNION 可以去重,UNION ALL 是不会去重。

如果我们结果集里允许出现重复数据就是用 UNION ALL,反之使用 UNION。

第四条:避免使用!=、<>操作符,使用 IN 代替

因为在 SQL 中使用到了这些操作符,会导致查询引擎放弃通过索引来查询数据,转而改成全表查询。

我们使用!=时,就算你字段上加了索引,也可能会导致索引失效。

第五条:避免使用 %开头的 LIKE 查询

这一点,我们上一篇文章中有专门说过,所以这里就不在赘述了。

第六点:避免查询字段采用函数计算

因为索引是基于索引字段排好序的数据结构,而当我们使用函数后,这个字段的有序就被打破了,从而导致索引失效。

以上是我们拿到 SQL 后,不需要什么工具都应该能考虑的点。

索引

索引不是 SQL 优化唯一的点,但他是很重要的点,关于索引这一块,那我们就需要借助于一些工具了。

第一条:确保我们查询条件和连接条件的字段上建了索引

如果查询条件和连接条件对应的列没有建索引,我就应该考虑给其加上索引。

第二条:利用好覆盖索引

关于覆盖索引,如果还不清楚的话,建议先搞清楚什么是覆盖索引。

前面我们说到避免使用 select*,提到了覆盖索引,假设我们需要查询的字段刚好在二级索引里,这样我们就在二级索引里找到了我们想要的数据,不需要再回表操作。我们查询时就不写 select * ,改成 select xx,这样就可以减少回表的操作。

第三条:正确使用联合索引,避免过多使用索引

因为联合索引通常是由多个字段组成,如果联合索引里的字段太多了,当我们没插入一条数据,都会有很大的可能触发索引树的重构,也就是索引树的分列,这里就涉及到一个常见的面试题:为什么我们的主键 id 需要自增?

第四条:更新频率较高的列,慎用索引

和第三条有那么些管理,因为更新频繁的列,就代表着它的树的维护是非常频繁的,可能我们没插入一条数据就会进行分裂,然后再重构这个树。

第五点:避免范围查询数据量过多

因为在 MySQL 中一条查询 SQL 是否走索引,是取决与 MySQL 的执行计划的成本来的。这个成本是 MySQL 优化器来进行选择的。

比如我们当前一条 SQL 需要在二级索引上面找到大量的数据,然后再回到主键索引树上,然后再返回数据,这里可能就会存在 1+1=2 的成本情况。如果直接全表扫描的成本是 1,那这个时候优化器就会直接选择全表扫描。

所以,我们尽量避免范围查询数据量过多的情况。

以上就是索引相关的优化点。

其实,很多时候,我们就算加了索引,SQL 也不见得就会变快。站在开发者的角度,我们是需要把所有相关索引失效的场景给排除掉。

说白了,最终问题不是咱们的,把锅给甩出去。

其他手段

第一条:善于利用 EXPLAIN

充分理由好 EXPLAIN,让其帮忙我们去分析 SQL 的执行计划。通过 EXPLAIN 我们可以知道是否走索引,扫描了多少数据,这也是我们每个程序员必备的 SQL 优化工具:EXPLAIN。

关于 EXPLAIN 这里就不做深入介绍了,还不会的,请自行差相关资料学习(网上的文章和视频多多滴)。

第二条:分页优化、排序优化、分组优化

三个优化中,后面两个是基于索引来做的,就是你排序的字段需要加上索引,你分组的字段需要加上索引,并且索引要成功被利用到。

分页优化就相对特殊,因为都是后面慢慢才会发现的。比如说现在要查询第 10010 后面的 10 条数据,分页查询会先扫描 10010 前面的数据都扫描一遍,最后再返回,其实前面的数据,我们根本就不需要,但是分页查询会先扫描前面的数据的。这样肯定会造成性能的浪费,因此我们需要深分页优化。

这个感兴趣的可以网上搜搜 MySQL 分页优化相关视频和文章来学习一下。

第三条:分解复杂查询

这个相对来说比较好理解,但难度系数却很高。

我们在实际开发中,可能会遇到一条 SQL 有几十行几百行甚至上千行。

如果你使用了前面相关的办法,但这个 SQL 还是慢,那此时就可以考虑拆分

第四条:批量插入

在大批量数据进行插入时,每插入一条数据,就去提交一次事务,面对大量数据插入时,这样就会浪费性能。

我们可以每次插入 500 条,但是这个 500 条数据,我这里只是举例,实际工作中遇到对其做一些压测这类的,找到一个相关最优的提交数据量。

第五条:监控和分析工具

这里的监控和分析工具,并不是针对一条 SQL,而是针对我们整个数据库的性能。

第六条:硬件优化

这个是 boss 不想看到的,实在没招了才用这招,也是所谓的绝招。

第七条:分库分表、读写分离

关于分库分表和读写分类,其实还是和硬件优化以及拆分思想有那么些关联。

好了关于慢 SQL 优化,我们一共从三个大方向以及 18 条建议来说明,相信很多面试官都会很惊讶,居然总结了这么多。

用户头像

还未添加个人签名 2023-02-14 加入

还未添加个人简介

评论

发布
暂无评论
如何优化慢SQL,还是傻傻的只会加索引?_Java_采菊东篱下_InfoQ写作社区