大家好，今天来为大家解答神奇的SQL性能优化→让SQL飞翔这个问题的一些问题点，包括也一样很多人还不知道，因此呢，今天就来为大家分析分析，现在让我们一起来看看吧！如果解决了您的问题，还望您关注下本站哦，谢谢~

下面将尝试介绍一些不依赖于特定数据库实现的优化技术，使SQL执行速度更快，消耗内存更少。一般的优化技巧可以通过简单调整SQL语句来实现。

环境准备

以下内容是从SQL层面展开的，而不是针对特定数据库。也就是说，下面的内容基本上适用于任何关系数据库。

但关系型数据库如此之多，一一列举例子显然不现实；我们使用常用的MySQL来演示示例。

MySQL版本：5.7.30-log，存储引擎：InnoDB

准备两张表：tbl_customer和tbl_recharge_record

使用高效的查询

对于某个查询，有时存在多种SQL实现，例如IN、EXISTS和连接之间的转换。

理论上，产生相同结果的不同SQL语句应该具有相同的性能，但不幸的是，查询优化器生成的执行计划很大程度上受到外部结构的影响。

因此，如果想要优化查询性能，就必须知道如何编写SQL语句，以便优化器能够生成更高效的执行计划

使用 EXISTS 代替 IN

关于IN，相信大家都很熟悉。它易于使用且易于理解。 IN虽然易于使用，但存在性能瓶颈。

如果IN的参数是1、2、3这样的值列表，一般不需要特别关注，但是如果参数是子查询，那么就需要关注了。

大多数时候，[NOT] IN 和[NOT] EXISTS 返回相同的结果，但是当两者都在子查询中使用时，EXISTS 更快。

假设我们要查询客户信息和充值记录，SQL怎么写？

相信大家首先想到的就是IN：SELECT * FROM tbl_customer WHERE ID IN(SELECT customer_id FROM tbl_recharge_record);

IN 使用起来非常简单，也很容易理解；我们看一下它的执行计划

我们看一下EXISTS的执行计划：

可以看到IN:subquery2的执行计划中生成了新的临时表，会导致效率变慢。

一般来说，EXISTS比IN快的原因有两个

1、如果在连接列（customer_id）上建立了索引，那么在查询tbl_recharge_record时，就可以通过索引来查询，而不是全表查询。

2.使用EXISTS，一旦找到满足条件的一行数据，就会终止查询。不需要像使用IN时那样扫描整个表（对于NOT EXISTS也是如此）

当IN的参数为子查询时，数据库会先执行子查询，然后将结果存储在临时表（内联视图）中，然后扫描整个视图。在许多情况下，这种方法非常消耗资源。

使用EXISTS，数据库不会生成临时表

但从代码可读性的角度来看，IN 比EXISTS 更好。使用IN 时的代码看起来更加清晰易懂。

因此，如果你确定使用IN可以快速得到结果，就没有必要改成EXISTS。

事实上，很多数据库也尝试过提升IN的性能。

在Oracle数据库中，如果我们在索引列上使用IN，索引也会首先被扫描

从版本7.4 开始，PostgreSQL 在使用子查询作为IN 谓词参数时还提高了查询速度

也许未来的某一天，IN无论在哪个关系型数据库上都能有和EXISTS一样的性能。

关于EXISTS，更多详情请参见：神奇的SQL谓词难以理解的EXISTS

使用连接代替 IN

事实上，在日常工作中，更多人使用连接而不是IN来提高查询性能，而不是EXISTS。并不是连接比较好，而是EXISTS很难掌握。

回到问题：如果我们使用连接来查询有充值记录的客户信息，那么SQL应该怎么写呢？

这种写法可以充分利用索引；而且，因为没有子查询，数据库不会生成中间表；所以查询效率还是不错的

至于JOIN和EXISTS哪个性能更好，很难说；如果没有索引，EXISTS可能会稍微好一点，但是如果有索引，两者就差不多了。

避免排序

说起SQL排序，我们首先想到的肯定是：ORDER BY，通过它我们可以按指定列顺序输出结果。

然而，除了ORDER BY所示的排序之外，数据库内部还有很多秘密执行排序的操作；执行排序的代表性操作包括以下

如果你只在内存中进行排序，那就没问题；但如果由于内存不足而需要在硬盘上排序，那么性能就会大幅下降。

因此，尽量避免（或减少）不必要的排序可以大大提高查询效率。

灵活使用集合运算符的 ALL 可选项

SQL 中有三个集合运算符UNION、INTERSECT 和EXCEPT。子表表示这些集合运算的并集、交集和差集。

