不經(jīng)一番寒徹骨，怎得梅花撲鼻香閱讀這篇文章大概需要20分鐘！

大家好前面我們介紹一下關(guān)于實(shí)戰(zhàn)的知識(shí)點(diǎn)。主要應(yīng)用于表數(shù)據(jù)比較多的情況下，如何巧妙地從中取出幾條數(shù)據(jù)。今天介紹一下索引失效的幾種不好發(fā)現(xiàn)的情況。

聲明一下跟丁奇老師學(xué)習(xí)的，自己做一個(gè)技術(shù)總結(jié)

說(shuō)到索引失效，今天主要從三個(gè)方向分析。函數(shù)操作，隱式類型轉(zhuǎn)換，隱式字符編碼轉(zhuǎn)換。這里不包含like，!=等

函數(shù)操作

假設(shè)你現(xiàn)在維護(hù)了一個(gè)交易系統(tǒng)，其中交易記錄表 tradelog 包含交易流水號(hào)（tradeid）、交易員 id（operator）、交易時(shí)間（t_modified）等字段。為了便于描述，我們先忽略其他字段。這個(gè)表的建表語(yǔ)句如下：

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假設(shè)，現(xiàn)在已經(jīng)記錄了從 2016 年初到 2018 年底的所有數(shù)據(jù)，運(yùn)營(yíng)部門有一個(gè)需求是，要統(tǒng)計(jì)發(fā)生在所有年份中 7 月份的交易記錄總數(shù)。這個(gè)邏輯看上去并不復(fù)雜，你的 SQL 語(yǔ)句可能會(huì)這么寫：

mysql> select count(*) from tradelog where month(t_modified)=7;

由于 t_modified 字段上有索引，于是你就很放心地在生產(chǎn)庫(kù)中執(zhí)行了這條語(yǔ)句，但卻發(fā)現(xiàn)執(zhí)行了特別久，才返回了結(jié)果。

如果你問(wèn) DBA 同事為什么會(huì)出現(xiàn)這樣的情況，他大概會(huì)告訴你：如果對(duì)字段做了函數(shù)計(jì)算，就用不上索引了，這是 MySQL 的規(guī)定。

現(xiàn)在你已經(jīng)學(xué)過(guò)了 InnoDB 的索引結(jié)構(gòu)了，可以再追問(wèn)一句為什么？為什么條件是 where t_modified='2018-7-1’的時(shí)候可以用上索引，而改成 where month(t_modified)=7 的時(shí)候就不行了？

下面是這個(gè) t_modified 索引的示意圖。方框上面的數(shù)字就是 month() 函數(shù)對(duì)應(yīng)的值。

如果你的 SQL 語(yǔ)句條件用的是 where t_modified='2018-7-1’的話，引擎就會(huì)按照上面綠色箭頭的路線，快速定位到 t_modified='2018-7-1’需要的結(jié)果。

實(shí)際上，B+ 樹提供的這個(gè)快速定位能力，來(lái)源于同一層兄弟節(jié)點(diǎn)的有序性。

但是，如果計(jì)算 month() 函數(shù)的話，你會(huì)看到傳入 7 的時(shí)候，在樹的第一層就不知道該怎么辦了。

也就是說(shuō)，對(duì)索引字段做函數(shù)操作，可能會(huì)破壞索引值的有序性，因此優(yōu)化器就決定放棄走樹搜索功能。

需要注意的是，優(yōu)化器并不是要放棄使用這個(gè)索引。

在這個(gè)例子里，放棄了樹搜索功能，優(yōu)化器可以選擇遍歷主鍵索引，也可以選擇遍歷索引 t_modified，優(yōu)化器對(duì)比索引大小后發(fā)現(xiàn)，索引 t_modified 更小，遍歷這個(gè)索引比遍歷主鍵索引來(lái)得更快。因此最終還是會(huì)選擇索引 t_modified。

接下來(lái)，我們使用 explain 命令，查看一下這條 SQL 語(yǔ)句的執(zhí)行結(jié)果。

