sql性能优化，如何优化 in/not in这类关键字的语句？

使用 EXISTS 替代 IN
EXISTS 子查询通常比 IN 子查询更高效，特别是在子查询返回大量结果时。

示例：

  -- 使用 IN
  SELECT * FROM Orders
  WHERE UserId IN (SELECT UserId FROM Users WHERE IsVIP = 1);

  -- 使用 EXISTS
  SELECT * FROM Orders o
  WHERE EXISTS (SELECT 1 FROM Users u WHERE u.UserId = o.UserId AND u.IsVIP = 1);

使用 JOIN 替代 IN
JOIN 操作通常比 IN 子查询更高效，特别是在复杂的查询中。

示例：

  -- 使用 IN
  SELECT * FROM Orders
  WHERE UserId IN (SELECT UserId FROM Users WHERE IsVIP = 1);

  -- 使用 JOIN
  SELECT o.*
  FROM Orders o
  JOIN Users u ON u.UserId = o.UserId
  WHERE u.IsVIP = 1;

使用 NOT EXISTS 替代 NOT IN
NOT EXISTS 子查询通常比 NOT IN 子查询更高效，特别是在子查询返回大量结果或包含 NULL 值时。

示例

  -- 使用 NOT IN
  SELECT * FROM Orders
  WHERE UserId NOT IN (SELECT UserId FROM Users WHERE IsVIP = 1);

  -- 使用 NOT EXISTS
  SELECT * FROM Orders o
  WHERE NOT EXISTS (SELECT 1 FROM Users u WHERE u.UserId = o.UserId AND u.IsVIP = 1);

使用 LEFT JOIN 替代 NOT IN
LEFT JOIN 操作通常比 NOT IN 子查询更高效，特别是在处理 NULL 值时。

示例：

  -- 使用 NOT IN
  SELECT * FROM Orders
  WHERE UserId NOT IN (SELECT UserId FROM Users WHERE IsVIP = 1);

  -- 使用 LEFT JOIN
  SELECT o.*
  FROM Orders o
  LEFT JOIN Users u ON u.UserId = o.UserId AND u.IsVIP = 1
  WHERE u.UserId IS NULL;

确保索引**
确保 IN 和 NOT IN 子查询中使用的列上有合适的索引。

示例：

  CREATE INDEX idx_users_userid ON Users(UserId);
  CREATE INDEX idx_users_isvip ON Users(IsVIP);

避免使用 IN 进行范围查询**
IN 关键字不适合进行范围查询，特别是在子查询返回大量结果时。

示例：

  -- 避免这种写法
  SELECT * FROM Orders
  WHERE OrderDate IN (SELECT OrderDate FROM Orders WHERE UserId = 1);

  -- 使用 JOIN 或 EXISTS
  SELECT o.*
  FROM Orders o
  JOIN Orders o2 ON o.OrderDate = o2.OrderDate
  WHERE o2.UserId = 1;

处理 NULL 值**
NOT IN 子查询在子查询结果包含 NULL 值时会返回空结果集。可以使用 NOT EXISTS 或 LEFT JOIN 来避免这个问题。

示例

  -- 使用 NOT IN
  SELECT * FROM Orders
  WHERE UserId NOT IN (SELECT UserId FROM Users WHERE IsVIP IS NULL OR IsVIP = 1);

  -- 使用 NOT EXISTS
  SELECT * FROM Orders o
  WHERE NOT EXISTS (SELECT 1 FROM Users u WHERE u.UserId = o.UserId AND (u.IsVIP IS NULL OR u.IsVIP = 1));

示例：综合优化

假设我们有一个订单表 Orders 和一个用户表 Users，我们希望查询所有属于 VIP 用户的订单。以下是使用 IN、EXISTS 和 JOIN 的比较和优化示例。

创建表和索引

 CREATE TABLE Users (
 	UserId INT PRIMARY KEY,
 	Name NVARCHAR(100),
 	IsVIP BIT
 );

 CREATE TABLE Orders (
 	OrderId INT PRIMARY KEY,
 	UserId INT,
 	OrderDate DATE,
 	TotalAmount DECIMAL(10, 2)
 );

 CREATE INDEX idx_users_isvip ON Users(IsVIP);
 CREATE INDEX idx_orders_userid ON Orders(UserId);

插入示例数据

 INSERT INTO Users (UserId, Name, IsVIP)
 VALUES
 (1, 'Alice', 1),
 (2, 'Bob', 0),
 (3, 'Charlie', 1);

 INSERT INTO Orders (OrderId, UserId, OrderDate, TotalAmount)
 VALUES
 (101, 1, '2023-10-01', 100.00),
 (102, 2, '2023-10-02', 200.00),
 (103, 3, '2023-10-03', 150.00);

使用 IN 子查询

 -- 使用 IN 子查询
 SET STATISTICS TIME ON;
 SELECT * FROM Orders
 WHERE UserId IN (SELECT UserId FROM Users WHERE IsVIP = 1);
 SET STATISTICS TIME OFF;

使用 EXISTS 子查询

 -- 使用 EXISTS 子查询
 SET STATISTICS TIME ON;
 SELECT * FROM Orders o
 WHERE EXISTS (SELECT 1 FROM Users u WHERE u.UserId = o.UserId AND u.IsVIP = 1);
 SET STATISTICS TIME OFF;