默认情况下，这些运算符进行排序以消除重复项

使用临时意味着执行排序或分组。显然这个SQL不进行分组，而是进行排序操作。

如果我们不关心结果是否有重复数据，或者事先知道不会有重复数据，我们可以使用UNION ALL来代替UNION

可以看到，执行计划中并没有排序操作。

对于INTERSECT 和EXCEPT 也是如此。添加ALL选项后，将不再进行排序。

添加ALL选项是一种非常有效的优化方法，但是它在各个数据库中的实现参差不齐，如下图

注意：Oracle使用MINUS而不是EXCEPT； MySQL 根本不实现INTERSECT 和EXCEPT 操作。

使用 EXISTS 代替 DISTINCT

为了排除重复数据，DISTINCT也会进行排序

还记得使用CONNECT 而不是IN 的情况吗？如果不使用DISTINCT

SQL: SELECT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id=tc.id

那么查到的结果会有很多重复记录，我们改进SQL

SELECT DISTINCT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id=tc.id

你会发现执行计划中有Usingtemporary，说明使用了排序操作。

我们使用EXISTS 进行优化

可以看到，排序操作已经被避免了

在极值函数中使用索引

SQL 语言中有两个极值函数：MAX 和MIN。使用这两个函数时会进行排序。

例如：从tbl_recharge_record 中选择MAX(recharge_amount)

会进行全表扫描，并进行隐式排序，找到单次充值的最大金额。

但如果参数字段上建有索引，则只需要扫描索引，不需要扫描全表。

例如： SELECT MAX(customer_id) FROM tbl_recharge_record;

会扫描索引：idx_c_id找到充值记录中最大的客户ID

该方法并没有消除排序过程，而是优化了排序前的搜索速度，从而减弱了排序对整体性能的影响。

能写在 WHERE 子句里的条件不要写在 HAVING 子句里

我们看两条SQL及其执行结果

从结果来看，两条SQL是一样的；但从性能的角度来看，第二个语句效率更高，原因有两个。

减少要排序的数据量

GROUP BY 子句将在聚合时进行排序。如果提前通过WHERE子句过滤掉一些行，就可以减轻排序的负担。

有效利用索引

索引可以用在WHERE子句的条件中

HAVING子句对聚合后生成的视图进行过滤，但很多时候聚合视图并不会继承原表的索引结构。

关于HAVING，更多详情请参见：神奇的SQL HAVING 容易被低估的主角

在 GROUP BY 子句和 ORDER BY 子句中使用索引

一般来说，GROUP BY 子句和ORDER BY 子句都会排序

如果GROUP BY和ORDER BY的列有索引，可以提高查询效率

特别是在某些数据库中，如果在列上建立了唯一索引，那么排序过程本身就会被省略。

使用索引

使用索引是最常用的SQL 优化方法。这个大家都知道，但恐怕大家都不知道：为什么有索引查询却这么慢（为什么不使用索引）

总之，查询尽可能靠近索引，避免出现不使用索引的情况。

减少临时表

在SQL中，子查询的结果将被视为新表（临时表）。这个新表可以像原来的表一样通过SQL操作。

但是频繁使用临时表会带来两个问题

1、临时表相当于原表数据的备份，会消耗内存资源。

2、很多时候（尤其是聚合时），临时表并没有继承原表的索引结构。

因此，尽量减少临时表的使用也是提高性能的重要途径。

灵活使用 HAVING 子句

为聚合结果指定过滤条件时，使用HAVING子句是一个基本原则

但是如果你不熟悉HAVING，我们经常会想办法替换它，就像这样

但在指定聚合结果的过滤条件时，不需要专门生成中间表。您可以按如下方式使用HAVING 子句

HAVING子句和聚合操作是同时执行的，因此比生成临时表后执行WHERE子句效率更高，而且代码看起来更简洁。

需要对多个字段使用 IN 谓词时，将它们汇总到一处

SQL-92 中添加了行间比较功能。这样，比较谓词=、 IN 谓词的参数就不再只是标量值，而应该是值列表。

让我们看一个使用IN 谓词的多个字段的示例

此代码中使用了两个子查询。我们可以进行列汇总优化，并一起编写逻辑。

这样子查询就不需要考虑相关性，只需执行一次即可。

还可以进一步简化，将多个字段组合写在IN中

简化后，连接字段时不用担心类型转换问题，字段也不会被处理，所以可以使用索引

先进行连接再进行聚合

同时使用连接和聚合时，先执行连接操作可以避免中间表的生成。

