搞定面試官 - MySQL 索引為什么會(huì)失效?

大家好，我是程序員啊粥，前邊給大家分享了 MySQL InnoDB 索引模型 、為什么 Delete 刪除數(shù)據(jù)后表文件大小并沒有變化、如何計(jì)算一個(gè)索引的長(zhǎng)度以及如何查看 SQL 的執(zhí)行計(jì)劃 以上幾篇都是偏理論知識(shí)，從今天開始，我們開始 MySQL 索引實(shí)戰(zhàn)內(nèi)容，具體介紹一下 MySQL 索引的用法以及索引為什么會(huì)失效。

首先介紹一下索引的相關(guān)語法：

創(chuàng)建索引的語法

    
      -- 創(chuàng)建索引
    
    
CREATE INDEX indexName ON table_name (column_name);
    
ALTER table tableName ADD INDEX indexName(columnName);
    

      -- 刪除索引
    
    
DROP INDEX [indexName] ON mytable;
  

語法還是非常簡(jiǎn)單的，沒什么太多說的，遵循相關(guān)語法規(guī)定即可，當(dāng)然你也可以使用相關(guān)的一些 MySQL 客戶端管理工具去創(chuàng)建，比如 Navicat 等。

下邊介紹一下具體的一些使用語法：

索引為什么會(huì)失效

今天的實(shí)戰(zhàn)內(nèi)容以如下表為例：

    CREATE TABLE `tb_item` (
    
  `id` bigint NOT NULL COMMENT '書籍id，同時(shí)也是書籍編號(hào)',
    
  `title` varchar(100) NOT NULL COMMENT '書籍名稱',
    
  `sell_point` varchar(500) DEFAULT NULL COMMENT '書籍賣點(diǎn)',
    
  `price` bigint NOT NULL COMMENT '書籍價(jià)格，單位為：分',
    
  `num` int NOT NULL COMMENT '庫(kù)存數(shù)量',
    
  `barcode` varchar(30) DEFAULT NULL COMMENT '書籍條形碼',
    
  `image` varchar(500) DEFAULT NULL COMMENT '書籍圖片',
    
  `cid` bigint NOT NULL COMMENT '所屬類目，葉子類目',
    
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '書籍狀態(tài)，1-正常，2-下架，3-刪除',
    
  `created` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
    
  `updated` datetime NOT NULL COMMENT '更新時(shí)間',
    
  `upload_id` bigint DEFAULT NULL,
    
  PRIMARY KEY (`id`),
    
  KEY `cid` (`cid`),
    
  KEY `status` (`status`),
    
  KEY `updated` (`updated`),
    
  KEY `tb_item_title_price_num` (`title`,`price`,`num`)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='書籍表';
  

InnoDB 索引因?yàn)槭褂昧?B+ 樹數(shù)據(jù)結(jié)構(gòu)，所以在使用上我們就需要了解這種結(jié)構(gòu)，具體你可以回顧我前邊這篇文章：MySQL InnoDB 索引模型。

也正是因?yàn)槭褂昧诉@種結(jié)構(gòu)，所以在使用上我們需要遵循一些原則，才能讓索引不失效。

最左前綴法則

如果你是建立聯(lián)合索引，那么我們?cè)谑褂貌樵儣l件的時(shí)候，需要從這個(gè)索引的最左列開始，并且不跳過索引中的列；如果跳躍某一列，那么就會(huì)造成索引部分失效；比如你建立的聯(lián)合索引字段是 (a, b , c)，那么，你的查詢條件就必須是 where a = and b = and c = 這樣的格式(具體 a b c 還是 c b a 的順序不會(huì)影響索引，MySQL 優(yōu)化器會(huì)自動(dòng)優(yōu)化這種順序)；

當(dāng)然，你如果直接把前綴去掉的話，那整個(gè)索引就會(huì)失效，不再是部分失效。

