MySQL 8.0.26大商品量查询慢：分区是否可行及优化方案咨询

环境与表结构 #

使用MySQL 8.0.26，涉及以下核心表结构：

• product表：id(int)、create_at、status、group_id、pics、is_new、lost_sale
• product_group表：id、brand_id、seller_id、name
• product_log表：create_at、product_id、price、price_discount、rating、comment、order_count、sales_count、quantity
• category_product表：id、category_id、product_id

业务逻辑与问题 #

当前业务需展示关联卖家、分类、品牌、搜索词的商品：

1. 按营收、评分、名称、销量等字段排序，选取前100个商品并计算详情
2. 翻页时通过偏移量查询下100条
3. 当前统计30天数据，计划扩展至90天，但当商品量超过9万时，出现查询超时或内存耗尽的情况

咨询问题 #

• 分区能否解决该问题？
• 还有哪些负载优化方案？

查询示例 #

按营收排序取100条商品 #

WITH temp_products AS (SELECT `p`.`id`        AS `id`,
                              `p`.`create_at` AS `create_at`,
                              `p`.`lost_sale` AS `revenue_lost`,
                              `pg`.`name`     AS `name`,
                              `b`.`id`        AS `brand_id`,
                              `b`.`name`      AS `brand_name`,
                              `s`.`id`        AS `seller_id`,
                              `s`.`name`      AS `salesman_name`,
                              `s`.`ogrn`      AS `salesman_ogrn`,
                              `s`.`trademark` AS `salesman_trademark`
                       FROM `product` `p`
                            LEFT JOIN `product_group` `pg` ON p.group_id = pg.id
                            LEFT JOIN `seller` `s` ON pg.seller_id = s.id
                            LEFT JOIN `brand` `b` ON pg.brand_id = b.id
                       WHERE `pg`.`seller_id` = '414336'),
 base AS (SELECT `p`.`id`, `pl`.`create_at`, (pl.price_discount * pl.order_count) AS `revenue`
          FROM `temp_products` `p`
                   join `product_log` `pl` ON p.id = pl.product_id
          WHERE `pl`.`create_at` BETWEEN 1659294000 AND 1661886000),
 summary AS (SELECT `id`, sum(revenue) AS `revenue` FROM `base` GROUP BY `id`)
SELECT `id`, `revenue`
FROM `summary`
ORDER BY `revenue` DESC
LIMIT 100

100条商品详情统计查询 #

WITH products AS (SELECT `p`.`id`        AS `id`,
                         `p`.`create_at` AS `create_at`,
                         `p`.`lost_sale` AS `revenue_lost`,
                         `pg`.`name`     AS `name`,
                         `b`.`id`        AS `brand_id`,
                         `b`.`name`      AS `brand_name`,
                         `s`.`id`        AS `seller_id`,
                         `s`.`name`      AS `salesman_name`,
                         `s`.`ogrn`      AS `salesman_ogrn`,
                         `s`.`trademark` AS `salesman_trademark`
                  FROM `product` `p`
                       LEFT JOIN `product_group` `pg` ON p.group_id = pg.id
                       LEFT JOIN `seller` `s` ON pg.seller_id = s.id
                       LEFT JOIN `brand` `b` ON pg.brand_id = b.id
                  WHERE `pg`.`seller_id` = '414336'),
 logs AS (SELECT `pl`.`product_id` AS `product_id`,
                 `pl`.`create_at` AS `create_at`,
                 `pl`.`price` AS `price`,
                 `pl`.`price_discount` AS `price_discount`,
                 `pl`.`rating` AS `rating`,
                 `pl`.`comment` AS `comment`,
                 `pl`.`order_count` AS `order_count`,
                 `pl`.`refund_count` AS `refund_count`,
                 `pl`.`quantity` AS `quantity`,
                 (pl.order_count * pl.price_discount) AS `revenue`,
                 row_number() over (partition by product_id order by create_at desc) AS `day`
          FROM `product_log` `pl`
          WHERE (pl.product_id in (select id from products))
            AND (`pl`.`create_at` BETWEEN 1656702000 AND 1661886000))
 SELECT `p`.`id` AS `id`,
        `p`.`id` AS `sku`,
        concat('https://www.wildberries.ru/catalog/', p.id, '/detail.aspx') AS `url`,
        `p`.`create_at` AS `create_at`,
        `p`.`name` AS `name`,
        coalesce(p.brand_id, '') AS `brand_id`,
        coalesce(p.brand_name, '') AS `brand_name`,
        coalesce(p.seller_id, '') AS `salesman_id`,
        coalesce(p.salesman_name, '') AS `salesman_name`,
        coalesce(p.salesman_ogrn, '') AS `salesman_ogrn`,
        coalesce(p.salesman_trademark, '') AS `salesman_trademark`,
        `p`.`revenue_lost` AS `revenue_lost`,
        (select price_discount from logs where product_id = p.id and day = 1) AS `price`,
        (select round(avg(rating)) 
         from logs 
         where product_id = p.id 
           and rating > 0 
           and create_at between 1659294000 and 1661886000) AS `rating`,
        (select comment 
         from logs 
         where product_id = p.id 
           and day = 1) AS `review_count`,
        (select quantity 
         from logs 
         where product_id = p.id 
         and day = 1) AS `in_stock`,
        (select round(AVG(price_discount))
         from logs
         where product_id = p.id
           and create_at between 1659294000 and 1661886000) AS `price_average`,
        (select sum(order_count)
         from logs
         where product_id = p.id
           and create_at between 1659294000 and 1661886000) AS `order_value`,
        (select sum(order_count)
         from logs
         where product_id = p.id
           and create_at between 1656702000 and 1659294000) AS `order_prev`,
        (select sum(revenue)
         from logs
         where product_id = p.id
           and create_at between 1659294000 and 1661886000) AS `revenue_value`,
        (select sum(revenue)
         from logs
         where product_id = p.id
           and create_at between 1656702000 and 1659294000) AS `revenue_prev`,
        (SELECT GROUP_CONCAT(order_count)
         FROM (SELECT order_count 
               FROM logs 
               WHERE product_id = p.id 
               LIMIT 30) as order_chart
        ) AS `order_chart`,
        (select IF(COUNT(*) > 30, 30, COUNT(*)) AS c
         from logs
         where product_id = p.id
           and create_at between 1659294000 and 1661886000
           and quantity > 0) AS `was_on_sale_for_month`
 FROM `products` `p`
 ORDER BY `revenue_value` DESC

