系统里的交易数据按交易时间做了冷热表分离（热表仅存储最近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
### 执行计划及耗时