使用 JOIN 操作

 -- 使用 JOIN 操作
 SET STATISTICS TIME ON;
 SELECT o.*
 FROM Orders o
 JOIN Users u ON u.UserId = o.UserId
 WHERE u.IsVIP = 1;
 SET STATISTICS TIME OFF;

解释

使用 EXISTS 和 JOIN：
- EXISTS 子查询和 JOIN 操作通常比 IN 子查询更高效，特别是在子查询返回大量结果时。
- EXISTS 子查询只检查是否存在符合条件的记录。
- JOIN 操作通过连接表来获取符合条件的记录。
索引：
- 确保 UserId 和 IsVIP 列上有合适的索引，以提高查询性能。
- CREATE INDEX idx_users_isvip ON Users(IsVIP);
- CREATE INDEX idx_orders_userid ON Orders(UserId);

示例：完整优化

以下是一个完整的示例，展示了如何在 SQL 查询中应用上述优化策略。

创建表和索引

 CREATE TABLE Users (
 	UserId INT PRIMARY KEY,
 	Name NVARCHAR(100),
 	IsVIP BIT
 );

 CREATE TABLE Orders (
 	OrderId INT PRIMARY KEY,
 	UserId INT,
 	OrderDate DATE,
 	TotalAmount DECIMAL(10, 2)
 );

 CREATE INDEX idx_users_isvip ON Users(IsVIP);
 CREATE INDEX idx_orders_userid ON Orders(UserId);

插入示例数据

 INSERT INTO Users (UserId, Name, IsVIP)
 VALUES
 (1, 'Alice', 1),
 (2, 'Bob', 0),
 (3, 'Charlie', 1);

 INSERT INTO Orders (OrderId, UserId, OrderDate, TotalAmount)
 VALUES
 (101, 1, '2023-10-01', 100.00),
 (102, 2, '2023-10-02', 200.00),
 (103, 3, '2023-10-03', 150.00);

使用 IN 子查询

 -- 使用 IN 子查询
 SET STATISTICS TIME ON;
 SELECT * FROM Orders
 WHERE UserId IN (SELECT UserId FROM Users WHERE IsVIP = 1);
 SET STATISTICS TIME OFF;

使用 EXISTS 子查询

 -- 使用 EXISTS 子查询
 SET STATISTICS TIME ON;
 SELECT * FROM Orders o
 WHERE EXISTS (SELECT 1 FROM Users u WHERE u.UserId = o.UserId AND u.IsVIP = 1);
 SET STATISTICS TIME OFF;

使用 JOIN 操作

 -- 使用 JOIN 操作
 SET STATISTICS TIME ON;
 SELECT o.*
 FROM Orders o
 JOIN Users u ON u.UserId = o.UserId
 WHERE u.IsVIP = 1;
 SET STATISTICS TIME OFF;

使用表值参数

 -- 定义表值参数类型
 CREATE TYPE dbo.UserIdTableType AS TABLE (UserId INT);

 -- 创建存储过程
 CREATE PROCEDURE GetOrdersForVIPUsers
 	@UserIds dbo.UserIdTableType READONLY
 AS
 BEGIN
 	SELECT o.*
 	FROM Orders o
 	JOIN @UserIds u ON u.UserId = o.UserId;
 END;

 -- 调用存储过程
 DECLARE @UserIds dbo.UserIdTableType;
 INSERT INTO @UserIds (UserId)
 SELECT UserId FROM Users WHERE IsVIP = 1;

 EXEC GetOrdersForVIPUsers @UserIds;

解释

使用 EXISTS 和 JOIN：
- EXISTS 子查询和 JOIN 操作通常比 IN 子查询更高效，特别是在子查询返回大量结果时。
- EXISTS 子查询只检查是否存在符合条件的记录。
- JOIN 操作通过连接表来获取符合条件的记录。
索引：
- 确保 UserId 和 IsVIP 列上有合适的索引，以提高查询性能。
- CREATE INDEX idx_users_isvip ON Users(IsVIP);
- CREATE INDEX idx_orders_userid ON Orders(UserId);
表值参数：
- 使用表值参数可以减少子查询的开销，特别是在子查询返回的结果集较大时。
实际性能测试：
- 使用 SET STATISTICS TIME ON 和 SET STATISTICS TIME OFF 查看查询的执行时间。

运行结果示例

	-- 使用 IN 子查询
	Table 'Users'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
	Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	 SQL Server 执行时间:
	   CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。

	-- 使用 EXISTS 子查询
	Table 'Users'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
	Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	 SQL Server 执行时间:
	   CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。

	-- 使用 JOIN 操作
	Table 'Users'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
	Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

	 SQL Server 执行时间:
	   CPU 时间 = 0 毫秒，经过时间 = 1 毫秒。

总结

优化 IN 和 NOT IN 查询：
- 使用 EXISTS 替代 IN：通常更高效。
- 使用 JOIN 替代 IN：通常更高效。
- 使用 NOT EXISTS 替代 NOT IN：避免 NULL 值问题，通常更高效。
- 使用 LEFT JOIN 替代 NOT IN：避免 NULL 值问题，通常更高效。
- 确保索引：为查询列创建合适的索引，提高查询性能。
- 避免复杂查询：确保子查询中的操作尽可能简单，避免复杂的计算或排序操作。

posted @ 2025-01-05 12:35 似梦亦非梦阅读(10) 评论(0) 编辑收藏举报

刷新页面返回顶部

chenshibao

sql性能优化，如何优化 in/not in这类关键字的语句？

示例：综合优化

解释

示例：完整优化

解释

运行结果示例

总结

公告