# 一. 索引概述 ## 1. 介紹 **索引是幫助MySQL高效獲取數據的數據結構（有序）。在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找演算法，這種數據結構就是索引。** ![](https://tcs-de ...

一. 索引概述

1. 介紹

索引是幫助MySQL高效獲取數據的數據結構（有序）。在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找演算法，這種數據結構就是索引。

2. 演示

表結構及其數據如下：

假如我們要執行的SQL語句為：select * from user where age = 45;

(1). 無索引情況

在無索引情況下，就需要從第一行開始掃描，一直掃描到最後一行，我們稱之為全表掃描，性能很低。

(2). 有索引情況

如果我們針對於這張表建立了索引，假設索引結構就是二叉樹，那麼也就意味著，會對age這個欄位建立一個二叉樹的索引結構。

此時我們在進行查詢時，只需要掃描三次就可以找到數據了，極大的提高了查詢的效率。

註：這裡我們只是假設索引的結構是二叉樹，介紹一下索引的大概原理，只是一個示意圖，並不是索引的真實結構，索引的真實結構，下麵會有介紹。

3. 特點

優勢	劣勢
提高數據檢索的效率，降低資料庫的IO成本	索引列也是要占用空間的
通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消耗	索引大大提高了查詢效率，同時卻也降低了更新表的速度，如對錶進行INSERT、UPDATE、DELETE時，效率降低

二. 索引結構

1. 簡介

MySQL的索引是在存儲引擎層實現的，不同的存儲引擎有不同的索引結構，主要包含以下幾種：

索引結構	描述
B+Tree索引	最常見的索引類型，大部分引擎都支持B+樹索引
Hash索引	底層數據結構是用哈希表實現的，只有精確匹配索引列的查詢才有效，不支持範圍查詢
R-tree(空間索引)	空間索引是MyISAM引擎的一個特殊索引類型，主要用於地理空間數據類型，通常使用較少
Full-text(全文索引)	全文索引是一種通過建立倒排索引，快速匹配文檔的方式。類似於Lucene，Solr，ES

上述是MySQL中所支持的所有索引結構，接下來，我們再來看看不同的存儲引擎對於索引結構的支持情況。

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本之後支持	支持	不支持

2. 二叉樹

假如說MySQL的索引結構採用二叉樹的數據結構，比較理想的結構如下：

如果主鍵是順序插入的，則會形成一個單向鏈表，結構如下：

所以，如果選擇二叉樹作為索引結構，會存在以下缺點：

順序插入時，會形成一個鏈表，查詢性能大大降低。
大數據量情況下，層級較深，檢索速度慢

此時可能會想到，我們可以選擇紅黑樹，紅黑樹是一顆自平衡二叉樹，那這樣即使是順序插入數據，最終形成的數據結構也是一顆平衡的二叉樹，結構如下：

但是，即使如此，由於紅黑樹也是一顆二叉樹，所以也會存在一個缺點：

大數據量情況下，層級較深，檢索速度慢。

所以，在MySQL的索引結構中，並沒有選擇二叉樹或者紅黑樹，而是選擇B+Tree，在詳解B+Tree之前，先來介紹一個B-Tree。

3. B-Tree

B-Tree，B樹是一種多叉路平衡查找樹，相對於二叉樹，B樹每個節點可以有多個分支，即多叉。

以一顆最大度數（max-degree）為5（5階）的b-tree為例，那這個B樹每個節點最多存儲4個key，5個指針：

註：樹的度數指的是一個節點的子節點格式

特點：

5階的B樹，每一個節點最多存儲4個key，對應5個指針。
一旦節點存儲的key數量達到5，就會裂變，中間元素向上分裂。
在B樹中，非葉子節點和葉子節點都會存放數據。

4. B+Tree

B+Tree是B-Tree的變種，我們以一顆最大度數（max-degree）為4（4階）的b+tree為例，來看一下其結構示意圖：

我們可以看到，兩部分：

