优化解析 5000 万行 MySQL 表的请求

我看到了几个加快此查询速度的机会.(参考:马库斯·维南德的https://use-the-index-luke.com/本Electron 书.)

1. 将您的correlated subquery(SELECT MAX(bill2.billing_date)...)替换为独立子查询.
2. try 使您的所有WHERE条语句都达到sargable--能够利用索引.
3. 添加适当的索引.

Independent subquery获取每个订户/订阅的最新账单日期，如下所示.该查询只需要运行一次，而您拥有的相关子查询运行多次.

      SELECT MAX(billing_date) billing_date,
             subscriber_id,
             subscription_id
        FROM billing
       WHERE billing_value = 'not_ok bizao_tobe'
       GROUP BY subscriber_id, subscription_id

使用此索引可以提速子查询.该索引允许子查询满足几乎奇迹般地快到loose index scan.

CREATE INDEX value_subscriber_subscription_date ON billing
   (billing_value, subscriber_id, subscription_id, billing_date DESC); 

然后像这样重写整个查询以使用它.我还在这里重写了一些其他内容以提高可读性:主要是将col = a OR col = b OR col = c更改为col IN (a,b,c).我还更改了一些WHERE子句的顺序，同样是为了可读性.WHERE子句的顺序对性能并不重要.

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

Sargability您按一天中的小时数对用户群进行划分意味着您需要这个条款，正如您所指出的.

AND (HOUR(sub.created_at) >  1 AND HOUR(sub.created_at) < 5 )

该子句将hr()函数应用于每个符合条件的行，因此它必须扫描所有行.慢的.将一个称为created_hour的virtual column加到您的subscription表中.我们马上就会为这一栏建立索引.

ALTER TABLE subscription 
     ADD COLUMN created_hour TINYINT 
     GENERATED ALWAYS AS (HOUR(created_at)) VIRTUAL;

然后开始使用虚拟列来划分您的用户.

AND (sub.created_hour >  1 AND sub.created_hour < 5)

Indexes个复合(多列)索引是加速像您这样的复杂查询的方法.列的顺序在索引中很重要.具有相等匹配的列首先进入，然后是具有范围匹配的列.

首先，让我们在subscription表上放置一个符合查询要求的复合索引.在此过程中，我们将为我们的新虚拟专栏建立索引.这使查询规划器可以高效地找到您的批次.

该索引中的最后一列是created_at.这会加快你的ORDER BY ... LIMIT操作速度.

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

接下来，让我们看看如何在主查询中使用billing.(我们已经添加了一个索引来帮助进行子查询).在billing_value上匹配相等，然后在billing_date上按日期范围匹配.所以你需要这个索引.

CREATE INDEX value_date ON billing (billing_value, billing_date);

您已经在subscriber上拥有了所需的指数.