一、索引

1、索引概述

1.1、介紹

1.2、特點

2、索引結構

2.1、概述

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

注意： 我們平常所說的索引，如果沒有特別指明，都是指 B+ 樹結構組織的索引。

2.2、B+Tree

B+Tree 是 B-Tree 的變種，我們以一顆最大度數（ max-degree ）為 4 （ 4 階）的 b+tree 為例，來看一

下其結構示意圖：

我們可以看到，兩部分：

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

知識小貼士 : 樹的度數指的是一個節點的子節點個數。

2.3、MySQL里的B+tree索引結構

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

2.4、Hash

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

2.4.1、結構

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

hash 表中。

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

2.4.2、特點

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

2.4.3、存儲引擎支持

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

思考題： 為什么InnoDB存儲引擎選擇使用B+tree索引結構?

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

3、索引分類

3.1、索引分類

3.2、聚集索引&二級索引

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

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

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

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

具體過程如下 :

1. 由于是根據name字段進行查詢，所以先根據name='Arm'到name字段的二級索引中進行匹配查找。但是在二級索引中只能查找到 Arm 對應的主鍵值 10。
2. 由于查詢返回的數據是*，所以此時，還需要根據主鍵值10，到聚集索引中查找10對應的記錄，最終找到10對應的行row。
3. 最終拿到這一行的數據，直接返回即可。

回表查詢： 這種先到二級索引中查找數據，找到主鍵值，然后再到聚集索引中根據主鍵值，獲取

數據的方式，就稱之為回表查詢。

4、索引語法

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 ;

5、SQL性能分析

5.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:?? 更新次數

那么通過查詢 SQL 的執行頻次，我們就能夠知道當前數據庫到底是增刪改為主，還是查詢為主。 那假如說是以查詢為主，我們又該如何定位針對于那些查詢語句進行優化呢？ 次數我們可以借助于慢查詢日志。

接下來，我們就來介紹一下 MySQL 中的慢查詢日志。

5.2、慢查詢日志

慢查詢日志記錄了所有執行時間超過指定參數（ long_query_time ，單位：秒，默認 10 秒）的所有

SQL 語句的日志。

MySQL 的慢查詢日志默認沒有開啟，我們可以查看一下系統變量 slow_query_log 。

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

# 開啟MySQL慢日志查詢開關
slow_query_log=1
# 設置慢日志的時間為2秒，SQL語句執行時間超過2秒，就會視為慢查詢，記錄慢查詢日志
long_query_time=2

systemctl restart mysqld

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

1. 執行如下SQL語句 :?

   select * from tb_user; -- 這條SQL執行效率比較高, 執行耗時 0.00sec
   select count(*) from tb_sku; -- 由于tb_sku表中, 預先存入了1000w的記錄, count一次,耗時13.35sec

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

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

5.3、profile詳情

SELECT @@have_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 各個階段的耗時情況 :

5.4、explain

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

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

6、索引使用

6.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.78sec ，就是因為 sn 沒有索引，而造成查詢效率很低。

那么我們可以針對于 sn 字段，建立一個索引，建立了索引之后，我們再次根據 sn 進行查詢，再來看一下查詢耗時情況。

創建索引：

create index idx_sku_sn on tb_sku(sn) ;

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

SELECT * FROM tb_sku WHERE sn = '100000003145001';

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

6.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';

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

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

上述的 SQL 查詢時，存在 profession 字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條

件。但是查詢時，跳過了 age 這個列，所以后面的列索引是不會使用的，也就是索引部分生效，所以索引的長度就是47 。

思考題:?

當執行SQL語句:

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

時，是否滿足最左前綴法則，走不走上述的聯合索引，索引長度？

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

注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時，聯合索引的最左邊的字段 ( 即是

第一個字段 ) 必須存在，與我們編寫 SQL 時，條件編寫的先后順序無關。

6.3、范圍查詢

聯合索引中，出現范圍查詢 (>,<) ，范圍查詢右側的列索引失效。

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

當范圍查詢使用 > 或 < 時，走聯合索引了，但是索引的長度為 49 ，就說明范圍查詢右邊的 status 字

段是沒有走索引的。

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

6.4、索引失效情況

6.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';

