1. 引入索引

索引：提高數據庫的性能，索引是物美價廉的東西了。

• 不用加內存，不用改程序，不用調sql，只要執行正確的 create index ，查詢速度就可能提高成百上千倍。
• 但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的IO。所以它的價值，在于提高一個海量數據的檢索速度。

常見索引分為：

• 主鍵索引
• 唯一索引
• 普通索引
• 全文索引(fulltext)：解決中子文索引問題。

下面舉一個例子來查看索引的作用

已有一張具有八百萬條數據的表，在查詢的時候，看看沒有索引時有什么問題？

查詢員工編號為998877的員工

可以看到耗時5.54秒，這還是在本機一個人來操作，在實際項目中，如果放在公網中，假如同時有1000個人并發查詢，那很可能就死機。

解決方法，創建索引

換一個員工編號，查詢時間也快了很多。

我們知道，MySQL的服務器mysqld本質是在內存中運行的，所有的CRUD操作也都是在內存中運行的，所以，索引在內存中，就是以特定的數據結構組織的一種結構，加快了查找的效率

2. MySQL與磁盤交互的基本單位

之前學習文件系統，就是在磁盤的基本結構下建立的，文件系統讀取基本單位，就不是扇區，而是數據塊。
故，系統讀取磁盤，是以塊為單位的，基本單位是 4KB

點擊了解文件系統的4KB

而 MySQL 作為一款應用軟件，可以想象成一種特殊的文件系統。它有著更高的IO場景，所以，為了提高基本的IO效率， MySQL 進行IO的基本單位是 16KB

也就是說，磁盤這個硬件設備的基本單位是 512 字節，而 MySQL InnoDB引擎 使用 16KB 進行IO交互。
即， MySQL 和磁盤進行數據交互的基本單位是 16KB，這個基本數據單元，在 MySQL 這里叫做page

---

• MySQL 中的數據文件，是以page為單位保存在磁盤當中的。
• MySQL 的 CRUD 操作，都需要通過計算，找到對應的插入位置，或者找到對應要修改或者查詢的數據。
• 而只要涉及計算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數據移動到內存當中。
• 所以在特定時間內，數據一定是磁盤中有，內存中也有。后續操作完內存數據之后，以特定的刷新策略，刷新到磁盤。而這時，就涉及到磁盤和內存的數據交互，也就是IO了，而此時IO的基本單位就是Page
• 為了更好的進行上面的操作， MySQL 服務器在內存中運行的時候，在服務器內部，就申請了被稱為 Buffer Pool 的大內存空間，來進行各種緩存，其實就是很大的內存空間，來和磁盤數據進行IO交互。
• 為了更高的效率，一定要盡可能的減少系統和磁盤IO的次數

3. 索引的理解

3.1 頁目錄

1. 建立測試表

2. 插入數據

為什么我們查找的時候是有序的了呢？誰排的序？為什么排序？

為了方便引入頁內目錄

思考一下，為何MySQL和磁盤進行IO交互的時候，要采用Page的方案進行交互呢?用多少，加載多少不香嗎?

• 如上面的5條記錄，如果MySQL要查找id=2的記錄，第一次加載id=1，第二次加載id=2，一次一條記錄，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
• 但，如果這5條(或者更多)都被保存在一個Page中(16KB，能保存很多記錄)，那么第一次IO查找id=2的時候，整個Page會被加載到MySQL的Buffer
  Pool中，這里完成了一次IO。
• 但是往后如果在查找id=1,3,4,5等，完全不需要進行IO了，而是直接在內存中進行了。所以，就在單Page里面，大大減少了IO的次數。
• 你怎么保證，用戶一定下次找的數據，就在這個Page里面？我們不能嚴格保證，但是有很大概率，因為有局部性原理。
• 往往IO效率低下的最主要矛盾不是IO單次數據量的大小，而是IO的次數

MySQL 中要管理很多數據表文件，而要管理好這些文件，就需要先描述，在組織，我們目前可以簡單理解成一個個獨立文件是由一個或者多個Page構成的

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構成雙向鏈表

因為有主鍵的問題， MySQL 會默認按照主鍵給我們的數據進行排序，從上面的Page內數據記錄可以看出，數據是有序且彼此關聯的。

為什么數據庫在插入數據時要對其進行排序呢？我們按正常順序插入數據不是也挺好的嗎？

• 插入數據時排序的目的，就是優化查詢的效率。
• 頁內部存放數據的模塊，實質上也是一個鏈表的結構，鏈表的特點也就是增刪快，查詢修改慢，所以查詢的效率是必須要進行優化的。
• 正是因為有序，在查找的時候，從頭到后都是有效查找，沒有任何一個查找是浪費的，而且，如果運氣好，是 可以提前結束查找過程的。