一、分区的作用与适用场景 #

分区能缓解部分压力，但不是万能解：

• 适合场景：针对product_log按create_at做范围分区（比如按月份/季度），查询90天数据时，MySQL只会扫描对应3个分区的数据，避免全表扫描，减少IO和内存占用。
• 局限性：如果查询涉及跨多个分区，或者排序/聚合需要扫描大量数据（比如按营收排序要计算全量商品的营收总和），分区无法解决排序和聚合的内存瓶颈。

二、核心优化方案 #

1. 索引优化 #

• product_log表：创建复合索引(product_id, create_at)，覆盖查询中用到的price_discount、order_count、rating、comment、quantity字段，即CREATE INDEX idx_pl_prod_create ON product_log(product_id, create_at) INCLUDE (price_discount, order_count, rating, comment, quantity);（MySQL 8.0.13+支持INCLUDE），避免回表查询。
• product_group表：针对seller_id创建索引，加速卖家商品的筛选：CREATE INDEX idx_pg_seller ON product_group(seller_id);
• 关联字段优化：确保product.group_id、product_group.id、product_group.seller_id、product_group.brand_id这些关联字段都有主键或普通索引。

2. 查询重构 #

• 替换嵌套子查询：详情查询中大量(select ... from logs where product_id = p.id)会导致每条商品执行一次子查询，改成JOIN+聚合的方式减少查询次数。比如用GROUP BY product_id预计算所有统计字段，再和products表关联。
• 合并CTE逻辑：将多个CTE合并，减少临时表的创建和数据重复扫描。比如第一个查询中，temp_products可直接和product_log关联后聚合，无需拆分多个CTE。
• 缩小数据范围：如果业务允许，优先筛选product.status（比如只查询在售商品），减少参与计算的商品数量。

3. 预聚合（统计表） #

• 针对业务需要的营收、销量、评分等统计指标，创建定时任务（比如每天凌晨）预计算并存储到统计表（如product_daily_stats），包含product_id、date、revenue、order_count、avg_rating等字段。查询时直接从统计表汇总，避免实时计算大量日志数据。
• 对于90天的统计，可以预计算每个商品的90天累计值，或按月份存储后查询时快速求和。

4. 分页优化 #

• 避免使用LIMIT offset, 100的偏移量分页：当偏移量很大时，MySQL需要扫描前N条数据后丢弃，性能极低。改成基于游标（keyset）的分页：以上一页最后一条的revenue和id作为条件，比如WHERE revenue < ? AND id < ? ORDER BY revenue DESC, id DESC LIMIT 100，利用索引直接定位到起始位置。

5. 内存与配置优化 #

• 调整MySQL配置：增大innodb_buffer_pool_size（建议设置为物理内存的50%-70%），让更多数据缓存到内存，减少磁盘IO；增大sort_buffer_size和join_buffer_size，优化排序和关联操作的内存使用。
• 拆分查询：先获取100个商品ID，再批量查询这些ID的详情统计，避免一次性处理大量数据导致内存耗尽。

6. 数据归档 #

• 对于超过90天的product_log数据，归档到历史表（如product_log_hist），保持主表数据量可控，查询时只扫描主表的90天数据，无需处理历史数据。

内容的提问来源于stack exchange，提问作者Александр Коваленко