面试题：MySQL查询优化之索引策略及执行计划分析

Question

有一个电商数据库，其中商品表（products）有字段id、name、price、category_id、stock等，销售记录表（sales_records）有字段id、product_id、sale_date、quantity等。现在需要查询出每个类别中价格最高的商品及其销售记录（sale_date、quantity）。请先写出SQL查询语句，然后分析查询执行计划，并说明如何通过索引优化该查询。

Answer 1

SQL查询语句

WITH RankedProducts AS (
    SELECT 
        p.id, 
        p.name, 
        p.price, 
        p.category_id, 
        s.sale_date, 
        s.quantity,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rn
    FROM 
        products p
    JOIN 
        sales_records s ON p.id = s.product_id
)
SELECT 
    id, 
    name, 
    price, 
    category_id, 
    sale_date, 
    quantity
FROM 
    RankedProducts
WHERE 
    rn = 1;

查询执行计划分析

1. JOIN操作：products表和sales_records表通过p.id = s.product_id进行连接。这一步会遍历两个表的数据，将匹配的行连接起来。
2. 窗口函数：ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) 为每个类别内的商品按价格降序分配一个行号。这一步需要对连接后的结果集进行排序操作。
3. 过滤：WHERE rn = 1 从排序后的结果中筛选出每个类别中价格最高的商品。

索引优化

1. 在products表的category_id和price字段上创建联合索引：

   CREATE INDEX idx_products_category_price ON products (category_id, price);
   

   这样在窗口函数按category_id分区并按price排序时，可以利用索引快速定位和排序数据，减少全表扫描。
2. 在sales_records表的product_id字段上创建索引：

   CREATE INDEX idx_sales_product_id ON sales_records (product_id);
   

   可以加速products表和sales_records表的连接操作，因为连接条件是基于product_id。通过索引，数据库可以快速定位sales_records表中与products表匹配的行，而无需全表扫描。