数据库工程师：如何提高 SQL 性能？

本文的主题是：如何提高SQL性能？

SQL性能优化是数据库工程师在实际工作中要处理的重要课题之一。对于一些数据库工程师来说，这几乎是唯一的话题。事实上，在Web服务等需要快速响应的应用场景中，SQL的性能直接决定了系统能否使用。

因此，本文不再像以前那样介绍SQL的各种功能的应用技巧，而是重点关注SQL的优化，介绍一些，让SQL执行更快，消耗更少的内存。优化技巧。

优化查询性能时，您需要了解您所使用的数据库的功能特征。而且查询速度慢不仅是SQL语句本身的原因，还有内存分配不良、文件结构不合理等其他原因。因此，本文介绍的SQL优化方法可能并不能解决所有性能问题，但确实查询性能不佳的原因往往是SQL编写不合理。

接下来我将尝试介绍一些不依赖于具体数据库实现的简单易用的优化方法。如果你在日常工作中感觉SQL执行速度慢，希望的这些方法可以帮到你。

01。使用高效的查询

在 SQL 中，不同的代码通常可以产生相同的结果。理论上，产生相同结果的不同代码应该具有相同的性能，但不幸的是，查询优化器生成的执行计划很大程度上受到代码外部结构的影响。 所以如果你想优化查询性能，你需要知道如何编写代码来让优化器更高效地执行。如果参数

是子查询，使用EXISTS代替谓词IN

IN会非常方便。该代码也很容易理解，因此经常使用。但是，虽然有用，但谓词 IN 可能会成为性能优化的瓶颈。总体而言，如果代码使用大量 IN 谓词，优化它们可以显着提高性能。

如果IN参数是“1,2,3”等值列表，一般不需要特别注意。但如果参数是子查询，就要注意了。

通常[NOT] IN 和[NOT] EXISTS 返回的结果是相同的。但如果两者都用于子查询，EXISTS 会更快。

我们先看一个例子。之前用于管理员工培训的两个表在这里用作测试数据。

Class_A

Class_B

我们尝试从表Klasse_A中找出哪些员工也出现在表Klasse_B中。接下来的两个 SQL 语句返回相同的结果，但使用 EXISTS 的 SQL 语句更快。

--慢
SELECT ＊
FROM Class_A
WHERE id IN 
  (SELECT id
  FROM Class_B);
--快
SELECT ＊
FROM Class_A  A
WHERE EXISTS
  (SELECT ＊
  FROM Class_B  B
  WHERE A.id = B.id);

两个结果如下所示：

id name
-- ----
1  田中
2  铃木

使用 EXISTS 会更快的原因有两个。

❤ 如果在连接列（id）上设置了索引，则查询Class_B时不需要检查实际表，只需检查索引即可。

❤ 使用 EXISTS 只要有一行数据符合条件就结束搜索。不需要像使用 IN 时那样扫描整个表。目前这同样适用于 NOT EXISTS。

如果 IN 参数是子查询，则数据库首先运行子查询，然后将结果存储在 临时工作表 （内联视图）中，然后扫描整个视图。在许多情况下，这种方法是非常劳动密集型的。使用 EXISTS 时，数据库不会生成临时工作表。

但是从代码可读性角度来看，IN 比 EXISTS 更好。使用 IN 时代码看起来更清晰、更容易理解。因此，如果您确信使用 IN 可以快速获得结果，则无需将其更改为 EXISTS。

此外，许多数据库最近尝试提高 IN 的性能。也许未来的某一天，IN 可以达到与 EXISTS 相同的性能，无论它驻留在哪个数据库中。如果参数

是子查询，请使用join代替IN

。为了提高IN的性能，除了使用EXISTS之外，还可以使用 join 。前面的查询语句可以按如下方式“扁平化”。

--使用连接代替IN
SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B
ON A.id = B.id;

这种写法至少可以使用一张表的“id”列中的索引。另外，由于没有子查询，数据库不会生成中间表。很难说哪一个比 EXISTS 更好，但是如果没有索引，EXISTS 可能会比 join 稍微好一些。 此外，如本文后面的许多示例所示，在某些情况下使用 EXISTS 比使用复合更合适。

02。避免排序

与面向过程的语言、SQL 语言中的 不同， 用户无法显式指示数据库执行排序操作。 对用户隐藏此类操作是SQL的设计思想。

