MySQL--索引入門

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。

Mysql在存儲數據之外，數據庫系統各種還維護著滿足特定查找算法的數據結構，這些數據結構以某種引用（指向）表中的數據，這樣我們就可以通過數據結構上實現的高級算法來快速找到我們想要的數據，而這種數據結構就是索引

簡單理解為“排好序的可以快速查找數據的數據結構”。

索引就好比字典上的目錄

索引概述

如果在查詢的數據表沒有索引，那么SQL語句查詢會從第一條記錄開始匹配判斷，直至匹配成功，而數據庫查詢數據是從磁盤查詢的，每一次匹配都是一次IO流操作，這樣的查詢被稱為全表掃描，及其耗費性能！

有索引的情況下，以二叉樹數據結構演示為例，如果現在表中有ID,NAME,AGE，三條屬性，現在對AGE建立索引，那么就需要維護這樣一個二叉樹，當我們往這張表插入數據時，就需要維護二叉樹的節點，這個節點是指向這一行數據的地址如下圖所示

然后我們就可以根據二叉樹查找的算法來匹配數據，這種方法十分的高效。

注意事項：上述二叉樹索引結構只是一個示意圖，并不是真實的索引結構

索引優勢

提高數據檢索的效率，降低數據庫的IO成本
通過索引對數據進行排序，降低數據排序的成本，降低了CPU的消耗

索引劣勢

索引實際上也是一張表，保存了主鍵和索引的字段，并且指向實體表的記錄，所以索引也是需要占用磁盤空間的。

在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進行數據增刪改的同時，MySQL不僅要更新數據，還需要保存一下索引文件，每次更新添加了的索引列的字段，都會去調整因為更新帶來的減值變化后的索引的信息。

索引數據結構（一般都是指B-Tree）

介紹

MySQL的索引是在引擎層實現的，主要包含以下幾種：

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

索引結構	InnoDB	MySIAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree（空間索引）	不支持	支持	不支持
Full-text（全文索引）	5.6版本支持	支持	不支持

平時大多數的索引，如果沒有特別指明，都是指B+樹結構組織的索引

索引結構

MySQL的數據是存儲在磁盤中的，每次查詢數據語句都需要將數據從磁盤中加載到內存中，，相當于進行了一次IO操作，而磁盤IO操作十分耗時，所以我們優化SQL的方向就是降低IO操作的次數，訪問二叉樹的每個節點就會發生一次IO，如果想要減少磁盤IO操作，就需要盡量降低樹的高度。那如何降低樹的高度呢？

二叉樹

弊端：當極端情況下，數據遞增插入是，會一直向右插入，形成鏈表，查詢效率會降低

解決方法：紅黑樹是弱平衡樹，通過紅黑樹解決樹的平衡問題

但紅黑樹也是二叉樹，也存在大數據量情況下，層級較深，檢索速度慢

B-Tree（多路平衡查找樹）

以一課最大度數（度數：一個節點的子節點個數）為5（5階）的B-Tree為例（每個節點最多存儲4個key，5個指針）：

插入數據的變化過程：

該樹為5階B-Tree，一個節點有5個指針，4個key

首先插入234，345，23，899，現在這個節點已經有4個key，繼續添加1200，節點已經不能裝下了，樹會發生裂變，中間元素向上分裂

則會變為

，繼續添加1234，比345大，則會向右邊插入，比1200大，則會放在1200的右邊，再插入1500，還是放在右邊，此時右邊的節點已經已經有4個key了，繼續添加1000，右邊節點有5個key，繼續分裂，中間元素向上分裂，1200為中間數，向上與345為同一個節點

，繼續插入234，346，都放在左邊，此時左邊的節點已經已經有4個key了，繼續插入12，中間元素向上分裂，123變為中間元素，向上分裂

，繼續插入1567，1800，都放在右邊，此時右邊的節點已經已經有4個key了，繼續插入1980，1567變為中間元素向上分裂

，現在上面的節點已經有4個key了，繼續插入2000，1888，放在右邊，此時右邊的節點已經已經有4個key了，在插入2456，1980變成中間元素向上分裂，上面的節點也要在向上分裂，1200為中間元素向上分裂，最后得

