不新增索引时如何优化MySQL查询？现有索引下方案咨询

问题背景 #

针对数据量庞大的st_ret_txn_aeps表，当前查询采用index_merge方式扫描122328行，性能不佳。已知为created_at字段创建索引可解决问题，但需基于现有索引结构优化查询，不新增索引。

原查询 #

select 
  `sale_amount`, 
  `provider_transaction_id`, 
  `id` as `acc_id`, 
  `status`, 
  `service_id`, 
  `provider_status`, 
  `is_sale_settled`, 
  CASE WHEN status = 1 
  and is_sale_settled = 1 then 4 WHEN status = 1 
  and is_sale_settled = 0 then 3 ELSE 5 END AS acc_ics_status, 
  `system_transaction_id`, 
  `engine_transaction_id`, 
  `transaction_date`, 
  `created_at` as `transaction_date_time`, 
  HOUR(created_at) as transaction_hour 
from 
  `st_ret_txn_aeps` 
where 
  `created_at` >= '2022-10-03 14:45:01' 
  and `created_at` <= '2022-10-03 14:48:00' 
  and `service_id` = 14 
  and `transaction_date` = '2022-10-03'

当前执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st_ret_txn_aeps
   partitions: NULL
         type: index_merge
possible_keys: transaction_id_sevrice_id_unique,st_ret_txn_aeps_service_id_index,idx_txn_date
          key: idx_txn_date,st_ret_txn_aeps_service_id_index
      key_len: 3,1
          ref: NULL
         rows: 122328
     filtered: 11.11
        Extra: Using intersect(idx_txn_date,st_ret_txn_aeps_service_id_index); Using where
1 row in set, 1 warning (0.00 sec)

表结构（核心索引部分） #

CREATE TABLE `st_ret_txn_aeps` (
  -- 非索引字段省略
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_id_sevrice_id_unique` (`service_id`,`engine_transaction_id`),
  UNIQUE KEY `system_transaction_id_unique` (`system_transaction_id`),
  KEY `st_ret_txn_aeps_service_id_index` (`service_id`),
  KEY `idx_txn_date` (`transaction_date`),
  -- 其他索引省略
) ENGINE=InnoDB AUTO_INCREMENT=122756253 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

优化方案 #

1. 移除冗余过滤条件 #

created_at的范围已经限定在2022-10-03当天的时间段内，因此transaction_date = '2022-10-03'是冗余条件——符合created_at范围的数据必然满足transaction_date等于当天。移除该条件后，优化器无需再执行两个索引的交集操作（index_merge），可直接选择过滤性更强的st_ret_txn_aeps_service_id_index单索引，减少扫描行数。

优化后SQL：

select 
  `sale_amount`, 
  `provider_transaction_id`, 
  `id` as `acc_id`, 
  `status`, 
  `service_id`, 
  `provider_status`, 
  `is_sale_settled`, 
  CASE WHEN status = 1 
  and is_sale_settled = 1 then 4 WHEN status = 1 
  and is_sale_settled = 0 then 3 ELSE 5 END AS acc_ics_status, 
  `system_transaction_id`, 
  `engine_transaction_id`, 
  `transaction_date`, 
  `created_at` as `transaction_date_time`, 
  HOUR(created_at) as transaction_hour 
from 
  `st_ret_txn_aeps` 
where 
  `service_id` = 14 
  and `created_at` >= '2022-10-03 14:45:01' 
  and `created_at` <= '2022-10-03 14:48:00'

2. 强制指定单索引，避免index_merge #

如果优化器仍倾向于使用index_merge，可通过FORCE INDEX强制指定使用st_ret_txn_aeps_service_id_index索引。单索引扫描的开销通常低于两个索引的交集合并，尤其是当service_id的过滤性足够强时。

优化后SQL：

select 
  `sale_amount`, 
  `provider_transaction_id`, 
  `id` as `acc_id`, 
  `status`, 
  `service_id`, 
  `provider_status`, 
  `is_sale_settled`, 
  CASE WHEN status = 1 
  and is_sale_settled = 1 then 4 WHEN status = 1 
  and is_sale_settled = 0 then 3 ELSE 5 END AS acc_ics_status, 
  `system_transaction_id`, 
  `engine_transaction_id`, 
  `transaction_date`, 
  `created_at` as `transaction_date_time`, 
  HOUR(created_at) as transaction_hour 
from 
  `st_ret_txn_aeps` FORCE INDEX(st_ret_txn_aeps_service_id_index)
where 
  `service_id` = 14 
  and `created_at` >= '2022-10-03 14:45:01' 
  and `created_at` <= '2022-10-03 14:48:00'

3. 利用覆盖索引减少回表操作 #

现有唯一索引transaction_id_sevrice_id_unique包含service_id和engine_transaction_id，可先通过该索引筛选出符合service_id=14的engine_transaction_id集合，再关联原表获取其他字段。这种方式能减少回表次数，适用于engine_transaction_id对应的行数远少于直接扫描的情况。

示例SQL：

select 
  t.`sale_amount`, 
  t.`provider_transaction_id`, 
  t.`id` as `acc_id`, 
  t.`status`, 
  t.`service_id`, 
  t.`provider_status`, 
  t.`is_sale_settled`, 
  CASE WHEN t.status = 1 
  and t.is_sale_settled = 1 then 4 WHEN t.status = 1 
  and t.is_sale_settled = 0 then 3 ELSE 5 END AS acc_ics_status, 
  t.`system_transaction_id`, 
  t.`engine_transaction_id`, 
  t.`transaction_date`, 
  t.`created_at` as `transaction_date_time`, 
  HOUR(t.created_at) as transaction_hour 
from 
  (select `engine_transaction_id` from `st_ret_txn_aeps` where `service_id`=14) idx
join `st_ret_txn_aeps` t on idx.engine_transaction_id = t.engine_transaction_id
where 
  t.`created_at` >= '2022-10-03 14:45:01' 
  and t.`created_at` <= '2022-10-03 14:48:00'

4. 调整条件顺序（辅助优化） #

将过滤性最强的条件（如service_id=14）放在WHERE子句最前面，可帮助优化器更快识别最优索引选择逻辑，进一步提升执行效率（效果视数据分布而定）。

内容的提问来源于stack exchange，提问作者lizzy lakh