如何优化MariaDB中SUM()聚合查询？提速Top3畅销品统计

我需要获取销量Top3的产品，当前在MariaDB中执行以下SQL查询：

SELECT
     SUM(quantity) as quantity
    ,productId
FROM krakoweats.orderproducts
GROUP BY productId
ORDER BY quantity DESC
LIMIT 3;

该表结构如下：

Columns:
orderId int(11) PK 
productId int(11) PK 
quantity int(11) 
unityPrice double 
createdAt datetime 
updatedAt datetime

针对包含约1100万条记录的krakoweats.orderproducts表，该查询执行耗时约9秒，希望找到提速方法。已有的索引信息如下：

+---------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| orderproducts |          0 | order_products_order_id_product_id |            1 | orderId     | A         |       55747 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| orderproducts |          0 | order_products_order_id_product_id |            2 | productId   | A         |      579777 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| orderproducts |          1 | order_products_product_id          |            1 | productId   | A         |       31254 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+-------

• 创建覆盖索引：现有索引仅包含productId，查询时需要回表读取quantity字段，IO开销大。创建包含productId和quantity的联合覆盖索引，让数据库直接从索引完成分组求和，无需访问主表数据：

  CREATE INDEX idx_product_quantity ON krakoweats.orderproducts (productId, quantity);
  

  索引生效后，查询的IO操作会大幅减少，直接降低耗时。

• 验证索引生效：创建索引后，用EXPLAIN查看执行计划，确认是否使用了新索引：

  EXPLAIN SELECT SUM(quantity) as quantity, productId FROM krakoweats.orderproducts GROUP BY productId ORDER BY quantity DESC LIMIT 3;
  

  若执行计划中type列显示range或ref，Extra列显示Using index，则说明索引已正常工作。

• 预计算统计结果：如果对销量数据的实时性要求不高，可定时统计销量并存储到单独的统计表中，查询时直接读取结果：

  -- 创建统计表
  CREATE TABLE product_sales_stats (
      productId int(11) PRIMARY KEY,
      total_quantity int(11) NOT NULL
  );
  
  -- 初始化统计数据
  INSERT INTO product_sales_stats (productId, total_quantity)
  SELECT productId, SUM(quantity) FROM krakoweats.orderproducts GROUP BY productId;
  
  -- 定时更新（比如每天凌晨执行）
  REPLACE INTO product_sales_stats (productId, total_quantity)
  SELECT productId, SUM(quantity) FROM krakoweats.orderproducts GROUP BY productId;
  
  -- 查询Top3销量产品
  SELECT productId, total_quantity FROM product_sales_stats ORDER BY total_quantity DESC LIMIT 3;
  

  这种方式的查询速度基本是毫秒级，适合非实时场景。

• 简化排序逻辑：如果排序是瓶颈，可先完成分组求和，再对结果集排序取Top3，减少排序的数据量（覆盖索引生效后此优化效果有限，但可作为补充）：

  SELECT * FROM (
      SELECT SUM(quantity) as quantity, productId
      FROM krakoweats.orderproducts
      GROUP BY productId
  ) t
  ORDER BY quantity DESC LIMIT 3;
  

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