不过，这并不意味着数据库内不能进行排序。相反，秘密排序经常发生在数据库中。因此，用户最终需要了解哪些编辑被排序了（从这个意义上说，“隐藏编辑”的目标似乎还有很长的路要走）。

执行排序的代表性操作包括以下内容。

§ GROUP BY 子句

§ ORDER BY 子句

§ 聚合函数（SUM、COUNT、AVG、MAX、MIN）

iq DISTINCT

❤ 设置运算符（UNION、INTERSECT、EXCEPT）

§窗口函数（RANK、ROW_NUMBER等）

如果排序仅在内存中完成，那没问题；但如果内存不足硬盘排序需要，那么伴随着“哒哒哒”的硬盘存取声，排序的性能也会大大恶化（以下数据可能不是准确……据说硬盘的访问速度比内存慢100万倍）。因此，我们的目标是避免（或减少）不必要的排序。

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

SQL中有3种集合运算符：UNION、INTERSECT和EXCEPT。

默认情况下，这些运算符会排序为排除重复数据。

SELECT ＊ FROM Class_A
UNION
SELECT ＊ FROM Class_B;

结果：

id name
-- -----
1  田中
2  铃木
3  伊集院
4  西园寺

如果你不关心结果是否会有重复，或者你事先知道不会有重复，使用 UNION ALL 而不是 UNION 。这样就不会发生排序。

SELECT ＊ FROM Class_A
UNION ALL
SELECT ＊ FROM Class_B;

结果：

这同样适用于 INTERSECT 和 EXCEPT。添加ALL选项后，就没有排序了。

添加 ALL 选项是一种非常有效的优化性能的手段，但问题是不同的数据库有不同的实现。下表概述了各种数据库的当前实现。

集合运算符ALL选项的实现

1． Oracle 使用 MINUS 而不是 EXCEPT

2。 MySQL本身还没有实现INTERSECT和EXCEPT操作

上表显示了各个数据库厂商对标准SQL的符合程度，非常有趣。事实上，DB2 忠实地实现了所有功能。 PostgreSQL虽然是开源软件，但也考虑到了所有细节，非常符合学院派风格。 MySQL和SQL Server稍差一些。 Oracle非常重视你自己的个性。很容易假设所有数据库都支持 ALL 选项，但事实并非如此。请注意。

使用EXISTS代替DISTINCT

为了消除重复数据，DISTINCT也进行了排序。如果需要对两个表之间的联接结果进行重复数据删除，请考虑使用 EXISTS 而不是 DISTINCT 以避免排序。

Items

SalesHistory

我们来思考如何从上面的Items产品表中找到同样出现在SalesHistory销售记录表中的产品。基本上，它是查找有销售记录的产品。使用

IN 是一种练习。但正如我们之前所说：如果 IN 参数是子查询，那么使用 join 比 IN 更好。 因此我们使用“item_no”列来连接两个表，如下所示。

SELECT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

结果：

it
item_no
-------
10
10
20
20
30
30
30em_no

因为是一对多连接，所以重复数据出现在“item_no”栏中。为了消除重复数据，我们需要使用DISTINCT。

SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

item_no
-------
10
20
30

然而，更好的方法是使用 EXISTS。

SELECT item_no
SELECT item_no
FROM Items I
WHERE EXISTS
  (SELECT ＊
  FROM SalesHistory SH
  WHERE I.item_no = SH.item_no);

该语句在运行时不会排序。使用 EXISTS 与使用联接一样高效。

在极值函数中使用索引（MAX/MIN）

SQL 语言中有两种极值函数：MAX 和 MIN。

两个函数在使用时都是排序的。但如果参数字段建立了索引，则只需要扫描索引，不需要扫描整个表。以刚才的Items表为例，SQL语句可以写成如下。

--这样写需要扫描全表
SELECT MAX(item)
FROM Items;
--这样写能用到索引
SELECT MAX(item_no)
FROM Items;

因为item_no是表items的唯一索引，所以效果更好。对于联合索引来说，只要查询条件是联合索引的第一个字段，该索引就有效。所以你也可以在SalesHistory表的sale_date字段中使用极值函数。

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

可以写在WHERE子句中的条件不一定可以写在HAVING子句中

例如下面两条SQL语句返回的结果是一样的。

--聚合后使用HAVING子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING sale_date = '2007-10-01';

