跨表排序引发全表扫描及分页性能问题的优化咨询

系统表结构概述 #

ProductType 表 #

ID=1, Description="Milk"
ID=2, Description="Bread"
ID=3, Description="Salt"
ID=4, Description="Sugar"

Product 表结构 #

ID, ProductTypeID, Name

产品名称存储规则：Product:Name 不含类型描述，完整名称由 ProductType.Description + Product.Name 拼接而成，例如：

ID=101, ProductTypeID=1, Name="bottle 1l" -- 展示为 "Milk bottle 1l"
ID=102, ProductTypeID=4, Name="pack 1kg" -- 展示为 "Sugar pack 1kg"

Product2Section 表结构 #

CREATE TABLE `Product2Section` (
  `SectionId` int(10) unsigned NOT NULL,
  `ProductId` int(10) unsigned NOT NULL,
  KEY `idx_ProductId` (`ProductId`),
  KEY `idx_SectionId` (`SectionId`),
  KEY `idx_ProductId_SectionId` (`ProductId`,`SectionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

性能问题描述 #

1. 关联 ProductType 与 Product 排序问题：关联两表并使用 ORDER BY TypeDescription ASC, ProductName ASC 排序时，无法利用索引，触发全表扫描；分页场景下（如 LIMIT 50000,100）查询耗时极长。尝试为VARCHAR类型的产品名字段建索引，但EXPLAIN显示未使用索引，仍存在全表扫描。
2. 仅关联 Product2Section 与 Product 的排序分页问题：移除ProductType相关逻辑后，使用Product2Section关联查询时性能依然缓慢，核心查询语句如下：

SELECT DISTINCT
    DRIVER.ProductId AS ID,
    p.*
FROM
    Product2Section AS DRIVER
LEFT JOIN Product p ON
    (p.ID = DRIVER.ProductId)
WHERE
    DRIVER.SectionId IN(
544,545,546,548,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,566,567,568,570,571,572,573,574,575,1337,1343,1353,1358,1369,1385,1956,1957,1964,1973,1979,1980,1987,1988,1994,1999,2016,2020,576,577,578,579,580,582,586,587,589,590,591,593,596,597,598,604,605,606,608,609,612,613,614,615,617,619,620,621,622,624,625,626,627,628,629,630,632,634,635,637,639,640,642,643,644,645,647,648,651,656,659,660,661,662,663,665,667,669,670,672,674,675,777
    )
ORDER BY
    p.ProductName ASC
LIMIT 500900,100;

EXPLAIN 查询结果 #

调整查询顺序（从Product表出发关联Product2Section）后，EXPLAIN仍显示Using temporary和Using filesort，性能无改善。

补充数据库统计信息 #

Product2Section 表统计数据 #

InnoDB缓冲池配置 #

SHOW VARIABLES LIKE 'innodb_buffer%';

innodb_buffer_pool_chunk_size   134217728   
innodb_buffer_pool_dump_at_shutdown ON  
innodb_buffer_pool_dump_now OFF 
innodb_buffer_pool_dump_pct 25  
innodb_buffer_pool_filename ib_buffer_pool  
innodb_buffer_pool_instances    4   
innodb_buffer_pool_load_abort   OFF 
innodb_buffer_pool_load_at_startup  ON  
innodb_buffer_pool_load_now OFF 
innodb_buffer_pool_size 3758096384  

用户疑问 #

1. 是否需要调整数据库设计，将查询相关数据合并至Product表？
2. 有哪些其他优化方案？
3. 对VARCHAR类型字段建索引能否提升排序速度？

优化方案 #

1. 针对 Product2Section + Product 排序分页的索引优化 #

当前瓶颈在于先筛选后排序，导致需要对大量结果集进行临时表排序。可通过以下方式让数据库直接利用索引完成排序：

• 在 Product 表创建联合索引：idx_ProductName_ID (ProductName, ID)
• 调整查询逻辑，先从Product表按排序顺序获取符合条件的ID，再关联获取其他数据：

SELECT p.*
FROM Product p
JOIN (
    SELECT DISTINCT DRIVER.ProductId
    FROM Product2Section DRIVER
    WHERE DRIVER.SectionId IN(/* 你的SectionId列表 */)
) AS filtered_products ON p.ID = filtered_products.ProductId
ORDER BY p.ProductName ASC
LIMIT 500900,100;

或者使用延迟关联减少排序数据量：

SELECT p.*
FROM (
    SELECT p.ID
    FROM Product p
    JOIN Product2Section DRIVER ON p.ID = DRIVER.ProductId
    WHERE DRIVER.SectionId IN(/* 你的SectionId列表 */)
    GROUP BY p.ID
    ORDER BY p.ProductName ASC
    LIMIT 500900,100
) AS sorted_ids
JOIN Product p ON sorted_ids.ID = p.ID
ORDER BY p.ProductName ASC;

排序仅针对ProductName和ID字段，利用索引直接完成排序，避免全量数据的filesort。

2. 针对 ProductType + Product 排序的优化 #

如果需要按TypeDescription, ProductName排序，可选择以下两种方案：

• 冗余字段方案：在Product表新增冗余字段TypeDescription（同步ProductType的Description值），并创建联合索引idx_TypeDesc_ProdName_ID (TypeDescription, ProductName, ID)
• 覆盖索引方案：在Product表建idx_ProductTypeID_Name_ID (ProductTypeID, Name, ID)，同时在ProductType表建idx_ID_Description (ID, Description)，让关联查询通过索引获取排序所需字段，避免回表。

3. VARCHAR字段索引对排序的作用 #

VARCHAR字段的索引可以提升排序速度，但前提是排序字段是索引的前缀，且查询能直接利用该索引完成排序，无需额外筛选或回表。如果排序前需要关联其他表筛选数据，数据库可能无法直接使用Product表的VARCHAR索引，此时需调整查询逻辑或增加冗余字段让索引生效。

4. 是否需要合并表？ #

合并表（如将ProductType的Description冗余到Product表）是有效方案，尤其当ProductType数据变更频率低时，冗余字段维护成本低，能大幅减少关联查询开销，让排序直接利用Product表的联合索引。若ProductType变更频繁，可通过触发器或应用层逻辑同步冗余字段，保证数据一致性。

5. 分页优化的其他思路 #

避免使用大偏移量的LIMIT，改用游标式分页：以上一页最后一条数据的ProductName和ID作为条件，例如：

SELECT p.*
FROM Product p
JOIN Product2Section DRIVER ON p.ID = DRIVER.ProductId
WHERE DRIVER.SectionId IN(/* 你的SectionId列表 */)
  AND (p.ProductName > '上一页最后名称' OR (p.ProductName = '上一页最后名称' AND p.ID > 上一页最后ID))
ORDER BY p.ProductName ASC, p.ID ASC
LIMIT 100;

这种方式可完全利用索引，避免大偏移量导致的全量扫描排序。

内容的提问来源于stack exchange，提问作者Alex