如何：优化SQL查询

介绍

在本文中，我将讨论用于分析和优化SQL查询的基本方法。 带有适当索引的正确编写的SQL可以将数小时内运行的查询转换为数秒内运行的查询。 虽然本文中的代码植根于SQL Server，但大多数概念将适用于其他数据库。

我们将结束：

1. 分析查询性能
2. 优化SQL语法
3. 查询执行计划
4. 创建适当的指数

分析查询性能

SQL Server带有一些选项来分析查询的性能。 我在查询中使用的基本测试方法是：

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE 
SET STATISTICS IO ON
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
GO 
-- SQL GOES HERE
GO 
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
SET STATISTICS PROFILE OFF
GO

我要做的第一件事是清除所有缓存，以便我可以在没有先前运行帮助的情况下重新运行SQL。

CHECKPOINT-写入所有未写入的缓冲区。

DBCC DROPCLEANBUFFERS-清除缓冲区高速缓存。

DBCC FREEPROCCACHE-清除计划缓存。

之后，我打开一些统计选项。

SET STATISTICS IO-向您显示有关查询发生的磁盘活动的信息。 数字越小，速度越快。

SET STATISTICS TIME ON-显示运行查询需要多长时间（以毫秒为单位）。

SET STATISTICS PROFILE ON-向您显示查询执行计划的文本版本。 该计划揭示了数据库用于运行查询的每个步骤以及运行该步骤所花费的时间。

优化SQL语法

您可以选择许多语法来加快查询速度。 我将在这里讨论其中的一些选择。 前八个是相对自我解释的，但后三个值得进一步讨论。

• 避免使用视图。
• 尽可能使用INNER JOIN而不是OUTER JOIN之一。
• 避免使用SELECT *，仅返回所需的字段。
• 除非需要，否则不要排序或排序。
• 不要联接到不需要查询的表。
• 避免NOT，即NOT IN和NOT EXISTS。
• EXISTS比IN快。
• 如果不需要DISTINCT记录，请使用UNION ALL而不是UNION。
• 有时，UNION比OR更快。 要知道哪一个更快，请同时尝试两者并查看统计信息。
• 在WHERE子句中过滤记录时，请尝试避免对字段进行计算。 将它们移到方程的常数侧。 如果无法避免，请使用包含计算的索引。
• 在90％的时间内，在WHERE子句中使用子查询进行过滤比通过JOINing到子查询进行过滤要慢。 要知道哪一个更快，请同时尝试两者并查看统计信息。

在WHERE子句中，使用OR条件有时会大大降低查询速度。 如果发生这种情况，请改用UNION或UNION ALL。

-- OR syntax
SELECT someField
FROM someTable
WHERE otherField = 5 OR otherField = 6; 
-- UNION syntax
SELECT someField
FROM someTable
WHERE otherField = 5 
UNION ALL 
SELECT someField
FROM someTable
WHERE otherField = 6; 

它需要更长的代码，但是可以节省大量时间。 我曾经有一个查询，使用OR语法花费了数小时，而使用UNION则花费了不到5秒。 两者都进行测试，然后查看统计信息，以了解哪种情况更快。

当您对WHERE子句中的字段进行计算时，查询不再只能扫描索引，因为它现在必须对每条记录执行计算以查看是否应返回该记录。 例如，如果我有一个日期字段，并且想知道它距离当前日期还不到7天，那么我有两种语法选择：

DATEDIFF(D, dateField, GETDATE()) <= 5 
dateField BETWEEN DATEADD(D, -5, GETDATE()) AND GETDATE()

第一个比较慢，因为不能使用dateField的索引，因为它必须为每个记录计算。 同样，但语法也大不相同：

Left(LastName, 1) = 'T' 
LastName LIKE 'T%'

通常，如果需要基于另一个表或同一表过滤记录，则在JOIN中使用子查询比在WHERE子句中使用子查询更快。 例如，如果我想要所有字段作为记录，但仅想要基于类别和日期字段的最新记录，则可以采用以下两种方法之一：

SELECT *
FROM someTable AS t
WHERE dateField = (
   SELECT MAX(dateField)
   FROM someTable
   WHERE categoryField = t.categoryField
); 
SELECT *
FROM someTable AS t1
INNER JOIN (
   SELECT categoryField, MAX(dateField) AS dateField
   FROM someTable
   GROUP BY categoryField
) AS t2
ON t1.categoryField = t2.categoryField
   AND t1.dateField = t2.dateField;

两者都进行测试，然后查看统计信息，以了解哪种情况更快。 我曾经遇到过使用WHERE子句查询快得多的情况，以及在JOIN中查询快得多的情况。 但是JOIN方法在大多数情况下都是正确的。 查询执行计划

查询执行计划会告诉您数据库运行查询所需采取的步骤以及估计的/实际时间。 如果查询运行缓慢，这将显示它挂在哪一步。 数据库可以使用许多不同的运算符来运行查询，但慢速查询的主要根源是表扫描和RID查找。 表扫描读取表的每一行，以找到要返回的正确记录。 当使用非聚集索引查找匹配的行时，将使用RID查找。 它仅指向数据堆上的某个位置，并且必须转到堆中以查找值，而不是要存储在索引中的数据。 如果看到其中任何一个，则应创建适当的索引以摆脱它们。 但是，关于表扫描有一个警告，对于行数少的表，表扫描比索引扫描要快。

创建适当的指数

最好在主键字段上使用聚簇索引。 聚簇索引可显着加快字段搜索速度。 聚集索引确定表中数据的物理顺序。 因此，您只能拥有一个。 但是，您可以根据需要拥有任意数量的非聚集索引。

创建索引时，顺序很重要。 按以下顺序放置字段：WHERE子句，JOIN子句，ORDER BY子句，SELECT子句。 因此，如果我想优化以下查询：

SELECT t1.someField
FROM someTable t1
INNER JOIN otherTable t2
ON t1.PK = t2.FK
WHERE t1.otherField = 15
ORDER BY t1.dateField

然后，我将按照以下顺序在具有字段的someTable上创建索引：otherField，PK，dateField，someField。 并在FK字段的otherTable上建立索引。

顺便说一句，前面的示例也是一个完整的查询。 这意味着索引包含查询所需的所有字段。 这意味着数据库不必参考表中的数据来查找查询的其他数据。 在可行的情况下，您应该创建一个索引以完全覆盖您要经常运行的查询。

但是，必须注意，索引可能会“太大”。 在技​​术限制和可用性方面。 字段越多，索引中包含的字段越大，使用索引运行查询所花费的时间就越长。

结论

关于硬件的快速说明。 无论您进行多少优化，有时您只需要纯能力。 如果您已尽最大可能优化了查询，但它仍然太慢，则可能必须升级硬件。

我希望本文为您提供了一些基本工具，使您可以使用几个小时才能运行的查询，并使它可以在几秒钟内运行。 请记住，优化是一个非常深入的主题，这些只是您可以用来加快查询速度的基本工具。 下次您有长时间运行的查询时，请分析统计信息和执行计划，在执行计划的帮助下创建正确的索引，并测试不同版本的SQL语法。

From: https://bytes.com/topic/sql-server/insights/935234-how-optimize-sql-queries