比如上述表，我們使用查詢語句為 explain select * from tb_item where price = 45 and num = 23232 ;

我們建立的索引字段是 title, price, num，但是我們查詢條件直接跳過了 title 這個(gè)字段，使用 explain 可以看到這條 SQL 的執(zhí)行計(jì)劃，key 的值是 Null，意味著這句 SQL 沒有利用到索引，而是走了全表掃描。

那么我們最合理的使用，就是使用最左前綴匹配，查詢條件改成這樣： explain select * from tb_item where title = '編譯原理' and price = 45 and num = 23232 ;

再來看一下執(zhí)行計(jì)劃，我們看到 key 這倆變?yōu)榱? tb_item_title_price_num ，同時(shí)索引長(zhǎng)度為 314 ，證明是使用到了聯(lián)合索引 tb_item_title_price_num 的三個(gè)完整字段的(關(guān)于索引長(zhǎng)度的計(jì)算方式可以參考這篇文章)

title 字段的索引長(zhǎng)度是 3 * 100 + 0 + 2 = 302

price 字段的索引長(zhǎng)度是 ?8

num ? 字段的索引長(zhǎng)度是 4

tb_item_title_price_num 索引總共長(zhǎng)度是 302 + 8 + 4 = 314

接下來我們修改查詢條件為 explain select * from tb_item where title = '編譯原理' and num = 23232 ;

此時(shí) Key len 變?yōu)?302，說明只利用到了 title 的索引，因?yàn)椴樵儣l件跳過了 price 字段，導(dǎo)致部分索引失效。

同時(shí) Extra 為 Using index condition ，說明使用了索引，但是需要回表查詢數(shù)據(jù)。

覆蓋索引

在索引使用過程中，尤其是聯(lián)合索引的使用中，我們?nèi)绾魏侠淼慕⑺饕?，再加上合理的查詢條件的話，我們是可以使用到覆蓋索引的，減少回表次數(shù)，也就是減少了 IO 次數(shù)，可以成倍的提高查詢效率。

下邊我們來演示下使用覆蓋索引的情況，比如使用如下查詢語句： explain select id, title from tb_item where title = '編譯原理' and num = 12000 ;

這個(gè)時(shí)候我們可以看到 Extra 值為： Using where; Using index ，這意味著這次查詢時(shí)使用了索引的，同時(shí)因?yàn)橐樵兊牧幸呀?jīng)在索引中可以直接獲取到，所以不需要回表去獲取數(shù)據(jù)，可以直接在索引中找到需要的字段，這也是一般要求不允許 select * 查詢的原因，因?yàn)檫@樣的話需要獲取所有字段，沒法利用覆蓋索引來提高效率。

關(guān)于執(zhí)行計(jì)劃中 Extra 字段的說明，參考我之前的這篇文文章。

Extra 字段說明：

using index ：使用覆蓋索引的時(shí)候就會(huì)出現(xiàn) using where：在查找使用索引的情況下，需要回表去查詢所需的數(shù)據(jù) using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù) using index ; using where：查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表。

同時(shí)，阿里開發(fā)規(guī)范中對(duì)于索引規(guī)范的建議，也是有對(duì)于覆蓋索引的說明的：

范圍查詢

在實(shí)際開發(fā)中，范圍查詢也是我們需要經(jīng)常使用的一個(gè)東西，比如統(tǒng)計(jì)過去 3 天、過去 7 天的用戶量等等。

但這個(gè)時(shí)候有個(gè)問題需要注意，那就是在使用范圍查詢的時(shí)候，范圍查詢 右邊的 列索引會(huì)失效。

比如下圖中的幾種查詢條件，我們可以看到寫法上差不多的，但是最后索引字段的長(zhǎng)度是完全不一樣的。

其中第一條 select id from tb_item where title = '編譯原理' and price = 56 and num = 10000 ,是我們常用的等值查詢，這在上一步最左前綴的時(shí)候已經(jīng)說了，肯定是可以完整用到索引的，執(zhí)行計(jì)劃也驗(yàn)證了我們的結(jié)論。

