面试题：MySQL排序优化及特殊场景处理

Question

在一个非常大的`product`表中，有`product_id`（产品ID）、`category`（类别）、`price`（价格）、`views`（浏览量）等字段。需要查询每个类别中价格最高且浏览量在前5的产品信息。请写出高效的SQL查询语句，并简要说明优化思路。

Answer 1

SQL查询语句

WITH RankedProducts AS (
    SELECT
        product_id,
        category,
        price,
        views,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, views DESC) AS rank
    FROM
        product
)
SELECT
    product_id,
    category,
    price,
    views
FROM
    RankedProducts
WHERE
    rank <= 5;

优化思路

1. 窗口函数优化：使用窗口函数 ROW_NUMBER() 按类别分区，先按价格降序，再按浏览量降序排列，给每个产品在其所属类别内生成一个排名。这种方式避免了复杂的自连接操作，提高查询效率。
2. CTE 提高可读性：通过公用表表达式（CTE） RankedProducts 来封装窗口函数的结果，使主查询更简洁易懂，并且CTE在执行时会被优化器单独处理，可能会提高执行效率。
3. 索引优化：如果表非常大，在 category、price 和 views 字段上建立联合索引 (category, price DESC, views DESC)，可以加速窗口函数中的排序操作，从而提升整体查询性能。