MySQL 8.0 對 limit 的優化技巧

2022-10-22 14:01:10

一、前言

提到 limit 優化，大多數 MySQL DBA 都不會陌生，能想到各種應對策略，比如延遲關聯，書籤式查詢等等，之前我也寫過一篇優化的文章：https://www.jb51.net/article/234357.htm ，有興趣的朋友可以複習一下。

二、MySQL 8.0 對limit 的改進

對於 limit N 帶有 group by ，order by 的 SQL 語句 (order by 和 group by 的欄位有索引可以使用)，MySQL 優化器會盡可能選擇利用現有索引的有序性，減少排序–這看起來是 SQL 的執行計劃的最優解，但是實際上效果其實是南轅北轍，相信很多 DBA 遇到的相關案例中 sql 執行計劃選擇 order by id 的索引進而導致全表掃描，而不是利用 where 條件中的索引查詢過濾資料。MySQL 8.0.21 版本之前，並沒有什麼引數來控制這種行為，但是自 MySQL 8.0.21 之後提供一個優化器引數 prefer_ordering_index ，通過設定 optimizer_switch 來開啟或者關閉該特性。比如:

SET  optimizer_switch  = "prefer_ordering_index=off";
SET  optimizer_switch = "prefer_ordering_index=on";

三、實踐出真知

測試環境 MySQL 社群版 8.0.30

構造測試資料

CREATE TABLE t (
id1 BIGINT  NOT NULL  PRIMARY KEY auto_increment, 
id2 BIGINT NOT NULL,
c1 VARCHAR(50) NOT NULL,
c2 varchar(50) not null,
INDEX i (id2, c1));

insert into t(id2,c1,c2) values(1,'a','xfvs'),(2,'bbbb','xfvs'),(3,'cdddd','xfvs'),(4,'dfdf','xfvs'),(12,'bbbb','xfvs'),(23,'cdddd','xfvs'),(14,'dfdf','xfvs'),
(11,'bbbb','xfvs'),(13,'cdddd','xfvs'),(44,'dfdf','xfvs'),(31,'bbbb','xfvs'),(33,'cdddd','xfvs'),(34,'dfdf','xfvs');

3.1 預設開啟引數

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

查詢非索引欄位，id2 上有索引，order by 主鍵 id1 ，explain 檢視執行計劃 type index 說明使用索引掃描使用 using where 過濾結果集。這個是優化器的自以為的最優選擇，但是實際上遇到資料集合比較大的表，該執行計劃就不是最優解，反而導致慢查。

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 69.23
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

3.2 關閉該引數

mysql  (test) > SET optimizer_switch = "prefer_ordering_index=off";

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

經過調整之後，檢視執行計劃發現優化器選擇 id2 索引欄位找到記錄做過濾，並且使用了ICP 特性，減少物理 io 請求，而不是選擇使用主鍵 id1 遍歷索引然後回表查詢。

顯然通過人為介入引數調整優化器的行為能帶來更好的優化效果。

四、總結

從不同版本的 MySQL 發展軌跡來看 MySQL 的優化器越來越智慧 (比如大家期待已久的直方圖特性) ，能更多的減少人為干預，提升執行計劃的準確性。

到此這篇關於MySQL 8.0 對 limit 的優化的文章就介紹到這了,更多相關MySQL 8.0 limit優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！