MySQL🐬 升級 MySQL 8.0 的地雷：prefer_ordering_index

筆者曾任職 Yahoo，現在區塊鏈產業打滾，《軟體需求溝通 ─ 從外商公司學跨部門協作開發》線上課程講師，紛絲團《程式猿吃香蕉🍌》

升級到 MySQL 8.0 後，可以自行設定 prefer_ordering_index 參數，選擇將其設為 on 或 off。

先說結論：如果將 prefer_ordering_index 設為 off，當 SQL 查詢使用 ORDER BY ... LIMIT 時，很可能會出現 filesort，尤其是在沒有 WHERE 條件縮小資料集 (data set) 的情況下，容易導致查詢變慢。

這類 ORDER BY ... LIMIT 語句在分頁功能中很常見。如果不小心設置了不合適的 prefer_ordering_index，在升級到 MySQL 8.0 後，可能會突然收到大量慢查詢 (slow query) 的系統報警。

(本文使用 MySQL 8.0.28，因為 Aurora MySQL lts 版本 3.04.2 對應此版本)

━━

▍prefer_ordering_index 有什麼用？

以下舉兩個例子來看prefer_ordering_index參數的作用。假設我們有一張資料表如下：

--建立資料表
CREATE TABLE tbl (
  id BIGINT NOT NULL PRIMARY KEY auto_increment,  
  sub_id BIGINT NOT NULL, 
  data1 VARCHAR(50) NOT NULL, 
  data2 VARCHAR(50) NOT NULL,
  INDEX idx (sub_id, data1)
);

--寫入資料
insert into tbl(sub_id, data1, data2) values(1,'a','aa'),(2,'b','bb'),(3,'c','cc'),(4,'d','dd'),(5,'e','ee'),(6,'f', 'ff'),(7,'g', 'gg'),(8,'h', 'hh'),(9,'i','ii'),(10,'j','jj'),(11,'k','kk'),(12,'l','ll'),(13,'m','mm');

它有一個主鍵 id 的聚簇索引 (Clustered index) 以及一個二級索引 (Secondary index) 名稱為 idx。

◎ 例子：當 prefer_ordering_index 為on時，執行以下 SQL

select data2 from tbl where sub_id > 8 ORDER BY id ASC LIMIT 2

顧名思義，prefer_ordering_index指的是優先使用 order by 語句的索引。這個例子中，因為是 order by id，所以 MySQL 「傾向」優先採用主鍵 id 的聚簇索引。

從以下的 explain 結果，可以看到key: PRIMARY，確實是使用主鍵 id 的聚簇索引。

mysql> explain select data2 from tbl where sub_id > 8 ORDER BY id LIMIT 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: index
possible_keys: idx
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 5
     filtered: 38.46
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

◎ 例子：當 prefer_ordering_index 為off時，執行以下 SQL

select data2 from tbl where sub_id > 8 ORDER BY id ASC LIMIT 2

和上個例子一模一樣的 SQL，使用 explain 看執行計畫時 (如下)，可以發現這一次出現了filesort 。

mysql> explain select data2 from tbl where sub_id > 8 ORDER BY id LIMIT 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: range
possible_keys: idx
          key: idx
      key_len: 8
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.08 sec)

乍看之下你會很驚訝， 語句中 order by id 在聚簇索引中不是已經依照 id 排序好了嗎？為什麼 MySQL 還要做耗費時間的 filesort？

原因就出在 prefer_ordering_index 設定成了off ，會優先採用其他索引。從執行計畫中可以看到 key:idx，這一次 MySQL 採用的是二級索引，將資料取回後，再對 id 做 filesort 排序。

『如果把 SQL 語句拿掉 where 條件呢？總會走聚簇索引了吧？』

可以用 explain 試一下：

mysql> explain select data2 from tbl ORDER BY id LIMIT 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

答案是：MySQL 還是會做 filesort 排序。這也是文章一開始提到的地雷：

如果將 prefer_ordering_index 設為 off，當 SQL 查詢使用 ORDER BY ... LIMIT 時，很可能會出現 filesort，尤其是在沒有 WHERE 條件縮小資料集 (data set) 的情況下，容易導致查詢變慢。

━━

▍prefer_ordering_index 要 on 還是 off ?

還得看應用程式的各別情況。

MySQL 8.0 預設 prefer_ordering_index 為 on。在實務中，有些團隊在升級測試時就出現了慢查詢。

因為prefer_ordering_index 為 on，原先 MySQL 5.7 優化器自動走 where 索引的 SQL，改走了 order by 的索引，因為資料量大，又無法透過 where 的索引快速篩選，反而造成慢查詢 (slow query)。

而有些團隊的 DBA 傾向將 prefer_ordering_index 為 off ，因為大部分情況下ORDER BY ... LIMIT 語句都會搭配 WHREE，先走 where 的索引篩選資料，資料量變小後，通常查詢會變快，但缺點是要做filesort，容易導致沒有 where 條件的 SQL 語句出現慢查詢。