如果有1千萬條數據，一定需要多個Page來保存1千萬條數據，多個Page彼此使用雙鏈表鏈接起來，而且每個Page內部的數據也是基于鏈表的。那么，查找一條特定記錄，也一定是線性查找，這效率也太低了。

因此，就需要引入頁目錄了

頁目錄就像書中的目錄，它標志著一段內容的起點

• 單頁情況

針對上面的單頁Page，我們能否也引入目錄呢？當然可以

那么當前，在一個Page內部，我們引入了目錄。比如，我們要查找id=4記錄，之前必須線性遍歷4次，才能拿到結果。現在直接通過目錄2[3]，直接進行定位新的起始位置，然后再遍歷2 次，提高了效率。
現在我們可以再次正式回答上面的問題了，為何通過鍵值 MySQL 會自動排序？ - - 可以很方便引入目錄（拿空間換時間）

• 多頁情況

MySQL 中每一頁的大小只有 16KB ，單個Page大小固定，所以隨著數據量不斷增大，16KB 不可能存下所有的數據，那么必定會有多個頁來存儲數據。

在單表數據不斷被插入的情況下， MySQL 會在容量不足的時候，自動開辟新的Page來保存新的數據，然后通過指針的方式，將所有的Page組織起來。

這樣，我們就可以通過多個Page遍歷，Page內部通過目錄來快速定位數據。

可是，貌似這樣也有效率問題，在Page之間，也是需要 MySQL 遍歷的，遍歷意味著依舊需要進行大量的IO，將下一個Page加載到內存，進行線性檢測。這樣就顯得我們之前的Page內部的目錄，有點杯水車薪了。

那么如何解決呢？- - 解決方案，其實就是我們之前的思路，給Page也帶上目錄

存在一個目錄頁來管理頁目錄，目錄頁中的數據存放的就是指向的那一頁中最小的數據。有數據，就可通過比較，找到該訪問那個Page，進而通過指針，找到下一個Page。
其實目錄頁的本質也是頁，普通頁中存的數據是用戶數據，而目錄頁中存的數據是普通頁的地址。

3.2 B+樹

可是，我們每次檢索數據的時候，該從哪里開始呢？雖然頂層的目錄頁少了，但是還要遍歷啊？不用擔心，可以繼續加目錄頁

上圖就是傳說中的B+樹！至此，我們已經給我們的表user構建完了主鍵索引。

整個B+樹，叫做mysql中InnoDB下的索引結構，是緩存在mysql的buffer_pool中的

隨便找一個id=？我們發現，現在查找的Page數一定減少了，也就意味著IO次數減少了，那么效率也就提高了。

總結

• Page分為目錄頁和數據頁。目錄頁只放各個下級Page的最小鍵值。
• 查找的時候，自定向下找，只需要加載部分目錄頁到內存，即可完成算法的整個查找過程，大大減少了IO次數

InnoDB 在建立索引結構來管理數據的時候，其他數據結構為何不行？

• 鏈表？線性遍歷
• 二叉搜索樹？退化問題，可能退化成為線性結構
• AVL &&紅黑樹？雖然是平衡或者近似平衡，但是畢竟是二叉結構，相比較多階B+，意味著樹整體過高，大家都是自頂向下找，層高越低，意味著系統與硬盤更少的IO Page交互。雖然你很秀，但是有更秀的。
• Hash？官方的索引實現方式中， MySQL 是支持HASH的，不過 InnoDB 和 MyISAM 并不支持。Hash跟進其算法特征，決定了雖然有時候也很快(O(1))，不過，在面對范圍查找就明顯不行，另外還有其他差別，有興趣可以查一下。
• B樹？最值得比較的是 InnoDB 為何不用B樹作為底層索引？

B樹：節點既有數據，又有Page指針

B+樹：只有葉子節點有數據，其他目錄頁，只有鍵值和Page指針；B+樹的葉子節點，全部相連，而B沒有

為何選擇B+樹呢？

• 非葉子節點不存儲data，這樣一個節點就可以存儲更多的key
• 可以使得樹更矮，所以IO操作次數更少。
• 葉子節點相連，更便于進行范圍查找

4. 聚簇索引、非聚簇索引

MyISAM 的主鍵索引是一個非聚集索引，這意味著索引和數據是分開存儲的

索引的葉子節點存儲的是主鍵值和數據記錄的地址，而不是數據記錄本身。數據記錄存儲在單獨的 .MYD 文件中，而索引存儲在 .MYI 文件中，表結構在.sdi文件中。

MyISAM 這種用戶數據與索引數據分離的索引方案，叫做非聚簇索引

• MyISAM

• InnoDB

