SQL提高查询性能的几种方式
创建索引，提高性能
索引可以极大地提高查询性能，其背后的原理：
- 索引是的数据库引擎能够快速的找到表中的数据，它们类似于书籍的目录，使得你不需要逐页查找所需要的信息
- 索引能够帮助数据库引擎直接定位到所需的数据，从而大大减少磁盘I/O操作，如果没有索引，SQL SERSER可能需要执行全表的扫描来查询数据，这需要大量的磁盘I/O操作
- 在分布式查询中，如果远程服务器上的表有索引，那么只需要将所需要的数据行发送的请求服务器，而不是整个表，从而减少了网络的流量
- 查询优化器会使用索引统计信息来生成最有效的查询计划。
SQL Server 提供了多种类型的索引，以优化查询性能和满足不同的数据访问需求，以下是一些主要常用的索引类型：
- 聚集索引：每个表只能有一个聚集索引。这种索引决定了表中数据的物理存储顺序。聚集索引使用行的键值对数据进行排序和存储.
CREATE CLUSTERED INDEX IDX_Table_Column ON Table (Column); - 非聚集索引：非聚集索引与聚集索引不同，它不影响数据的物理存储顺序，而是创建一个不同的数据结构（B-tree），其中包含键值和对应行数据的指针。一个表可以有多个非聚集索引。
CREATE NONCLUSTERED INDEX IDX_Table_Column ON Table (Column); - 唯一索引：唯一索引确保索引键中的每个值只出现一次。这意味着每个索引键对应一个唯一的数据行。唯一索引可以是聚集索引或非聚集索引。
CREATE UNIQUE NONCLUSTERED INDEX IDX_Table_Column ON Table (Column); - 复合索引：复合索引是包含两个或更多列的索引。复合索引的顺序很重要，因为 SQL Server 将首先按照第一列排序，然后在每个第一列的值内按照第二列排序，依此类推。
CREATE INDEX IDX_Table_Column1_Column2 ON Table (Column1, Column2); - 过滤索引：过滤索引是非聚集索引的一种变体，它只包含满足特定过滤谓词的行。这可以减小索引的大小并提高查询性能。
CREATE NONCLUSTERED INDEX IDX_Table_Column ON Table (Column) WHERE Column IS NOT NULL; - 全文索引：全文索引用于在全文查询中快速查找文本数据中的词语。
CREATE FULLTEXT INDEX ON Table (TextColumn) KEY INDEX IDX_Table_Column;
避免在WHERE子句中使用NOT和<>运算符，提高性能
在SQL Server查询中，尽量避免在WHERE子句中使用NOT和<>运算符的主要原因是这两种运算符可能会降低查询性能。以下是具体的解释：
- 索引不利用： SQL Server通常会使用索引来加速查询。但是，当你使用NOT或<>运算符时，SQL Server可能无法有效地使用索引，因为这些运算符需要扫描所有的行而不只是索引的一部分。这可能导致查询速度变慢。
- 全表扫描： 当使用NOT或<>运算符时，SQL Server可能需要执行全表扫描，即需要检查表中的每一行以确定哪些行满足查询条件。全表扫描通常比使用索引扫描要慢得多。
- 结果预测困难： 对于优化器来说，预测使用NOT或<>运算符的查询结果的行数比较困难，这可能会导致生成的执行计划不是最优的。
因此，尽管在某些情况下，使用NOT或<>运算符是必要的，但在可能的情况下，应尽量避免使用它们，以提高查询性能。
在某些情况下，我们可以通过其他查询语句来避免使用"NOT"和"<>"运算符达到同样的结果，这可能有助于SQL SERVER更有效地使用索引，从而提高查询性能
- 使用 = 和 IN 运算符： 如果你知道你想要查询的具体值，你可以使用 = 或 IN 运算符，而不是使用 <>。例如，如果你想要查询所有不是 'A' 或 'B' 的行，你可以将查询从 WHERE column <> 'A' AND column <> 'B' 改写为 WHERE column IN ('C', 'D', 'E', ...)
- 使用 BETWEEN 运算符： 如果你想要查询的值在一个范围内，你可以使用 BETWEEN 运算符，而不是使用 <>。例如，如果你想要查询所有不在1到10之间的行，你可以将查询从 WHERE column NOT BETWEEN 1 AND 10 改写为 WHERE column < 1 OR column > 10。
- 使用 IS NULL 和 IS NOT NULL： 如果你想要查询的是空值或非空值，你可以使用 IS NULL 或 IS NOT NULL 运算符，而不是使用 <>。例如，如果你想要查询所有非空的行，你可以将查询从 WHERE column <> NULL 改写为 WHERE column IS NOT NULL。
- 使用EXISTS和NOT EXISTS：特别是在处理相关子查询时，EXISTS和NOT EXISTS在某些情况下可能比使用NOT和<>运算符更高效。
对于存储大数据集时，将表变量改为临时表，提高性能
表变量和临时表都是用于在SQL Server中存储一些临时数据的工具。它们之间存在一些关键的区别，包括在性能方面的差异。
表变量
表变量在SQL Server中被定义为一个变量，这意味着它的生命周期只在声明它的批处理或存储过程中。表变量通常用于存储返回不多的数据，例如几百行。
性能方面：
- 表变量不会导致重新编译，因此在某些情况下，它可以提高性能。
- 表变量不会在磁盘上创建，而是在内存中创建，通常可以提供更好的性能。
- 表变量不会参与事务，因此不会导致锁定和日志记录，这可能会提高性能。
创建表变量，如下所示
DECLARE @TableVariable TABLE
(
ID INT,
Value NVARCHAR(50)
)
临时表
临时表在SQL Server中被定义为一个真正的表，存储在tempdb数据库中，并且可以在当前会话中使用。临时表通常用于存储大量数据，例如数千或数万行。
性能方面：
- 临时表可能会导致存储过程的重新编译，这可能会降低性能。
- 临时表在磁盘上创建，这可能会比在内存中创建表变量慢。
- 临时表参与事务，可能会导致锁定和日志记录，这可能会降低性能。
创建临时表，如下所示
CREATE TABLE #TempTable
(
ID INT,
Value NVARCHAR(50)
)
总的来说，表变量和临时表各有优势，选择哪种类型取决于你的特定需求。如果你需要存储大量数据，或者需要使用索引、统计信息等功能，那么临时表可能是更好的选择。如果你只需要存储少量数据，并且希望避免重新编译和日志记录，那么表变量可能是更好的选择。
使用 OPTION(RECOMPILE)，提高性能
在 SQL Server 中，OPTION (RECOMPILE) 是一种查询提示，它会使 SQL Server 在每次运行查询时都生成一个新的执行计划。这在某些情况下可以帮助提高查询性能。以下是其背后的原理：
- 参数灵敏性：当查询因参数值的变化而表现出不同的性能特性时，OPTION (RECOMPILE) 可以提高性能。这是因为每次查询执行时，SQL Server 都会根据当前参数值生成一个新的执行计划。
- 避免计划缓存问题：如果查询计划在缓存中占用大量空间，或者因为参数嗅探问题导致性能下降，那么 OPTION (RECOMPILE) 可以帮助解决这些问题。因为每次查询执行时，都会生成一个新的执行计划，而不是重用缓存中的旧计划。
- 数据修改操作：对于那些涉及大量数据修改的查询（如 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提高查询性能的几种方式/
