Mysql索引篇

最近在很多網站上看了索引的相關知識，各種說法的都有，但是又不是很全，有的概念很模糊，下面是由小編整理的Mysql索引知識點。

一.首先我們說下什麼是索引，為什麼要用索引

索參照於快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢資料所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜尋資料檔案，而不必檢視所有資料，那麼將會節省很大一部分時間。

二. 索引型別分為兩類：

1.hash索引

2.bTree

三.下面我們簡單分析一下hash索引和bTree索引。

1. 雜湊表是一種以鍵 - 值（key-value）儲存資料的結構，我們只要輸入待查詢的鍵即 key，就可以找到其對應的值即 Value。雜湊的思路很簡單，把值放在陣列裡，用一個雜湊函數把 key 換算成一個確定的位置，然後把 value 放在陣列的這個位置。

不可避免地，多個 key 值經過雜湊函數的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個連結串列。

2. 說到bTree，就不得不提二元樹，二元樹分為很多，例：二叉查詢樹，平衡二元樹等。當然還有重點紅黑樹。
1） 二叉查詢樹的特點是： 父節點左子樹所有節點的值小於父節點的值。右子樹所有節點的值大於父節點的值。 下面以一張圖為例來體現二叉查詢樹。

有一個需求，查詢張三，如果不使用二叉查詢樹那麼我們需要查詢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
第3條：
explain select * from employees where age=15 and name='張三';
sql在執行的時候，優化器替我們把索引的順序優化了，由 age -> name 變成 name -> age，這時
索引是生效的。
第4條：
explain select * from employees where position='北京' and name='張三';
索引順序優化為name - > position,但是這時索引只有name索引生效，position沒有生效，因為我
們建立的索引順序是 name  -> age - > position，你會發現跳過了age，索引本質也是一棵樹，少
了一個節點，下面的索引當然不會生效了，這就沒有滿足最左原則法則。
第5條：
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索引底層及優化的詳細內容，更多請關注TW511.COM其它相關文章！