為什麼不指定索引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 萬行。

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

IDX_E00CEDDE37D3107C61114566.6使用索引條件；使用地點1簡單r0_參考ride_booking_id_IDX#ride_booking_id_IDX#109ector.b1_.id1100.01簡單s2_eq_ref主要，IDX_B9327A739F2C3FAB，spot_type_IDX主要4ector.r0_.from_spot_id172.52使用地點1簡單s3_eq_ref主要主要4ector.r0_.to_spot_id1100.0使用地點

id	選擇類型	表	分割區	類型	可能的鍵	鍵	key_len	參考	行	已過濾	額外
1	簡單	b1_		範圍	主要，booking_id_end_IDX，booking_id_IDX，booking_id_start_IDX，IDX_E00CEDDEB75363F7，IDX_E00CEDDE37D3107C，IDX_E00CEDDEDEA4208C，booking_paid_atf

與不使用索引的相同查詢比較：

id選擇類型表分割區類型可能的鍵鍵key_len參考行已過濾額外1簡單s2_參考主要，IDX_B9327A739F2C3FAB，spot_type_IDXspot_type_IDX767常數161100.0使用索引條件1簡單r0_參考IDX_9B3D7CD0ABAF30D3，IDX_9B3D7CD03301C60，ride_booking_id_IDX，booking_from_spot_to_spot_IDXIDX_9B3D7CD0ABAF30D35ector.s2_.id392100.01簡單b1_eq_ref主要，booking_id_end_IDX，booking_id_IDX，booking_id_start_IDX，IDX_E00CEDDEB75363F7，IDX_E00CEDDE37D3107C，IDX_E00CEDDEDEA4208C，booking_paid_atf 1

據我所知，我用來與 start_at 和 end_at 進行比較的日期是查詢速度明顯加快的原因。

因此，我嘗試將最慢的部分隔離到較小的查詢：

從預訂 b 中選擇 *，其中 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01';

#在餐桌預訂上，我有兩個索引(start_at) 和(end_at)，當您接近最大值和最小值時，它們可以幫助此查詢運行得更快（由於索引將過濾大多數行，因此只剩下很少的行需要掃描）。

但是，當我在過去足夠遠的地方取一個隨機值時，它會變得慢得多。上面的查詢運行了 10 秒，因為它只按預期使用兩個索引之一，我不知道為什麼解釋在這樣一個簡單的查詢上沒有顯示 merge_index ：

					主要	108	ector.r0_.booking_id	#1	5.0	使用地點
簡單	s3_		eq_ref	主要	主要	4	ector.r0_.to_spot_id	1	100.0	使用地點

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_dsted=on、block_dsted.半連接=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、provided_merge=on、i_invisible_indexes=i_filter=on、who_merge=oni_invisible_indexescani_i_indexs、Fy_Fone_scani_Fy_i_indexscani_Fy_scans、Fy_i_indexscani_Fy_i_indexs、Fy_scanscans、Fy_s_indexscani_Fy_i_indexs、Fy_scanscani=i_i_i_indexs、Fy_i_index. 、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，在這種情況下它很可能會使用索引；因為它將是一個索引覆蓋的查詢。