大家好，我是程序員啊粥商佑，前邊給大家分享了

• MySQL InnoDB 索引模型
• 在 MySQL InnoDB 中锯茄，為什么 delete 刪除數(shù)據(jù)之后表數(shù)據(jù)文件大小沒有變
• 如何計算一個索引的長度
• 如何查看 SQL 的執(zhí)行計劃

以上幾篇都是偏理論知識，從今天開始茶没，我們開始 MySQL 索引實戰(zhàn)內(nèi)容肌幽，具體介紹一下 MySQL 索引的用法。

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

索引語法

-- 創(chuàng)建索引
CREATE INDEX indexName ON table_name (column_name);
ALTER table tableName ADD INDEX indexName(columnName);
-- 刪除索引
DROP INDEX [indexName] ON mytable; 

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

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

索引使用

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

CREATE TABLE `tb_item` (
  `id` bigint NOT NULL COMMENT '書籍id，同時也是書籍編號',
  `title` varchar(100) NOT NULL COMMENT '書籍名稱',
  `sell_point` varchar(500) DEFAULT NULL COMMENT '書籍賣點',
  `price` bigint NOT NULL COMMENT '書籍價格涣易，單位為：分',
  `num` int NOT NULL COMMENT '庫存數(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)建時間',
  `updated` datetime NOT NULL COMMENT '更新時間',
  `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='書籍表';

image.png

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

也正是因為使用了這種結(jié)構(gòu)，所以在使用上我們需要遵循一些原則隆嗅，才能讓索引不失效界阁。

最左前綴法則

如果你是建立聯(lián)合索引，那么我們在使用查詢條件的時候胖喳，需要從這個索引的最左列開始泡躯，并且不跳過索引中的列；如果跳躍某一列丽焊，那么就會造成索引部分失效较剃；比如你建立的聯(lián)合索引字段是 (a, b , c)，那么技健，你的查詢條件就必須是 where a = and b = and c = 這樣的格式(具體 a b c 還是 c b a 的順序不會影響索引写穴，MySQL 優(yōu)化器會自動優(yōu)化這種順序)；

當(dāng)然雌贱，你如果直接把前綴去掉的話啊送，那整個索引就會失效偿短，不再是部分失效。

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

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

image.png

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

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

image.png

索引長度的計算公式：

因為聯(lián)合索引的結(jié)構(gòu)特點缚够， 我們需要確認(rèn)命中索引 tb_item_title_price_num 是命中了 title 列幔妨、price 列，還是 num 列谍椅。

想要會分析误堡，就需要掌握索引長度的計算方法了。

1雏吭、索引長度公式

1. 所有的索引字段锁施，如果沒有設(shè)置 not null，則需要加一個字節(jié)杖们。

2. 定長字段悉抵，int 占四個字節(jié)、date 占三個字節(jié)摘完、char(n) 占 n 個字符姥饰。

3. 對于變成字段 varchar(n)，則有 n 個字符 + 兩個字節(jié)孝治。

4. 不同的字符集列粪，一個字符占用的字節(jié)數(shù)不同。latin1 編碼的谈飒，一個字符占用一個字節(jié)岂座，gbk 編碼的，一個字符占用兩個字節(jié)步绸，utf8 編碼的掺逼，一個字符占用三個字節(jié)吃媒，utf8mb4 編碼的瓤介，一個字符占四個字節(jié)

5. 索引長度 char()吕喘、varchar() 索引長度的計算公式：

Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長度 + 1(允許 null) + 2(變長列)

綜上可得：上述 tb_item 表中，使用的 utf8 編碼

所以 title 字段的索引長度是 3 * 100 + 0 + 2 = 302

price 字段的索引長度是 8

num 字段的索引長度是 4

tb_item_title_price_num 索引總共長度是 302 + 8 + 4 = 314

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

此時 Key len 變?yōu)?302刑桑，說明只利用到了 title 的索引氯质，因為查詢條件跳過了 price 字段，導(dǎo)致部分索引失效祠斧。

image.png

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

覆蓋索引

在索引使用過程中辕漂，尤其是聯(lián)合索引的使用中，我們?nèi)绾魏侠淼慕⑺饕獬偌由虾侠淼牟樵儣l件的話钉嘹，我們是可以使用到覆蓋索引的，減少回表次數(shù)鲸阻，也就是減少了 IO 次數(shù)跋涣，可以成倍的提高查詢效率。

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

image.png

這個時候我們可以看到 Extra 值為：Using where; Using index陈辱，這意味著這次查詢時使用了索引的，同時因為要查詢的列已經(jīng)在索引中可以直接獲取到细诸，所以不需要回表去獲取數(shù)據(jù)沛贪，可以直接在索引中找到需要的字段，這也是一般要求不允許 select * 查詢的原因震贵，因為這樣的話需要獲取所有字段鹏浅，沒法利用覆蓋索引來提高效率。

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

Extra 字段說明：

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

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

image.png

范圍查詢

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

但這個時候有個問題需要注意武学，那就是在使用范圍查詢的時候祭往，范圍查詢右邊的列索引會失效。

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

image.png

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

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

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

索引列運算

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

因為 MySQL 在做索引的時候是對你的字段值本身做索引折联，而不是對你運算后的值做索引润文，你可以回顧下關(guān)于 MySQL InnoDB B+ 樹的索引模型

所以我們在實際使用中需要徹底避免在索引列上做計算仿贬，因為沒有任何一個理由支持我們必須要這么做奕删。

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

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

image.png

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

字符串不加引號

MySQL 在索引查詢中苛萎，會自動的進(jìn)行的字段類型轉(zhuǎn)換，如果我們對于一個數(shù)字格式的字符串字段，在查詢的時候沒有用單引號腌歉，那么會觸發(fā) MySQL 查詢優(yōu)化器的類型自動轉(zhuǎn)換蛙酪。

比如你有張表存的是手機號，字段叫 phone究履，然后針對查詢語句 select id from tb_user where phone = 1888888888，那怕你在 phone 字段上額外建了索引脸狸，它也是不會走索引的最仑。

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

這個時候因為對索引列做了函數(shù)運算炊甲，就導(dǎo)致了索引的失效泥彤。

模糊查詢

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

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

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

or 連接條件

用 or 分割開的條件，如果 or 前的條件列中有索引俗批，而后面的列中沒有索引俗或，那么索引會失效，不管是這兩個字段中的任何一個索引岁忘，都會失效辛慰。

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

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

image.png

通過執(zhí)行計劃可以看到干像，索引全部失效了昆雀。

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

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

image.png

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

其實前邊說了好幾個原則狞膘，但是在具體使用中，我們還是需要用實際情況來分析什乙，首先如何選擇索引是 MySQL 自己做的事情挽封，如果 MySQL 評估使用索引會比全表更慢，則不使用索引臣镣。

那么辅愿，什么情況下它評估使用索引還不如直接全表掃描呢智亮？

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

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

image.png

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

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

image.png

可以看到同樣的查詢語句颠区，只不過是值不同，就會造成一個索引生效通铲，一個索引失效毕莱，究其原因，是因為表中 title 為'高等數(shù)學(xué)'的數(shù)據(jù)占比太多颅夺，MySQL 判斷與其走索引還不如直接全表掃描朋截，所以索引失效了。

image.png

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

前綴索引

前邊我有篇文章提到過稚字，InnoDB 引擎對于索引的字段長度是有限制的饲宿，TODO，所以在我們遇到字段類型過長的時候胆描，可以截取一部分來建立索引瘫想，從而節(jié)約索引空間，提高查詢效率昌讲。

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

1. 創(chuàng)建索引，指定索引長度語法：create index idx_xxx on table_name(column(n))
2. 前綴長度的選擇：可以根據(jù)索引的選擇性來決定短绸，選擇性越高則查詢效率越高车吹，唯一索引的選擇性是 1 醋闭，這是最好的索引選擇性，性能也是最好的证逻。
3. 索引列區(qū)分度查詢，類似如下
   • select count(distinct email)/count(*) from tb_user;
   • select count(distinct substring(email,1,5))/cont(*) from tb_user
4. 前綴索引的好處：
   • 使用前綴索引，定義好長度丈咐，就可以做到既節(jié)省空間棵逊，又不用額外增加太多的查詢成本辆影。
5. 前綴索引的缺點
   • 使用前綴索引就用不上覆蓋索引對查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時需要考慮的一個因素

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

image.png

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

關(guān)于索引失效和索引使用原則衅澈，需要遵循最最前綴匹配原則键菱，這是 B+ 樹的索引模型決定的。此外今布，不當(dāng)?shù)氖褂梅绞骄福瑫斐伤饕牟糠质В热绶秶樵儾磕⒆址患右柷置桑褂盟饕凶侄芜M(jìn)行函數(shù)運算以及使用 Or 查詢條件時其中某個字段沒有索引等等。

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