【MySQL篇】索引特性，索引的工作原理以及索引的創建與管理

一，初識索引

二，MySQL與磁盤交互的基本單位

?三，MySQL中數據文件的特性

四，理解page和索引

?五，聚簇索引和非聚簇索引

六，索引操作

查詢索引

創建主鍵索引

唯一索引的創建

普通索引的創建?

全文索引的創建

刪除索引

索引創建原則?

一，初識索引

MySQL索引類似于書籍的索引（目錄），每個書籍都有目錄，通過目錄可以快速定位到要查找的頁。

MySQL索引是一種數據結構，用于加快數據庫查詢的速度和性能。

索引能夠顯著提高查詢的速度，尤其是在大型表中進行搜索時。通過使用索引，MySQL可以直接定位到滿足條件的數據行，不需要遍歷整個表。

但是查詢速度的提高，同時是以插入，更新，和刪除的速度為代價的，這些寫操作，增加了大量的IO。所以它的價值，在于提高一個海量數據的檢索速度。

常見的索引分類：

主鍵索引（primary key）
唯一鍵索引（unique）
普通索引（index）
全文索引（fulltext）

二，MySQL與磁盤交互的基本單位

MySQL在應用層給用戶提供存儲服務，用戶可以進行CURD操作，而存儲的都是數據，數據在磁盤這個外設中。磁盤是計算機的一個機械設備，相比于計算機其他電子元件，磁盤效率是比較低的。我們平時對表的操作，都是需要進行IO的。

我們知道，操作系統和磁盤交互（IO）的基本單位是4KB。而MySQL作為一款應用層軟件，可以想象成一種特殊的文件系統。它有著更高的IO場景，所以，為了提高基本的IO效率，MySQL和磁盤進行數據交互的基本單位是16KB（存儲引擎為Innodb）。這個基本數據單元，在MySQL中叫做page。

?三，MySQL中數據文件的特性

MySQL中的數據文件，是以page為單位保存在磁盤上的。

MySQL的CURD操作，都是需要計算的，找到對應的插入位置，找到對應要修改或者查詢的數據。

而只要涉及到計算，就需要CPU的參與，而為了便于CPU的參與，一定要先將數據移到內存中。

所以再特定時間內，數據一定是磁盤中有，內存中也有。后序操作完內存數據之后，以特點的刷新策略，刷新的磁盤上。而這時就涉及到磁盤和內存的數據交互，也就是IO了。此時IO的基本單位是page。

為了更好的進行上面的操作，MySQL服務器在內存中運行的時候，在服務器內部，就申請了Buffer pool的大內存空間，來進行各種緩存。其實就是很大的空間，來和磁盤進行IO交互。

在Innodb存儲引擎下，Buffer pool的大小為128M。MySQL需要自己對這部分空間進行管理。

為了更高的效率，一定要盡可能的減少系統的磁盤的IO次數。

四，理解page和索引

MySQL內部，將來Buffer pool緩沖區中一定需要并且存在大量的page，所以MySQL必須將這些page管理起來。通過“先描述，再組織”。所以page內部并不是單純的存儲數據，page內部也需要寫入對應的管理信息。我們目前可以簡單的理解成一個個獨立的文件是由一個或者多個page構成的。

?思考一下：為何MySQL和磁盤進行IO交互時候，要采用page的方案進行交互呢？為什么不是用多少加載多少呢？

因為預加載可以有效減少IO的次數。通過局部性原理，當前訪問某些數據或代碼的某一行時，下次訪問可能會在這次訪問的周邊進行訪問。
往往IO效率低下的最主要矛盾不是單次IO數據量的大小，而是IO的次數。

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

?上面的單個頁，在查詢數據的時候，直接將一整頁的數據加載到內存，以減少硬盤IO次數，從而提高性能。但是，我們也可以看到，現在的頁內部，實際上是采用了鏈表的結構，前一條數據指向后一條數據，本質上還是通過數據的逐條比較來取出特定的數據。
如果有1千萬條數據，一定需要多個page頁保存起來，多個page彼此采用雙向鏈表連接起來，而每個page內部也是有鏈表來管理的。那么查找特定的一條記錄，也一定是線性查找，效率太低了。

所以這時就需要引入目錄了。

頁目錄：

就以書籍為例，每本數都有目錄。我們如果要看指定的章節，找到章節有兩種做法：

從頭逐頁的向后翻，直到找到目標內容。
通過提供的目錄，找到指定的頁數。當然在找目錄的過程中，可以順序查找，不過因為目錄肯定少，所以可以快速提高定位。
本質上，書本中的目錄是花了額外的紙做的，但卻提高了效率。
所以，目錄是一種空間換時間的做法。

單頁情況：

針對之前鏈式結構的page頁，我們可以引入頁目錄。

那么當前，在一個page內部，引入了頁目錄。?比如，要查找id=4的記錄，之前線性遍歷4次，才能拿到結果。現在直接通過目錄2，直接定位新的其實位置，提高了效率。

多頁情況：

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

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

