SQL提高查询性能的几种方式

## 创建索引，提高性能

索引可以极大地提高查询性能，其背后的原理：
1. 索引是的数据库引擎能够快速的找到表中的数据，它们类似于书籍的目录，使得你不需要逐页查找所需要的信息
2. 索引能够帮助数据库引擎直接定位到所需的数据，从而大大减少磁盘I/O操作，如果没有索引，SQL SERSER可能需要执行全表的扫描来查询数据，这需要大量的磁盘I/O操作
3. 在分布式查询中，如果远程服务器上的表有索引，那么只需要将所需要的数据行发送的请求服务器，而不是整个表，从而减少了网络的流量
4. 查询优化器会使用索引统计信息来生成最有效的查询计划。

SQL Server 提供了多种类型的索引，以优化查询性能和满足不同的数据访问需求，以下是一些主要常用的索引类型：
1. 聚集索引：每个表只能有一个聚集索引。这种索引决定了表中数据的物理存储顺序。聚集索引使用行的键值对数据进行排序和存储.
```
CREATE CLUSTERED INDEX IDX_Table_Column
ON Table (Column);
```
2. 非聚集索引：非聚集索引与聚集索引不同，它不影响数据的物理存储顺序，而是创建一个不同的数据结构（B-tree），其中包含键值和对应行数据的指针。一个表可以有多个非聚集索引。
```
CREATE NONCLUSTERED INDEX IDX_Table_Column
ON Table (Column);
```
3. 唯一索引：唯一索引确保索引键中的每个值只出现一次。这意味着每个索引键对应一个唯一的数据行。唯一索引可以是聚集索引或非聚集索引。
```
CREATE UNIQUE NONCLUSTERED INDEX IDX_Table_Column
ON Table (Column);
```
4. 复合索引：复合索引是包含两个或更多列的索引。复合索引的顺序很重要，因为 SQL Server 将首先按照第一列排序，然后在每个第一列的值内按照第二列排序，依此类推。
```
CREATE INDEX IDX_Table_Column1_Column2
ON Table (Column1, Column2);
```
5. 过滤索引：过滤索引是非聚集索引的一种变体，它只包含满足特定过滤谓词的行。这可以减小索引的大小并提高查询性能。
```
CREATE NONCLUSTERED INDEX IDX_Table_Column
ON Table (Column)
WHERE Column IS NOT NULL;
```
6. 全文索引：全文索引用于在全文查询中快速查找文本数据中的词语。
```
CREATE FULLTEXT INDEX ON Table (TextColumn)
KEY INDEX IDX_Table_Column;
```

## 避免在WHERE子句中使用NOT和<>运算符，提高性能
在SQL Server查询中，尽量避免在WHERE子句中使用NOT和<>运算符的主要原因是这两种运算符可能会降低查询性能。以下是具体的解释：
1. 索引不利用： SQL Server通常会使用索引来加速查询。但是，当你使用NOT或<>运算符时，SQL Server可能无法有效地使用索引，因为这些运算符需要扫描所有的行而不只是索引的一部分。这可能导致查询速度变慢。
2. 全表扫描： 当使用NOT或<>运算符时，SQL Server可能需要执行全表扫描，即需要检查表中的每一行以确定哪些行满足查询条件。全表扫描通常比使用索引扫描要慢得多。
3. 结果预测困难： 对于优化器来说，预测使用NOT或<>运算符的查询结果的行数比较困难，这可能会导致生成的执行计划不是最优的。
因此，尽管在某些情况下，使用NOT或<>运算符是必要的，但在可能的情况下，应尽量避免使用它们，以提高查询性能。

在某些情况下，我们可以通过其他查询语句来避免使用"NOT"和"<>"运算符达到同样的结果，这可能有助于SQL SERVER更有效地使用索引，从而提高查询性能
1. 使用 = 和 IN 运算符： 如果你知道你想要查询的具体值，你可以使用 = 或 IN 运算符，而不是使用 <>。例如，如果你想要查询所有不是 'A' 或 'B' 的行，你可以将查询从 WHERE column <> 'A' AND column <> 'B' 改写为 WHERE column IN ('C', 'D', 'E', ...)
2. 使用 BETWEEN 运算符： 如果你想要查询的值在一个范围内，你可以使用 BETWEEN 运算符，而不是使用 <>。例如，如果你想要查询所有不在1到10之间的行，你可以将查询从 WHERE column NOT BETWEEN 1 AND 10 改写为 WHERE column < 1 OR column > 10。
3. 使用 IS NULL 和 IS NOT NULL： 如果你想要查询的是空值或非空值，你可以使用 IS NULL 或 IS NOT NULL 运算符，而不是使用 <>。例如，如果你想要查询所有非空的行，你可以将查询从 WHERE column <> NULL 改写为 WHERE column IS NOT NULL。
4. 使用EXISTS和NOT EXISTS：特别是在处理相关子查询时，EXISTS和NOT EXISTS在某些情况下可能比使用NOT和<>运算符更高效。

## 对于存储大数据集时，将表变量改为临时表，提高性能
表变量和临时表都是用于在SQL Server中存储一些临时数据的工具。它们之间存在一些关键的区别，包括在性能方面的差异。