key="t_modified"表示的是，使用了 t_modified 這個(gè)索引；我在測(cè)試表數(shù)據(jù)中插入了 10 萬(wàn)行數(shù)據(jù)，rows=100335，說(shuō)明這條語(yǔ)句掃描了整個(gè)索引的所有值；Extra 字段的 Using index，表示的是使用了覆蓋索引。

也就是說(shuō)，由于在 t_modified 字段加了 month() 函數(shù)操作，導(dǎo)致了全索引掃描。為了能夠用上索引的快速定位能力，我們就要把 SQL 語(yǔ)句改成基于字段本身的范圍查詢。按照下面這個(gè)寫法，優(yōu)化器就能按照我們預(yù)期的，用上 t_modified 索引的快速定位能力了。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

當(dāng)然，如果你的系統(tǒng)上線時(shí)間更早，或者后面又插入了之后年份的數(shù)據(jù)的話，你就需要再把其他年份補(bǔ)齊。

到這里我給你說(shuō)明了，由于加了 month() 函數(shù)操作，MySQL 無(wú)法再使用索引快速定位功能，而只能使用全索引掃描。

不過(guò)優(yōu)化器在個(gè)問(wèn)題上確實(shí)有“偷懶”行為，即使是對(duì)于不改變有序性的函數(shù)，也不會(huì)考慮使用索引。比如，對(duì)于 select * from tradelog where id + 1 = 10000 這個(gè) SQL 語(yǔ)句，這個(gè)加 1 操作并不會(huì)改變有序性，但是 MySQL 優(yōu)化器還是不能用 id 索引快速定位到 9999 這一行。所以，需要你在寫 SQL 語(yǔ)句的時(shí)候，手動(dòng)改寫成 where id = 10000 -1 才可以。

隱式類型轉(zhuǎn)換

我們一起看一下這條 SQL 語(yǔ)句：

mysql> select * from tradelog where tradeid=110717;

交易編號(hào) tradeid 這個(gè)字段上，本來(lái)就有索引，但是 explain 的結(jié)果卻顯示，這條語(yǔ)句需要走全表掃描。你可能也發(fā)現(xiàn)了，tradeid 的字段類型是 varchar(32)，而輸入的參數(shù)卻是整型，所以需要做類型轉(zhuǎn)換。

那么，現(xiàn)在這里就有兩個(gè)問(wèn)題：

數(shù)據(jù)類型轉(zhuǎn)換的規(guī)則是什么？
為什么有數(shù)據(jù)類型轉(zhuǎn)換，就需要走全索引掃描？

先來(lái)看第一個(gè)問(wèn)題，你可能會(huì)說(shuō)，數(shù)據(jù)庫(kù)里面類型這么多，這種數(shù)據(jù)類型轉(zhuǎn)換規(guī)則更多，我記不住，應(yīng)該怎么辦呢？

這里有一個(gè)簡(jiǎn)單的方法，看 select “10” > 9 的結(jié)果：

如果規(guī)則是“將字符串轉(zhuǎn)成數(shù)字”，那么就是做數(shù)字比較，結(jié)果應(yīng)該是 1；
如果規(guī)則是“將數(shù)字轉(zhuǎn)成字符串”，那么就是做字符串比較，結(jié)果應(yīng)該是 0。

從圖中可知，select “10” > 9 返回的是 1，所以你就能確認(rèn) MySQL 里的轉(zhuǎn)換規(guī)則了：在 MySQL 中，字符串和數(shù)字做比較的話，是將字符串轉(zhuǎn)換成數(shù)字。

這時(shí)，你再看這個(gè)全表掃描的語(yǔ)句：

mysql> select * from tradelog where tradeid=110717;

就知道對(duì)于優(yōu)化器來(lái)說(shuō)，這個(gè)語(yǔ)句相當(dāng)于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是說(shuō)，這條語(yǔ)句觸發(fā)了我們上面說(shuō)到的規(guī)則：對(duì)索引字段做函數(shù)操作，優(yōu)化器會(huì)放棄走樹搜索功能。

