为什么不指定索引MySQL会变慢

Question

我正在尝试优化 SQL 查询，但我想知道如何正确执行此操作。

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';

在这里，我强制使用索引(booking_id, from_spot_id, to_spot_id)，这会导致查询在最近日期的 25 秒内执行大约 100 毫秒！

booking 表大约有 200 万行，而 ride 表大约有 500 万行。

但是，我可以看到它使用强制索引扫描更多行：

id	选择类型	表	分区	类型	可能的键	键	key_len	参考	行	已过滤	额外
1	简单	b1_		范围	主要，booking_id_end_IDX，booking_id_IDX，booking_id_start_IDX，IDX_E00CEDDEB75363F7，IDX_E00CEDDE37D3107C，IDX_E00CEDDEDEA4208C，booking_paid_at_IDX，booking_cancelled_at_IDX	IDX_E00CEDDE37D3107C	6		111456	6.6	使用索引条件；使用地点
1	简单	r0_		参考	ride_booking_id_IDX	ride_booking_id_IDX	109	ector.b1_.id	1	100.0
1	简单	s2_		eq_ref	主要，IDX_B9327A739F2C3FAB，spot_type_IDX	主要	4	ector.r0_.from_spot_id	1	72.52	使用地点
1	简单	s3_		eq_ref	主要	主要	4	ector.r0_.to_spot_id	1	100.0	使用地点

与不使用索引的相同查询相比：

id	选择类型	表	分区	类型	可能的键	键	key_len	参考	行	已过滤	额外
1	简单	s2_		参考	主要，IDX_B9327A739F2C3FAB，spot_type_IDX	spot_type_IDX	767	常量	161	100.0	使用索引条件
1	简单	r0_		参考	IDX_9B3D7CD0ABAF30D3，IDX_9B3D7CD03301C60，ride_booking_id_IDX，booking_from_spot_to_spot_IDX	IDX_9B3D7CD0ABAF30D3	5	ector.s2_.id	392	100.0
1	简单	b1_		eq_ref	主要，booking_id_end_IDX，booking_id_IDX，booking_id_start_IDX，IDX_E00CEDDEB75363F7，IDX_E00CEDDE37D3107C，IDX_E00CEDDEDEA4208C，booking_paid_at_IDX，booking_cancelled_at_IDX	主要	108	ector.r0_.booking_id	1	5.0	使用地点
1	简单	s3_		eq_ref	主要	主要	4	ector.r0_.to_spot_id	1	100.0	使用地点

据我所知，我用来与 start_at 和 end_at 进行比较的日期是查询速度明显加快的原因。

因此，我尝试将最慢的部分隔离到较小的查询：

从预订 b 中选择 *，其中 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01';

在餐桌预订上，我有两个索引 (start_at) 和 (end_at)，当您接近最大值和最小值时，它们可以帮助此查询运行得更快（由于索引将过滤大多数行，因此只剩下很少的行需要扫描）。

但是，当我在过去足够远的地方取一个随机值时，它会变得慢得多。上面的查询运行了 10 秒，因为它只按预期使用两个索引之一，我不知道为什么解释在这样一个简单的查询上没有显示 merge_index ：

id	选择类型	表	分区	类型	可能的键	键	key_len	参考	行	已过滤	额外
1	简单	b		范围	IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C	IDX_E00CEDDEB75363F7	6		1147319	50	使用索引条件；使用地点

由于我无法拥有满足两个范围条件的索引，因此我尝试将查询分成两半

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';

此查询的运行速度显着加快，大约需要 600 毫秒。然而，由于查询的简单性以及它返回大约 7k 行的事实，我预计它最多只有两位数。

我不明白为什么查询没有自动选择我的索引 (id, start_at) 和 (id, end_at) ？我缺少什么？

我知道我可以对表进行分区以获得更好的结果，但我有无法删除的外键，所以这不是一个解决方案。我是否应该考虑另一种架构，并有一个表单独保存预订日期，而不包含任何外键，并让预订表引用它，以便我可以对预订表进行分区？在预订时使用外键引用分区预订表是否可行？

Mysql 引擎正在 AWS 中运行，版本如下：8.0.mysql_aurora.3.02.2

SELECT @@optimizer_switch 的输出是：

index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、物化=on，半连接=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、provided_merge=on、use_invisible_indexes=off、skip_scan=on、hash_join=on、subquery_to_衍生=off、prefer_ordering_index =开，hypergraph_optimizer=关，衍生_条件_pushdown=开

Answer 1

您的索引 (id, start_at) 未被选取，因为没有固定的 id 可供搜索。

根据您的使用案例，您可能需要在 start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; 将立即生效；根据搜索条件，MySQL 可能会通过 MERGE INDEX 优化操作使用一个索引或同时使用这两个索引。

如果您希望使用单个索引，则需要仔细选择字段的顺序，因为索引的使用顺序与其定义的顺序相同。

编辑：OPs编辑后，这是我的想法。

这很好地解释了事情。假设你是SELECT *，MySQL将被迫读取整个表。尝试仅选择id，在这种情况下它很可能会使用索引；因为它将是一个索引覆盖的查询。