第二條查詢語句 select id from tb_item where title = '編譯原理' and price > 56 and num = 10000 , 我們使用了范圍查詢，這個(gè)時(shí)候可以看到 key_len 變成了 310，這說明部分索引失效了，也就是范圍查詢右邊的列，num 這個(gè)列的索引失效了。

第三條查詢語句 select id from tb_item where title = '編譯原理' and price >= 56 and num = 10000 ，是一種很好的規(guī)避這種索引失效的一種手段，在業(yè)務(wù)允許的情況下我們可以使用大于等于或者小于等于來代替大于或者小于，這種情況下是可以完整使用到索引的。

索引列運(yùn)算

我遇到過很多開發(fā)人員，會(huì)在 SQL 中摻雜運(yùn)算，這在你的數(shù)據(jù)量不大的前提下，確實(shí)可以為你提供方便，但是一旦你的數(shù)據(jù)量起來之后，你如果在索引列上做計(jì)算，這會(huì)直接導(dǎo)致索引的失效，進(jìn)而引發(fā)全表掃描。

因?yàn)?MySQL 在做索引的時(shí)候是對(duì)你的字段值本身做索引，而不是對(duì)你運(yùn)算后的值做索引，你可以回顧下關(guān)于 B+ 樹的索引模型，TODO，

所以我們?cè)趯?shí)際使用中需要徹底避免在索引列上做計(jì)算，因?yàn)闆]有任何一個(gè)理由支持我們必須要這么做。

比如這個(gè)查詢語句 explain select * from tb_item where substring(title,4, 4) = '組成原理';

看一下它的執(zhí)行計(jì)劃：

可以看到是完全沒有用到索引的，直接開始全表掃描，你試想一下，假如你的表就幾十上百萬數(shù)據(jù)，這一個(gè)全表掃描下去，你怕是半夜都不敢睡覺吧。

字符串字段不加引號(hào)

MySQL 在索引查詢中，會(huì)自動(dòng)的進(jìn)行的字段類型轉(zhuǎn)換，如果我們對(duì)于一個(gè)數(shù)字格式的字符串字段，在查詢的時(shí)候沒有用單引號(hào)，那么會(huì)觸發(fā) MySQL 查詢優(yōu)化器的類型自動(dòng)轉(zhuǎn)換。

比如你有張表存的是手機(jī)號(hào)，字段叫 phone，然后針對(duì)查詢語句 select id from tb_user where phone = 1888888888 ，那怕你在 phone 字段上額外建了索引，它也是不會(huì)走索引的。

因?yàn)檫@條語句在查詢優(yōu)化器的處理下會(huì)變成 select id from tb_user where cast(phone as signed int) = 1888888888 去執(zhí)行。

這個(gè)時(shí)候因?yàn)閷?duì)索引列做了函數(shù)運(yùn)算，就導(dǎo)致了索引的失效。

模糊查詢

關(guān)于模糊查詢，這個(gè)也就等同于最左前綴原則，你如果是在字段的頭部位置進(jìn)行模糊搜索的話，首先不遵循最左前綴匹配原則，那索引自然就失效了。

反之，如果是尾部字段進(jìn)行模糊匹配的話，那么索引還是同樣生效的。

因此，我們真的需要模糊搜索功能的話，最佳的方式是使用搜索引擎，而不是在 MySQL 中直接 like 查詢。

Or 連接條件

用 or 分割開的條件，如果 or 前的條件列中有索引，而后面的列中沒有索引，那么索引會(huì)失效，不管是這兩個(gè)字段中的任何一個(gè)索引，都會(huì)失效。

比如我們這張表 tb_item 表中 barcode 列沒有索引，使用如下查詢語句

explain select id, title from tb_item where title = '編譯原理' or barcode = '202457815';

