從 MySQL 5.5.11 遷移到 MariaDB 5.5.41 後糟糕的執行計劃

我們剛剛將我們的網站數據庫從執行 MySQL 5.5.11 的伺服器遷移到執行 MariaDB 5.5.41 的伺服器。一切正常，除了至少有一個來自我們店面的查詢在新伺服器上生成非常糟糕的執行計劃，導致掃描大約 6 GB 的表。

這是一個包含兩個 InnoDB 表的查詢，它們之間有一個相對簡單的連接，在兩個被索引的列上（在 catalog_category_flat_store_1 中的 PK，但不是 core_url_rewrite 上的覆蓋索引）：

SELECT
   `main_table`.`entity_id`,
   main_table.`name`,
   main_table.`path`,
   `main_table`.`is_active`,
   `main_table`.`is_anchor`,
   `url_rewrite`.`request_path`
FROM `catalog_category_flat_store_1` AS `main_table`
   LEFT JOIN `core_url_rewrite` AS `url_rewrite`
       ON url_rewrite.category_id=main_table.entity_id
       AND url_rewrite.is_system=1
       AND url_rewrite.product_id IS NULL
       AND url_rewrite.store_id='1'
       AND url_rewrite.id_path LIKE 'category/%'
WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/5/204/225/%') AND (`level` <= 5) ORDER BY `main_table`.`position` ASC

catalog_category_flat_store_1 有 627 行，而 core_url_rewrite 有 8,687,266 行（殺了我）。

EXPLAIN 告訴我 catalog_category_flat_store_1 是“使用索引條件；使用位置；使用文件排序”，而 core_url_rewrite（巨大的表）是“為每條記錄檢查的範圍（索引映射：0xF4）”，儘管據稱使用 category_id 上的索引作為其鑰匙。

在舊伺服器上，catalog_category_flat_store_1 是“使用位置；使用文件排序”，而 core_url_rewrite 沒有為“額外”列出任何內容，並且似乎使用了正確的索引。

我注意到 MariaDB 有很多用於 optimizer_switch 的附加選項，我想知道這些選項中的任何一個是否會導致不同的優化器行為，所以我認為它值得測試。問題是我不知道讓 MariaDB 像 MySQL 一樣的值應該是什麼。

這是舊伺服器，查詢執行良好：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

這是新伺服器，完全相同的查詢最終可能會執行很長時間：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

來自 Magento core_url_rewrite 地獄的 PS 問候。

好吧，這有點像巫毒/霰彈槍調試，但我暫時可以正常執行。我將extended_keys選項設置為 on，並在有問題的 800 萬行表上創建了一個覆蓋索引。現在我得到的執行計劃甚至比舊伺服器提出的可接受計劃（僅使用 Magento 的預設索引）還要好。

儘管如此，這一切似乎都有些搖擺不定，所以我暫時保持開放狀態，以防有人有更好的建議或解釋。

好吧，這篇文章很老了，但是在將數據庫從 MySQL 5.6 遷移到 MariaDB 10.2 之後，我經歷了幾乎相同的情況。在舊伺服器上，對錶（未針對索引進行優化）的查詢所用時間不超過 1 秒。使用 MariaDB 在新伺服器上執行相同的查詢需要 1 分 50 秒！在帶有 MariaDB 的伺服器中，我添加了缺失的索引，問題就解決了。consutla 步驟延遲不到 1 秒。但奇怪的是，同樣缺乏索引的 MySQL 並沒有產生問題，並且能夠比 MariaDB 更快地執行查詢。

好吧，經過一番調查，我修改了變數optimizer_switch，使參數optimize_join_buffer_size為“ON”（預設為“off”），這樣就足夠了，現在 MariaDB 也可以快速解決查詢，即使它沒有索引（顯然有索引還是更快，但是沒有索引已經不是那麼嚴重的問題了）

引用自：https://dba.stackexchange.com/questions/99300