优化5000万行MySQL billing表的查询请求

我有一个需在特定时段多次执行的查询，当前可用但随着用户量增长，数据库CPU负载和查询耗时持续上升。

原查询语句 #

SELECT bill.* FROM billing bill
            INNER JOIN subscriber s ON (s.subscriber_id = bill.subscriber_id) 
            INNER JOIN subscription sub ON(s.subscriber_id = sub.subscriber_id)
            WHERE s.status = 'C' 
            AND bill.subscription_id = sub.subscription_id                      
            AND sub.renewable = 1
            AND (hour(sub.created_at) > 1 AND hour(sub.created_at) < 5 )
            AND sub.store = 'BizaoStore'
            AND (sub.purchase_token = 'myservice' or sub.purchase_token = 'myservice_wait' ) 
            AND bill.billing_date > '2022-12-31 07:00:00' AND bill.billing_date < '2023-01-01 10:00:00'
            AND (bill.billing_value = 'not_ok bizao_tobe' or bill.billing_value =  'not_ok BILL010 2' or bill.billing_value =  'not_ok BILL010' or bill.billing_value = 'not_ok BILL010 3')
            AND (SELECT MAX(bill2.billing_date)
                FROM billing bill2
                WHERE bill2.subscriber_id = bill.subscriber_id
                AND bill2.subscription_id = bill.subscription_id 
                AND bill2.billing_value = 'not_ok bizao_tobe') 
            = bill.billing_date order by sub.created_at DESC LIMIT 300;

执行逻辑 #

• 两台服务器分别对应特定服务，每台每分钟执行8次，持续约3小时
• 每次执行时调整hour(sub.created_at)的区间（拆分用户群为8份提升效率）
• 因第三方服务器不稳定，每次仅处理300条结果

表信息 #

billing表（约5000万条记录） #

• 列：billing_id（主键）、subscriber_id、subscription_id、billing_date、billing_value等
• 现有索引：PRIMARY KEY (billing_id)，subscriber_id单列索引，subscription_id单列索引，billing_date单列索引

subscriber表（约200万条记录） #

• 列：subscriber_id（主键）、status等
• 现有索引：PRIMARY KEY (subscriber_id)，status单列索引

subscription表（约250万条记录） #

• 列：subscription_id（主键）、subscriber_id、created_at、renewable、store、purchase_token等
• 现有索引：PRIMARY KEY (subscription_id)，subscriber_id单列索引，created_at单列索引

已发现的优化线索 #

添加billing_id > 特定值条件后查询速度大幅提升，推测耗时主要来自遍历5000万行的billing表，当前使用MySQL 5.7.38。

优化建议 #

1. 重构行级子查询，避免重复计算 #

原查询中每行都执行一次MAX(billing_date)子查询，带来大量重复计算。改用子查询+JOIN提前计算每个subscriber_id + subscription_id的最大日期：

SELECT bill.* 
FROM billing bill
INNER JOIN (
    SELECT subscriber_id, subscription_id, MAX(billing_date) AS max_date
    FROM billing
    WHERE billing_value = 'not_ok bizao_tobe'
    GROUP BY subscriber_id, subscription_id
) lb ON bill.subscriber_id = lb.subscriber_id 
    AND bill.subscription_id = lb.subscription_id 
    AND bill.billing_date = lb.max_date
INNER JOIN subscriber s ON s.subscriber_id = bill.subscriber_id
INNER JOIN subscription sub 
    ON sub.subscriber_id = s.subscriber_id 
    AND bill.subscription_id = sub.subscription_id
WHERE s.status = 'C' 
    AND sub.renewable = 1
    -- 替换hour函数，让created_at索引生效
    AND sub.created_at >= CONCAT(DATE(sub.created_at), ' 02:00:00') 
    AND sub.created_at < CONCAT(DATE(sub.created_at), ' 05:00:00')
    AND sub.store = 'BizaoStore'
    -- 用IN替代OR，优化条件判断
    AND sub.purchase_token IN ('myservice', 'myservice_wait')
    AND bill.billing_date > '2022-12-31 07:00:00' 
    AND bill.billing_date < '2023-01-01 10:00:00'
    AND bill.billing_value IN ('not_ok bizao_tobe', 'not_ok BILL010 2', 'not_ok BILL010', 'not_ok BILL010 3')
ORDER BY sub.created_at DESC 
LIMIT 300;

2. 创建复合索引，消除索引失效与回表开销 #

• subscription表：针对过滤+排序条件创建复合索引，覆盖所有查询条件，避免排序和回表：

  CREATE INDEX idx_sub_store_token_renew_created ON subscription(store, purchase_token, renewable, created_at);
  

• billing表：针对关联+过滤条件创建复合索引，覆盖子查询和主查询的字段：

  -- 主查询用索引：覆盖关联字段+过滤字段
  CREATE INDEX idx_bill_sub_subs_date_value ON billing(subscriber_id, subscription_id, billing_date, billing_value);
  -- 子查询用索引：快速计算MAX(billing_date)
  CREATE INDEX idx_bill_sub_subs_value_date ON billing(subscriber_id, subscription_id, billing_value, billing_date);
  

3. 结合billing_id范围拆分，延续已验证的优化效果 #

既然billing_id > 特定值能大幅提速，可以将现有按小时拆分用户的逻辑，结合billing_id范围进一步分片，比如每次查询同时指定billing_id区间和sub.created_at小时区间，减少单次扫描的数据量：

