mysql - myquery 在 mysql 中花费的时间太长

Question

我的 transactionDetail 表有 3 亿条数据。为我的查询获取数据需要很长时间。

下面是我的查询

 select 
 MerchantId as y_m,BoothId ,
 TransactionTypeId , 
 count(Amount) , 
 sum(Amount)  
 from TransactionDetail  
 where TransactionDate>='2014-02-26' 
 and TransactionDate<'2019-02-27' 
 and not (BoothId like 'TEST%')  
 and MerchantId in (select MerchantId from MerchantGroup where  MerchantClassId='MD-SAFAL') 
 group by MerchantId, BoothId, TransactionTypeId 
 order by y_m asc, BoothId asc, TransactionTypeId asc;

TransactionDetail 表具有以下键和索引

• 主键(TransactionId)，

索引如下

• KEY idxTransactionDetail003 (MerchantId),

• key idxTransactionDetail004(交易日期)

MerchantGroup 表在 MerchantId 列上有索引

Answer 1

您可以在 TransactionDetail 列(MerchantId、TransactionDate、BoothId)上添加复合索引

并且您可以使用内部联接而不是 IN 子句

并使用 not like 而不是 not ( like )

    select d.MerchantId as y_m
    ,d.BoothId 
    ,d.TransactionTypeId 
    , count(d.Amount) , sum(d.Amount)  
    from TransactionDetail d
    INNER JOIN (
        select MerchantId 
        from MerchantGroup 
        where MerchantClassId='MD-SAFAL'
    ) t t.MerchantId = d.MerchantId
    where d.TransactionDate>='2014-02-26' 
        and d.TransactionDate<'2019-02-27' 
    and d.BoothId not like 'TEST%'  
    group by d.MerchantId, d.BoothId, d.TransactionTypeId 
    order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;