InnoDB 這種用戶數據與索引數據在一起索引方案，叫做聚簇索引

---

MyISAM 引擎同樣使用B+樹作為索引結果，葉節點的data域存放的是數據記錄的地址。下圖為 MyISAM表的主索引， Col1 為主鍵。

MySQL 除了默認會建立主鍵索引外，我們用戶也有可能建立按照其他列信息建立的索引，一般這種索引可以叫做普通索引。

對于 MyISAM ，建立普通索引和主鍵索引沒有差別，無非就是主鍵不能重復，而非主鍵可重復。

下圖就是基于 MyISAM 的 Col2 建立的索引，和主鍵索引沒有差別

InnoDB 除了主鍵索引，用戶也會建立普通索引，我們以上表中的 Col3 建立對應的輔助

InnoDB 的非主鍵索引中葉子節點并沒有數據，而只有對應記錄的key值。

• 所以通過普通索引，找到目標記錄，需要兩遍索引： 首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄,這種過程，就叫做回表查詢
• 為何 InnoDB 針對這種輔助（普通）索引的場景，不給葉子節點也附上數據呢？因為太浪費空間了，同一份數據沒有必要存兩份

5. 索引的操作

5.1 索引的創建

5.1.1 創建主鍵索引

1. 第一種方式：在創建表的時候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30)
);

2. 第二種方式：在創建表的最后，指定某列或某幾列為主鍵索引

create table user2(id int, name varchar(30), primary key(id)
);

3. 第三種方式：創建表以后再添加主鍵

create table user3(id int, name varchar(30)
);alter table user3 add primary key(id);

主鍵索引的特點：

• 一個表中，最多有一個主鍵索引，當然可以也可是使用復合主鍵
• 主鍵索引的效率高（主鍵不可重復）
• 創建主鍵索引的列，它的值不能為null，且不能重復
• 主鍵索引的列的類型基本上是int

5.1.2 創建唯一索引

1. 第一種方式：表定義時，在某列后直接指定unique唯一屬性

create table user4(id int primary key, name varchar(30) unique
);

2. 第二種方式：創建表時，在表的后面指定某列或某幾列為unique

create table user5(id int primary key,name varchar(30), unique(name));

3. 第二種方式：創建表以后再添加唯一鍵

create table user6(id int primary key, name varchar(30)
);alter table user6 add unique(name);

唯一索引的特點：

• 一個表中，可以有多個唯一索引
• 查詢效率高
• 如果在某一列建立唯一索引，必須保證這列不能有重復數據
• 如果一個唯一索引上指定not null，等價于主鍵索引

5.1.3 普通索引的創建

1. 第一種方式：在表的定義最后，指定某列為索引

create table user8(id int primary key,name varchar(20),email varchar(30),index(name)
);

2. 第二種方式：創建完表以后指定某列為普通索引

create table user9(id int primary key,name varchar(20), email varchar(30)
);alter table user9 add index(name); 

3. 第三種方式：創建一個索引名為 idx_name 的索引

create table user10(
id int primary key, name varchar(20), email varchar(30)
);create index idx_name on user10(name);

普通索引的特點

• 一個表中可以有多個普通索引，普通索引在實際開發中用的比較多
• 如果某列需要創建索引，但是該列有重復的值，那么我們就應該使用普通索引

5.1.4 全文索引的創建

當對文章字段或有大量文字的字段進行檢索時，會使用到全文索引。

MySQL提供全文索引機制，但是有要求，要求表的存儲引擎必須是MyISAM，而且默認的全文索引支持英文，不支持中文。

假設你有一個存儲文章的表，希望為標題和內容字段添加全文索引

查詢有沒有database數據，如果使用如下查詢方式，雖然查詢出數據，但是沒有使用到全文索引

使用全文索引

索引創建原則

• 比較頻繁作為查詢條件的字段應該創建索引
• 唯一性太差的字段不適合單獨創建索引，即使頻繁作為查詢條件
• 更新非常頻繁的字段不適合作創建索引
• 不會出現在where子句中的字段不該創建索引

5.2 索引的查詢

1. 第一種方法： show keys from 表名

復合索引，當索引值就是我們想要的值時設置，這樣就可以之間將索引返回了；這也叫做索引覆蓋（覆蓋的是主鍵索引，無須回表查詢了）

2. 第二種方法: show index from 表名，結果與第一種方法相同
3. desc 表名；

5.3 刪除索引

1. 第一種方法，刪除主鍵索引：

alter table 表名 drop primary key;

2. 第二種方法，刪除其他索引：

 alter table 表名 drop index 索引名；

索引名就是show keys from 表名中的 Key_name 字段

3. 第三種方法方法：

 drop index 索引名 on 表名