### 表变量
表变量在SQL Server中被定义为一个变量，这意味着它的生命周期只在声明它的批处理或存储过程中。表变量通常用于存储返回不多的数据，例如几百行。
性能方面：
1. 表变量不会导致重新编译，因此在某些情况下，它可以提高性能。
2. 表变量不会在磁盘上创建，而是在内存中创建，通常可以提供更好的性能。
3. 表变量不会参与事务，因此不会导致锁定和日志记录，这可能会提高性能。
创建表变量，如下所示
```
DECLARE @TableVariable TABLE
(
ID INT,
Value NVARCHAR(50)
)
```

### 临时表
临时表在SQL Server中被定义为一个真正的表，存储在tempdb数据库中，并且可以在当前会话中使用。临时表通常用于存储大量数据，例如数千或数万行。
性能方面：
1. 临时表可能会导致存储过程的重新编译，这可能会降低性能。
2. 临时表在磁盘上创建，这可能会比在内存中创建表变量慢。
3. 临时表参与事务，可能会导致锁定和日志记录，这可能会降低性能。
创建临时表，如下所示
```
CREATE TABLE #TempTable
(
ID INT,
Value NVARCHAR(50)
)
```
总的来说，表变量和临时表各有优势，选择哪种类型取决于你的特定需求。如果你需要存储大量数据，或者需要使用索引、统计信息等功能，那么临时表可能是更好的选择。如果你只需要存储少量数据，并且希望避免重新编译和日志记录，那么表变量可能是更好的选择。

## 使用 OPTION(RECOMPILE)，提高性能
在 SQL Server 中，OPTION (RECOMPILE) 是一种查询提示，它会使 SQL Server 在每次运行查询时都生成一个新的执行计划。这在某些情况下可以帮助提高查询性能。以下是其背后的原理：
1. 参数灵敏性：当查询因参数值的变化而表现出不同的性能特性时，OPTION (RECOMPILE) 可以提高性能。这是因为每次查询执行时，SQL Server 都会根据当前参数值生成一个新的执行计划。
2. 避免计划缓存问题：如果查询计划在缓存中占用大量空间，或者因为参数嗅探问题导致性能下降，那么 OPTION (RECOMPILE) 可以帮助解决这些问题。因为每次查询执行时，都会生成一个新的执行计划，而不是重用缓存中的旧计划。
3. 数据修改操作：对于那些涉及大量数据修改的查询（如 INSERT、UPDATE、DELETE），使用 OPTION (RECOMPILE) 可以帮助 SQL Server 生成一个更优的执行计划，因为它会考虑到最新的数据分布。

以下是一个使用 OPTION (RECOMPILE) 的例子
假设我们有一个名为 Employees 的表，我们想要根据 salary 列的值来获取一些记录。我们可能会创建一个存储过程来执行这个查询，如下所示：
```
CREATE PROCEDURE GetEmployees @Salary INT
AS
BEGIN
SELECT * FROM Employees WHERE Salary > @Salary
END
```
在这个存储过程中，SQL Server 会为第一次运行存储过程时的 @Salary 参数值生成一个执行计划。然后，对于后续的运行，它会重用这个执行计划，无论 @Salary 参数的值是多少。现在，假设 Employees 表中的 Salary 分布是不均匀的，有些薪水范围的员工数量远多于其他薪水范围。在这种情况下，为某个特定的 @Salary 值生成的执行计划可能对其他 @Salary 值并不是最优的。为了解决这个问题，我们可以在查询中使用 OPTION (RECOMPILE)，如下所示：
```
CREATE PROCEDURE GetEmployees @Salary INT
AS
BEGIN
SELECT * FROM Employees WHERE Salary > @Salary OPTION (RECOMPILE)
END
```
现在，每次运行存储过程时，SQL Server 都会为当前的 @Salary 参数值生成一个新的执行计划，这可以提高查询性能。

然而，需要注意的是，OPTION (RECOMPILE) 并不总是提高性能。因为每次查询执行时都生成新的执行计划会消耗CPU资源，所以如果查询非常频繁，可能会导致CPU资源的浪费。因此，建议在使用 OPTION (RECOMPILE) 时，应根据具体的查询和系统性能来进行权衡。

# 总结
以上是我工作时常使用提高性能的几种方法，性能优化是一个持续不断的过程，它需要我们在实践中不断地学习，尝试和改进。而且，每个数据库和每个查询都有其独特性，所以最有效的优化策略可能因情况而异。如果你们有更多的方法、技巧或者是实践经验，希望你们能在评论区分享哦。让我们一起在这个领域里进一步深化我们的知识，共同提高我们的技能。在这个过程中，我期待与你们的交流和学习，让我们一起在SQL查询性能优化的道路上不断前行。

作者：百宝门-后端组-李桂林

原文地址：https://blog.baibaomen.com/sql%e6%8f%90%e9%ab%98%e6%9f%a5%e8%af%a2%e6%80%a7%e8%83%bd%e7%9a%84%e5%87%a0%e7%a7%8d%e6%96%b9%e5%bc%8f/