綠色框框起來的部分，是索引部分，僅僅起到索引數據的作用，不存儲數據。
紅色框框起來的部分，是數據存儲部分，在其葉子節點中要存儲具體的數據。

B+Tree和B-Tree相比，主要有以下三點區別：

所有的數據都會出現在葉子節點。
葉子節點形成一個單向鏈表。
非葉子節點僅僅起到索引數據作用，具體的數據都是在葉子節點存放的。

上述我們所看到的結構是標準的B+Tree的數據結構，接下來，我們再來看看MySQL優化之後的B+Tree。

MySQL索引數據結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區間訪問的性能，利於排序。

5. Hash

MySQL中除了支持B+Tree索引，還支持一種索引---Hash索引。

(1). 結構

哈希索引就是採用一定的hash演算法，將鍵值換算成新的hash值，映射到對應的槽位上，然後存儲在hash表中。

如果兩個（或多個）鍵值，映射到一個相同的槽位上，他們就產生了hash衝突（也稱為hash碰撞），可以通過鏈表來解決。

(2). 特點

Hash索引只能用於對等比較(=，in)，不支持範圍查詢(between，>，<，...)
無法利用索引完成排序操作
查詢效率高，通常(不存在hash衝突的情況)只需要一次檢索就可以了，效率通常要高於B+Tree索引

(3). 存儲引擎支持

在MySQL中，支持hash索引的是Memory存儲引擎。而InnoDB中具有自適應hash功能，hash索引是InnoDB存儲引擎根據B+Tree索引在指定條件下自動構建的。

問題1：為什麼InnoDB存儲引擎選擇使用B+Tree索引結構？

相對於二叉樹，層級更少，搜索效率高；
對於B-tree，無論是葉子節點還是非葉子節點，都會保存數據，這樣導致一頁中存儲的鍵值減少，指針跟著減少，要同樣保存大量數據，只能增加樹的高度，導致性能降低；
相對Hash索引，B+tree支持範圍匹配及排序操作。

三. 索引分類

在MySQL資料庫中，將索引的具體類型主要分為以下幾類：主鍵索引、唯一索引、常規索引、全文索引。

分類	含義	特點	關鍵字
主鍵索引	針對於表中主鍵創建的索引	預設自動創建，只能有一個	PRIMARY
唯一索引	避免同一個表中某數據列中的值重覆	可以有多個	UNIQUE
常規索引	快速定位特定數據	可以有多個
全文索引	全文索引查找的是文本中的關鍵詞，而不是比較索引中的值	可以有多個	FULLTEXT

1. 聚集索引&二級索引

在InnoDB存儲引擎中，根據索引的存儲形式，又可以分為以下兩種：

分類	含義	特點
聚集索引(Clustered Index)	將數據存儲與索引放到了一塊，索引結構的葉子節點保存了行數據	必須有，而且只有一個
二級索引(Secondary Index)	將數據與檢索分開存儲，索引結構的葉子節點關聯的是對應的主鍵	可以存在多個

聚集索引選取規則：

如果存在主鍵，主鍵索引就是聚集索引。
如果不存在主鍵，將使用第一個唯一（UNIQUE）索引作為聚集索引。
如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

聚集索引和二級索引的具體結構如下：

聚集索引的葉子節點下掛的是這一行的數據。
二級索引的葉子節點下掛的是該欄位值對應的主鍵值。

接下來，我們來分析一下，當我們執行如下的SQL語句時，具體的查找過程是什麼樣子的。

具體過程如下：

A. 由於是根據name欄位進行查詢，所以先根據name="Arm"到name欄位的二級索引中進行匹配查找。但是在二級索引中只能查找到Arm對應的主鍵值10.

B. 由於查詢返回的數據是*，所以此時，還需要根據主鍵值10到聚集索引中查找10對應的記錄，最終找到10對應的行row。

C. 最終拿到這一行的數據，直接返回即可。

回表查詢：這種先到二級索引中查找數據，找到主鍵值，然後再到聚集索引中根據主鍵值，獲取數據的方式，就稱之為回表查詢。

