原文鏈接：blog.ouyangsihai.cn >> MySQL的B+樹索引的概念、使用、優(yōu)化及使用場景

在看這篇文章之前牲证，我們回顧一下前面的幾篇關(guān)于MySQL的文章，應(yīng)該對你讀下面的文章有所幫助步淹。

• InnoDB與MyISAM等存儲引擎對比
• 面試官問你B樹和B+樹从隆，就把這篇文章丟給他
• MySQL的B+樹索引的概念诚撵、使用缭裆、優(yōu)化及使用場景
• MySQL全文索引最強(qiáng)教程
• MySQL的又一神器-鎖，MySQL面試必備

0 前言

這篇文章不會講解索引的基礎(chǔ)知識寿烟，主要是關(guān)于MySQL數(shù)據(jù)庫的B+樹索引的相關(guān)原理澈驼，里面的一些知識都參考了MySQL技術(shù)內(nèi)幕這本書，也算對于這些知識的總結(jié)筛武。對于B樹和B+樹相關(guān)的知識缝其，可以參考我的這篇博客：面試官問你B樹和B+樹，就把這篇文章丟給他

1 索引的管理

索引有很多中類型：普通索引徘六、唯一索引内边、主鍵索引、組合索引待锈、全文索引漠其，下面我們看看如何創(chuàng)建和刪除下面這些類型的索引。

1.1 索引的創(chuàng)建方式

索引的創(chuàng)建是可以在很多種情況下進(jìn)行的竿音。

• 直接創(chuàng)建索引

CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))

[UNIQUE|FULLLTEXT]：表示可選擇的索引類型和屎，唯一索引還是全文索引，不加話就是普通索引春瞬。
table_name：表的名稱柴信，表示為哪個表添加索引。
column_name(length)：column_name是表的列名宽气，length表示為這一列的前l(fā)ength行記錄添加索引随常。

• 修改表結(jié)構(gòu)的方式添加索引

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))

• 創(chuàng)建表的時候同時創(chuàng)建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
)

1.2 主鍵索引和組合索引創(chuàng)建的方式

前面講的都是普通索引潜沦、唯一索引和全文索引創(chuàng)建的方式，但是绪氛，主鍵索引和組合索引創(chuàng)建的方式卻是有點(diǎn)不一樣的止潮，所以單獨(dú)拿出來講一下。

組合索引創(chuàng)建方式

• 創(chuàng)建表的時候同時創(chuàng)建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name(id,title)
)

• 修改表結(jié)構(gòu)的方式添加索引

ALTER TABLE table_name ADD INDEX name_city_age (name,city,age); 

