如何优化耗时超10秒的大型SQL查询？

问题背景 #

现有一条CPU占用高、执行耗时超10秒的SQL查询，pos_transactions和club_transactions各约100万行数据，其中group_wallets_transactions是视图，单独执行该视图耗时约6秒。

原查询语句 #

select 
  sum(t.total) payment_amount, 
  sum(IFNULL(t.credit, group_wallets_transactions.credit)) cashbacked_amount, 
  case when group_wallets_transactions.type = "daily_cashback"
         or group_wallets_transactions.type = "payment_consume" then "cashback"
       else group_wallets_transactions.type end type, 
  t.business_id as b_id, 
  business_sub_categories.name sub_category, 
  business_sub_categories.selected_img, 
  group_wallets_transactions.created_at as date, 
  businesses.shop_name 
from 
  `group_wallets_transactions` 
  left join (
    (
      select 
        'cash' as pay_type, 
        sum(amount) as total, 
        round(sum(daily_percentage * amount / 100), 2) as credit, 
        DATE_ADD(Date(created_at), INTERVAL 1 DAY) as created_at, 
        `user_id`, 
        `business_id` 
      from 
        `pos_transactions` 
      where 
        (`user_id` = 408670) 
        and `rewarding_model` = 'cash' 
      group by 
        DATE_ADD(Date(created_at), INTERVAL 1 DAY), 
        `user_id`, 
        `business_id`
    ) 
    union all 
    (
      select 
        'credit' as pay_type, 
        sum(amount) as total, 
        round(sum(daily_percentage * amount / 100), 2) as credit, 
        DATE_ADD(Date(created_at), INTERVAL 1 DAY) as created_at, 
        `user_id`, 
        `business_id` 
      from 
        `club_transactions` 
      where 
        (`user_id` = 408670) 
        and `customer_cashback_status` = 1 
        and `club_transactions`.`had_payment_reward` = 1 
        and `club_transactions`.`rewarding_model` = 'cash' 
        and `status` = 'paid' 
      group by 
        DATE_ADD(Date(created_at), INTERVAL 1 DAY), 
        `user_id`, 
        `business_id`
    )
  ) as `t` on date(group_wallets_transactions.created_at) = date(t.created_at) 
  and `group_wallets_transactions`.`type` in ('daily_cashback') 
  left join `businesses` on `businesses`.`id` = `t`.`business_id` 
  left join `business_sub_categories` on `businesses`.`sub_category_id` = `business_sub_categories`.`id` 
where 
  (`group_wallets_transactions`.`user_id` = 408670) 
group by 
  `type`, 
  `business_id`, 
  `sub_category`, 
  `selected_img`, 
  `date`, 
  `shop_name` 
order by 
  `group_wallets_transactions`.`created_at` desc

group_wallets_transactions视图定义 #

CREATE ALGORITHM = UNDEFINED DEFINER = `administrator` @`%` SQL SECURITY DEFINER VIEW `group_wallets_transactions` AS 
select 
  `wallets_transactions`.`user_id` AS `user_id`, 
  `wallets_transactions`.`type` AS `TYPE`, 
  cast(`wallets_transactions`.`created_at` as date) AS `created_at`, 
  sum(`wallets_transactions`.`credit`) AS `credit` 
from 
  `wallets_transactions` 
group by 
  `wallets_transactions`.`user_id`, 
  `wallets_transactions`.`type`, 
  cast(`wallets_transactions`.`created_at` as date)

优化方案 #

1. 重构视图，消除运行时分组键计算 #

视图中用cast(created_at as date)作为分组键，会导致无法利用created_at上的索引，直接触发全表扫描：

• 给wallets_transactions表新增计算列created_date（DATE类型），默认值设为DATE(created_at)，并创建复合索引(user_id, type, created_date, credit)
• 修改视图定义，直接使用预计算的created_date：

CREATE VIEW `group_wallets_transactions` AS 
select 
  `wallets_transactions`.`user_id` AS `user_id`, 
  `wallets_transactions`.`type` AS `TYPE`, 
  `wallets_transactions`.`created_date` AS `created_at`, 
  sum(`wallets_transactions`.`credit`) AS `credit` 
from 
  `wallets_transactions` 
group by 
  `user_id`, 
  `type`, 
  `created_date`

这样视图查询可以直接命中复合索引，大幅减少扫描数据量。

2. 优化子查询索引与分组逻辑 #

子查询中DATE_ADD(Date(created_at), INTERVAL 1 DAY)的分组方式同样无法利用索引，针对性调整：

• 给pos_transactions创建复合索引：(user_id, rewarding_model, created_at, amount, daily_percentage, business_id)
• 给club_transactions创建复合索引：(user_id, rewarding_model, status, customer_cashback_status, had_payment_reward, created_at, amount, daily_percentage, business_id)
• 子查询的分组键可以简化为DATE(created_at) + INTERVAL 1 DAY，利用索引前缀快速过滤用户数据后再分组，避免全表扫描。

3. 简化连接条件，移除日期转换函数 #

原连接条件date(group_wallets_transactions.created_at) = date(t.created_at)存在冗余转换：

• 视图重构后created_at是DATE类型，子查询的created_at也是DATE类型（DATE_ADD(Date(...), 1 DAY)结果为DATE），直接改为group_wallets_transactions.created_at = t.created_at，消除函数运算开销。

4. 提前过滤数据，缩小连接范围 #

将group_wallets_transactions.type in ('daily_cashback')从连接条件移至主查询WHERE子句，提前过滤无关数据：

where 
  `group_wallets_transactions`.`user_id` = 408670
  and `group_wallets_transactions`.`type` = 'daily_cashback'

减少后续连接、分组的数据量。

5. 优化分组与排序效率 #

主查询分组列较多，确保分组列（尤其是business_id、created_at）有索引支持；排序用group_wallets_transactions.created_at desc，利用视图的created_date索引直接排序，避免文件排序。

6. 考虑物化视图或预处理数据 #

如果业务允许非实时数据，可以用MySQL 8.0+的物化视图定期刷新group_wallets_transactions的结果，或者将子查询的统计结果预处理到临时表，再进行连接查询，避免重复计算。

7. 验证执行计划，调优索引 #

重新运行EXPLAIN检查执行计划，确保所有表都命中预期索引，若存在全表扫描，调整索引前缀顺序，优先匹配过滤条件、分组条件。

内容的提问来源于stack exchange，提问作者Martin AJ