6.4.2、字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。

如果字符串不加單引號，對于查詢結果，沒什么影響，但是數據庫存在隱式類型轉換，索引將失效。

6.4.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 模糊查詢中，在關鍵字后面加 %，索引可以生效。而如果在關鍵字前面加了%，索引將會失效。

6.4.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也要建立索引。

create index idx_user_age on tb_user(age);

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

?

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

?6.4.5、數據分布影響

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

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

?

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

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

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

6.5、SQL提示

目前tb_user表的數據情況如下

?索引情況如下:

?把上述的 idx_user_age, idx_email 這兩個之前測試使用過的索引直接刪除。

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;

?

查詢走了聯合索引。?

create index idx_user_pro on tb_user(profession);

?C. 創建單列索引后，再次執行A中的SQL語句，查看執行計劃，看看到底走哪個索引。

1. use index ： 建議 MySQL 使用哪一個索引完成此次查詢（僅僅是建議， mysql 內部還會再次進行評估）。

   explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';

2. ignore index ： 忽略指定的索引。

   explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';

3. force index ： 強制使用索引

   explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';

?6.6、覆蓋索引

?接下來，我們來看一組SQL的執行計劃，看看執行計劃的差別，然后再來具體做一個解析

explain select id, profession from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;explain select id,profession,age, status from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;explain select id,profession,age, status, name from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

?上述這幾條SQL的執行結果為：

為了大家更清楚的理解，什么是覆蓋索引，什么是回表查詢，我們一起再來看下面的這組 SQL 的執行過程。

A. 表結構及索引示意圖 :

?id是主鍵，是一個聚集索引。 name字段建立了普通索引，是一個二級索引（輔助索引）。

?B. 執行SQL : select * from tb_user where id = 2;

?根據id查詢，直接走聚集索引查詢，一次索引掃描，直接返回數據，性能高。

C. 執行SQL：selet id,name from tb_user where name = 'Arm';

?D. 執行SQL：selet id,name,gender from tb_user where name = 'Arm';

?

思考題：

一張表 , 有四個字段 (id, username, password, status), 由于數據量大 , 需要對以下SQL 語句進行優化 , 該如何進行才是最優方案 :

select id,username,password from tb_user where username = 'itcast';

答案: 針對于 username, password建立聯合索引, sql為:

create index idx_user_name_pass on tb_user(username,password);

這樣可以避免上述的 SQL 語句，在查詢的過程中，出現回表查詢。

?6.7、前綴索引

1. 語法

   create index idx_xxxx on table_name(column(n)) ;

   示例 :
   ?
   為 tb_user 表的 email 字段，建立長度為5的前綴索引。

   create index idx_email_5 on tb_user(email(5));

   
   ?
2. 前綴長度
   ?
   可以根據索引的選擇性來決定，而選擇性是指不重復的索引值（基數）和數據表的記錄總數的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1 ，這是最好的索引選擇性，性能是最好的。
   ?

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

   小貼士: 求取不重復的值, 在字段前加上distinct關鍵字來去重。

3. 前綴索引的查詢流程
   
   ?

   

?6.8、單列索引與聯合索引

我們先來看看 tb_user 表中目前的索引情況:
?

?在查詢出來的索引中，既有單列索引，又有聯合索引

接下來，我們來執行一條SQL語句，看看其執行計劃：

create unique index idx_user_phone_name on tb_user(phone,name);

在業務場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯合索引，而非單列索引。

?如果查詢使用的是聯合索引，具體的結構示意圖如下：

?

?7、索引設計原則

1. 針對于數據量較大，且查詢比較頻繁的表建立索引。
   ?
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
   ?
3. 盡量選擇區分度高的列作為索引，盡量建立唯一索引，區分度越高，使用索引的效率越高。
   ?
4. 如果是字符串類型的字段，字段的長度較長，可以針對于字段的特點，建立前綴索引。
   ?
5. 盡量使用聯合索引，減少單列索引，查詢時，聯合索引很多時候可以覆蓋索引，節省存儲空間，避免回表，提高查詢效率。
   ?
6. 要控制索引的數量，索引并不是多多益善，索引越多，維護索引結構的代價也就越大，會影響增刪改的效率。