通過執(zhí)行計(jì)劃可以看到，索引全部失效了。

反之，如果 or 兩邊的字段都有索引，則索引依然可以生效

explain select id, title from tb_item where title = '編譯原理' or price = 128;

數(shù)據(jù)分布的影響

其實(shí)前邊說了好幾個(gè)原則，但是在具體使用中，我們還是需要用實(shí)際情況來分析，首先如何選擇索引是 MySQL 自己做的事情，如果 MySQL 評(píng)估使用索引會(huì)比全表更慢，則不使用索引。

那么，什么情況下它評(píng)估使用索引還不如直接全表掃描呢？

常見的一種情況是表中的數(shù)據(jù)分析分布，如果這個(gè)字段的值區(qū)分度不夠明顯，那么 MySQL 極有可能進(jìn)行全表掃描。

比如使用這條查詢語句 explain select * from tb_item where title = '高等數(shù)學(xué)';

執(zhí)行計(jì)劃顯示可以看到是沒有走索引的，按理來說我們建立了聯(lián)合索引 tb_item_title_price_num ( title , price , num ) ，同時(shí)也遵循最左前綴匹配原則，是可以走到索引的，可現(xiàn)在的執(zhí)行計(jì)劃說沒有用到索引。

接下來我們修改查詢條件為： explain select * from tb_item where title = '編譯原理';

可以看到同樣的查詢語句，只不過是值不同，就會(huì)造成一個(gè)索引生效，一個(gè)索引失效，究其原因，是因?yàn)楸碇?title 為'高等數(shù)學(xué)'的數(shù)據(jù)占比太多，MySQL 判斷與其走索引還不如直接全表掃描，所以索引失效了。

可以看到，表中總共 841 行數(shù)據(jù)，其中高等數(shù)據(jù)就占了 803 條。

前綴索引

前邊我有篇文章提到過，InnoDB 引擎對(duì)于索引的字段長(zhǎng)度是有限制的，TODO，所以在我們遇到字段類型過長(zhǎng)的時(shí)候，可以截取一部分來建立索引，從而節(jié)約索引空間，提高查詢效率。

關(guān)于前綴索引，我們需要明確以下幾年內(nèi)容：

1. 創(chuàng)建索引，指定索引長(zhǎng)度語法：create index idx_xxx on table_name(column(n))

2. 前綴長(zhǎng)度的選擇：可以根據(jù)索引的選擇性來決定，選擇性越高則查詢效率越高，唯一索引的選擇性是 1 ，這是最好的索引選擇性，性能也是最好的。

3. 索引列區(qū)分度查詢，類似如下

• select count(distinct email)/count(*) from tb_user ;

• select count(distinct substring(email,1,5))/cont(*) from tb_user

前綴索引的好處：

• 使用前綴索引，定義好長(zhǎng)度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。

前綴索引的缺點(diǎn)

• 使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素

阿里開發(fā)規(guī)范中對(duì)于前綴索引的規(guī)約說明

今天的內(nèi)容到此就要結(jié)束了，簡(jiǎn)單總結(jié)一下：

關(guān)于索引失效和索引使用原則，需要遵循最最前綴匹配原則，這是 B+ 樹的索引模型決定的。此外，不當(dāng)?shù)氖褂梅绞?，?huì)造成索引的部分失效，比如范圍查詢、字符串不加引號(hào)，使用索引列字段進(jìn)行函數(shù)運(yùn)算以及使用 Or 查詢條件時(shí)其中某個(gè)字段沒有索引等等。

內(nèi)容比較多，而且是偏實(shí)戰(zhàn)型的，雖然我提供了具體的示例，但還是希望你能自己動(dòng)手操作一遍，這樣才能記得更牢靠，下次看見面試官你就直接糊他臉上。

大家好，我是程序員啊粥，關(guān)注我，我們一起在技術(shù)的世界中向上生長(zhǎng)。