合理地使用视图

View是我们日常工作中经常使用的一个非常方便的工具

然而，在没有经过深思熟虑的情况下定义复杂的视图可能会造成巨大的性能问题

尤其是当视图的定义语句包含以下操作时，SQL的效率会非常低，执行速度会变得非常慢。

总结

文章中虽然列出了几个关键点，但其实优化的核心思想只有一个，就是找出性能瓶颈在哪里，然后解决。

事实上，不仅仅是数据库和SQL。计算机世界中最容易成为性能瓶颈的就是对硬盘的访问，即文件系统（因此可以通过增加内存，或者使用访问速度更快的硬盘等方式来提高性能）

无论是减少排序、使用索引，还是避免使用临时表，本质都是减少对硬盘的访问。

以下提示摘要

1.当参数是子查询时，使用EXISTS或JOIN而不是IN。

2.在SQL中，很多操作都是秘密排序的。尽量避免这些操作。

用户评论

夏以乔木

这个标题太吸引人了！我一直想让我们的 SQL 查询速度更快，看看这篇文章能不能给我一些启发！

    有9位网友表示赞同！

败类

终于找到一篇关于 SQL 优化的好文章了！以前总是觉得数据库慢的问题很蛋疼，希望能从这篇文章里学到点东西，让我的查询速度飞起来！

    有18位网友表示赞同！

孤自凉丶

写的真棒，对SQL 性能优化的内容讲解得很详细，把各种技巧都涵盖了，我已经迫不及待想实践一下了!

    有15位网友表示赞同！

残花为谁悲丶

虽然我是SQL新手，但我觉得这篇文章也能让我受益匪浅。它介绍的优化方法很易懂，并且用通俗的语言解释，让人更容易理解！

    有20位网友表示赞同！

反正是我

真的羡慕那些掌握 SQL 性能优化的高手啊，每次查询数据都要等好久，简直要命！如果能够像文章里讲的那样让SQL飞起来就好了。

    有13位网友表示赞同！

敬情

这篇文章很有趣，让我了解到SQL 的优化有很多技巧。我一直都觉得代码的问题就在于细节，而这篇文章正好指出了哪些 SQL 细节需要关注！

    有17位网友表示赞同！

巷雨优美回忆

感觉有点扯淡！我写 SQL 代码的时候也都是考虑性能的，怎么还是那么慢？文中提到的方法有没有针对性？

    有12位网友表示赞同！

颓废i

讲道理，这种优化技巧其实很多工具都可以帮我完成，学习这些方法真的很有必要吗？

    有6位网友表示赞同！

疯人疯语疯人愿

这篇文章的内容太基础了，对于已经进行过 SQL 优化的开发者来说，并没有什么新的收获。期待能够看到更深入、更实用的技术分享！

    有15位网友表示赞同！

无望的后半生

标题确实很吸引人，但是文章内容缺乏针对性，没有具体案例和数据支持，读完感觉还是不太明白如何实际操作。希望作者能够提供更多实践经验和工具建议！

    有11位网友表示赞同！

我绝版了i

我曾经尝试过很多 SQL 优化方法，但效果总是比较有限。希望这篇文章能给我一些新的启发！

    有10位网友表示赞同！

∞◆暯小萱◆

总觉得 SQL 的性能优化是一个没有尽头的挑战，即使按照文章里说的方法改进，还是要不断学习和探索！

    有11位网友表示赞同！

↘▂_倥絔

文章中提到的索引策略我很感兴趣，之前不太了解这个方面的知识，现在可以好好研究一下了！

    有12位网友表示赞同！

﹎℡默默的爱

希望能看到更多关于不同数据库平台的 SQL 性能优化技巧分享，比如 MySQL、PostgreSQL 和 MongoDB 等。

    有14位网友表示赞同！

一笑抵千言

这篇文章让我意识到，SQL 性能优化不仅仅是代码的问题，数据库结构和硬件配置也是重要的影响因素！

    有16位网友表示赞同！

▼遗忘那段似水年华

文章内容很棒，尤其是对查询计划分析的讲解非常清晰易懂！可以帮助我更深入地理解 SQL 执行过程。

    有20位网友表示赞同！

红尘滚滚

希望作者能再分享一些关于 SQL 监控和性能测试的知识，这样才能更好地了解系统的性能状况并进行及时优化！

    有15位网友表示赞同！

殃樾晨

感觉这篇文章的写作风格有点罗嗦，能不能简明扼要一点？ 有些地方也可以加入图表或代码示例让内容更直观易懂。

    有13位网友表示赞同！