快捷導(dǎo)航

MySQL?8.0?對(duì)?limit?的優(yōu)化技巧

更新時(shí)間：2022年10月20日 08:30:37 作者：ActionTech

從不同版本的 MySQL 發(fā)展軌跡來(lái)看 MySQL 的優(yōu)化器越來(lái)越智能 (比如大家期待已久的直方圖特性) ，能更多的減少人為干預(yù)，提升執(zhí)行計(jì)劃的準(zhǔn)確性，這篇文章主要介紹了MySQL?8.0?對(duì)?limit?的優(yōu)化,需要的朋友可以參考下

一、前言

提到 limit 優(yōu)化，大多數(shù) MySQL DBA 都不會(huì)陌生，能想到各種應(yīng)對(duì)策略，比如延遲關(guān)聯(lián)，書(shū)簽式查詢等等，之前我也寫(xiě)過(guò)一篇優(yōu)化的文章：http://m.fzitv.net/article/234357.htm ，有興趣的朋友可以復(fù)習(xí)一下。

二、MySQL 8.0 對(duì)limit 的改進(jìn)

對(duì)于 limit N 帶有 group by ，order by 的 SQL 語(yǔ)句 (order by 和 group by 的字段有索引可以使用)，MySQL 優(yōu)化器會(huì)盡可能選擇利用現(xiàn)有索引的有序性，減少排序–這看起來(lái)是 SQL 的執(zhí)行計(jì)劃的最優(yōu)解，但是實(shí)際上效果其實(shí)是南轅北轍，相信很多 DBA 遇到的相關(guān)案例中 sql 執(zhí)行計(jì)劃選擇 order by id 的索引進(jìn)而導(dǎo)致全表掃描，而不是利用 where 條件中的索引查找過(guò)濾數(shù)據(jù)。MySQL 8.0.21 版本之前，并沒(méi)有什么參數(shù)來(lái)控制這種行為，但是自 MySQL 8.0.21 之后提供一個(gè)優(yōu)化器參數(shù) prefer_ordering_index ，通過(guò)設(shè)置 optimizer_switch 來(lái)開(kāi)啟或者關(guān)閉該特性。比如:

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

三、實(shí)踐出真知

測(cè)試環(huán)境 MySQL 社區(qū)版 8.0.30

構(gòu)造測(cè)試數(shù)據(jù)

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 默認(rèn)開(kāi)啟參數(shù)

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 查看執(zhí)行計(jì)劃 type index 說(shuō)明使用索引掃描使用 using where 過(guò)濾結(jié)果集。這個(gè)是優(yōu)化器的自以為的最優(yōu)選擇，但是實(shí)際上遇到數(shù)據(jù)集合比較大的表，該執(zhí)行計(jì)劃就不是最優(yōu)解，反而導(dǎo)致慢查。

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 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 關(guān)閉該參數(shù)

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

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 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)

經(jīng)過(guò)調(diào)整之后，查看執(zhí)行計(jì)劃發(fā)現(xiàn)優(yōu)化器選擇 id2 索引字段找到記錄做過(guò)濾，并且使用了ICP 特性，減少物理 io 請(qǐng)求，而不是選擇使用主鍵 id1 遍歷索引然后回表查詢。

顯然通過(guò)人為介入?yún)?shù)調(diào)整優(yōu)化器的行為能帶來(lái)更好的優(yōu)化效果。