顾问头条

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

2024-09-26 21:18 由 buguge 发表于 #数据库

 系统将交易数据按交易时间分为热表（最近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

 

 

 

### 执行计划及耗时