，現在樹的層級來到了三層，如果要存儲數據，每一個數據是掛在key下的，

B+Tree

以一顆最大度數為4階的B+tree為例：

插入數據變化過程：

該樹為5階B+Tree，一個節點有5個指針，4個key

首先插入232，234，567，1000，現在這個節點已經有4個key，在插入1234，中間元素向上分裂，即567向上分裂，B+樹的所有元素都會出現在葉子節點上，

，繼續插入1234,此時右邊已經有4個key了，繼續插入2345，中間元素1000向上分裂，

，插入100，200，在插入35，中間元素200向上分裂，

，以此類推，而下面鏈表的首元素就是向上分離的中間元素

MySQL索引中的B+Tree結構

參考博客：一文搞懂MySQL索引所有知識點（建議收藏）_一文搞懂mysql索引所有知識點敖丙-CSDN博客

B樹的缺點：

B樹不支持范圍查詢的快速查找，你想想這么一個情況如果我們想要查找10和35之間的數據，查找到15之后，需要回到根節點重新遍歷查找，需要從根節點進行多次遍歷，查詢效率有待提高。
如果data存儲的是行記錄，行的大小隨著列數的增多，所占空間會變大。這時，一個頁中可存儲的數據量就會變少，樹相應就會變高，磁盤IO次數就會變大。

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

B+樹的最底層葉子節點包含了所有的索引項。

從圖上可以看到，B+樹在查找數據的時候，由于數據都存放在最底層的葉子節點上，所以每次查找都需要檢索到葉子節點才能查詢到數據。

所以在需要查詢數據的情況下每次的磁盤的IO跟樹高有直接的關系，

但是從另一方面來說，由于數據都被放到了葉子節點，所以放索引的磁盤塊鎖存放的索引數量是會跟著增加的。

所以相對于B樹來說，B+樹的樹高理論上情況下是比B樹要矮的。也存在索引覆蓋查詢的情況，在索引中數據滿足了當前查詢語句所需要的全部數據，此時只需要找到索引即可立刻返回，不需要檢索到最底層的葉子節點。

等值查詢

假如我們查詢值等于9的數據。查詢路徑磁盤塊1->磁盤塊2->磁盤塊6。

第一次磁盤IO：將磁盤塊1加載到內存中，在內存中從頭遍歷比較，9<15，走左路，到磁盤尋址磁盤塊2。
第二次磁盤IO：將磁盤塊2加載到內存中，在內存中從頭遍歷比較，7<9<12，到磁盤中尋址定位到磁盤塊6。
第三次磁盤IO：將磁盤塊6加載到內存中，在內存中從頭遍歷比較，在第三個索引中找到9，取出data，如果data存儲的行記錄，取出data，查詢結束。如果存儲的是磁盤地址，還需要根據磁盤地址到磁盤中取出數據，查詢終止。（這里需要區分的是在InnoDB中Data存儲的為行數據，而MyIsam中存儲的是磁盤地址。）

范圍查詢

假如我們想要查找9和26之間的數據。查找路徑是磁盤塊1->磁盤塊2->磁盤塊6->磁盤塊7。

首先查找值等于9的數據，將值等于9的數據緩存到結果集。這一步和前面等值查詢流程一樣，發生了三次磁盤IO。
查找到15之后，底層的葉子節點是一個有序列表，我們從磁盤塊6，鍵值9開始向后遍歷篩選所有符合篩選條件的數據。
第四次磁盤IO：根據磁盤6后繼指針到磁盤中尋址定位到磁盤塊7，將磁盤7加載到內存中，在內存中從頭遍歷比較，9<25<26，9<26<=26，將data緩存到結果集。
主鍵具備唯一性（后面不會有<=26的數據），不需再向后查找，查詢終止。將結果集返回給用戶。

可以看到B+樹可以保證等值和范圍查詢的快速查找，MySQL的索引就采用了B+樹的數據結構。

B+Tree和B-Tree的區別：

B樹：非葉子節點和葉子節點都會存儲數據。
B+樹：只有葉子節點才會存儲數據，非葉子節點只存儲鍵值，起到索引的效果。葉子節點之間使用雙向指針連接，最底層的葉子節點形成了一個雙向有序鏈表。

Hash（哈希索引）

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

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

