代码之家 › 专栏 › 技术社区 › aSystemOverload

子查询延迟更新

mysql-dependent-subquery indexing mysql

aSystemOverload · 技术社区 · 7 年前

这个查询引用了大约300万条记录,现在需要一个绝对的运行时间。数据取自excel电子表格,其中cust/invoices在下一行,2016年的月值在列中显示为当前值。

此查询检查同一个月内是否有相同/不同产品的值,如果可以忽略,则输出1;如果后续查询应考虑该值,则输出0。

我为product设置了一个索引,它负责处理初始条件,但绝对是子查询杀死了这一点:

    UPDATE tbl_transactions a
    SET ProdInCust_Mnth_Same_SameProd_LowerVal =
        CASE WHEN
                (
                    SELECT COUNT(TransactionID) 
                    FROM tbl_transactions_tmp b 
                    WHERE 
                        b.TransactionID<>a.TransactionID AND
                        b.CustomerRef=a.CustomerRef AND
                        b.TransMonth=a.TransMonth AND
                        (
                            (
                                (b.Product='PLATINUM') AND
                                b.TransValue<0
                            )
                            OR                                  
                            (
                                a.TransValue=0 AND
                                (b.Product='PLATINUM' OR b.Product='GOLD' OR b.Product='SILVER') AND
                                b.TransValue<0
                            )
                            OR
                            (
                                a.TransValue<0 AND
                                (b.Product='PLATINUM' OR b.Product='GOLD') AND
                                ((b.TransValue=a.TransValue AND b.RowReference>a.RowReference) OR
                                    b.TransValue<a.TransValue
                                )
                            )
                        )
                )>0 THEN 1 ELSE 0 END   
    WHERE Product='GOLD';

解释产生:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   UPDATE  a   \N  index   IDX_tbl_transactions_Product    PRIMARY 8   \N  2828152 100 Using where
2   DEPENDENT SUBQUERY  b   \N  ref IX_Transactions_SP_ProcessTransAA   IX_Transactions_SP_ProcessTransAA   45  finance.a.CustomerRef,finance.a.TransMonth  1   20.7    Using where; Using index

从视觉上看,这是一个全索引扫描,我推测红色背景表明这是不好的。

任何我可以进一步优化的想法。

3 回复 | 直到 7 年前

Alexey 7 年前

尝试将子查询的where子句中使用的所有字段的索引添加到两个表中,并使用 EXISTS 而不是 COUNT :

UPDATE tbl_transactions a
SET ProdInCust_Mnth_Same_SameProd_LowerVal =
    CASE WHEN EXISTS
            (
                SELECT TransactionID
                FROM tbl_transactions_tmp b 
                WHERE 
                    b.TransactionID<>a.TransactionID AND
                    b.CustomerRef=a.CustomerRef AND
                    b.TransMonth=a.TransMonth AND
                    (
                        (
                            (b.Product='PLATINUM') AND
                            b.TransValue<0
                        )
                        OR                                  
                        (
                            a.TransValue=0 AND
                            (b.Product='PLATINUM' OR b.Product='GOLD' OR b.Product='SILVER') AND
                            b.TransValue<0
                        )
                        OR
                        (
                            a.TransValue<0 AND
                            (b.Product='PLATINUM' OR b.Product='GOLD') AND
                            ((b.TransValue=a.TransValue AND b.RowReference>a.RowReference) OR
                                b.TransValue<a.TransValue
                            )
                        )
                    )
            ) THEN 1 ELSE 0 END   
WHERE Product='GOLD';

裁判: Is EXISTS more efficient than COUNT(*)>0?

Bharati Mathapati 7 年前

你能试试下面的吗- 使用cte或temp表并使用case和where评估预期结果。在更新put appropriate where clasus时使用此表值。希望这可以帮助您创建查询。查询可能不会给出确切的结果,但可以帮助您创建查询。

UPDATE  a
    SET ProdInCust_Mnth_Same_SameProd_LowerVal = c.val
    tbl_transactions a
    JOIN cte c on a.TransactionID = c.TransactionID        
    --WHERE Product='GOLD';

WITH cte AS
(
SELECT b.TransactionID,  b.CustomerRef,b.TransMonth,b.TransValue, COUNT(TransactionID) ,
    case when  COUNT(TransactionID) > 0 then 1 else 0 END as val
                    FROM tbl_transactions_tmp b 
                    WHERE 
                       -- b.TransactionID<>a.TransactionID AND
                       -- b.CustomerRef=a.CustomerRef AND
                        b.TransMonth=a.TransMonth AND
                        (
                            (
                                (b.Product='PLATINUM') AND  b.TransValue<0
                            )
                            OR                                  
                            (
                                b.TransValue=0 AND
                                (b.Product='PLATINUM' OR b.Product='GOLD' OR b.Product='SILVER') AND
                                b.TransValue<0
                            )
                            OR
                            (
                                b.TransValue<0 AND
                                (b.Product='PLATINUM' OR b.Product='GOLD') AND
                                ((b.TransValue=a.TransValue AND b.RowReference>a.RowReference) OR
                                    b.TransValue<a.TransValue
                                )
                            )
                        )
                )
                group by b.CustomerRef
)

Rick James diyism 7 年前

你有这个综合指数吗?或者更广泛的指数启动有这两列吗?

INDEX(CustomerRef, TransMonth)   -- in either order

上个月是否可以每月计算一次信息或部分信息?将其存储在摘要表中,然后查询该表可能会快得多。

你知不知道你是否是I/O绑定的?如果您是I/O绑定的,那么 innodb_buffer_pool_size ,您有多少内存,这个表有多大(GB)?