MySQL系列

---

---

前言

上一篇：MySQL 函數大賞：聚合、日期、字符串等函數剖析

在MySQL數據庫中，索引是一種特殊的數據結構，它與表中數據關聯，就像書籍的目錄與正文的關系——目錄通過章節標題和頁碼快速定位內容，而索引則通過存儲數據的關鍵列值及其對應物理位置，幫助數據庫快速定位目標數據。

---

本篇文章以主流的InnoDB引擎為例，展開介紹

案例

在MySQL中操作存儲大量數據的表時，我們往往會面臨處理數據慢，性能低下等問題，這時只需要建立索引就可以將這種問題優化。

從操作執行時間不難看出，建立索引給我們操作帶來的巨大提升。

在學習索引是如何優化MySQL的性能之前，需要先知道MySQL為什么存在性能方面的問題。

一、認識MySQL與磁盤

1.1 MySQL與存儲

MySQL 給用戶提供數據存儲服務的，早在之前我就介紹過，MySQL使用的庫、表對數據存儲，在Linux下都表現為特殊結構的文件，要想對數據進行持久化保存，這些文件最終都要存儲在磁盤中，所有MySQL下數據存儲在磁盤這個外設當中，相比于計算機其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一個重要話題。

Linux文件系統
這篇文章中詳細介紹了系統對磁盤的訪問，強烈建議看一下,這里就不介紹了

1.2 MySQL 與磁盤交互基本單位

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

磁盤這個硬件設備的基本單位是 512 字節，操作系統在和磁盤交互時以4KB為單位，而 MySQL InnoDB引擎使用 16KB 和內存進行IO交互。所以可以理解為， MySQL 和磁盤進行數據交互的基本單位是 16KB 。這個基本數據單元，在 MySQL 這里叫做page（注意和系統的page區分）。

 SHOW GLOBAL STATUS LIKE 'innodb_page_size';

使用這個sql語句可以查找引擎頁大小。

二、 MySQL 數據交互核心：BufferPool 與 IO 優化機制

通過前文介紹可知，MySQL 中的數據文件在磁盤上以 page（16KB） 為單位存儲。當執行 CURD（增刪改查）操作時，MySQL 需先通過計算定位目標數據的位置——這一過程依賴 CPU 參與，而 CPU 只能直接操作內存數據。因此，數據必須先從磁盤臨時加載到內存中，形成“磁盤一份、內存一份”的臨時狀態。待內存中的數據操作完成后，再通過特定策略將更新同步回磁盤，這一過程即涉及磁盤與內存的交互（IO），而 IO 的基本單位正是 page。

為高效管理內存中的數據、減少頻繁的磁盤 IO，MySQL 服務器在啟動時會在內存中申請一塊專用的大內存區域，稱為 BufferPool（緩沖池）。它的核心作用是：

• 緩存熱點數據：將頻繁訪問的 page 臨時存儲在內存中，避免每次操作都直接讀寫磁盤（局部性原理：當你對某一塊數據操作時，你的下一次操將有很大概率，會使用后面的數據）。
• 優化 IO 效率：所有數據操作先在 BufferPool 中完成，操作完成后同步到磁盤，大幅減少磁盤 IO 次數（不可以操作一行，就獲取一行）。

因此，減少系統與磁盤的 IO 次數是提升 MySQL 效率的核心原則，而 BufferPool 正是實現這一目標的關鍵機制——它通過內存緩存降低了磁盤 IO 對性能的影響。

三、索引的理解

3.1 測試案例

建立測試表
create table if not exists user (
id int primary key, --一定要添加主鍵哦，只有這樣才會默認生成主鍵索引
age int not null,
name varchar(16) not null
);
插入多條記錄，注意此處數據的主鍵順序
insert into user (id, age, name) values(3, 18, '楊過');insert into user (id, age, name) values(4, 16, '小龍女');insert into user (id, age, name) values(2, 26, '黃蓉');insert into user (id, age, name) values(5, 36, '郭靖');insert into user (id, age, name) values(1, 56, '歐陽鋒');

可以發現MySQL會將插入的數據默認變為有序，那么這樣做有什么好處呢？
排序插入是為了優化查詢效率

具體形式后面介紹

3.2 page

我們目前可以簡單理解一個獨立表文件是由一個或者多個Page構成的，那么這個表該如何管理這些page呢？

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構成雙向鏈表（像這種結構在學習Linux時，我們經常遇到），因為有主鍵的問題， MySQL 會默認按照主鍵給我們的數據進行排序，從上面的Page內數據記錄可以看出，數據是有序且彼此關聯的。

頁內部存放數據的模塊，實質上是一個鏈表的結構，鏈表的特點也就是增刪快，查詢修改慢，所以優化查詢的效率是必須的，正是因為有序，在查找的時候，從頭到后都是有效查找，沒有任何一個查找是浪費的（這一點在后面感受）

通過頁模式，MySQL 查詢時會將一整頁數據（16KB）加載到內存，以此減少硬盤 IO 次數、提升性能。

