MySQL 联合性能问题

Question

我有 3 个具有相同列的表(SuccessOrder、FailedOrder 和 PendingOrder)。每个表都有超过 200 万条记录。我需要合并这 3 个表中的所有数据并按 CreatedDate 对它们进行排序以显示在我的门户中。我正在使用 UNION ALL 合并所有表的结果。

如果我执行每个子查询，在 1 或 2 秒内得到结果。如果我执行整个查询(使用 UNION ALL 的 3 个子查询)，则需要超过 5 分钟。

select * from (
select * from SuccessOrder
UNION ALL
select * from FailedOrder
UNION ALL
select * from PendingOrder
) t order by t.ID;

有没有 UNION ALL 的替代方案？

是否可以在没有 UNION ALL 的情况下从 3 个查询创建 View ？

以下是从工作台测试的个人和联合查询。我看不出 UNION ALL 和 UNION ALL - ORDER BY

有什么区别

第一次查询

持续时间/获取时间:2.182 秒/1.513 秒

SELECT  col1, col2, ...
    FROM  CompleteTxn ct
    left outer join  CompleteItem ci  ON (ct.Id = ci.TxnId)
    left outer join  ItemDispute id  ON (ct.Id = id.TxnId and  ci.Id = id.ItemId )
    left outer join  Merchant mc  ON (ct.MerchantId = mc.Id)
    left outer join  AdditionalTxnInfo addti  ON (ct.Id = addti.TxnId)
    where  (ct.PartitionKey>=55 AND  ct.PartitionKey<=56 )
      and  ( ct.TxnCompleteTime >= '2016-08-01 17:00:00'
        and  ct.TxnCompleteTime <= '2016-08-09 17:00:00'
           )
      and  ct.MnoId in (22,24,25,23,26,220,221,200,223,224,320, 400)
      and  ct.Status in (1,2,3,4,5);

第二个查询

持续时间/获取时间:0.279 秒/0.861 秒

SELECT  col1, col2, ...
    FROM  FailedOrder ct
    left outer join  FailedItem ci  ON (ct.Id = ci.TxnId)
    left outer join  ItemDispute id  ON (ct.Id = id.TxnId and  ci.Id = id.ItemId )
    left outer join  Merchant mc  ON (ct.MerchantId = mc.Id)
    left outer join  AdditionalTxnInfo addti  ON (ct.Id = addti.TxnId)
    where  (ct.PartitionKey>=55 AND  ct.PartitionKey<=56 )
      and  ( ct.TxnCompleteTime >= '2016-08-01 17:00:00'
        and  ct.TxnCompleteTime <= '2016-08-09 17:00:00'
           )
      and  ct.MnoId in (22,24,25,23,26,220,221,200,223,224,320, 400);

按我们的顺序联合所有

持续时间/获取时间:104.802 秒/0.00027 秒

select  *
    FROM  
    (
        SELECT  col1, col2, ...
            FROM  FailedOrder ct
            left outer join  FailedItem ci  ON (ct.Id = ci.TxnId)
            left outer join  ItemDispute id  ON (ct.Id = id.TxnId
                      and  ci.Id = id.ItemId 
                          )
            left outer join  Merchant mc  ON (ct.MerchantId = mc.Id)
            left outer join  AdditionalTxnInfo addti  ON (ct.Id = addti.TxnId)
    where  (ct.PartitionKey>=55 AND  ct.PartitionKey<=56 )
      and  ( ct.TxnCompleteTime >= '2016-08-01 17:00:00'
        and  ct.TxnCompleteTime <= '2016-08-09 17:00:00'
           )
      and  ct.MnoId in (22,24,25,23,26,220,221,200,223,224,320, 400)
              and  ct.Status in (1,2,3,4,5)
    UNION  ALL 
        SELECT  col1, col2, ...
            FROM  CompleteTxn ct
            left outer join  CompleteItem ci  ON (ct.Id = ci.TxnId)
            left outer join  ItemDispute id  ON (ct.Id = id.TxnId
                      and  ci.Id = id.ItemId
                          )
            left outer join  Merchant mc  ON (ct.MerchantId = mc.Id)
            left outer join  AdditionalTxnInfo addti  ON (ct.Id = addti.TxnId)
    where  (ct.PartitionKey>=55 AND  ct.PartitionKey<=56 )
      and  ( ct.TxnCompleteTime >= '2016-08-01 17:00:00'
        and  ct.TxnCompleteTime <= '2016-08-09 17:00:00'
           )
      and  ct.MnoId in (22,24,25,23,26,220,221,200,223,224,320, 400)        ) t ;

Union All with order by

持续时间/获取时间:104.895 秒/0.00028 秒

选择 * 从 (

SELECT col1, col2, ... FROM FailedOrder ct left outer join FailedItem ci ON (ct.Id = ci.TxnId) left outer join ItemDispute id ON (ct.Id = id.TxnId and ci.Id = id. ItemId) left outer join Merchant mc ON (ct.MerchantId = mc.Id) left outer join AdditionalTxnInfo addti ON (ct.Id = addti.TxnId) 其中 (ct.PartitionKey>=55 AND ct.PartitionKey<=56) and ( ct.TxnCompleteTime >= '2016-08-01 17:00:00' and ct.TxnCompleteTime <= '2016-08-09 17:00:00') and ct.MnoId in (22,24,25,23, 26,220,221,200,223,224,320,400) 和 ct.Status in (1,2,3,4,5)

联合所有

SELECT col1, col2, ... FROM CompleteTxn ct left outer join CompleteItem ci ON (ct.Id = ci.TxnId) left outer join ItemDispute id ON (ct.Id = id.TxnId and ci.Id = id. ItemId) left outer join Merchant mc ON (ct.MerchantId = mc.Id) left outer join AdditionalTxnInfo addti ON (ct.Id = addti.TxnId) 其中 (ct.PartitionKey>=55 AND ct.PartitionKey<=56) and ( ct.TxnCompleteTime >= '2016-08-01 17:00:00' and ct.TxnCompleteTime <= '2016-08-09 17:00:00') and ct.MnoId in (22,24,25,23, 26,220,221,200,223,224,320,400)

) t ORDER BY id desc;

Answer 1

根据我的假设，您正在处理近 600 万条记录的庞大数据。

1)与 Union 相比，Union All 快得多

2) 您正在将所有选择语句放入派生表中。

3)您再次对 ID 执行 ORDER BY(性能问题)

如果您对大数据进行排序，将会对性能产生巨大影响。

所有结果将按顺序排序，因此肯定会增加排序成本百分比

select Col1,Col2 from SuccessOrder
UNION ALL
select Col1,Col2  from FailedOrder
UNION ALL
select Col1,Col2  from PendingOrder