要 on 還是 off ? 各有利弊，回到系統設計常說的話，所有決策都是權衡取捨 (trade off)。

『但同一個應用程式，如果有些要 on 有些要 off 呢？』

這是個很實際的問題，有兩個方法:

• (1) 在 SQL 語句裡面加 force index 強制走某個索引。
• (2) ORDER BY... LIMIT 語句如果是為了做分頁，可以改造為 Cursor pagination 的做法，例如：上一頁的最大 id 是 20，下一頁查詢就可以這樣寫 select * from mytable where id > 20 order by id limit 10 ，用上一頁最大的 id 來做初步篩選，減少資料量，但缺點是瀏覽時必須依照分頁順序，不能跳頁。

━━

▍進階討論：prefer_ordering_index 為 on 一定不會 filesort 嗎？

在剛才的例子中，當 prefer_ordering_index 為 on 時，走的是 order by 的索引，因為已經排序過，所以不需要做 filesort，但優化器一定每次都這樣走嗎？

回顧一下，先前 prefer_ordering_index 為 on的範例：

--建立資料表
CREATE TABLE tbl (
  id BIGINT NOT NULL PRIMARY KEY auto_increment,  
  sub_id BIGINT NOT NULL, 
  data1 VARCHAR(50) NOT NULL, 
  data2 VARCHAR(50) NOT NULL,
  INDEX idx (sub_id, data1)
);

--SQL 查詢語句
select data2 from tbl where sub_id > 8 ORDER BY id ASC LIMIT 2

這裡要注意一個小細節，資料表的二級索引是 idx(sub_id, data1)，SQL 查詢語句要求返回的 data2 並不在二級索引裡面。

所以即使 MySQL 走二級索引 idx 也無法利用覆蓋索引 (Covering Index) 的優勢，再加上 prefer_ordering_index 是 on，傾向使用 order by 的索引 id，因此，查詢優化器便毫不猶豫地選擇了聚簇索引。

但如果把 SQL 改為：

select data1 from tbl where sub_id > 8 ORDER BY id ASC LIMIT 2

SQL 要求返回的 data1 在二級索引 idx(sub_id, data1) 裡，可以發揮覆蓋索引 (Covering Index) 的優勢，這時候 MySQL 應該要聰明地選擇二級索引使用吧？下指令 explain 看看：

mysql> explain select data1 from tbl where sub_id > 8 ORDER BY id ASC LIMIT 2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: range
possible_keys: idx
          key: idx
      key_len: 8
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

從執行計畫可以看到 key:idx，MySQL 確實夠「聰明」選擇了二級索引，利用了覆蓋索引 (Covering Index) 的優勢，並且在 prefer_ordering_index 為 on 的情況下，出現了 filesort。

所以， prefer_ordering_index 為 on 一定不會 filesort 嗎？答案是不一定。

如果想了解覆蓋索引，可以看我先前的這篇文章：MySQL🐬 InnoDB 教我的事： Index 索引、鎖、資源各司其職。

━━

▍prefer_ordering_index 的設定方式

登入 MySQL 後，可以用以下指令查看 prefer_ordering_index參數的值：

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

若結果是 1 表示 prefer_ordering_index 為 on，如果是 0 表示為 off。要將 prefer_ordering_index 改設定為 off 也很容易，可以用以下的指令：

mysql> SET optimizer_switch = "prefer_ordering_index=off";
Query OK, 0 rows affected (0.00 sec)

再次查詢就會看到 prefer_ordering_index 已設定為 off了。

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=off%';
+-------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=off%' |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

當 MySQL 8.0 升級後無故出現慢查詢時，可以看看是不是被 prefer_ordering_index影響。

━━

▍總結

MySQL 8.0 開放了 prefer_ordering_index 參數讓使用者自行配置，帶來了方便卻也衍生出問題。在升級的時候不可不慎。

prefer_ordering_index 預設為 on，可能導致升級時出現慢查詢，因為 MySQL 會優先使用 order by 的索引，而非 where 的索引，造成查詢效率降低。

而將 prefer_ordering_index 設為 off，則多數情況下可以利用 where 索引來提高查詢速度，但會導致無 where 條件的查詢變慢。設定為 on 還是 off 各有利弊，需根據實際情況進行權衡取捨。

若對 MySQL InnoDB 有興趣，也可以看看我的其他文章 :)

MySQL🐬 InnoDB 教我的事： 最近最少使用 LRU 串列的優化

MySQL🐬 InnoDB 教我的事： Index 索引、鎖、資源各司其職

MySQL🐬 InnoDB 教我的事：想鎖的沒鎖 ？不該鎖的被鎖了！

若是喜歡我分享的內容，歡迎幫我按個拍手，可拍 50下，給我一點鼓勵，或是加入我的粉絲團《程式猿吃香蕉🍌》，一起分享軟體知識與心得！

程式猿吃香蕉