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 似梦亦非梦阅读(1440) 评论(0) 收藏举报

刷新页面返回顶部

chenshibao

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

示例：综合优化

解释

示例：完整优化

解释

运行结果示例

总结

公告