--聚合前使用WHERE子句过滤
SELECT sale_date, SUM(quantity)
FROM SalesHistory
WHERE sale_date = '2007-10-01'
GROUP BY sale_date;

结果：

sale_date          sum(quantity)
--------------    --------------
'2007-10-01'             17

但从性能角度来看，第二种解释更有效。通常有两个原因。第一个是排序是在使用 GROUP BY 子句聚合时完成的。如果通过 WHERE 子句提前过滤掉某些行，则可以减少排序负担。第二个是索引可以用在 WHERE 子句的术语中。HAVING子句用于过滤聚合后生成的视图，但聚合后的视图往往不继承原表的索引结构。

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

一般情况下，GROUP BY 子句和 ORDER BY 子句会进行排序以排列和替换行。但是， 可以通过将索引列指定为 GROUP BY 和 ORDER BY 列来执行快速搜索。 特别是，在某些数据库中，如果在编辑对象的列上设置唯一索引，则排序过程本身将被省略。如果您有兴趣，请确认您使用的数据库是否支持此功能。

03。你真的使用索引吗？

一般情况下我们都会为数据量比较大的表创建索引。简单理解一下，索引的工作原理和C语言中的指针数组是一样的。 也就是说，搜索轻量级引用比搜索复杂对象数组更有效。 而且最流行的B树索引也进行了一些优化，使用二分查找来提高搜索速度。

假设我们在名为 col_1 的列上创建索引，然后考虑以下 SQL 语句。这条SQL语句本来是想使用索引的，但执行过程中实际上是进行了全表扫描。人们常常不自觉地写下这个吗？

对索引字段执行操作

SELECT ＊
FROM SomeTable
WHERE col_1 ＊ 1.1 > 100;

人们普遍认为SQL语言的主要目的不是执行操作。但事实上，数据库引擎甚至不会为我们做这种级别的转换。

将运算表达式放在查询条件右侧即可使用索引。这样写就好了。

WHERE col_1 > 100 / 1.1

同样，如果查询条件左侧使用函数，则无法使用索引。

SELECT ＊
FROM SomeTable
WHERE SUBSTR(col_1, 1, 1) = 'a';

如果无法避免对左侧进行操作，使用函数索引也是一种方式，但不建议随便做。

使用索引时，条件表达式的左侧必须是原始字段。

请记住，这是优化索引时的主要关注点。

使用IS NULL谓词

一般情况下，索引字段中不存在NULL，因此指定IS NULL和IS NOT NULL会导致索引无法使用，导致查询性能较低。

SELECT ＊
FROM  SomeTable
WHERE  col_1 IS NULL;

至于为什么索引字段中不存在NULL，简单来说：NULL 不是一个值。 非值不包含在值集合中（详见《三值逻辑与NULL》一文）。

但是，如果你想使用类似于IS NOT NULL的函数，并且想使用索引，你可以使用以下方法，假设列“col_1”的最小值为1。

--IS NOT NULL的代替方案
SELECT ＊
FROM SomeTable
WHERE  col_1 > 0;

的原理非常简单。只要使用不等号并指定一个小于最小值的数字，就可以选择column_1中的所有值。由于 col_1 > NULL 的执行结果未知，因此不会选择“col_1”列中值为 NULL 的行。但是，如果要选择“非 NULL 行”，正确的做法是使用 IS NOT NULL。上述写法的含义有些混乱，因此不推荐。仅在紧急情况下使用它。

使用否定形式

以下否定形式不能在索引中使用。

❤

❤！ =

❤ NOT IN

因此下面的SQL语句也会执行全表扫描。 ？两者都用，但是效率比AND差很多。

SELECT ＊
FROM  SomeTable
WHERE  col_1 > 100 OR col_2 = 'abc';

如果必须使用 OR，一种方法是使用位图索引。然而，这种类型的索引更新数据的性能开销会增加，所以在使用它之前应该权衡利弊。

使用联合索引时，列的顺序错误

假设有一个联合索引“col_1，col_2，col_3”，顺序是这样的。

此时，指定条件的顺序很重要。联合索引中

○   SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
○   SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
×   SELECT ＊ FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
×   SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
×   SELECT ＊ FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

的第1列（col_1）必须写在搜索条件的开头，并且索引中列的顺序不能颠倒。在某些数据库中，顺序颠倒后也可以使用索引，但性能仍然比顺序正确时差。

