共計 6971 個字符，預計需要花費 18 分鐘才能閱讀完成。
今天丸趣 TV 小編給大家分享一下 Mysql 索引底層及優化方法是什么的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
一. 首先我們說下什么是索引，為什么要用索引
索引用于快速找出在某個列中有一特定值的行，不使用索引，MySQL 必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢數據所花費的時間就越多，如果表中查詢的列有一個索引，MySQL 能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那么將會節省很大一部分時間。
二. 索引類型分為兩類：1.hash 索引 2.bTree 三. 下面我們簡單分析一下 hash 索引和 bTree 索引。
1. 哈希表是一種以鍵 – 值（key-value）存儲數據的結構，我們只要輸入待查找的鍵即 key，就可以找到其對應的值即 Value。哈希的思路很簡單，把值放在數組里，用一個哈希函數把 key 換算成一個確定的位置，然后把 value 放在數組的這個位置。
不可避免地，多個 key 值經過哈希函數的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個鏈表。
2. 說到 bTree，就不得不提二叉樹，二叉樹分為很多，例：二叉查找樹，平衡二叉樹等。當然還有重點紅黑樹。
1）二叉查找樹的特點是：父節點左子樹所有節點的值小于父節點的值。右子樹所有節點的值大于父節點的值。下面以一張圖為例來體現二叉查找樹。
IDname5 張五 6 張六 7 張七 2 張二 1 張一 4 張四 3 張三
有一個需求，查找張三，如果不使用二叉查找樹那么我們需要查找 7 次，使用二叉查找樹我們只需要查找 4 次就可以找到我們想要的值。
根據上面說的使用二叉查找樹的確可以減少查詢次數，但是大家有沒有想過，如果數據庫的數據是 1，2，3，4，5，6，7 這樣依次遞增的數據呢，繼續使用二叉查找樹就會變成一個鏈表了。那這樣如果我們想要查找 7 那么需要查找 7 次，掃描表也是需要 7 次。這樣跟沒有建立索引沒有區別，這也是弊端之一。下圖為例說明。
2）平衡二叉樹：又被稱為 AVL 樹，它的左右兩個子樹的高度差的絕對值不超過 1，并且左右兩個子樹都是一棵平衡二叉樹，AVL 樹是最早發明的自平衡二叉查找樹。在 AVL 樹中，任何節點的兩個子樹的高度最大差別只能為 1，所以它又被稱為高度平衡樹。查詢、增加和刪除在平均和最壞情況下都是 O(log n)。增加和刪除會需要通過一次或多次樹旋轉來重新平衡這個樹。
我們引入二叉樹的目的是為了提高二叉樹的搜索的效率, 從而減少樹的平均搜索長度，為此, 就必須在每顆二叉樹插入一個結點時調整樹的結構, 讓二叉樹搜索能夠保持平衡, 從而可能降低樹的高度, 減少的平均樹的搜索長度。
平衡二叉樹特點如下：
1. 它的左子樹和右子樹都是 AVL 樹
2. 左子樹和右子樹的高度差不能超過 1
例圖：
3）紅黑樹：可以理解為紅黑樹是凌駕于平衡二叉樹之上的一棵樹，紅黑樹不會追求“完全平衡”，它只會求部分達到平衡要求，降低了對旋轉的要求，從而提高性能。此外，由于它的設計，所有不平衡都能夠在三次旋轉之內解決。在紅黑樹中，它的算法時間復雜度與 AVL 相同，并且統計性能會逼 AVL 樹更高。所以紅黑樹相對于平衡二叉樹來說，不是嚴格意義上的平衡二叉樹，紅黑樹插入和刪除效率更高一些，查詢的效率比平衡二叉樹來說相對低一些，但是二者查詢效率差值做對比，基本可以忽略不計。紅黑樹特點如下：
1. 節點是紅色或黑色。
2. 根節點是黑色。
3. 每個紅色節點的兩個子節點都是黑色。（紅色節點的子節點必須是黑色節點）
4. 從任一節點到其每個葉子的所有路徑都包含相同數目的黑色節點。
故紅黑樹是黑色平衡的樹，左子樹與右子樹高度差不會超過 2。紅節點的父節點、子節點只能是黑節點。
例圖：
4）BTree（B 樹）：當然上面說到了紅黑樹，性能非常高。以上圖為例，樹的高度最高才為 4，共 9 條數據，但是對于 Mysql 數據庫，動則幾百萬條數據，幾千萬條數據，那樹的高度就不可估量了，比如說上百萬條數據需要經過 30-50 次磁盤 IO 才能查詢到數據，甚至更多的次數，顯然不能滿足 Mysql 索引高效的查詢效率。那如果我們控制樹的高度呢，那這樣就會極大減少了請求磁盤 IO 的請求次數，如果高度控制在 4，那只需要經過 4 次磁盤 IO 就可以查詢到數據。
但是怎么樣控制樹的高度呢，紅黑樹是每個節點只存儲一個元素，如果每個節點存多個元素呢，這樣就可以解決高度問題了，肯定有同學有疑問，把所有的元素都放到一個節點上，那高度值就是 1 了，不是更快嗎？這樣想肯定是錯的，Mysql 每一次跟磁盤 IO 打交道是有大小限制的，Mysql 限制每一個節點的大小是 16K。想查看自己 Mysql 限制節點大小的同學可以執行下面的 sql。
show global status like‘Innodb_page_size’
下面以圖為例體現 BTree
BTree 特點如下：
1. 所有索引元素不重復
2. 節點的數據索引從左到右依次遞增
3. 葉節點具有相同的深度，葉節點的指針為空
4. 葉子節點和非葉子結點都存儲索引和數據
5）B+ 樹：上面說到了 BTree 控制了樹的高度的問題，可以滿足 Mysql 對于索引的需求，但是最終 Mysq 索引實現不是 BTree 而是 B + 樹，Mysql 對 B 樹做了一點點改造，得到了 B + 樹，也可以理解為 B + 樹是 B 樹的升級版。
下面以圖為例說明：
從這張圖可以看到，我們的非葉子節點只存儲了索引并沒有存儲 data，而且葉子節點間用指針相連。B 樹的葉子節點和非葉子節點都存儲了索引和數據，而且葉子結點的指針為空，B+ 樹把數據放在了葉子節點上，這樣非葉子節點就可以存放更多的索引，每次從磁盤 IO 也能獲取更多的索引。
B+ 樹特點如下：
1. 非葉子節點不存儲 data，只存儲索引 (冗余) 和下層指針，可以放更多的索引
2. 葉子節點包含所有索引字段，和數據
3. 葉子節點用雙指針連接，提高區間訪問的性能
在百度上和很多博客上畫的 B + 樹是錯誤的哦，一定要避坑哦。
有興趣看 Mysql 官方對 B + 樹的解釋的可以去看看。
鏈接: Mysql 官網.
四. 索引分類
1. 按照索引的存儲關聯分類：分為兩大類
1.）聚集索義（聚簇索引）：葉節點包含了完整的數據記錄，不需要回表。
2.）非聚集索引：需要回表，二次查樹，影響性能。
1.1）大家都知道 Mysql 常用的存儲引擎有兩種 MyISAM 和 InnoDB，但是大家實際了解過兩種存儲引擎底層的數據存儲結構嗎？
下面以圖為例為大家說明：
其中 test.myisam 表是 MyISAM 存儲引擎，actor 表是 InnoDB 存儲引擎，可以看到 MyISAM 存儲引擎有三個文件，分別是 frm、MYD、MYI，很容易理解 frm-frame 的簡稱，存的是表的結構，MYD-MYData 存的是數據，MYI-MYIndex 存的是索引，索引和數據是分開存儲的，再看 InnoDB 只有 frm、IBD，其中 frm 一樣也是存的表的結構，IBD 文件存的是索引和數據，這點 InnoDB 和 MyISAM 不一樣。
下面以圖為例說明 MyISAM 存儲引擎主鍵索引是需要回表操作（非聚集索引）
其中 15 存的是主鍵索引，0x07 存的是 15 所在行記錄的磁盤文件地址指針，比如我們想找到 15 的數據，那首先應該先通過主鍵索引樹，找到 15 所對應的指針，然后找到了這個指針再去 MyD 文件中找具體的數據，需要進行二次查找，這個過程稱為回表操作。
2.1）下面以圖為例說明 InnoDB 存儲引擎主鍵索引不需要進行回表操作。（聚集索引）
InnoDB 存儲引擎子節點首先 15 那一行存放的是索引，15 下面的那一列存放的是索引所在行的其他所有字段，如果我們想要查 15 的數據，直接就可以找到，不需要在經過二次查樹。
2. 按照功能分類：主要分為五大類
2.1 主鍵索引：InnoDB 主鍵索引不需要回表操作
2.2 普通索引（二級索引）：InnoDB 普通索引需要回表操作，對于二級索引，會默認和主鍵做聯合索引。
2.3 唯一索引
2.4 全文索引
2.5 聯合索引：需要滿足最左前綴原則
3. 在 2.2 中提到了普通索引需要回表操作，那有沒有不需要回表的普通索引呢，答案是有的，在某個查詢里面，索引已經覆蓋了我們的查詢需求，我們稱為覆蓋索引。這時是不需要回表操作的。
由于覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。
舉個例子：下面是這個表的初始化語句。
mysql create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT ,
index k(k))
engine=InnoDB;
insert into T values(100,1, aa),(200,2, bb),
(300,3, cc),(500,5, ee),(600,6, ff),(700,7, gg在上面這個表 T 中，如果我執行 select * from T where k between 3 and 5，需要執行幾次樹的搜索操作，會掃描多少行？
現在，我們一起來看看這條 SQL 查詢語句的執行流程。看下圖。
1.）在 k 索引樹上找到 k=3 的記錄，取得 ID = 300；
2.）再到 ID 索引樹查到 ID=300 對應的 R3；
3.）在 k 索引樹取下一個值 k=5，取得 ID=500；
4.）再回到 ID 索引樹查到 ID=500 對應的 R4；
5.）在 k 索引樹取下一個值 k=6，不滿足條件，循環結束。
在這個過程中，回到主鍵索引樹搜索的過程，我們稱為回表。可以看到，這個查詢過程讀了 k 索引樹的 3 條記錄（步驟 1、3 和 5），回表了兩次（步驟 2 和 4）。
在這個例子中，由于查詢結果所需要的數據只在主鍵索引上有，所以不得不回表。
如果執行的語句是 select ID from T where k between 3 and 5，這時只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢里面，索引 k 已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。
在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。又因為前面我們提到的，InnoDB 使用了 B+ 樹索引模型，所以數據都是存儲在 B+ 樹中的。每一個索引在 InnoDB 里面對應一棵 B+ 樹。
五. 索引優化 1. 上面描述了索引基本概念、分類以及底層的基本結構相關知識。下面聊一聊索引優化的相關知識吧。
1.）當組合索引中只要有一列含有 null 值，索引失效
2.）在列上做計算索引失效，范圍之后的索引全部失效
3.）在查詢條件上使用函數會造成索引失效
4.）在 where 字句中使用 != 或 操作符，導致索引失效
5.）避免使用 or，導致索引失效
6.）使用模糊查詢也會造成索引失效，可以用 like‘a%’而不是 like‘%a%’
7.）盡量使用覆蓋索引，減少 select * 語句
8.）滿足最左前綴法則，最左前列開始并且不跳過索引中的列
9.）字符串不加單引號索引失效
2. 下面以實戰說明索引優化。
新創建一個員工表，有 5 個屬性，如下。
create table employees(
id int primary key auto_increment comment 主鍵自增 ,
name varchar(30) not null default comment 名字 ,
age int not null default 1 comment 年齡 ,
id_card varchar(40) not null default comment 身份證號 ,
position varchar(40) not null default comment 位置
-- 創建聯合索引
create index name_index on employees (name,age,position);
-- 插入一條數據
insert into employees(name,age,id_card,position) values( 張三 ,15,
201124199011035321 , 北京
-- 下面以 10 條 sql 測試，注意建立的聯合索引順序是 name，age，position
1.explain select * from employees where age=15 and position= 北京 and name= 張三
2.explain select * from employees where name= 張三 and age=15 and position= 北京
3.explain select * from employees where age=15 and name= 張三
4.explain select * from employees where position= 北京 and name= 張三
5.explain select * from employees where position= 北京 and age=15;
6.explain select * from employees where position= 北京 and age 15 and name= 張三
7.explain select * from employees where position= 北京
8.explain select * from employees where age=15;
9.explain select * from employees where name= 張三
10.explain select * from employees where name != 張三以上 10 條 sql 有哪些是索引失效，有哪些是索引沒有失效的呢？相信同學們已經有了答案，但是答案對不對呢，下面我們一起分析下。首先說第 1 條，查詢條件把 3 個索引全部用上了，但是索引的順序有變化，由 name，age，position 變成
了 age，position，name，想到這里肯定有很多同學給出的答案就是索引失效，但是事實證明這個結果
是錯的，索引生效，肯定有很多同學疑惑，為什么呢，這條 sql 不滿足最左原則法則呀，這就要涉及到 sql
的執行流程了，這里博主簡單說下，sql 執行有 1 個優化器的過程，優化器的作用之一就是索引的選擇優化，所以優化器幫我們把索引的順序變成正確的了，所以索引生效。下面是第 1 條按照索引順序 sql 和第 2 條沒有按照索引順序 sql 的執行結果。執行結果入下圖：可以發現全部生效。第 1 條 sql type 的值為 ref、字節是 288 并且 ref 有 3 個 const，全部生效。
第 2 條 sql type 的值為 ref、字節是 288 并且 ref 有 3 個 const，全部生效。
想學習 sql 的執行流程的可以看博主的另一篇關于 sql 執行流程的文章哦。有的同學有疑問了，那最左原則沒有用了嗎？答案：有用的。
現在我們說下第 3、4、5 條 sql
explain select * from employees where age=15 and name= 張三
sql 在執行的時候，優化器替我們把索引的順序優化了，由 age - name 變成 name - age，這時
索引是生效的。explain select * from employees where position= 北京 and name= 張三
索引順序優化為 name - position, 但是這時索引只有 name 索引生效，position 沒有生效，因為我
們建立的索引順序是 name - age - position，你會發現跳過了 age，索引本質也是一棵樹，少
了一個節點，下面的索引當然不會生效了，這就沒有滿足最左原則法則。explain select * from employees where position= 北京 and age=15;
這就和第 4 條 sql 一樣的道理了，第一個索引都不見了，后面的不可能生效。執行結果如下：可以發現第 3 條 sql type 的值為 ref、字節是 126 并且 ref 有 2 個 const，全部生效。
第 4 條 sql 只有 122 字節并且 ref 只有 1 個 const，只有 name 索引生效。
第 5 條 sql type 的值為 all，字節和 ref 都是空，全部失效。
下面說第 6 條 sql，剩下的 sql 都是和之前的 sql 一樣的道理。explain select * from employees where position= 北京 and age 15 and name= 張三
這條 sql 我們會發現，把索引字段全部使用了并且當作條件查詢，不一樣的是 age 是范圍查找，優化器替我
們把索引順序優化成 name - age - position ，按照我們索引優化第 2 條：在列上做計算索引失效，范圍之后的索引全部失效，想必答案同學們都知道了。執行結果如下：
第 6 條 sql 只有 126 字節并且 type 的值為 range，范圍查找，只有 name 和 age 索引生效。
以上就是“Mysql 索引底層及優化方法是什么”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注丸趣 TV 行業資訊頻道。