現(xiàn)在，我留給你一個(gè)小問(wèn)題，id 的類型是 int，如果執(zhí)行下面這個(gè)語(yǔ)句，是否會(huì)導(dǎo)致全表掃描呢？

select * from tradelog where id="83126";

你可以先自己分析一下，再到數(shù)據(jù)庫(kù)里面去驗(yàn)證確認(rèn)。

接下來(lái)，我們?cè)賮?lái)看一個(gè)稍微復(fù)雜點(diǎn)的例子。

隱式字符編碼轉(zhuǎn)換

假設(shè)系統(tǒng)里還有另外一個(gè)表 trade_detail，用于記錄交易的操作細(xì)節(jié)。為了便于量化分析和復(fù)現(xiàn)，我往交易日志表 tradelog 和交易詳情表 trade_detail 這兩個(gè)表里插入一些數(shù)據(jù)。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步驟*/
  `step_info` varchar(32) DEFAULT NULL, /*步驟信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

這時(shí)候，如果要查詢 id=2 的交易的所有操作步驟信息，SQL 語(yǔ)句可以這么寫：

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*語(yǔ)句Q1*/

運(yùn)行結(jié)果是：

第一行顯示優(yōu)化器會(huì)先在交易記錄表 tradelog 上查到 id=2 的行，這個(gè)步驟用上了主鍵索引，rows=1 表示只掃描一行；
第二行 key=NULL，表示沒(méi)有用上交易詳情表 trade_detail 上的 tradeid 索引，進(jìn)行了全表掃描。

在這個(gè)執(zhí)行計(jì)劃里，是從 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查詢匹配字段。因此，我們把 tradelog 稱為驅(qū)動(dòng)表，把 trade_detail 稱為被驅(qū)動(dòng)表，把 tradeid 稱為關(guān)聯(lián)字段。

接下來(lái)，我們看下這個(gè) explain 結(jié)果表示的執(zhí)行流程：

第 1 步，是根據(jù) id 在 tradelog 表里找到 L2 這一行；
第 2 步，是從 L2 中取出 tradeid 字段的值；
第 3 步，是根據(jù) tradeid 值到 trade_detail 表中查找條件匹配的行。explain 的結(jié)果里面第二行的 key=NULL 表示的就是，這個(gè)過(guò)程是通過(guò)遍歷主鍵索引的方式，一個(gè)一個(gè)地判斷 tradeid 的值是否匹配。

進(jìn)行到這里，你會(huì)發(fā)現(xiàn)第 3 步不符合我們的預(yù)期。因?yàn)楸?trade_detail 里 tradeid 字段上是有索引的，我們本來(lái)是希望通過(guò)使用 tradeid 索引能夠快速定位到等值的行。但，這里并沒(méi)有。

如果你去問(wèn) DBA 同學(xué)，他們可能會(huì)告訴你，因?yàn)檫@兩個(gè)表的字符集不同，一個(gè)是 utf8，一個(gè)是 utf8mb4，所以做表連接查詢的時(shí)候用不上關(guān)聯(lián)字段的索引。這個(gè)回答，也是通常你搜索這個(gè)問(wèn)題時(shí)會(huì)得到的答案。

但是你應(yīng)該再追問(wèn)一下，為什么字符集不同就用不上索引呢？

我們說(shuō)問(wèn)題是出在執(zhí)行步驟的第 3 步，如果單獨(dú)把這一步改成 SQL 語(yǔ)句的話，那就是：

mysql> select * from trade_detail where tradeid=$L2.tradeid.value;

其中，$L2.tradeid.value 的字符集是 utf8mb4。

參照前面的兩個(gè)例子，你肯定就想到了，字符集 utf8mb4 是 utf8 的超集，所以當(dāng)這兩個(gè)類型的字符串在做比較的時(shí)候，MySQL 內(nèi)部的操作是，先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集，再做比較。