這樣我們就可以通過多個page的遍歷，page內部通過目錄快速定位數據。可是這樣，貌似也有效率問題，在page之間，還是線性遍歷，意味著還是需要大量的IO。將下一個page加載到內存，進行線性檢測。這樣就顯得我們 page內部的目錄有點杯水車薪了。

解決方案，給page也帶上目錄：

?使用一個目錄項來指向某一頁，而這個目錄項不存放數據，只存放它指向page中最小數據的鍵值
和頁內目錄不同，這種目錄管理的是頁，而頁內目錄管理的是數據。
其中，每個目錄項的構成是鍵值+指針。

目錄頁（page目錄）管理一個個的目錄，目錄頁中的數據存放的就是指向那一頁中最小的數據。通過該數據，與我們要查找的數據進行比較，找到訪問哪個page。

對于一個page目錄，它的大小是16KB，假設忽略掉前后指針，該page只存儲一個數據和對應的指針，在64位環境下，16*1024/(4+8)=1365。即一個page目錄，大概可以管理1365個page。也就是1365*1024/1024/1024=21MB，大概可以管理21MB的數據。

但是，我們的page目錄也可能會產生線性遍歷造成的多次IO，降低效率問題。同樣，我們可以在上層再加一層目錄page。

一般而言，兩三層的設計已經足夠了，可以管理特別大的數據。

這個結構就是B+樹。

但是，實際存儲的時候，除了葉子節點之間還會以鏈表的形式連接，其他節點都不會連接。這剛好符合B+樹！

注意：

葉子節點保存有數據，其他節點不保存數據，只保存目錄項。？？？

原因：非葉子節點不保存數據，那么就可以存儲更多的目錄項，目錄頁，就可以管理更多的page。換句話說，查找數據時，可以淘汰掉的目錄頁更多，進行 IO的次數就可以大大減少。在IO層面，提高了效率。同時，每一個page節點，都有目錄項，大大提高了搜索效率。

葉子節點為什么全部鏈接起來？？？

首先，這是B+樹的特點。所以MySQL使用這種數據結構。

方便進行范圍查找。

上面的圖，描述的就是MySQL innodb 下的索引結構。我們在建表的時候，就會生成這樣一顆B+樹，他會將我們表中的主鍵一列作為索引，而如果我們在建表的時候沒有指明主鍵，系統會默認生成一個主鍵。一般我們插入數據的時候，就是在該結構下進行CURD的。

總結：

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

?五，聚簇索引和非聚簇索引

前面所講到的都是innodb存儲引擎下的結構。

聚簇索引：innodb存儲引擎下的結構就是聚簇索引，在葉子節點中，索引page和數據page放在一起存儲。

非聚簇索引：MyISAM存儲引擎下的結構就是非聚簇索引，在葉子節點中，索引page和數據page分開存儲。也就是說葉子節點沒有數據，只有對應數據的地址。

驗證：

mysql> create table test1(
? ? -> id int primary key,
? ? -> name varchar(20) not null)engine=innodb;

?mysql> create table test2( id int primary key, name varchar(20) not nuull)engine=MyISAM;

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

對于MyISAM而言，建立主鍵索引和普通索引沒有區別，主鍵索引的葉子節點存儲的是指向數據的指針，那么創建普通索引的時候，就是再創建一個B+樹，以指定列作為鍵值，葉子節點存儲指向數據的指針即可。所以，建立普通索引和主鍵索引沒有區別。
而對于Innode而言，我們知道主鍵索引對應的B+樹，葉子節點會存放主鍵值和數據。在我們創建普通索引的時候，同樣會創建一顆B+樹，以指定列為鍵值，但是葉子節點中不存儲數據，而是存儲主鍵值。在查數據的時候，需要兩邊索引：首先通過普通索引（普通索引對應的B+樹）查找到主鍵值，然后通過主鍵值在主鍵索引（主鍵對應的B+樹）中查找數據。這種過程，叫做回表查詢。

所以，建立索引本質就是以該列為鍵值，創建一顆B+樹。

六，索引操作

查詢索引

show keys from 表名；

show index from 表名；

創建主鍵索引

第一種方式

-- 在創建表的時候，直接在字段名后指定 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

一個表中，最多有一個主鍵索引，當然可以使用復合主鍵
主鍵索引的效率高（主鍵不可重復）

唯一索引的創建

第一種方式

-- 在表定義時，在某列后直接指定 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，等價于主鍵索引

案列：

?mysql> alter table test1 add unique(name);

普通索引的創建?

第一種方式

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); -- 創建完表以后指定某列為普通索引

第三種方式?

create table user10(id int primary key, name varchar ( 20 ), email

varchar ( 30 ));

-- 創建一個索引名為 idx_name 的索引

create index idx_name on user10(name);//給索引起名字idx_name

普通索引的特點：

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

全文索引的創建

當對文章字段或有大量文字的字段進行檢索時，會使用到全文索引。 MySQL 提供全文索引機制，但是有要求，要求表的存儲引擎必須是MyISAM ，而且默認的全文索引支持英文，不支持中文。

案列：

mysql> create table articles(
? ? -> id int unsigned auto_increment not null primary key,
? ? -> title varchar(200),
? ? -> body text,
? ? -> FULLTEXT(title,body))engine=myisam;