主鍵索引創(chuàng)建方式
主鍵索引是一種特殊的唯一索引钞楼，一個表只能有一個主鍵喇闸，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引询件。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`)
)

1.3 刪除索引

刪除索引可利用ALTER TABLE或DROP INDEX語句來刪除索引燃乍。類似于CREATE INDEX語句，DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語句處理宛琅，語法如下刻蟹。

(1)DROP INDEX index_name ON talbe_name
(2)ALTER TABLE table_name DROP INDEX index_name
(3)ALTER TABLE table_name DROP PRIMARY KEY

第3條語句只在刪除PRIMARY KEY索引時使用，因?yàn)橐粋€表只可能有一個PRIMARY KEY索引嘿辟，因此不需要指定索引名舆瘪。

1.4 索引實(shí)例

上面講了一下基本的知識，接下來红伦，還是通過一個具體的例子來體會一下英古。

• step1：創(chuàng)建表

 create table table_index(
    id int(11) not null auto_increment,
    title char(255) not null,
    primary key(id)
);

• step2：添加索引

首先，我們使用直接添加索引的方式添加一個普通索引昙读。

CREATE INDEX idx_a ON table_index(title);

接著召调，我們用修改表結(jié)構(gòu)的時候添加索引。

ALTER TABLE table_index ADD UNIQUE INDEX idx_b (title(100));

最后蛮浑，我們再添加一個組合索引唠叛。

 ALTER TABLE table_index ADD INDEX idx_id_title (id,title);

這樣，我們就把前面索引的方式都用上一遍了沮稚，我相信你也熟悉這些操作了艺沼。

• step3：使用SHOW INDEX命令查看索引信息

如果想要查看表中的索引信息，可以使用命令SHOW INDEX,下面的例子蕴掏，我們查看表table_index的索引信息障般。

 SHOW INDEX FROM table_index\G;

image

得到上面的信息，上面的信息什么意思呢囚似？我們逐一介紹剩拢！

• step4：刪除索引

直接刪除索引方式

DROP INDEX idx_a ON table_index;

修改表結(jié)構(gòu)時刪除索引

ALTER TABLE table_index DROP INDEX idx_b;

1.5 Cardinality關(guān)鍵字解析

在上面介紹了那么多個關(guān)鍵字的意思牡彻，但是Cardinality這個關(guān)鍵字非常的關(guān)鍵，優(yōu)化器會根據(jù)這個值來判斷是否使用這個索引出爹。在B+樹索引中庄吼，只有高選擇性的字段才是有意義的，高選擇性就是這個字段的取值范圍很廣严就，比如姓名字段总寻，會有很多的名字，可選擇性就高了盈蛮。

一般來說废菱，判斷是否需要使用索引技矮，就可以通過Cardinality關(guān)鍵字來判斷抖誉，如果非常接近1，說明有必要使用衰倦，如果非常小袒炉，那么就要考慮是否使用索引了。

需要注意的一個問題時樊零，這個關(guān)鍵字不是及時更新的我磁，需要更新的話，需要使用ANALYZE TABLE驻襟，例如夺艰。

analyze table table_index;

image

因?yàn)槟壳皼]有數(shù)據(jù)，所以沉衣，你會發(fā)現(xiàn)郁副，這個值一直都是0，沒有變化豌习。

image

InoDB存儲引擎Cardinality的策略

在InnoDB存儲引擎中存谎，這個關(guān)鍵字的更新發(fā)生在兩個操作中：insert和update拔疚。但是，并不是每次都會更新既荚，這樣會增加負(fù)荷稚失，所以，對于這個關(guān)鍵字的更新有它的策略：

• 表中1/16的數(shù)據(jù)發(fā)生變化
• InnoDB存儲引擎的計數(shù)器stat_modified_conter>2000000000

默認(rèn)InnoDB存儲引擎會對8個葉子節(jié)點(diǎn)進(jìn)行采樣恰聘，采樣過程如下：

• B+樹索引中葉子節(jié)點(diǎn)數(shù)量句各，記做A
• 隨機(jī)取得B+樹索引中的8個葉子節(jié)點(diǎn)。統(tǒng)計每個頁不同的記錄個數(shù)晴叨，分別為p1-p8
• 根據(jù)采樣信息得到Cardinality的預(yù)估值：(p1+p2+p3+...+p8)*A/8

因?yàn)殡S機(jī)采樣诫钓，所以，每次的Cardinality值都是不一樣的篙螟，只有一種情況會一樣的菌湃，就是表中的葉子節(jié)點(diǎn)小于或者等于8，這時候遍略，怎么隨機(jī)采樣都是這8個惧所，所以也就一樣的。

1.6 Fast Index Creation

在MySQL 5.5之前绪杏，對于索引的添加或者刪除下愈，每次都需要創(chuàng)建一張臨時表，然后導(dǎo)入數(shù)據(jù)到臨時表蕾久，接著刪除原表势似，如果一張大表進(jìn)行這樣的操作，會非常的耗時僧著，這是一個很大的缺陷履因。

InnoDB存儲引擎從1.0.x版本開始加入了一種Fast Index Creation（快速索引創(chuàng)建）的索引創(chuàng)建方式。

這種方式的策略為：每次為創(chuàng)建索引的表加上一個S鎖（共享鎖）盹愚，在創(chuàng)建的時候栅迄，不需要重新建表，刪除輔助索引只需要更新內(nèi)部視圖皆怕，并將輔助索引空間標(biāo)記為可用毅舆，所以，這種效率就大大提高了愈腾。

1.7 在線數(shù)據(jù)定義

MySQL5.6開始支持的在線數(shù)據(jù)定義操作就是：允許輔助索引創(chuàng)建的同時憋活，還允許其他insert、update虱黄、delete這類DM操作悦即，這就極大提高了數(shù)據(jù)庫的可用性。

所以，我們可以使用新的語法進(jìn)行創(chuàng)建索引：

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
[ALGORITHM = {DEFAULT|INPLACE|COPY}]
[LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]

ALGORITHM指定創(chuàng)建或者刪除索引的算法

• COPY：創(chuàng)建臨時表的方式
• INPLACE：不需要創(chuàng)建臨時表
• DEFAULT：根據(jù)參數(shù)old_alter_table參數(shù)判斷盐欺，如果是OFF,采用INPLACE的方式

LOCK表示對表添加鎖的情況

• NONE：不加任何鎖
• SHARE：加一個S鎖赁豆，并發(fā)讀可以進(jìn)行，寫操作需要等待
• EXCLUSIVE：加一個X鎖冗美，讀寫都不能并發(fā)進(jìn)行
• DEFAULT：先判斷是否可以使用NONE魔种，如不能，判斷是否可以使用SHARE粉洼，如不能节预，再判斷是否可以使用EXCLUSIVE模式。

2 B+ 樹索引的使用

2.1 聯(lián)合索引

聯(lián)合索引是指對表上的多個列進(jìn)行索引属韧，這一部分我們將通過幾個例子來講解聯(lián)合索引的相關(guān)知識點(diǎn)安拟。

首先，我們先創(chuàng)建一張表以及為這張表創(chuàng)建聯(lián)合索引宵喂。

create table t_index(
a char(2) not null default '',
b char(2) not null default '',
c char(2) not null default '',
d char(2) not null default ''
)engine myisam charset utf8;

創(chuàng)建聯(lián)合索引

alter table t_index add index abcd(a,b,c,d);

插入幾條測試數(shù)據(jù)

insert into t_index values('a','b','c','d'),
('a2','b2','c2','d2'),
('a3','b3','c3','d3'),
('a4','b4','c4','d4'),
('a5','b5','c5','d5'),
('a6','b6','c6','d6');

到這一步糠赦，我們已經(jīng)基本準(zhǔn)備好了需要的數(shù)據(jù)，我們可以進(jìn)行更深一步的聯(lián)合索引的探討锅棕。

我們什么時候需要創(chuàng)建聯(lián)合索引呢

索引建立的主要目的就是為了提高查詢的效率拙泽，那么聯(lián)合索引的目的也是類似的，聯(lián)合索引的目的就是為了提高存在多個查詢條件的情況下的效率裸燎，就如上面建立的表一樣顾瞻，有多個字段，當(dāng)我們需要利用多個字段進(jìn)行查詢的時候德绿，我們就需要利用到聯(lián)合索引了荷荤。

什么時候聯(lián)合索引才會發(fā)揮作用呢

有時候，我們會用聯(lián)合索引移稳，但是蕴纳，我們并不清楚其原理，不知道什么時候聯(lián)合索引會起到作用秒裕，什么時候又是會失效的袱蚓？

帶著這個問題，我們了解一下聯(lián)合索引的最左匹配原則几蜻。

最左匹配原則：這個原則的意思就是創(chuàng)建組合索引，以最左邊的為準(zhǔn)体斩，只要查詢條件中帶有最左邊的列梭稚，那么查詢就會使用到索引。

下面絮吵，我們用幾個例子來看看這個原則弧烤。

EXPLAIN SELECT * FROM t_index WHERE a = 'a' \G;

image

我們看看這條語句的結(jié)果，首先蹬敲，我們看到使用了索引暇昂，因?yàn)?strong>查詢條件中帶有最左邊的列a莺戒，那么利用了幾個索引呢？這個我們需要看key_len這個字段急波，我們知道utf8編碼的一個字符3個字節(jié)从铲，而我們使用的數(shù)據(jù)類型是char(2)，占兩個字節(jié)澄暮，索引就是2*3等于6個字節(jié)名段，所以只有一個索引起到了作用。

EXPLAIN SELECT * FROM t_index WHERE b = 'b2' \G;

image

這個語句我們可以看出泣懊，這個沒有使用索引伸辟，因?yàn)?code>possible_keys為空，而且馍刮，從查詢的行數(shù)rows可以看出為6（我們測試數(shù)據(jù)總共6條）信夫，說明進(jìn)行了全盤掃描的，說明這種情況是不符合最左匹配原則卡啰，所以不會使用索引查詢忙迁。

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY d \G;

image

這種情況又有點(diǎn)不一樣了，我們使用了一個排序碎乃，可以看出使用了索引姊扔，通過key_len為12可以得到使用了2個索引a、b梅誓，另外在Extra選項(xiàng)中可以看到使用了Using filesort恰梢，也就是文件排序，這里使用文件排序的原因是這樣的：上面的查詢使用了a梗掰、b索引嵌言，但是當(dāng)我們用d字段來排序時，（a及穗，d）或者（b摧茴，d）這兩個索引是沒有排序的，聯(lián)合索引的使用有一個好處埂陆，就是索引的下一個字段是會自動排序的苛白，在這里的這種情況來說，c字段就是排序的焚虱，但是d是不會购裙，如果我們用c來排序就會得到不一樣的結(jié)果。

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY c \G;

image

是不是可以看到鹃栽，當(dāng)我們用c進(jìn)行排序的時候躏率，因?yàn)槭褂昧薬、b索引，所以c就自動排序了薇芝，所以也就不用filesort了蓬抄。

講到這里，我相信通過上面的幾個例子夯到，對于聯(lián)合索引的相關(guān)知識已經(jīng)非常的透徹清晰了嚷缭，最后，我們再來聊幾個常見的問題黄娘。

Q1：為什么不對表中的每一個列創(chuàng)建一個索引呢

第一峭状，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間，這種時間隨著數(shù)據(jù)量的增加而增加逼争。
第二优床，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外誓焦，每一個索引還要占一定的物理空間胆敞，如果要建立聚簇索引，那么需要的空間就會更大杂伟。
第三移层，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候赫粥，索引也要動態(tài)的維護(hù)观话，這樣就降低了數(shù)據(jù)的維護(hù)速度。

Q2：為什么需要使用聯(lián)合索引

減少開銷越平。建一個聯(lián)合索引(col1,col2,col3)频蛔，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引秦叛，都會增加寫操作的開銷和磁盤空間的開銷晦溪。對于大量數(shù)據(jù)的表，使用聯(lián)合索引會大大的減少開銷挣跋！

覆蓋索引三圆。對聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2避咆。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)舟肉，而無需回表，這減少了很多的隨機(jī)io操作牌借。減少io操作度气，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以膨报，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高现柠。索引列越多院领，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表够吩，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)比然，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)周循，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)强法，然后再排序，再分頁湾笛；如果是聯(lián)合索引饮怯，通過索引篩選出1000w10% 10% *10%=1w，效率提升可想而知嚎研！

覆蓋索引
覆蓋索引是一種從輔助索引中就可以得到查詢的記錄蓖墅，而不需要查詢聚集索引中的記錄，使用覆蓋索引的一個好處是輔助索引不包含整行記錄的所有信息临扮，所以大小遠(yuǎn)小于聚集索引论矾，因此可以大大減少IO操作。覆蓋索引的另外一個好處就是對于統(tǒng)計問題有優(yōu)化杆勇，我們看下面的一個例子贪壳。

explain select count(*) from t_index \G;

image

如果是myisam引擎，Extra列會輸出Select tables optimized away語句蚜退，myisam引擎已經(jīng)保存了記錄的總數(shù)闰靴，直接返回結(jié)果，就不需要覆蓋索引優(yōu)化了关霸。

如果是InnoDB引擎传黄，Extra列會輸出Using index語句，說明InnoDB引擎優(yōu)化器使用了覆蓋索引操作队寇。

2.2 索引提示

MySQL數(shù)據(jù)庫支持索引提示功能膘掰，索引提示功能就是我們可以顯示的告訴優(yōu)化器使用哪個索引，一般有下面兩種情況可能使用到索引提示功能（INDEX HINT）：

• MySQL數(shù)據(jù)庫的優(yōu)化器錯誤的選擇了某個索引佳遣，導(dǎo)致SQL運(yùn)行很慢
• 某SQL語句可以選擇的索引非常的多识埋，這時優(yōu)化器選擇執(zhí)行計劃時間的開銷可能會大于SQL語句本身。

這里我們接著上面的例子來講解零渐，首先窒舟，我們先為上面的t_index表添加幾個索引；

alter table t_index add index a (a);
alter table t_index add index b (b);
alter table t_index add index c (c);

接著诵盼，我們執(zhí)行下面的語句惠豺；

EXPLAIN SELECT * FROM t_index WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

image

你會發(fā)現(xiàn)這條語句就可以使用三個索引银还，這個時候，我們可以顯示的使用索引提示來使用a這個索引洁墙，如下：

EXPLAIN SELECT * FROM t_index USE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

image

這樣就顯示的使用索引a了蛹疯，如果這種方式有時候優(yōu)化器還是沒有選擇你想要的索引，那么热监，我們可以另外一種方式FORCE INDEX捺弦。

EXPLAIN SELECT * FROM t_index FORCE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

image

這種方式則一定會選擇你想要的索引。

2.3 索引優(yōu)化

Multi-Range Read 優(yōu)化

MySQL5.6開始支持孝扛，這種優(yōu)化的目的是為了減少磁盤的隨機(jī)訪問列吼，并且將隨機(jī)訪問轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問，這種優(yōu)化適用于range苦始、ref寞钥、eq_ref類型的查詢。

Multi-Range Read 優(yōu)化的好處：

• 讓數(shù)據(jù)訪問變得較為順序盈简。
• 減少緩沖區(qū)中頁被替換的次數(shù)凑耻。
• 批量處理對鍵值的查詢操作。

我們可以使用參數(shù)optimizer_switch中的標(biāo)記來控制是否開啟Multi-Range Read 優(yōu)化柠贤。下面的方式將設(shè)置為總是開啟狀態(tài)：

SET @@optimizer_switch='mrr=on,mrr_cost_based=off';

Index Condition Pushdown（ICP） 優(yōu)化

這種優(yōu)化方式也是從MySQL5.6開始支持的香浩，不支持這種方式之前，當(dāng)進(jìn)行索引查詢時臼勉，首先我們先根據(jù)索引查找記錄邻吭，然后再根據(jù)where條件來過濾記錄。然而宴霸，當(dāng)支持ICP優(yōu)化后囱晴，MySQL數(shù)據(jù)庫會在取出索引的同時，判斷是否可以進(jìn)行where條件過濾瓢谢，也就是將where過濾部分放在了存儲引擎層畸写，大大減少了上層SQL對記錄的索取。

ICP支持range氓扛、ref枯芬、eq_ref、ref_or_null類型的查詢采郎，當(dāng)前支持MyISAM和InnoDB存儲引擎千所。

我們可以使用下面語句開啟ICP：

set @@optimizer_switch = "index_condition_pushdown=on"

或者關(guān)閉：

set @@optimizer_switch = "index_condition_pushdown=off"

當(dāng)開啟了ICP之后，在執(zhí)行計劃Extra可以看到Using index condition提示蒜埋。

3 索引的特點(diǎn)淫痰、優(yōu)點(diǎn)、缺點(diǎn)及適用場景

索引的特點(diǎn)

• 可以加快數(shù)據(jù)庫的檢索速度
• 降低數(shù)據(jù)庫插入整份、修改待错、刪除等維護(hù)的速度
• 只能創(chuàng)建在表上籽孙，不能創(chuàng)建在視圖上
• 既可以直接創(chuàng)建也可以間接創(chuàng)建

索引的優(yōu)點(diǎn)

• 創(chuàng)建唯一性索引，保證數(shù)據(jù)庫表中的每一行數(shù)據(jù)的唯一性
• 大大加快數(shù)據(jù)的檢索速度
• 加快數(shù)據(jù)庫表之間的連接朗鸠，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義
• 在使用分組和排序字句進(jìn)行數(shù)據(jù)檢索時蚯撩，同樣可以顯著減少查詢的時間
• 通過使用索引础倍，可以在查詢中使用優(yōu)化隱藏器烛占，提高系統(tǒng)性能

索引的缺點(diǎn)

• 第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間沟启，這種時間隨著數(shù)據(jù)量的增加而增加忆家。
• 第二，索引需要占物理空間德迹，除了數(shù)據(jù)表占數(shù)據(jù)空間之外芽卿，每一個索引還要占一定的物理空間，如果要建立聚簇索引胳搞，那么需要的空間就會更大卸例。
• 第三，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加肌毅、刪除和修改的時候筷转，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度悬而。

索引的適用場景

• 匹配全值

對索引中所有列都指定具體值呜舒，即是對索引中的所有列都有等值匹配的條件。

• 匹配值的范圍查詢

對索引的值能夠進(jìn)行范圍查找笨奠。

• 匹配最左前綴

僅僅使用索引中的最左邊列進(jìn)行查詢袭蝗，比如在 col1 + col2 + col3 字段上的聯(lián)合索引能夠被包含 col1、(col1 + col2)般婆、（col1 + col2 + col3）的等值查詢利用到到腥，可是不能夠被 col2、（col2蔚袍、col3）的等值查詢利用到乡范。
最左匹配原則可以算是 MySQL 中 B-Tree 索引使用的首要原則。

• 僅僅對索引進(jìn)行查詢

當(dāng)查詢的列都在索引的字段中時页响，查詢的效率更高篓足，所以應(yīng)該盡量避免使用 select *，需要哪些字段闰蚕，就只查哪些字段栈拖。

• 匹配列前綴

僅僅使用索引中的第一列，并且只包含索引第一列的開頭一部分進(jìn)行查找没陡。

• 能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進(jìn)行范圍匹配
• 如果列名是索引涩哟，那么使用 column_name is null 就會使用索引索赏，例如下面的就會使用索引：

explain select * from t_index where a is null \G

• 經(jīng)常出現(xiàn)在關(guān)鍵字order by、group by贴彼、distinct后面的字段
• 在union等集合操作的結(jié)果集字段
• 經(jīng)常用作表連接的字段
• 考慮使用索引覆蓋潜腻，對數(shù)據(jù)很少被更新，如果用戶經(jīng)常值查詢其中你的幾個字段器仗，可以考慮在這幾個字段上建立索引融涣，從而將表的掃描變?yōu)樗饕膾呙?/li>

索引失效情況

• 以%開頭的 like 查詢不能利用 B-Tree 索引，執(zhí)行計劃中 key 的值為 null 表示沒有使用索引
• 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時候也不會使用索引精钮，例如威鹿，where 'age'+10=30
• 對索引列進(jìn)行函數(shù)運(yùn)算，原因同上
• 正則表達(dá)式不會使用索引
• 字符串和數(shù)據(jù)比較不會使用索引
• 復(fù)合索引的情況下轨香，假如查詢條件不包含索引列最左邊部分忽你，即不滿足最左原則 leftmost，是不會使用復(fù)合索引的
• 如果 MySQL 估計使用索引比全表掃描更慢臂容，則不使用索引
• 用 or 分割開的條件科雳，如果 or 前的條件中的列有索引，而后面的列中沒有索引脓杉，那么涉及的索引都不會被用到
• 使用負(fù)向查詢（not 糟秘，not in， not like 丽已，<> ,!= ,!> ,!< ） 不會使用索引

參考文章

• http://www.yuanrengu.com/index.php/2017-01-13.html
• https://segmentfault.com/a/1190000009717352
• https://www.jb51.net/article/157920.htm
• https://www.cnblogs.com/xiaoxi/p/8269289.html
• 《MySQL技術(shù)內(nèi)幕》

文章有不當(dāng)之處蚌堵，歡迎指正，如果喜歡微信閱讀沛婴，你也可以關(guān)注我的微信公眾號：好好學(xué)java吼畏，獲取優(yōu)質(zhì)學(xué)習(xí)資源。