特點
- Hash索引只能用于對等比較（= ，in），不支持范圍查詢（between，>,<,....）
- hash表是無序，無索引的，無法利用索引完成排序操作
- 查詢效率高，通常指需要一次檢查就可以了，效率通常高于B+Tree索引
存儲引擎支持

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

面試題：為什么InnoDB存儲引擎選擇B+Tree索引結構？

相對于二叉樹，層級更少，而層級意味著IO操作的多少，我們優化SQL的方向就是降低IO操作的次數,即降低樹的高度（層級數）
B+樹只有葉子節點存放數據，其余節點不存放，而一個節點就是MySQL中的一頁（Page），而其余節點（Page）存放的指針和key也就增多，那么在相同數據量的情況下，B+樹的層級更少，那么性能就越高，
且B樹的范圍匹配的效率很低，需要每次都返回根節點查詢，而B+樹在查詢首個數據之后可以直接在葉子節點中遍歷需要的數據，
相對于Hash索引，B+樹支持范圍匹配和排序操作

索引使用場景

推薦建立索引：

主鍵自動建立唯一索引
頻繁作為查詢條件的字段應該創建索引（where后面的語句）
查詢中與其他表關聯的字段，外鍵關系建立索引
多字段查詢下傾向創建聯合索引
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統計或者分組字段

不推薦建立索引：

表記錄太少
經常增刪改查
where條件里用不到的字段不建立索引

索引分類

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

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

分類	含義	特點
聚集索引（Clustered Index）（B+樹）	將數據存儲與索引放在一塊，索引結構的葉子節點保存了行數據	必須有。而且只能有一個
二級索引（輔助索引）（Secondary index）	將數據與索引分開存儲，索引結構的葉子節點關聯的是對應的主鍵	可以存在多個

聚集索引的選舉規則：

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

聚集索引葉子節點存放的是行數據

二級索引中葉子節點存放的是指向聚集索引葉子節點的指針

聚集索引與二級索引的葉子節點存放的數據的關系就是指針與二級指針的關系

如下圖：

如果查詢的是name字段的話，那么數據庫進行的就是回表查詢

數據庫會先走二級索引，通過二級索引找到對應的主鍵值，再根據主鍵值到聚集索引當中拿到行數據

思考：InnoDB主鍵索引的B+Tree高度為多高?

假設：一行數據大小為1k，一頁中可以存儲16行這樣的數據，InnoDB的指針占用6個字節的空間，主鍵即使為bigint，占用字節數為8.

如果樹的高度為2：

由于B+樹只有葉子節點才存儲數據

16 * 1024 = n * 8 +(n+1 )*6

得出：n = 1170,每一個節點的key為1171個

得出存儲的記錄為：11701* 16 * =18736

高度為3時

1171 * 1171 * 16 =21939386條記錄

索引語法

創建索引

?create [unique|fulltext] index index_name on table_name (index_col_name,...);

如果一個索引只關聯一個字段，被稱為單列索引。如果一個索引關聯了多個字段，則被稱為聯合索引（組合索引）。

查看索引

?show index from table_name

刪除索引

?drop index index_name on table_name

案例：

前置代碼：