但頁模式內部采用鏈表結構，本質上需通過逐條數據比較定位目標。若表數據量大，且目標數據位于最后一個 page 的最后一條，仍需遍歷全表，導致查找速度過慢。

3.3 頁目錄

在課本中查找知識點時，我們會選擇優先查看目錄，找到具體的頁，再從頁中查找知識點，這樣的查找效率要比從頭開始找，高效的多。在這里每一個獨立的page或每一行數據，都可以視為“頁”，而我們要做的是，給這些“頁”添加屬于他們的目錄。
page內部：

現在，要在一個Page內部，查找id=3記錄，直接通過目錄2[3]，直接進行定位新的起始位置，提高了效率。現在我們可以再次正式回答上面的問題了，為何通過鍵值 MySQL 會自動排序？可以很方便引入目錄，提高查找效率

圖中是為了迎合上面的數據，在實際情況下目錄間的區間是很大的，在進行目錄查找時，一次查找可以pass掉很多數據。

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

上面的方法幫我們提高了表內部遍歷數據的效率，但是仍需要將每個page都，加載值內存中，為了進一步減少IO操作，我們采用頁目錄的方式

依照這個思路，我們還可以對目錄頁再次添加目錄管理，現在可以得出結論：

• Page分為目錄頁和數據頁。目錄頁只放各個下級Page的最小鍵值。
• 查找的時候，自定向下找，只需要加載部分目錄頁到內存，即可完成算法的整個查找過程，大大減少了IO次數
  這個結構最終就是一顆B+樹，整個過程中我們所要IO的次數，就是整個結構數的高度

目錄頁的本質也是頁，普通頁中存的數據是用戶數據，而目錄頁中存的數據是普通頁的地址。

3.3 對比其他結構

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操作次數更少。葉子節點相連，更便于進行范圍查找

具體結構特征，你可以搜點圖片理解

四、聚簇索引 VS 非聚簇索引

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

其中，MyISAM最大的特點是，將索引Page和數據Page分離，也就是葉子節點沒有數據，只有對應數據的地址相較于InnoDB索引，InnoDB是將索引和數據放在一起的。
現在我們就可以回答，第一篇文章遺留的問題了

innodb引擎
create table itest(
id int primary key,
name varchar(11) not null
)engine=InnoDB;
MyISAM引擎
create table mtest(
id int primary key,
name varchar(11) not null
)engine=MyISAM;

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

MySQL 除了默認會建立主鍵索引外，我們用戶也有可能建立按照其他列信息建立的索引，一般這種索引可以叫做輔助（普通）索引。對于MyISAM ,建立輔助（普通）索引和主鍵索引沒有差別，無非就是主鍵不能重復，而非主鍵可重復。
下圖就是基于 MyISAM 的 Col2 建立的索引，和主鍵索引沒有差別

同時我們以上表中的 Col3 建立對應的輔助索引如下圖：

MyISAM 的非主鍵索引中葉子節點并沒有數據，而只有對應記錄的key值。
所以通過輔助（普通）索引，找到目標記錄，需要兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這種過程，就叫做回表查詢為何MyISAM 針對這種輔助（普通）索引的場景，不給葉子節點也附上數據呢？原因就是太浪費空間了。
那么普通索引為什么要存在呢？
當我們以符合間進行索引時如：（姓名，qq），只知道第一個鍵值，需要查找第二個鍵值，我們就可以直接查找：

對于復合索引，匹配原則是，從做到右的，也就是說，我們只需要知道姓名，就可以得到qq，這種普通索引方式，要比主鍵索引更快。

五、索引操作

5.1 創建主鍵索引

// 在創建表的時候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));//在創建表的最后，指定某列或某幾列為主鍵索引
create table user2(id int, name varchar(30), primary key(id));//創建表以后再添加主鍵
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

主鍵索引的特點：

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

這些特點，在介紹主鍵時都說過，只是當時沒有提出索引的概念

5.2 唯一索引的創建

//在表定義時，在某列后直接指定unique唯一屬性。
create table user4(id int primary key, name varchar(30) unique);//創建表時，在表的后面指定某列或某幾列為unique
create table user5(id int primary key,name varchar(30), unique(name));//創建表以后再添加
create table user6(id int primary key, name varchar(30)）;
alter table user6 add unique(name);

唯一索引的特點：

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

5.3 普通索引的創建

 //在表的定義最后，指定某列為索引
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name)
);//創建完表以后指定某列為普通索引
create table user9(id int primary key, name varchar(20),email varchar(30));
alter table user9 add index(name); //創建一個索引名為 idx_name 的索引
create table user10(id int primary key, name varchar(20),email varchar(30));create index idx_name on user10(name);

普通索引的特點：

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

5.4 查詢索引

show keys from 表名;show index from 表名;

5.5 刪除索引

//刪除主鍵索引 
alter table 表名 drop primary key;//其他索引的刪除
alter table 表名 drop index 索引名; drop index 索引名 on 表名

余下指令你自己測試吧