問題1：一下兩條SQL語句，哪個執行效率高？為什麼？

A. select * from user where id = 10;

B. select * from user where name = 'Arm';

備註：id為主鍵，name欄位創建的有索引；

解答：A語句的執行性能要高於B語句。

因為A語句直接走聚集索引，直接返回數據。而B語句需要先查詢name欄位的二級索引，然後再查詢聚集索引，也就是需要進行回表查詢。

問題2：InnoDB主鍵索引的B+tree高度為多高呢？

假設：

一行數據大小為1k，一頁中可以存儲16行。InnoDB的指針占用6個位元組的空間，主鍵即使為bigint，占用位元組數為8。

高度為2：

n * 8 + (n + 1) * 6 = 16 * 1024，算出n約為1170

1171 * 16 = 18736

也就是說，如果樹的高度為2，則可以存儲18000多條數據。

高度為3：

1171 * 1171 * 16 = 21939856

也就是說，如果樹的高度為3，則可以存儲2200w左右的記錄。

四. 索引語法

1. 創建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

2. 查看索引

SHOW INDEX FROM table_name;

3. 刪除索引

DROP INDEX index_name ON table_name;

4. 案例演示

(1). 準備數據

create table tb_user( 
	id int primary key auto_increment comment '主鍵', 
	name varchar(50) not null comment '用戶名', 
	phone varchar(11) not null comment '手機號', 
	email varchar(100) comment '郵箱', 
	profession varchar(11) comment '專業', 
	age tinyint unsigned comment '年齡', 
	gender char(1) comment '性別 , 1: 男, 2: 女', 
	status char(1) comment '狀態', 
	createtime datetime comment '創建時間' 
) comment '系統用戶表';

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('呂布', '17799990000', '[email protected]', '軟體工程', 23, '1', '6', '2001-02-02 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', '[email protected]', '通訊工程', 33, '1', '0', '2001-03-05 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('趙雲', '17799990002', '[email protected]', '英語', 34, '1', '2', '2002-03-02 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孫悟空', '17799990003', '[email protected]', '工程造價', 54, '1', '0', '2001-07-02 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木蘭', '17799990004', '[email protected]', '軟體工程', 23, '2', '1', '2001-04-22 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大喬', '17799990005', '[email protected]', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', '[email protected]', '應用數學', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', '[email protected]', '化工', 38, '1', '5', '2001-05-23 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('項羽', '17799990008', '[email protected]', '金屬材料', 43, '1', '0', '2001-09-18 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', '[email protected]', '機械工程及其自動 化', 27, '1', '2', '2001-08-16 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韓信', '17799990010', '[email protected]', '無機非金屬材料工 程', 27, '1', '0', '2001-06-12 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荊軻', '17799990011', '[email protected]', '會計', 29, '1', '0', '2001-05-11 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('蘭陵王', '17799990012', '[email protected]', '工程造價', 44, '1', '1', '2001-04-09 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂鐵', '17799990013', '[email protected]', '應用數學', 43, '1', '2', '2001-04-10 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蟬', '17799990014', '[email protected]', '軟體工程', 40, '2', '3', '2001-02-12 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '[email protected]', '軟體工程', 31, '2', '0', '2001-01-30 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('羋月', '17799990016', '[email protected]', '工業經濟', 35, '2', '0', '2000-05-03 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '[email protected]', '化工', 38, '1', '1', '2001-08-08 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁傑', '17799990018', '[email protected]', '國際貿易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', '[email protected]', '城市規劃', 51, '2', '0', '2001-08-15 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韋', '17799990020', '[email protected]', '城市規劃', 52, '1', '2', '2000-04-12 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉頗', '17799990021', '[email protected]', '土木工程', 19, '1', '3', '2002-07-18 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('後羿', '17799990022', '[email protected]', '城市園林', 20, '1', '0', '2002-03-10 00:00:00'); 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '[email protected]', '工程造價', 29, '1', '4', '2003-05-26 00:00:00');

(2). name欄位為姓名欄位，該欄位的值可能會重覆，為該欄位創建索引。

CREATE INDEX idx_user_name ON tb_user(name);

(3). phone手機號欄位的值是非空且唯一的，為該欄位創建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

(4). 為profession、age、status創建聯合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

(5). 為email建立合適的索引來提升查詢效率。

CREATE INDEX idx_email ON tb_user(email);

五. SQL性能分析

1. SQL執行頻率

MySQL客戶端連接成功後，通過show [session | global] status 命令可以提供伺服器狀態信息。通過如下指令，可以查看當前資料庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

-- session 是查看當前會話; 
-- global 是查詢全局數據; 

SHOW GLOBAL STATUS LIKE 'Com_______';

Com_delete：刪除次數

Com_insert：插入次數

Com_select：查詢次數

Com_update：更新次數

註：通過上述指令，我們可以查看到當前資料庫到底是以查詢為主，還是以增刪該為主，從而為資料庫優化提供參考依據。如果是以增刪改為主，我們可以考慮不對其進行索引的優化。如果是以查詢為主，那麼就要考慮對資料庫的索引進行優化了。

2. 慢查詢日誌

慢查詢日誌記錄了所有執行時間超過指定參數（long_query_time，單位：秒，預設10秒）的所有SQL語句的日誌。

MySQL的慢查詢日誌預設沒有開啟，我們可以查看一下系統變數slow_query_log。

show variables like 'slow_query_log';

如果要開啟慢查詢日誌，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

# 開啟MySQL慢日誌查詢開關 
slow_query_log=1 

# 設置慢日誌的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日誌 
long_query_time=2

配置完畢之後，通過以下指令重新啟動MySQL伺服器進行測試，查看慢日誌文件中記錄的信息 /var/lib/mysql/localhost-slow.log

systemctl restart mysqld

然後，再次查看開關情況，慢查詢日誌就已經打開了。

測試：

A. 執行如下SQL語句：

select * from tb_user; -- 這條SQL執行效率比較高, 執行耗時 0.00sec

select count(*) from tb_sku; -- 由於tb_sku表中, 預先存入了1000w的記錄, count一次,耗時 13.35sec

B. 檢查慢查詢日誌：

最終我們發現，在慢查詢日誌中，只會記錄執行時間超出我們預設時間（2s）的SQL，執行較快的SQL是不會記錄的。

那這樣，通過慢查詢日誌，就可以定位出執行效率比較低的SQL，從而有針對性的進行優化。

3. profile詳情

show profiles能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去了。通過hava_profiling參數，能夠看到當前MySQL是否支持profile操作：

SELECT @@have_profiling;

SELECT @@profiling;

可以看到，當前MySQL是支持profile操作的，但是開關是關閉的。可以通過set語句在

session/global級別開啟profiling：

SET profiling = 1;

開關已經打開了，接下來，我們所執行的SQL語句，都會被MySQL記錄，並記錄執行時間消耗到哪兒去了。我們直接執行如下的SQL語句：

select * from tb_user; 

select * from tb_user where id = 1; 

select * from tb_user where name = '白起'; 

select count(*) from tb_sku;

執行一系列的業務SQL的操作，然後通過如下指令查看指令的執行耗時：

-- 查看每一條SQL的耗時基本情況 
show profiles; 

-- 查看指定query_id的SQL語句各個階段的耗時情況 
show profile for query query_id; 

-- 查看指定query_id的SQL語句CPU的使用情況 
show profile cpu for query query_id;

查看每一條SQL的耗時情況：

查看指定SQL各個階段的耗時情況：

4. explain

explain命令獲取MySQL如何執行SELECT語句的信息，包括在SELECT語句執行過程中表如何連接和連接的順序。

(1). 語法

-- 直接在select語句之前加上關鍵字 explain 
EXPLAIN SELECT 欄位列表 FROM 表名 WHERE 條件;

(2). explain執行計劃中各個欄位的含義

欄位	含義
id	select查詢的序列號，表示查詢中執行select子句或者是操作表的順序（id相同，執行順序從上到下；id不同，值越大，越先執行）。
select_type	表示SELECT的類型，常見的取值有SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的第二個或者後面的查詢語句）、SUBQUERY（SELECT/WHERE之後包含了子查詢）等
type	表示連接類型，性能由好到差的連接類型為NULL、system、const、eq_ref、ref、range、index、all。
possible_key	顯示可能應用在這張表上的索引，一個或多個。
key	實際使用的索引，如果為NULL，則沒有使用索引。
key_len	表示索引中使用的位元組數，該值為索引欄位最大可能長度，並非實際使用長度，在不損失精確性的前提下，長度越短越好。
rows	MySQL認為必須要執行查詢的行數，在innodb引擎的表中，是一個估計值，可能並不總是準確的。
filtered	表示返回結果的行數占需讀取行數的百分比，filtered的值越大越好。

六. 索引使用

1. 驗證索引效率

我們先通過一個簡單的案例，來驗證一下索引，看看是否能夠通過索引來提升數據查詢性能。在演示的時候，我們還是使用之前準備得一張表tb_sku，在這種表中準備了1000w的記錄。

這種表中id為主鍵，有主鍵索引，而其他欄位是沒有建立索引的。我們先來查詢其中的一條記錄，看看裡面的欄位情況，執行如下SQL：

select * from tb_sku where id = 1\G;

可以看到即使有1000w的數據，根據id進行數據查詢，性能依然很快，因為主鍵id是有索引的。那麼接下來，我們再來根據sn欄位進行查詢，執行如下SQL：

SELECT * FROM tb_sku WHERE sn = '100000003145001';

我們可以看到根據sn欄位進行查詢，查詢返回了一條數據，結果耗時20.78 sec，就是因為sn沒有索引而造成查詢效率很低。

那麼我們可以針對於sn欄位，建立一個索引，建立了索引之後，我們再次根據sn進行查詢，再來看一下查詢耗時情況。

創建索引：

create index idx_sku_sn on tb_sku(sn);

然後再次執行相同的SQL語句，再次查看SQL的耗時

SELECT * FROM tb_sku WHERE sn = '100000003145001';

我們明顯會看到，sn欄位建立了索引之後，查詢性能大大提升。建立索引前後，查詢耗時都不是一個數量級的。

2. 最左首碼法則

如果索引了多列（聯合索引），要遵守最左首碼法則。最左首碼法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效（後面的欄位索引失效）。

以tb_user表為例，我們先來查看一下之前tb_user表所創建的索引。

在tb_user表中，有一個聯合索引，這個聯合索引涉及到三個欄位，順序分別為：profession，age，status。

對於最左首碼法則指的是，查詢時，最左邊的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列後面的欄位索引將失效。接下來，我們演示幾組案例，看一下具體的執行計劃：

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '軟體工程' and age = 31;

explain select * from tb_user where profession = '軟體工程';

以上的這三組測試中，我們發現只要聯合索引最左邊的欄位profession存在，索引就會生效，只不過索引的長度不同。而且由以上三組測試，我們也可以推測出profession欄位索引長度為47、age欄位索引長度為2、status欄位索引長度為5。

explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

而通過上面的這兩組測試，我們也可以看到索引並未生效，原因是因為不滿足最左首碼法則，聯合索引最左邊的列prefession不存在。

explain select * from tb_user where profession = '軟體工程' and status = '0';

上述的SQL查詢時，存在profession欄位，最左邊的列是存在的，索引滿足最左首碼法則的基本條件。但是查詢時，跳過了age這個列，所以後面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是47。

問題1：當執行SQL語句：explain select * from tb_user where age = 31 and status = '0' and profeddion = '軟體工程';時，是否滿足最左首碼法則，走不走上述的聯合索引，索引長度是多少？

可以看到，是完全滿足最左首碼法則的，索引長度54，聯合索引是生效的。

註：最左首碼法則中指的最左邊的列是指在查詢時，聯合索引的最左邊的欄位（即是第一個欄位）必須存在，與我們編寫SQL時，條件編寫的先後順序無關。

3. 範圍查詢

聯合索引中，出現範圍查詢（>，<），範圍查詢右側的列索引失效。

explain select * from tb_user where profession = '軟體工程' and age > 30 and status = '0';

當範圍查詢使用> 或 < 時，走聯合索引了，但是索引的長度為49，就說明範圍查詢右邊的status欄位是沒有走索引的。

explain select * from tb_user where profession = '軟體工程' and age >= 30 and status = '0';

當範圍查詢使用>= 或 <= 時，走聯合索引了，索引的長度為54，就說明所有的欄位都是走索引的。

所以，在業務允許的情況下，儘可能的使用類似於 >= 或 <= 這類的範圍查詢，而避免使用 > 或 <。

4. 索引失效情況

(1). 索引列運算

不要在索引列上進行運算操作，索引將失效。

在tb_user表中，除了前面介紹的聯合索引之外，還有一個索引，是phone欄位的單列索引。

A. 當根據phone欄位進行等值匹配查詢時，索引生效。

explain select * from tb_user where phone = '17799990015';

B. 當根據phone欄位進行函數運算操作之後，索引失效

explain select * from tb_user where substring(phone,10,2) = '15';

(2). 字元串不加引號

字元串類型欄位使用時，不加引號，索引將會失效。

接下來，我們通過兩組示例來看看對於字元串類型的欄位，加單引號與不加單引號的區別：

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = '0'; 

explain select * from tb_user where profession = '軟體工程' and age = 31 and status = 0;

explain select * from tb_user where phone = '17799990015';

explain select * from tb_user where phone = 17799990015;

經過上面兩組示例，我們會明顯的發現，如果字元串不加單引號，對於查詢結果沒什麼影響，但是資料庫存在隱式類型轉換，索引將失效。

(3). 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

接下來，我們來看一下這三條SQL語句的執行效果，查看一下其執行計劃：

由於下麵查詢語句中，都是根據profession欄位查詢，符合最左首碼法則，聯合索引是可以生效的，我們主要看一下模糊查詢時，%加在關鍵字之前和加在關鍵字之後的影響。

explain select * from tb_user where profession like '軟體%';

explain select * from tb_user where profession like '%工程'; 

explain select * from tb_user where profession like '%工%';

經過上述的測試，我們發現在like模糊查詢中，在關鍵字後面加%，索引可以生效。而如果在關鍵字前面加了%，索引將會失效。

(4). or連接條件

用or分隔開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

explain select * from tb_user where id = 10 or age = 23; 

explain select * from tb_user where phone = '17799990017' or age = 23;

由於age沒有索引，所以即使id、phone有索引，索引也會失效。所以虛啊喲針對於age也要建立索引。

然後，我們可以對age欄位建立索引。

create index idx_user_age on tb_user(age);

建立了索引之後，我們再次執行上述的SQL語句，看看前後執行計劃的變化。

最終，我們發現，當or連接的條件，左右兩側欄位都有索引時，索引才會生效。

(5). 數據分佈影響

如果MySQL評估使用索引比全表更慢，則不使用索引。

select * from tb_user where phone >= '17799990005'; 

select * from tb_user where phone >= '17799990015';

經過測試我們發現，相同的SQL語句，只是傳入的欄位值不同，最終的執行計劃也完全不一樣，就是因為MySQL在查詢時，會評估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。因為索引是用來索引少量數據的，如果通過索引查詢返回大批量的數據，則還不如全表掃描來的快，此時索引就會失效。

接下來，我們再來看看is null 與 is not null 操作是否走索引。

執行如下兩條語句：

explain select * from tb_user where profession is null;

explain select * from tb_user where profession is not null;

接下來，我們做一個操作將profession欄位值全部更新為null。

然後，再次執行上述的兩條SQL，查看SQL語句的執行計劃。

Mysql進階篇（二）之索引