?create database if not exists Demo;use Demo;create table if not exists `user`(`id` int(4) not null auto_increment comment 'ID',`name` varchar(20) not null ? comment '姓名',`phone` varchar(11) not null comment '電話號碼',`email` varchar(20) default null comment '郵箱',`profession` varchar(20) default null comment '專業',`age` int(4) not null comment '年齡',`gender` tinyint(1) ?default null comment '性別，取值為0或1',`status` int(4) not null comment '狀態 取值為0與6之間',`createTime` datetime not null comment '創建時間',primary key (`id`))engine = innodb default charset = utf8;INSERT INTO `user` (`name`, `phone`, `email`, `profession`, `age`, `gender`, `status`, `createTime`) VALUES('王偉', '13800138001', 'wangwei@example.com', '計算機科學', 25, 1, 1, '2023-01-01 10:00:00'),('李娜', '13800138002', 'lina@example.com', '軟件工程', 28, 0, 3, '2023-01-02 11:00:00'),('張敏', '13800138003', 'zhangmin@example.com', '數據科學', 22, 0, 2, '2023-01-03 12:00:00'),('劉強', '13800138004', 'liuqiang@example.com', '人工智能', 30, 1, 6, '2023-01-04 13:00:00'),('陳靜', '13800138005', 'chenjing@example.com', '網絡安全', 26, 0, 4, '2023-01-05 14:00:00'),('楊光', '13800138006', 'yangguang@example.com', '計算機科學', 24, 1, 5, '2023-01-06 15:00:00'),('趙琳', '13800138007', 'zhaolin@example.com', '軟件工程', 27, 0, 1, '2023-01-07 16:00:00'),('黃磊', '13800138008', 'huanglei@example.com', '數據科學', 29, 1, 0, '2023-01-08 17:00:00'),('吳芳', '13800138009', 'wufang@example.com', '人工智能', 23, 0, 2, '2023-01-09 18:00:00'),('周濤', '13800138010', 'zhoutao@example.com', '網絡安全', 31, 1, 3, '2023-01-10 19:00:00'),('徐洋', '13800138011', 'xuyang@example.com', '計算機科學', 25, 1, 4, '2023-01-11 20:00:00'),('孫麗', '13800138012', 'sunli@example.com', '軟件工程', 28, 0, 5, '2023-01-12 21:00:00'),('馬超', '13800138013', 'machao@example.com', '數據科學', 22, 1, 6, '2023-01-13 22:00:00'),('朱婷', '13800138014', 'zhuting@example.com', '人工智能', 30, 0, 1, '2023-01-14 23:00:00'),('胡軍', '13800138015', 'hujun@example.com', '網絡安全', 26, 1, 2, '2023-01-15 10:00:00'),('林小燕', '13800138016', 'linxiaoyan@example.com', '計算機科學', 24, 0, 3, '2023-01-16 11:00:00'),('郭峰', '13800138017', 'guofeng@example.com', '軟件工程', 27, 1, 4, '2023-01-17 12:00:00'),('何潔', '13800138018', 'hejie@example.com', '數據科學', 29, 0, 5, '2023-01-18 13:00:00'),('高翔', '13800138019', 'gaoxiang@example.com', '人工智能', 23, 1, 6, '2023-01-19 14:00:00'),('羅娟', '13800138020', 'luojuan@example.com', '網絡安全', 31, 0, 0, '2023-01-20 15:00:00'),('鄭凱', '13800138021', 'zhengkai@example.com', '計算機科學', 25, 1, 1, '2023-01-21 16:00:00'),('謝芳', '13800138022', 'xiefang@example.com', '軟件工程', 28, 0, 2, '2023-01-22 17:00:00'),('董明', '13800138023', 'dongming@example.com', '數據科學', 22, 1, 3, '2023-01-23 18:00:00'),('蕭紅', '13800138024', 'xiaohong@example.com', '人工智能', 30, 0, 4, '2023-01-24 19:00:00'),('曹陽', '13800138025', 'caoyang@example.com', '網絡安全', 26, 1, 5, '2023-01-25 20:00:00'),('鄧超', '13800138026', 'dengchao@example.com', '計算機科學', 24, 1, 6, '2023-01-26 21:00:00'),('許晴', '13800138027', 'xuqing@example.com', '軟件工程', 27, 0, 0, '2023-01-27 22:00:00'),('彭宇', '13800138028', 'pengyu@example.com', '數據科學', 29, 1, 1, '2023-01-28 23:00:00'),('蔣雯', '13800138029', 'jiangwen@example.com', '人工智能', 23, 0, 2, '2023-01-29 10:00:00'),('蔡明', '13800138030', 'caiming@example.com', '網絡安全', 31, 1, 3, '2023-01-30 11:00：00）;

案例要求：

name字段為姓名字段,該字段的值可能會重復，為該字段創建索引
phone手機號字段的值是非空的，且唯一的，為該字段創建唯一索引。
為profession、age、status創建聯合索引
為email創建合適的索引來提升查詢效率

代碼展示：

show index from user;create index ? `idx_user_name` on `user`(`name`);create unique index `idx_user_phone` on `user`(`phone`);create index `id_user_pas` on `user`(`profession`,`age`,`status`);create index ? `idx_user_email` on `user`(`email`);drop index `idx_user_email` on `user`;