Aurora MySQL查询优化疑问：索引选择与多范围条件性能问题

一、原多表查询的性能差异原因 #

原查询语句：

SELECT 
    r0_.*
FROM ride r0_ use index (ride_booking_id_IDX)
LEFT JOIN booking b1_ ON r0_.booking_id = b1_.id 
LEFT JOIN spot s2_ ON r0_.from_spot_id = s2_.id 
LEFT JOIN spot s3_ ON r0_.to_spot_id = s3_.id 
WHERE b1_.start_at <= '2023-04-21' 
    AND b1_.end_at >= '2023-04-20' 
    AND b1_.paid_at IS NOT NULL 
    AND b1_.cancelled_at IS NULL 
    AND ((s2_.zone_id = 1 OR s3_.zone_id = 1)) 
    AND s2_.type = 'parking';

强制索引 vs 未强制索引的执行逻辑差异 #

• 强制索引（ride_booking_id_IDX）路径：
  先从booking表通过日期范围索引筛选符合条件的记录（约11万行），再通过booking.id关联ride表（ref类型精准匹配，单条关联），最后关联spot表做过滤。这种路径的核心优势是前置过滤大量无效booking数据，后续关联的ride和spot数据量被严格控制，看似扫描行数多，实际是精准关联后的小范围处理。

• 未强制索引路径：
  先从spot表按type='parking'筛选（约161行），再关联ride表（每条spot匹配392条ride），最后关联booking表并做日期过滤。问题在于前期筛选出的ride数据量过大（约6.3万行），且后续对booking的过滤逐行判断（过滤率仅5%），导致大量无效数据被处理，最终拖慢查询至25秒。

二、booking表日期范围查询的index_merge未触发问题 #

针对SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01';的查询，虽开启index_merge_intersection但未触发，原因如下：

1. 成本估算逻辑：当查询返回数据量占表总量一半以上（预估114万行），MySQL优化器认为单索引扫描后过滤的成本，低于合并两个索引做交集运算的成本（合并需对两个索引结果集做排序/哈希匹配，开销更高）。
2. 索引覆盖性不足：单独的start_at和end_at索引无法覆盖所有过滤条件，即使合并索引仍需回表取完整数据，优化器判定性价比不足。

拆分关联查询的性能瓶颈 #

你尝试的拆分查询：

SELECT * from booking b
INNER JOIN booking b2 use index(booking_id_start_IDX)  ON b.id = b2.id and b2.start_at < '2021-01-01'
INNER JOIN booking b3 use index(booking_id_end_IDX) ON b.id = b3.id and b3.end_at > '2021-01-01';

耗时600ms未达预期的原因：

• 关联操作需要对两个索引的结果集做匹配，且最终要回表获取b的完整数据，额外开销不可忽视。
• 优化器未自动选择(id, start_at)和(id, end_at)索引，大概率是表统计信息不准确，导致优化器误判索引成本；或索引命名/结构未被正确识别（需确认索引是否确实包含id和对应日期字段）。

三、具体优化建议 #

1. 原多表查询优化 #

• 优化booking表索引：创建联合索引(paid_at, cancelled_at, start_at, end_at, id)，将过滤条件前置，减少booking表扫描行数，进一步提升关联效率。
• 保留ride表的(booking_id, from_spot_id, to_spot_id)索引，确保关联时直接获取spot关联字段，减少回表。

2. booking表日期范围查询优化 #

• 创建**覆盖索引(start_at, end_at, id)**或(end_at, start_at, id)，让优化器在扫描单索引时直接完成两个日期条件的过滤，无需回表后再判断，大幅提速。
• 手动触发index_merge：使用FORCE INDEX FOR INTERSECTION (IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C)强制合并索引，验证小数据量场景下的性能提升。
• 更新表统计信息：执行ANALYZE TABLE booking;，让优化器获取准确数据分布，做出更合理的索引选择。

3. 替代分区表的架构方案 #

因外键限制无法对booking表分区，可采用以下方案：

• 创建独立的booking_date表：

  CREATE TABLE booking_date (
      booking_id BIGINT PRIMARY KEY,
      start_at DATETIME,
      end_at DATETIME,
      FOREIGN KEY (booking_id) REFERENCES booking(id) ON DELETE CASCADE
  ) ENGINE=InnoDB;
  

  通过触发器或应用层将booking表的start_at和end_at同步到该表，然后对booking_date表按start_at或end_at做范围分区。
• 查询时先从booking_date表筛选符合条件的booking_id，再关联booking表：

  SELECT b.*
  FROM booking_date bd
  JOIN booking b ON bd.booking_id = b.id
  WHERE bd.start_at < '2021-01-01' AND bd.end_at > '2021-01-01'
    AND b.paid_at IS NOT NULL AND b.cancelled_at IS NULL;
  

  该方案规避了booking表的外键分区限制，同时利用分区表优势快速过滤日期范围。

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