我正在嘗試優化 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_atf | IDX_E00CEDDE37D3107C6 | 111456 | 6.6 | 使用索引條件;使用地點 | |||
簡單 | r0_ | 參考 | ride_booking_id_IDX | #ride_booking_id_IDX | #109 | ector.b1_.id | 1 | 100.0 | |||
簡單 | s2_ | eq_ref | 主要,IDX_B9327A739F2C3FAB,spot_type_IDX | 主要 | 4 | ector.r0_.from_spot_id | 1 | 72.52 | 使用地點 | ||
簡單 | 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 :
選擇類型 | 表 | 分割區 | 類型 | 可能的鍵 | 鍵 | key_len | 參考 | 行 | 已過濾 | 額外 | |
---|---|---|---|---|---|---|---|---|---|---|---|
簡單 | s2_ | 參考 | 主要,IDX_B9327A739F2C3FAB,spot_type_IDX | spot_type_IDX | 767 | 常數 | 161 | 100.0 | 使用索引條件 | ||
簡單 | r0_ | 參考 | IDX_9B3D7CD0ABAF30D3,IDX_9B3D7CD03301C60,ride_booking_id_IDX,booking_from_spot_to_spot_IDX | IDX_9B3D7CD0ABAF30D3 | 5 | ector.s2_.id | 392 | 100.0 | |||
簡單 | b1_ | eq_ref | 主要,booking_id_end_IDX,booking_id_IDX,booking_id_start_IDX,IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C,IDX_E00CEDDEDEA4208C,booking_paid_atf | 主要 | 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=開
您的索引
(id, start_at)
未被選取,因為沒有固定的id
可供搜尋。根據您的使用案例,您可能需要在
start_at
上建立一個索引,在end_at
上建立另一個索引。之後,一個簡單的查詢SELECT * from booking b where b.start_at '2021-01-01';
將立即生效;根據搜尋條件,MySQL 可能會透過MERGE INDEX 優化操作使用一個索引或同時使用這兩個索引。如果您希望使用單一索引,則需要仔細選擇欄位的順序,因為索引的使用順序與其定義的順序相同。
編輯:OPs編輯後,這是我的想法。
這很好地解釋了事情。假設你是
SELECT *
,MySQL將被迫讀取整個表。嘗試僅選擇id
,在這種情況下它很可能會使用索引;因為它將是一個索引覆蓋的查詢。