前言：

有讀者可能會一臉懵？

啥是索引潛水？

你給起的名字的嗎？有沒有索引蛙泳？

這個名字還真不是我起的，今天要講的知識點就叫索引潛水（Index dive） 。

先要從一件怪事說起：

我先造點數(shù)據(jù)復現(xiàn)一下問題，創(chuàng)建一張用戶表：

CREATE TABLE `user` (
 ?`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 ?`name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
 ?`age` int(11) NOT NULL DEFAULT 0 COMMENT '年齡',
 ?PRIMARY KEY (`id`),
 ?KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通過一批用戶年齡，查詢該年齡的用戶信息，并查看一下SQL執(zhí)行計劃：

explain select * from user 
where age in (1,2,3,4,5,6,7,8,9);

where條件中有9個參數(shù)，重點關(guān)注一下執(zhí)行計劃中的預估掃描行數(shù)為279行。

到這里沒什么問題，預估的非常準，實際就是279行。

但是，問題來了，當我們在where條件中，再加一個參數(shù)，變成了10個參數(shù)，預估掃描行數(shù)本應該增加，結(jié)果卻大大減少了。

explain select * from user
where age in (1,2,3,4,5,6,7,8,9,10);

一下子減少到了30行，可是實際行數(shù)是多少呢？

實際是310行，預估掃描行數(shù)是30行，真是錯到姥姥家了。

MySQL咋回事啊，到底還能不能預估？

不能預估的話，換其他人！

大家肯定也是滿臉疑惑，直到我去官網(wǎng)上看到了一個詞語，索引潛水（Index dive） 。

跟這個詞語相關(guān)的，還有一個配置參數(shù) eq_range_index_dive_limit。

MySQL5.7.3之前的版本，這個值默認是10，之后的版本，這個值默認是200。

可以使用命令查看一下這個值的大小：

show variables like '%eq_range_index_dive_limit%';

當然，我們也可以手動修改這個值的大小：

set eq_range_index_dive_limit=200;

這個 eq_range_index_dive_limit 配置的作用就是：

當where語句in條件中參數(shù)個數(shù)小于這個值的時候，MySQL就采用索引潛水（Index dive） 的方式預估掃描行數(shù)，非常準確。

當where語句in條件中參數(shù)個數(shù)大于等于這個值的時候，MySQL就采用另一種方式索引統(tǒng)計（Index statistics） 預估掃描行數(shù)，誤差較大。

MySQL為什么要這么做呢？

都用索引潛水（Index dive） 的方式預估掃描行數(shù)，不好嗎？

其實這是基于成本的考慮，索引潛水估算成本較高，適合小數(shù)據(jù)量。索引統(tǒng)計估算成本較低，適合大數(shù)據(jù)量。

一般情況下，我們的where語句的in條件的參數(shù)不會太多，適合使用索引潛水預估掃描行數(shù)。

建議還在使用MySQL5.7.3之前版本的同學們，手動修改一下索引潛水的配置參數(shù)，改成合適的數(shù)值。

如果你們項目中in條件最多有500個參數(shù)，就把配置參數(shù)改成501。

這樣MySQL預估掃描行數(shù)更準確，可以選擇更合適的索引。

到此這篇關(guān)于MySQL查詢性能優(yōu)化七種方式索引潛水的文章就介紹到這了,更多相關(guān)MySQL查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論