系统里这个同时查冷热表的sql，动动手指，从12s降到3s

 系统将交易数据按交易时间分为热表（最近3个月）和冷表（3个月前）。为保证用户体验，当企业门户端查询跨越冷热表时，尤其针对大客户，查询性能优化至关重要。以下是程序的SQL查询语句及其优化版本。

 

系统里的交易数据按交易时间做了冷热表分离（热表仅存储最近3个月的交易数据，3个月前的交易数据自动结转至冷表），我们内部运营系统的交易查询功能进行了冷热数据分开查询。

然后企业客户端呢，为了不影响用户体验，企业门户端的交易查询功能，当选择的查询时间段同时涉及到冷热表时，需要union（合并）两表进行数据查询。这时，尤其是针对那些交易量比较大的客户来说，在查询性能上我们就要做一些努力。

上sql

select
  count(*) as orderNum,
  IFNULL(sum(amount), 0) as totalAmt,
  SUM(CASE WHEN order_status = 'SUCCESS' THEN amount ELSE 0 END) as totalSuccessAmt,
  SUM(CASE WHEN order_status = 'FAIL' THEN amount ELSE 0 END) as totalFailAmt
from
  (
    select
      *
    from
      order_detail
    WHERE
      enterprise_id = 1655100723787649
      and create_time >= '2024-02-04 00:00:00'
      and create_time <= '2024-09-20'
    UNION
    select
      *
    from
      order_detail_mig
    WHERE
      enterprise_id = 1655100723787649
      and create_time >= '2024-02-04 00:00:00'
      and create_time <= '2024-09-20'
  ) od

 

### 执行计划及耗时

本文sql查询方式：本地通过堡垒机访问生产库，执行耗时15s。（生产log实际耗时≈12s）

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	(null)	ALL	(null)	(null)	(null)	(null)	403059	100	(null)
2	DERIVED	order_detail	(null)	ref	uk_enterprise_order_no,idx_create_time,idx_create_time_payee_account_enterprise_id	uk_enterprise_order_no	8	const	376527	50	Using where
3	UNION	order_detail_mig	(null)	ref	uk_enterprise_order_no,idx_create_time	uk_enterprise_order_no	8	const	429592	50	Using where
4	UNION RESULT	<union2,3>	(null)	ALL	(null)	(null)	(null)	(null)	(null)	(null)	Using temporary

 

sql优化后v1

先把`UNION`换成`UNION ALL`

### 执行计划及耗时

 

sql优化后v2

select
    count(*) as orderNum,
    sum(amount) as totalAmt,
    order_status
from
  order_detail
WHERE
  enterprise_id = 1655100723787649
  and create_time >= '2024-02-04 00:00:00'
  and create_time <= '2024-09-20'
group by order_status
UNION ALL
select
    count(*) as orderNum,
    sum(amount) as totalAmt,
    order_status
from
  order_detail_mig
WHERE
  enterprise_id = 1655100723787649
  and create_time >= '2024-02-04 00:00:00'
  and create_time <= '2024-09-20'
group by order_status

 

 

 

### 执行计划及耗时