嵌套查询WHERE子句未提升MySQL窗口函数查询性能求优化

我在MySQL中运行一个包含RANK窗口函数的高负载查询，原本试图通过嵌套查询的WHERE子句过滤retrieveDate来减少处理数据量，但执行EXPLAIN后发现，无论将过滤范围设为1周、1个月还是2个月，性能都没有任何提升，求优化方案。

原查询语句 #

SELECT 
    t1.*,
    RANK () OVER (
        PARTITION BY 
            t1.`country`, 
            t1.`product_id`, 
            t1.`retrieveDate`, 
            t1.`retrieveHour` 
        ORDER BY 
            t1.`retrieveDatetime` DESC) AS `ranking`
FROM (
    SELECT * FROM product_data
    WHERE retrieveDate > (CURRENT_DATE() - INTERVAL 1 WEEK)
) t1
WHERE t1.ranking = 1

EXPLAIN输出结果 #

{
    "query_block": {
      "select_id": 1,
      "cost_info": {
        "query_cost": "355813.66"
      },
      "windowing": {
        "windows": [
          {
            "name": "<unnamed window>",
            "using_filesort": true,
            "filesort_key": [
              "`product_id`",
              "`country`",
              "`retrieveDate`",
              "`retrieveHour`",
              "`retrieveDatetime` desc"
            ],
            "functions": [
              "rank"
            ]
          }
        ],
        "cost_info": {
          "sort_cost": "271814.81"
        },
        "table": {
          "table_name": "product_data",
          "access_type": "ALL",
          "rows_examined_per_scan": 815526,
          "rows_produced_per_join": 271814,
          "filtered": "33.33",
          "cost_info": {
            "read_cost": "2446.25",
            "eval_cost": "27181.48",
            "prefix_cost": "83998.85",
            "data_read_per_join": "553M"
          },
          "used_columns": [
            "product_id",
            "country",
            "category",
            "rank",
            "primaryCategory",
            "primaryCategoryRank",
            "retrieveDatetime",
            "createdAt",
            "updatedAt",
            "retrieveDate",
            "retrieveHour"
          ],
          "attached_condition": "(`product_data`.`retrieveDate` > <cache>((curdate() - interval 1 week)))"
        }
      }
    }
  }

现有索引 #

• BTree复合索引：product_id(varchar, 位置1)、country(int, 位置2)、retrieveDate(date, 位置13)、retrieveHour(int, 位置14)、retrieveDatetime(datetime, 位置10)
• 独立的retrieveDatetime单字段索引

优化方案 #

1. 调整复合索引顺序，适配业务逻辑 #

当前复合索引的字段顺序无法被窗口函数的PARTITION BY和ORDER BY有效利用，需要创建覆盖索引，字段顺序遵循：

• 先放WHERE过滤字段：retrieveDate
• 再放PARTITION BY分组字段：country、product_id、retrieveHour
• 最后放ORDER BY排序字段：retrieveDatetime（DESC）

创建索引语句：

CREATE INDEX idx_prod_data_date_country_prod_hour_dt ON product_data 
(retrieveDate, country, product_id, retrieveHour, retrieveDatetime DESC);

该索引可直接满足过滤、分组、排序需求，消除全表扫描和文件排序（EXPLAIN中using_filesort会消失）。

2. 避免SELECT *，使用字段列表 #

原查询用SELECT *会读取大量冗余字段，增加IO开销。明确列出需要的字段，配合覆盖索引实现索引-only扫描，进一步提升性能。

修改后查询示例：

SELECT 
    t1.country,
    t1.product_id,
    t1.retrieveDate,
    t1.retrieveHour,
    t1.retrieveDatetime,
    -- 其他实际需要的字段
    RANK () OVER (
        PARTITION BY 
            t1.country, 
            t1.product_id, 
            t1.retrieveDate, 
            t1.retrieveHour 
        ORDER BY 
            t1.retrieveDatetime DESC) AS ranking
FROM (
    SELECT 
        country,
        product_id,
        retrieveDate,
        retrieveHour,
        retrieveDatetime
        -- 其他实际需要的字段
    FROM product_data
    WHERE retrieveDate > (CURRENT_DATE() - INTERVAL 1 WEEK)
) t1
WHERE t1.ranking = 1

3. 用分组聚合替代窗口函数（可选） #

如果仅需每个分组的最新一条记录，可替换RANK函数为分组聚合，避免窗口函数的排序开销：

SELECT 
    pd.country,
    pd.product_id,
    pd.retrieveDate,
    pd.retrieveHour,
    pd.retrieveDatetime
    -- 其他实际需要的字段
FROM product_data pd
JOIN (
    SELECT 
        country,
        product_id,
        retrieveDate,
        retrieveHour,
        MAX(retrieveDatetime) AS max_datetime
    FROM product_data
    WHERE retrieveDate > (CURRENT_DATE() - INTERVAL 1 WEEK)
    GROUP BY country, product_id, retrieveDate, retrieveHour
) pd_max 
ON pd.country = pd_max.country
AND pd.product_id = pd_max.product_id
AND pd.retrieveDate = pd_max.retrieveDate
AND pd.retrieveHour = pd_max.retrieveHour
AND pd.retrieveDatetime = pd_max.max_datetime
WHERE pd.retrieveDate > (CURRENT_DATE() - INTERVAL 1 WEEK);

该写法可复用上述复合索引，分组聚合效率更高。

4. 验证索引效果 #

创建新索引后执行EXPLAIN，需确认：

• access_type变为range或ref，不再是ALL（全表扫描）
• Extra字段显示Using index（覆盖索引扫描），无Using filesort

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