然后企业客户端呢,为了不影响用户体验,企业门户端的交易查询功能,当选择的查询时间段同时涉及到冷热表时,需要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
### 执行计划及耗时
添加评论