SQL Server多厂家分页查询：每个厂家最多50款产品的实现策略

在数据密集型应用中，分页查询是常见的需求。当需要从多个厂家（或分类）中分别获取限定数量的产品数据时，传统的TOP或LIMIT（SQL Server中需通过TOP实现）无法直接满足”按组分页”的需求。本文将详细介绍在SQL Server中实现”查询多个厂家，每个厂家最多50款产品”的多种技术方案，并分析其适用场景与性能优化策略。

一、问题场景与需求分析

1.1 典型业务场景

• 电商平台展示商品时，需从不同供应商（厂家）中分别展示最多50款热销商品。
• 制造业系统中，需从多个生产厂家中查询最新50款产品型号。
• 数据分析场景下，需对不同分类的数据进行等量抽样。

1.2 核心需求

• 分组限制：数据需按厂家分组，每组独立限制返回数量。
• 性能要求：在百万级数据量下，查询响应时间需控制在秒级。
• 结果确定性：需保证每次查询返回的50款产品相同（如按ID排序时）。

二、技术实现方案详解

方案一：ROW_NUMBER()窗口函数（推荐）

WITH RankedProducts AS (
    SELECT 
        p.*,
        m.ManufacturerName,
        ROW_NUMBER() OVER (PARTITION BY p.ManufacturerID ORDER BY p.ProductID) AS RowNum
    FROM Products p
    JOIN Manufacturers m ON p.ManufacturerID = m.ManufacturerID
)
SELECT * FROM RankedProducts
WHERE RowNum <= 50;

实现原理：

1. 通过PARTITION BY ManufacturerID将数据按厂家分组
2. 每组内按ProductID排序并分配行号
3. 外层查询筛选行号≤50的记录

优势：

• SQL标准语法，兼容性好
• 可灵活指定排序规则
• 适合复杂查询场景

性能优化：

• 确保ManufacturerID和排序字段有索引
• 对大表考虑分区表设计
• 避免在ORDER BY中使用函数计算

方案二：TOP N结合子查询（SQL Server特有）

SELECT p.*
FROM Products p
WHERE (
    SELECT COUNT(*) 
    FROM Products p2 
    WHERE p2.ManufacturerID = p.ManufacturerID 
    AND (p2.ProductID <= p.ProductID OR (p2.ProductID = p.ProductID AND p2.ProductID <= p.ProductID))
) <= 50
AND p.ManufacturerID IN (
    SELECT DISTINCT ManufacturerID FROM Products
);

实现原理：

1. 子查询计算当前产品在其厂家中的排名
2. 主查询筛选排名≤50的记录

注意事项：

• 此方案性能较差，仅适用于小数据量
• 排序逻辑需根据业务需求调整
• 不推荐在生产环境使用

方案三：OFFSET-FETCH（SQL Server 2012+）

-- 需要为每个厂家单独执行查询
DECLARE @ManufacturerIDs TABLE (ManufacturerID INT);
INSERT INTO @ManufacturerIDs SELECT DISTINCT ManufacturerID FROM Products;
DECLARE @Result TABLE (
    ProductID INT,
    ProductName NVARCHAR(100),
    ManufacturerID INT
);
DECLARE @CurrentID INT;
WHILE EXISTS (SELECT 1 FROM @ManufacturerIDs)
BEGIN
    SELECT TOP 1 @CurrentID = ManufacturerID FROM @ManufacturerIDs;
    INSERT INTO @Result
    SELECT TOP 50 p.*
    FROM Products p
    WHERE p.ManufacturerID = @CurrentID
    ORDER BY p.ProductID;
    DELETE FROM @ManufacturerIDs WHERE ManufacturerID = @CurrentID;
END
SELECT * FROM @Result;

实现原理：

1. 循环处理每个厂家ID
2. 对每个厂家执行独立的TOP查询
3. 合并结果

适用场景：

• 厂家数量较少时
• 需要对每个厂家执行不同查询逻辑时

三、性能优化最佳实践

3.1 索引设计策略

• 复合索引：在(ManufacturerID, ProductID)上创建索引
• 覆盖索引：包含查询所需的所有字段
• 过滤索引：对特定厂家创建专用索引

CREATE INDEX IX_Products_Manufacturer_Product 
ON Products (ManufacturerID, ProductID)
INCLUDE (ProductName, Price);

3.2 查询重写优化

• 避免在WHERE子句中使用函数
• 对大表考虑使用分区视图
• 使用临时表存储中间结果

3.3 执行计划分析

• 使用SET SHOWPLAN_TEXT ON分析查询计划
• 关注”Sort”操作和”Table Spool”操作
• 确保索引扫描而非表扫描

四、高级应用场景

4.1 动态排序需求

DECLARE @SortColumn NVARCHAR(50) = 'Price';
DECLARE @SortDirection NVARCHAR(4) = 'DESC';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
WITH RankedProducts AS (
    SELECT 
        p.*,
        m.ManufacturerName,
        ROW_NUMBER() OVER (PARTITION BY p.ManufacturerID ORDER BY ' + 
        QUOTENAME(@SortColumn) + ' ' + @SortDirection + ') AS RowNum
    FROM Products p
    JOIN Manufacturers m ON p.ManufacturerID = m.ManufacturerID
)
SELECT * FROM RankedProducts
WHERE RowNum <= 50;';
EXEC sp_executesql @SQL;

4.2 分页查询扩展

-- 获取第2页数据（每页50条）
DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 50;
WITH RankedProducts AS (
    SELECT 
        p.*,
        m.ManufacturerName,
        ROW_NUMBER() OVER (PARTITION BY p.ManufacturerID ORDER BY p.ProductID) AS RowNum
    FROM Products p
    JOIN Manufacturers m ON p.ManufacturerID = m.ManufacturerID
)
SELECT * FROM RankedProducts
WHERE RowNum BETWEEN (@PageNumber-1)*@PageSize + 1 AND @PageNumber*@PageSize;

五、常见问题与解决方案

5.1 数据倾斜问题

现象：某些厂家产品数量远多于其他厂家
解决方案：

• 对大厂家单独处理
• 使用采样查询替代精确查询
• 考虑使用APPROX_COUNT_DISTINCT进行估算

5.2 并发修改问题

现象：查询期间数据被修改导致结果不一致
解决方案：

• 使用SNAPSHOT隔离级别
• 添加版本号字段
• 对查询加锁（WITH (UPDLOCK)）

5.3 跨服务器查询

场景：需要从多个数据库服务器查询
解决方案：

• 使用分布式分区视图
• 通过链接服务器查询
• 考虑使用ETL工具预先聚合数据

六、总结与建议

1. 首选方案：ROW_NUMBER()窗口函数方案在大多数场景下是最优选择
2. 索引优先：确保排序和分区字段有适当索引
3. 批量处理：对于超大数据集，考虑分批处理
4. 结果缓存：对不常变动的查询结果进行缓存
5. 监控优化：定期检查执行计划，关注性能衰减

通过合理选择技术方案并结合性能优化策略，可以高效实现SQL Server中多厂家分页查询的需求，为业务系统提供稳定的数据支持。