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;

除了联合，还有别的选择吗？

是否可以从3个查询中创建视图而不使用UNION ALL？

以下是从Workbench测试的单个和联合查询。我看不出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 ;

使用order by联合All

持续时间/提取时间：104.895秒/0.00028秒

选择*FROM（

从FailedOrder ct left outer join FailedItem ci ON（ct.id=ci.TxNID）left outer join ItemPartition id ON（ct.id=id.TxNID和ci.id=id.ItemID）left outer join Merchant mc ON（ct.MerchanID=mc.id）left outer join AdditionalTxnInfo addti ON（ct.id=addti.TxNID）其中(ct.PartitionKey>=55和ct.PartitionKey<=56）和(ct.TxnCompleteTime>=

UNION ALL

从CompleteTxn ct左外部连接CompleteItem ci ON（ct.id=ci.TxNID）左外部连接ItemPartition id ON（ct.id=id.TxNID和ci.id=id.ItemID）左外部连接Merchant mc ON（ct.MerchanID=mc.id）左外部连接AdditionalTxnInfo addti ON（ct.id=addti.TxNID）其中(ct.PartitionKey>=55和ct.PartitionKey<=56）和(ct.TxnCompleteTime>=“2016-08-01 17:00:00”和ct.TxnCompleteTime<=“

）通过id描述测试订单；

Answer 1

您的Order By很可能是这里的罪魁祸首。您实际上是在select之后对整个数据库进行排序，并且取决于它的索引方式，这可能需要很长时间。

你可以：

• 确保表的索引是根据您的订购（重要）
• 全部删除顺序

我不认为union all的成本接近于对所有数据进行排序的成本。

Answer 2

根据我的假设，您正在处理大量的数据，几乎是600万条记录。

1）与Union相比，Union All的速度要快得多

2）将所有的select语句取到派生表中。

3）再次在ID上执行ORDER BY（性能问题）

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

所有的结果都是按顺序排序的，所以一定会增加排序成本百分比

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