-- 示例：同时指定billing_id范围和小时区间
SELECT bill.* 
FROM billing bill
INNER JOIN (
    SELECT subscriber_id, subscription_id, MAX(billing_date) AS max_date
    FROM billing
    WHERE billing_value = 'not_ok bizao_tobe'
    AND billing_id > 1000000 AND billing_id < 2000000 -- 添加billing_id范围
    GROUP BY subscriber_id, subscription_id
) lb ON bill.subscriber_id = lb.subscriber_id 
    AND bill.subscription_id = lb.subscription_id 
    AND bill.billing_date = lb.max_date
INNER JOIN subscriber s ON s.subscriber_id = bill.subscriber_id
INNER JOIN subscription sub 
    ON sub.subscriber_id = s.subscriber_id 
    AND bill.subscription_id = sub.subscription_id
WHERE s.status = 'C' 
    AND sub.renewable = 1
    AND sub.created_at >= CONCAT(DATE(sub.created_at), ' 02:00:00') 
    AND sub.created_at < CONCAT(DATE(sub.created_at), ' 05:00:00')
    AND sub.store = 'BizaoStore'
    AND sub.purchase_token IN ('myservice', 'myservice_wait')
    AND bill.billing_date > '2022-12-31 07:00:00' 
    AND bill.billing_date < '2023-01-01 10:00:00'
    AND bill.billing_value IN ('not_ok bizao_tobe', 'not_ok BILL010 2', 'not_ok BILL010', 'not_ok BILL010 3')
AND bill.billing_id > 1000000 AND bill.billing_id < 2000000 -- 添加billing_id范围
ORDER BY sub.created_at DESC 
LIMIT 300;

4. 调整执行顺序，先过滤小表再关联大表 #

先从subscription和subscriber小表过滤出目标用户，再关联billing大表，减少关联的数据量：

SELECT bill.*
FROM (
    -- 先过滤出符合条件的用户
    SELECT sub.subscriber_id, sub.subscription_id, sub.created_at
    FROM subscription sub
    INNER JOIN subscriber s ON sub.subscriber_id = s.subscriber_id
    WHERE s.status = 'C'
        AND sub.renewable = 1
        AND sub.created_at >= CONCAT(DATE(sub.created_at), ' 02:00:00') 
        AND sub.created_at < CONCAT(DATE(sub.created_at), ' 05:00:00')
        AND sub.store = 'BizaoStore'
        AND sub.purchase_token IN ('myservice', 'myservice_wait')
) filtered_users
INNER JOIN billing bill 
    ON filtered_users.subscriber_id = bill.subscriber_id 
    AND filtered_users.subscription_id = bill.subscription_id
INNER JOIN (
    SELECT subscriber_id, subscription_id, MAX(billing_date) AS max_date
    FROM billing
    WHERE billing_value = 'not_ok bizao_tobe'
    GROUP BY subscriber_id, subscription_id
) lb ON bill.subscriber_id = lb.subscriber_id 
    AND bill.subscription_id = lb.subscription_id 
    AND bill.billing_date = lb.max_date
WHERE bill.billing_date > '2022-12-31 07:00:00' 
    AND bill.billing_date < '2023-01-01 10:00:00'
    AND bill.billing_value IN ('not_ok bizao_tobe', 'not_ok BILL010 2', 'not_ok BILL010', 'not_ok BILL010 3')
ORDER BY filtered_users.created_at DESC 
LIMIT 300;

5. 缓存临时表，避免重复扫描大表 #

如果查询的时间范围固定（比如每日处理前一天数据），可以提前将符合条件的数据存入临时表，后续多次查询直接读取临时表：

-- 创建临时表存储过滤后的用户
CREATE TEMPORARY TABLE temp_target_users AS
SELECT sub.subscriber_id, sub.subscription_id, sub.created_at
FROM subscription sub
INNER JOIN subscriber s ON sub.subscriber_id = s.subscriber_id
WHERE s.status = 'C'
    AND sub.renewable = 1
    AND sub.store = 'BizaoStore'
    AND sub.purchase_token IN ('myservice', 'myservice_wait');

-- 创建临时表存储符合条件的账单最新记录
CREATE TEMPORARY TABLE temp_latest_bills AS
SELECT bill.*
FROM billing bill
INNER JOIN (
    SELECT subscriber_id, subscription_id, MAX(billing_date) AS max_date
    FROM billing
    WHERE billing_value = 'not_ok bizao_tobe'
        AND billing_date > '2022-12-31 07:00:00' 
        AND billing_date < '2023-01-01 10:00:00'
    GROUP BY subscriber_id, subscription_id
) lb ON bill.subscriber_id = lb.subscriber_id 
    AND bill.subscription_id = lb.subscription_id 
    AND bill.billing_date = lb.max_date
WHERE bill.billing_value IN ('not_ok bizao_tobe', 'not_ok BILL010 2', 'not_ok BILL010', 'not_ok BILL010 3');

-- 拆分小时区间查询临时表
SELECT tlb.*
FROM temp_latest_bills tlb
INNER JOIN temp_target_users ttu 
    ON tlb.subscriber_id = ttu.subscriber_id 
    AND tlb.subscription_id = ttu.subscription_id
WHERE ttu.created_at >= CONCAT(DATE(ttu.created_at), ' 02:00:00') 
    AND ttu.created_at < CONCAT(DATE(ttu.created_at), ' 05:00:00')
ORDER BY ttu.created_at DESC 
LIMIT 300;

内容的提问来源于stack exchange，提问作者And Row ID