如何优化关联product_score表按order列排序的查询速度？

背景概述 #

网站运营约20000个产品，每个产品对应不同算法版本（cohort_id）拥有唯一排序值（order，范围0-20000），关联product_score表做列表展示时出现性能瓶颈。

表结构定义 #

product_score表结构如下：

CREATE TABLE `product_score` (
  `product_id` int(10) UNSIGNED NOT NULL,
  `cohort_id` int(10) UNSIGNED NOT NULL,
  `score` float UNSIGNED NOT NULL,
  `last_updated` int(10) UNSIGNED NOT NULL DEFAULT 1709444313,
  `order` int(10) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `product_score`
  ADD PRIMARY KEY (`product_id`,`cohort_id`),
  ADD KEY `score` (`score`),
  ADD KEY `order` (`order`),
  ADD KEY `product_id` (`product_id`,`cohort_id`,`order`);

性能异常查询示例 #

以下查询在移除ORDER BY时响应迅速，但添加排序后耗时达1-2秒：

SELECT product.*, user.*, additional_data.* FROM product
INNER JOIN product_score AS Score ON Score.product_id = product.product_id AND Score.cohort_id = '24'
LEFT JOIN user ON user.user_id = product.user_id
LEFT JOIN additional_data...
ORDER BY Score.order LIMIT 20

已排查情况 #

• 已通过EXPLAIN确认JOIN逻辑正确命中product_id索引，未发现可新增的常规优化索引
• 移除左连接后查询耗时降至0.2秒，但左连接数据为业务必需，无法移除

性能分析信息 #

优化方案 #

1. 创建针对性联合索引 #

现有索引无法高效支撑cohort_id过滤+order排序的场景，建议创建覆盖过滤、排序、关联字段的联合索引：

CREATE INDEX idx_cohort_order_product ON product_score(cohort_id, `order`, product_id);

该索引可直接按cohort_id筛选数据，按order排序，同时直接获取product_id用于关联，避免回表查询，大幅减少IO开销。

2. 先缩小数据集再关联 #

避免先全量关联再排序的高开销，改为先从product_score中取出目标前20条产品ID，再关联其他表：

SELECT p.*, u.*, ad.*
FROM (
    SELECT product_id 
    FROM product_score 
    WHERE cohort_id = 24 
    ORDER BY `order` LIMIT 20
) AS top_scores
INNER JOIN product p ON p.product_id = top_scores.product_id
LEFT JOIN user u ON u.user_id = p.user_id
LEFT JOIN additional_data ad ...

此方式将数据集先压缩至20条，再执行关联操作，IO量会显著降低。

3. 检查左连接表的索引有效性 #

确保user.user_id、additional_data的关联字段存在主键或唯一索引，避免关联时触发全表扫描——即使主表仅20条数据，关联表的无索引扫描也会拖慢查询速度。

4. 避免SELECT *，仅查询必要字段 #

SELECT *会读取所有字段，增加数据传输与IO负载，明确指定业务所需字段，减少不必要的数据读取。

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