如果无法保证查询条件中列的顺序与索引一致，可以考虑将联合索引拆分为多个索引。 ？为类型

×   SELECT ＊ FROM SomeTable WHERE col_1 = 10;
○   SELECT ＊ FROM SomeTable WHERE col_1 ='10';
○   SELECT ＊ FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

的列“col_1”指定条件的示例从标准类型转换不仅会增加额外的性能开销，还会使索引不可用，这可能是有害的，没有任何好处。虽然这样写不会导致错误，但是不用费心，在需要类型转换时显式地进行类型转换（记住将转换写在条件表达式的右侧）。

04。缩小中间表

在SQL中，子查询的结果被视为一个新表。这个新表可以像原来的表一样通过代码进行操作。这种高度的相似性使得SQL编程非常灵活，但是使用大量没有约束的中间表会降低查询性能。

定期使用中间表会带来两个问题。一是扩展数据需要内存资源，二是原表中的索引不好用（尤其是聚合时）。因此，尽量减少中间表的使用也是提高性能的重要途径。

灵活使用HAVING子句

在指定聚合结果的过滤条件时，使用HAVING子句是一个基本原则。不习惯使用HAVING子句的数据库工程师可能会倾向于生成一个中间表，如下所示，然后在WHERE子句中指定过滤条件。

结果：

sale_date       tot_qty
------------   ---------
07-10-01               10
07-10-03               32
07-10-04               22

但是，当为聚合结果指定过滤条件时，不需要专门生成中间表。只需使用 HAVING 子句，如下所示。

SELECT sale_date, MAX(quantity)
FROM SalesHistory
GROUP BY sale_date
HAVING MAX(quantity) >= 10;

HAVING子句和聚合操作是同时执行的，所以相比中间表生成后执行的WHERE子句，效率更高，代码看起来更简洁。

如果需要在多个字段上使用IN谓词，请将它们汇总在一个地方

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

我们看下面的例子。此处谓词 IN 用于多个字段，“id”列是主键。

SELECT id, state, city
FROM Addresses1 A1
WHERE state IN 
  (SELECT state
  FROM Addresses2 A2
  WHERE A1.id = A2.id)
AND city IN 
  (SELECT city
  FROM Addresses2 A2
  WHERE A1.id = A2.id);

此代码中使用了两个子查询。但是，如果将这些字段连接在一起（如下所示），则可以将逻辑编写在一处。

SELECT ＊
FROM Addresses1 A1
WHERE id || state || city IN 
  (SELECT id || state|| city
  FROM Addresses2 A2);

这样子查询不需要考虑相关性，只能运行一次。另外，如果使用的数据库实现了逐行比较，我们可以将多个字段的组合写入IN中，如下所示。

SELECT ＊
FROM Addresses1 A1
WHERE (id, state, city) IN 
  (SELECT id, state, city
  FROM Addresses2 A2);

与之前连接字段的方法相比，这种方法有两个优点。首先，连接字段时不必担心类型转换问题。其次，该方法不处理字段，因此可以使用索引。

先连接，再聚合

《使用外连接》一文中提到，同时使用连接和聚合时，先执行连接操作可以防止中间表的生成。 原因是，从集合运算的角度来看，连接执行的是“乘法运算”。当连接表双方都是一对一或一对多关系时，连接后数据行数不会增加。并且由于在许多设计中可以将多对多关系拆分为两个一对多关系，因此这种技术可以在大多数情况下使用。

正确使用视图

视图是非常有用的工具，我想很多人在日常工作中经常使用它们。然而，定义复杂的视图而不对其进行太多考虑可能会导致巨大的性能问题。特别是如果视图定义语句包含以下操作，SQL的效率会非常低，执行速度会变得非常慢。

❤ 聚合函数（AVG、COUNT、SUM、MIN、MAX）

§ 设置运算符（UNION、INTERSECT、EXCEPT、等等.)

一般而言，请特别注意避免在视图中进行聚合操作。最近，越来越多的数据库实现了物化视觉等技术来解决视觉的这一缺点。当视图的定义变得复杂时考虑使用它。

05。总结

本文重点介绍优化SQL性能时的一些注意事项。虽然这里列出了一些要点，但优化的核心思想其实只有一个，那就是找出性能瓶颈在哪里，集中精力解决它。