這個(gè)設(shè)定很好理解，utf8mb4 是 utf8 的超集。類似地，在程序設(shè)計(jì)語(yǔ)言里面，做自動(dòng)類型轉(zhuǎn)換的時(shí)候，為了避免數(shù)據(jù)在轉(zhuǎn)換過(guò)程中由于截?cái)鄬?dǎo)致數(shù)據(jù)錯(cuò)誤，也都是“按數(shù)據(jù)長(zhǎng)度增加的方向”進(jìn)行轉(zhuǎn)換的。

因此，在執(zhí)行上面這個(gè)語(yǔ)句的時(shí)候，需要將被驅(qū)動(dòng)數(shù)據(jù)表里的字段一個(gè)個(gè)地轉(zhuǎn)換成 utf8mb4，再跟 L2 做比較。

也就是說(shuō)，實(shí)際上這個(gè)語(yǔ)句等同于下面這個(gè)寫法：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

CONVERT() 函數(shù)，在這里的意思是把輸入的字符串轉(zhuǎn)成 utf8mb4 字符集。這就再次觸發(fā)了我們上面說(shuō)到的原則：對(duì)索引字段做函數(shù)操作，優(yōu)化器會(huì)放棄走樹搜索功能。

到這里，你終于明確了，字符集不同只是條件之一，連接過(guò)程中要求在被驅(qū)動(dòng)表的索引字段上加函數(shù)操作，是直接導(dǎo)致對(duì)被驅(qū)動(dòng)表做全表掃描的原因。

作為對(duì)比驗(yàn)證，我給你提另外一個(gè)需求，“查找 trade_detail 表里 id=4 的操作，對(duì)應(yīng)的操作者是誰(shuí)”，再來(lái)看下這個(gè)語(yǔ)句和它的執(zhí)行計(jì)劃。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

這個(gè)語(yǔ)句里 trade_detail 表成了驅(qū)動(dòng)表，但是 explain 結(jié)果的第二行顯示，這次的查詢操作用上了被驅(qū)動(dòng)表 tradelog 里的索引 (tradeid)，掃描行數(shù)是 1。這也是兩個(gè) tradeid 字段的 join 操作，為什么這次能用上被驅(qū)動(dòng)表的 tradeid 索引呢？我們來(lái)分析一下。

假設(shè)驅(qū)動(dòng)表 trade_detail 里 id=4 的行記為 R4，那么在連接的時(shí)候（圖 5 的第 3 步），被驅(qū)動(dòng)表 tradelog 上執(zhí)行的就是類似這樣的 SQL 語(yǔ)句：

select operator from tradelog  where traideid =$R4.tradeid.value;

這時(shí)候 $R4.tradeid.value 的字符集是 utf8, 按照字符集轉(zhuǎn)換規(guī)則，要轉(zhuǎn)成 utf8mb4，所以這個(gè)過(guò)程就被改寫成：

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

你看，這里的 CONVERT 函數(shù)是加在輸入?yún)?shù)上的，這樣就可以用上被驅(qū)動(dòng)表的 traideid 索引。理解了原理以后，就可以用來(lái)指導(dǎo)操作了。如果要優(yōu)化語(yǔ)句有兩種辦法

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

比較常見的優(yōu)化方法是，把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，這樣就沒(méi)有字符集轉(zhuǎn)換的問(wèn)題了。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

如果能夠修改字段的字符集的話，是最好不過(guò)了。但如果數(shù)據(jù)量比較大，或者業(yè)務(wù)上暫時(shí)不能做這個(gè) DDL 的話，那就只能采用修改 SQL 語(yǔ)句的方法了。

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

這里，我主動(dòng)把 l.tradeid 轉(zhuǎn)成 utf8，就避免了被驅(qū)動(dòng)表上的字符編碼轉(zhuǎn)換，從 explain 結(jié)果可以看到，這次索引走對(duì)了。

總結(jié)

這里介紹的是MySQL索引失效的三種類型。通過(guò)介紹案例，再到SQL分析最后到結(jié)論總結(jié)。如果有不清楚的可以私信我

探討一下索引失效的幾種場(chǎng)景

函數(shù)操作

隱式類型轉(zhuǎn)換

隱式字符編碼轉(zhuǎn)換

總結(jié)