MySQL - 第10節(jié) - MySQL索引特性

這篇具有很好參考價值的文章主要介紹了MySQL - 第10節(jié) - MySQL索引特性。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

1.索引的概念

2.認識磁盤

2.1.磁盤的結構

2.1.1.磁盤的整體結構

2.1.2.磁盤中的一個盤片

2.1.3.扇區(qū)的定位方式

2.1.4.操作系統(tǒng)與磁盤交互的基本單位

2.2.磁盤的隨機訪問（Random Access）與連續(xù)訪問（Sequential Access）

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

4.建立共識

5.索引的理解

5.1觀察主鍵索引現(xiàn)象

5.2.推導主鍵索引結構的構建

5.3.索引結構可以采用哪些數(shù)據(jù)結構

5.4.聚簇索引 VS 非聚簇索引

6.索引操作

6.1.創(chuàng)建主鍵索引

6.2.創(chuàng)建唯一索引

6.3.創(chuàng)建普通索引

6.4.創(chuàng)建全文索引

6.5.查詢索引

6.6.刪除索引

6.7.索引創(chuàng)建原則

7.其他概念（自行了解）

1.索引的概念

索引的概念：

??數(shù)據(jù)庫表中存儲的數(shù)據(jù)都是以記錄為單位的，如果在查詢數(shù)據(jù)時直接一條條遍歷表中的數(shù)據(jù)記錄，那么查詢的時間復雜度將會是O(N)。
??索引的價值在于提高海量數(shù)據(jù)的檢索速度，只要執(zhí)行了正確的創(chuàng)建索引的操作，查詢速度就可能提高成百上千倍。當一張表創(chuàng)建索引后，在數(shù)據(jù)庫底層就會為表中的數(shù)據(jù)記錄構建特定的數(shù)據(jù)結構，后續(xù)在查詢表中數(shù)據(jù)時就能通過查詢該數(shù)據(jù)結構快速定位到目標數(shù)據(jù)。
??索引雖然提高了數(shù)據(jù)的查詢速度，但在一定程度上也會降低數(shù)據(jù)增刪改的效率，因為這時在對表中的數(shù)據(jù)進行增刪改操作時，除了需要進行對應的增刪改操作之外，可能還需要對底層建立的數(shù)據(jù)結構進行調整維護。

常見的索引分為：

??主鍵索引（primary key）。

??唯一索引（unique）。

??普通索引（index）。

??全文索引（fulltext）。

索引的價值：

使用如下SQL創(chuàng)建一個海量數(shù)據(jù)表：
drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;

-- 構建一個8000000條記錄的數(shù)據(jù)
-- 構建的海量表數(shù)據(jù)需要有差異性，所以使用存儲過程來創(chuàng)建

-- 產生隨機字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

-- 產生隨機數(shù)字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 創(chuàng)建存儲過程，向雇員表添加海量數(shù)據(jù)
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇員表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領導編號',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號'
);

-- 執(zhí)行存儲過程，添加8000000條記錄
call insert_emp(100001, 8000000);
上述SQL中創(chuàng)建了一個名為index_demon的數(shù)據(jù)庫，在該數(shù)據(jù)庫中創(chuàng)建了一個名為EMP的員工表，并向表當中插入了八百萬條記錄。

將上述SQL保存到index_data.sql文件中，然后在MySQL中使用source依次執(zhí)行文件中的SQL即可（該source依次執(zhí)行文件的SQL命令需要一些時間）。如下：

SQL執(zhí)行完畢后查看數(shù)據(jù)庫就能看到一個名為index_demon的數(shù)據(jù)庫。如下：

進入該數(shù)據(jù)庫，在數(shù)據(jù)庫中可以看到一個名為EMP的員工表。如下：

由于EMP表中有八百萬條記錄，因此在查看EMP表中的數(shù)據(jù)時可以帶上limit子句。如下：

通過desc命令可以看到，目前EMP員工表中沒有建立任何索引。如下：

查詢EMP表中指定工號的員工信息，可以看到每次查詢過程都需要花費4.6秒左右。如下：

當我們給員工表中的工號建立索引后，數(shù)據(jù)庫底層就會為員工表中的數(shù)據(jù)記錄構建特定的數(shù)據(jù)結構，需要注意的是，由于當前員工表中的數(shù)據(jù)量較大，因此建立索引時也需要花費較長時間。如下：

這時再查詢EMP表中指定工號的員工信息，可以看到幾乎檢測不到查詢時耗費的時間。如下：

根本原因就是，給員工工號創(chuàng)建索引后再根據(jù)員工工號來查詢數(shù)據(jù)，這時就能夠直接通過底層建立的數(shù)據(jù)結構來快速定位到目標數(shù)據(jù)，從而提高數(shù)據(jù)的檢索速度，這就是索引的價值。

2.認識磁盤

??mysqld中的所有操作全部都是在內存中進行的，后期mysqld才會進行持久化操作，將數(shù)據(jù)寫入磁盤中。

??MySQL給用戶提供存儲服務，存儲的數(shù)據(jù)在磁盤這個外設當中。

??磁盤是計算機中的一個機械設備，相比于計算機的其他電子元件，磁盤的效率是比較低的。

??而如何提高效率是MySQL的一個重要話題，因此我們有必要了解一下磁盤的相關內容。

2.1.磁盤的結構

2.1.1.磁盤的整體結構

磁盤的整體結構如下：

部分說明：

??永磁鐵：機械硬盤的存儲方式與磁帶比較類似，磁體具有記憶的功能，永磁鐵是為了保證磁性的穩(wěn)定。
??音圈馬達：硬盤讀取數(shù)據(jù)的關鍵部位，主要作用是將存儲在磁盤上的信息轉換為電信號向外傳輸。
??主軸：保證電機穩(wěn)定的轉動，磁盤轉動才能讀出數(shù)據(jù)。
??空氣濾波片：過濾空氣硬盤透氣孔中進入的空氣，保證硬盤內部清潔，同時還可以防止硬盤內部的零件氧化，確保硬盤安全使用。
??磁盤：硬盤一般都是鋁合金制作的，主要是用來存儲文件的。
??磁頭：用來讀取盤片上的信息。
??串行接口：用來連接電腦與硬盤的接口，起到傳輸?shù)淖饔谩?/p>

2.1.2.磁盤中的一個盤片

一個磁盤由多個盤片疊加而成，盤片的表面涂有磁性物質，這些磁性物質就是用來記錄二進制數(shù)據(jù)的，因為盤片的正反兩面都可涂上磁性物質，因此一個盤片有兩個盤面。

磁盤中的一個盤片如下：

部分說明：?

??磁道：磁盤表面被分為許多同心圓，每個同心圓稱為一個磁道，每個磁道都有一個編號，最外面的是0磁道。
??扇區(qū)：每個磁道被劃分成若干個扇區(qū)，每個扇區(qū)的存儲容量為512字節(jié)，每個扇區(qū)都有一個編號。
說明一下：

??由于每個扇區(qū)的存儲容量相同，因此最內側磁道上的扇區(qū)數(shù)據(jù)密度最大，而最外側磁道上的扇區(qū)數(shù)據(jù)密度最小。
??近三十年來，扇區(qū)大小一直是512字節(jié)，但最近幾年正在遷移到更大、更高效的4096字節(jié)扇區(qū)，通常稱為4K扇區(qū)。
??數(shù)據(jù)庫文件就是保存在磁盤中的一個個扇區(qū)中的，因此找到一個文件本質就是，在磁盤上找到保存該文件的所有扇區(qū)。

2.1.3.扇區(qū)的定位方式

??一個磁盤由多個盤片疊加而成，每個盤片有兩個盤面，所有盤面中半徑相同的同心磁道構成一個柱面。

??每個盤面都有一個對應的磁頭，每個磁頭都有一個編號，所有的磁頭都是連在同一個磁臂上的。

示意圖如下：

定位扇區(qū)時采用CHS尋址方式：

??磁頭（Heads）：每個盤面都有一個對應的磁頭，因此確定了磁頭也就確定了數(shù)據(jù)在哪一個盤面。
??柱面（Cylinder）：所有盤面中半徑相同的同心磁道構成柱面，因此在確定了數(shù)據(jù)在哪一個盤面的基礎上，再確定柱面也就確定了數(shù)據(jù)在該盤面上的哪一個磁道。
??扇區(qū)（Sector）：每個磁道被劃分成若干個扇區(qū)，因此在確定了數(shù)據(jù)在哪一個磁道的基礎上，再確定扇區(qū)也就確定了數(shù)據(jù)在該磁道上的哪個扇區(qū)。
簡單來說，CHS尋址方式就是先通過H確定數(shù)據(jù)所在的盤面，再通過C確定數(shù)據(jù)所在的磁道，最后通過S定位到目標扇區(qū)。

說明一下：

??CHS尋址方式是磁盤定位扇區(qū)的方式，但實際CHS尋址方式對磁盤以外的設備來說沒什么作用，因此系統(tǒng)軟件在定位磁盤上的數(shù)據(jù)時采用的是LBA（Logical Block Address，邏輯區(qū)塊地址）。
??LBA是描述計算機存儲設備上數(shù)據(jù)所在區(qū)塊的通用機制，LBA和CHS之間可以通過計算公式進行相互轉換，LBA存在的意義就是對底層邏輯器件進行虛擬化，讓系統(tǒng)軟件可以不用關心底層硬件具體的尋址方式，而實際底層硬件采用的還是CHS尋址方式。

2.1.4.操作系統(tǒng)與磁盤交互的基本單位

操作系統(tǒng)與磁盤進行IO交互的基本單位是4KB，而不是扇區(qū)的大小512字節(jié)，原因如下：

??物理內存實際是被劃分成一個個4KB大小的頁框的，磁盤上的數(shù)據(jù)也會被劃分成一個個4KB大小的頁幀，因此操作系統(tǒng)與磁盤以4KB為單位進行IO交互，就能提高數(shù)據(jù)加載和保存的效率。
??操作系統(tǒng)與磁盤進行IO交互時，如果直接以扇區(qū)的大小作為IO的基本單位，那么這時系統(tǒng)的IO代碼和硬件就是強相關的，將來當硬件的扇區(qū)大小發(fā)生變化時就需要對應修改操作系統(tǒng)的IO代碼。
??此外，以扇區(qū)的大小作為IO的基本單位太小了，這就意味著讀取同樣的數(shù)據(jù)內容，需要進行更多次的磁盤訪問，而磁盤的效率是比較低的，這樣IO效率就降低了。
因此操作系統(tǒng)與磁盤以4KB作為IO交互的基本單位，一方面是為了提高IO效率，另一方面是為了實現(xiàn)硬件和系統(tǒng)的解耦。

2.2.磁盤的隨機訪問（Random Access）與連續(xù)訪問（Sequential Access）

隨機訪問與連續(xù)訪問：

??隨機訪問：本次IO所給出的扇區(qū)地址與上次IO給出的扇區(qū)地址不連續(xù)，磁頭在兩次IO操作之間需要做比較大的移動動作才能找到目標扇區(qū)進行IO。
??連續(xù)訪問：本次IO所給出的扇區(qū)地址與上次IO給出的扇區(qū)地址是連續(xù)的，磁頭很快就能找到目標扇區(qū)進行IO。
需要注意的是，盡管兩次IO是在同一時刻發(fā)出的，但如果它們請求的扇區(qū)地址相差很大，那也只能稱為隨機訪問，因為連續(xù)訪問中的連續(xù)指的是訪問的扇區(qū)地址的連續(xù)，而不是訪問時間的連續(xù)，由于連續(xù)訪問不需要過多的定位，因此效率比較高。

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

MySQL與磁盤交互的基本單位：

MySQL作為一款應用軟件，可以想象成是一種特殊的文件系統(tǒng)（mysql的文件系統(tǒng)和linux的文件系統(tǒng)之間屬于上下層關系），它有著更高頻的IO場景，因此為了提高基本的IO效率，MySQL與磁盤交互的基本單位是16KB，這個基本數(shù)據(jù)單元在MySQL這里也叫做Page。

通過show命令查看系統(tǒng)中的全局變量，可以看到InnoDB存儲引擎交互的基本單位是16KB。如下：

說明一下：?本篇博客中沒有做特殊說明的地方，都是以InnoDB存儲引擎為例進行講解的。

Buffer Pool：

? 在MySQL中進行的各種CRUD數(shù)據(jù)操作時，都需要先通過計算找到對應的操作位置，只要涉及計算就需要CPU參與，而馮諾依曼體系結構決定了CPU只能和內存打交道，因此為了便于CPU參與，就需要先將數(shù)據(jù)加載到內存當中。
??所以在特定的時間內，MySQL中的數(shù)據(jù)一定是同時存在于磁盤和內存中的，當執(zhí)行SQL命令操作完內存數(shù)據(jù)后（對數(shù)據(jù)庫的數(shù)據(jù)做的任何操作，都是在內存中進行的），再以特定的刷新策略將內存中的數(shù)據(jù)刷新到磁盤當中，這時MySQL和磁盤進行數(shù)據(jù)交互的基本單位就是Page。
??為了更好的支持上述操作，MySQL服務器在啟動的時候會預先申請一塊內存空間來進行各種緩存，這塊內存空間叫做Buffer Pool，后續(xù)磁盤中加載的數(shù)據(jù)就會保存在Buffer Pool中，刷新數(shù)據(jù)時也就是將Buffer Pool中的數(shù)據(jù)刷新到磁盤。
??由于內核中是有內核緩沖區(qū)的（任何被打開的文件都有自己的內核緩沖區(qū)），因此MySQL從磁盤讀取數(shù)據(jù)時，需要先將數(shù)據(jù)從磁盤讀取到內核緩沖區(qū)，再將數(shù)據(jù)從內核緩沖區(qū)讀取到Buffer Pool，MySQL將數(shù)據(jù)刷新到磁盤時，同樣需要先將數(shù)據(jù)從Buffer Pool刷新到內核緩沖區(qū)，再將數(shù)據(jù)從內核緩沖區(qū)刷新到磁盤。
因此所謂的操作系統(tǒng)和磁盤交互的基本單位是4KB，就是指內核緩沖區(qū)與磁盤之間是以4KB為單位進行交互的。而MySQL的Buffer Pool和磁盤實際并不是直接交互的，因此所謂的MySQL與磁盤交互的基本單位是16KB，指的是MySQL的Buffer Pool與內核緩沖區(qū)之間是以16KB為單位進行交互的。只不過在說的時候更關注的是MySQL和磁盤之間的關系，所以直接說的是MySQL與磁盤交互的基本單位是16KB，相當于忽略了中間的內核緩沖區(qū)。

示意圖如下：

注：

1.MySQL其實是在操作系統(tǒng)之上構建的一個應用層服務。

2.MySQL的Buffer Pool中會充滿大量16KB的page，MySQL要管理這里所有的page，要管理就要先描述再組織，創(chuàng)建結構體對page描述（page結構體后面會詳細介紹），然后使用某個數(shù)據(jù)結構對結構體進行管理，那么此時對page的管理就轉換成了對該數(shù)據(jù)結構的增刪查改。

3.MySQL在選取數(shù)據(jù)結構對page結構體進行管理時，需要采用高效的數(shù)據(jù)結構提高MySQL數(shù)據(jù)搜索的速度，該高效數(shù)據(jù)結構的選取也是索引的基礎（具體采用何種高效數(shù)據(jù)結構下面進行講解）。

4.建立共識

??MySQL中的數(shù)據(jù)文件，是以page為單位保存在磁盤當中的。

??MySQL的CURD操作，都需要通過計算，找到對應的插入位置，或者找到對應要修改或者查詢的數(shù)據(jù)。

??而只要涉及計算，就需要CPU參與，而為了便于CPU參與，一定要能夠先將數(shù)據(jù)移動到內存當中。

??所以在特定時間內，數(shù)據(jù)一定是磁盤中有，內存中也有。后續(xù)操作完內存數(shù)據(jù)之后，以特定的刷新策略，刷新到磁盤。而這時，就涉及到磁盤和內存的數(shù)據(jù)交互，也就是IO了。而此時IO的基本單位就是Page。

??為了更好的進行上面的操作，MySQL服務器在內存中運行的時候，在服務器內部，就申請了被稱為Buffer Pool的大內存空間，來進行各種緩存。其實就是很大的內存空間，來和磁盤數(shù)據(jù)進行IO交互。

??為何更高的效率，一定要盡可能的減少系統(tǒng)和磁盤IO的次數(shù)。

5.索引的理解

5.1觀察主鍵索引現(xiàn)象

創(chuàng)建一個用戶表，表當中包含用戶的id、年齡和姓名，并將用戶的id設置成主鍵。如下：

創(chuàng)建表完畢后向表中插入一些數(shù)據(jù)，并且插入數(shù)據(jù)時沒有按照主鍵的大小順序插入。如下：

但最終當我們查看表中的數(shù)據(jù)時，卻發(fā)現(xiàn)顯示出來的數(shù)據(jù)是按照主鍵進行有序排列的。如下：

根本原因就是，因為我們創(chuàng)建表時設置了主鍵，即便向表中插入數(shù)據(jù)時是亂序插入的，MySQL底層也會自動按照主鍵對插入的數(shù)據(jù)進行排序。

問題：MySQL與磁盤進行交互時為什么不是按需交互，而是以Page為基本單位進行交互的？

答：當我們查詢表中的某一條記錄時，如果MySQL只從磁盤中將這一條記錄加載到內存當中，那么當我們繼續(xù)查詢表中的其他記錄時，MySQL就一定需要再次與磁盤進行IO交互。
而如果當我們查詢表中的某一條記錄時，MySQL直接將這條記錄所在的整個Page都加載到內存當中，那么當我們繼續(xù)查詢表中的其他記錄時，MySQL很可能就不再需要與磁盤進行IO交互了，因為這條記錄很可能也在被加載進來的Page當中，這時直接在內存中進行查詢即可，大大減少了IO的次數(shù)。
當然，我們不能保證用戶下一次要訪問的數(shù)據(jù)一定就在本次加載進來的Page當中，但是根據(jù)統(tǒng)計學原理，當一個數(shù)據(jù)正在被訪問時，那么下一次有很大可能會訪問其周圍的數(shù)據(jù)（局部性原理），因此我們有較大概率保證用戶下一次要訪問的數(shù)據(jù)和本次訪問的數(shù)據(jù)在同一個Page當中，如果局部性原理沒有起作用，那就再把對應的Page加載到內存當中即可。
也就是說，MySQL與磁盤進行交互時以Page為基本單位，可以減少與磁盤IO交互的次數(shù)，進而提高IO的效率。

5.2.推導主鍵索引結構的構建

單個Page：

??MySQL中要管理很多數(shù)據(jù)文件，在運行期間一定有大量的Page需要被換入換出，因此MySQL一定需要將內存中大量的Page管理起來。
??MySQL將內存中的每一個Page都用一個結構體描述起來，然后再將各個結構體以雙鏈表的形式組織起來，因此一個Page結構體內部既包含數(shù)據(jù)字段，也包含屬性字段。
??此外，為了方便后續(xù)數(shù)據(jù)的插入和刪除，每個Page結構體內部存儲的數(shù)據(jù)記錄會以單鏈表的形式組織起來，并且各個記錄之間會按照主鍵進行排序。
假設上述測試表中的記錄都在同一個Page當中，那么該Page的結構大致如下：

說明一下：

??每個Page結構體內部的數(shù)據(jù)會按照主鍵進行排序，目的是為了優(yōu)化數(shù)據(jù)查詢的效率，因為單鏈表在查找的時候是順序查找的，有序就意味著在查找的過程中有機會提前結束查詢過程。
??這也就是前面所說的，只要設置了主鍵，即便向表中插入的數(shù)據(jù)是亂序的，MySQL底層也會自動按照主鍵對插入的數(shù)據(jù)進行排序，因此查詢得到的數(shù)據(jù)是按照主鍵進行有序排序的。

單個Page內創(chuàng)建頁內目錄：

??Page結構體內部存儲的數(shù)據(jù)記錄是以單鏈表的形式組織起來的，當頁內部的數(shù)據(jù)量增多時，本質在頁內部進行的還是線性遍歷，效率低下。
??這時可以在Page結構體內部引入頁內目錄，將Page結構體內部存儲的數(shù)據(jù)記錄按照主鍵劃分為若干區(qū)域，頁內目錄中就存儲著這若干區(qū)域的最小鍵值。
??在Page結構體內部引入頁內目錄后，在頁內部查詢數(shù)據(jù)時就可以先通過頁內目錄找到目標數(shù)據(jù)所在區(qū)域的起始記錄，然后再從該記錄開始向后遍歷找到目標記錄。
比如在之前的Page內部引入頁內目錄后的結構大致如下：

說明一下：

??在每個Page結構體內部引入頁內目錄，目的是為了加速在單個Page內部數(shù)據(jù)查詢的效率。由于這個頁內目錄也是保存在Page內部的，而單個Page的大小是固定的，因此添加頁內目錄后Page內部能夠保存的數(shù)據(jù)記錄變少了，所以在Page內部引入頁內目錄本質是一種空間換時間的做法，就像給書添加目錄需要花費更多的紙張一樣。
??每個Page結構體內部的數(shù)據(jù)會按照主鍵進行排序，其實就是為了引入頁內目錄，因為只有數(shù)據(jù)按照主鍵排序后引入頁內目錄才有意義，就像書中每一頁都是按照頁碼進行排序的一樣，如果一本書的頁碼是亂序的，那么它的目錄根本就沒有意義。

多個Page：

??隨著數(shù)據(jù)量不斷增大，單個Page中無法存下所有數(shù)據(jù)，這時就需要用多個Page來存儲數(shù)據(jù)。

??這時在查詢數(shù)據(jù)時就需要，先遍歷Page雙鏈表確定目標數(shù)據(jù)在哪一個Page，然后再在該Page內部找到目標數(shù)據(jù)。

多個Page的示意圖如下：

Page之上創(chuàng)建頁目錄：

??雖然在單個Page內部能夠通過頁內目錄來快速定位數(shù)據(jù)，但在遍歷Page雙鏈表尋找目標Page時本質進行的還是線性遍歷。
??這時可以給各個Page結構體也建立頁目錄，頁目錄中的每個目錄項都指向一個Page，而這個目錄項存放的就是其指向的Page中存放的最小數(shù)據(jù)的鍵值。
??在給各個Page結構體建立頁目錄后，在查詢數(shù)據(jù)時就可以先通過遍歷頁目錄找到目標數(shù)據(jù)所在的Page，然后再在該Page內部找到目標數(shù)據(jù)。
給各個Page建立頁目錄后的示意圖如下：

說明一下：

??這里的頁目錄與之前的頁內目錄的區(qū)別在于，頁目錄管理的是一個個的Page，而頁內目錄管理的是一條條的記錄。此外，頁內目錄與其管理的多條記錄是保存在同一個Page中的，而頁目錄是重新申請的一個Page結構體來保存的。
??隨著數(shù)據(jù)量不斷增大，Page變得越來越多，這時一個頁目錄無法管理所有的Page，這時就需要更多個的頁目錄。這些頁目錄也是一個個的Page結構體，只不過這些Page結構體中存放的不是數(shù)據(jù)記錄，而是各個Page的目錄信息。但是在MySQL看來，無論Page當中存儲的是什么數(shù)據(jù)，都應該被管理起來，因此這些Page頁目錄也需要用雙鏈表連接起來。

頁目錄之上再創(chuàng)建頁目錄：

??就算給各個Page結構體也建立了頁目錄，但隨著數(shù)據(jù)量不斷增大，頁目錄的數(shù)量也會越來越多，這時在遍歷頁目錄尋找目標Page時本質進行的還是線性遍歷。
??類似的，我們可以不斷在頁目錄之上再創(chuàng)建頁目錄，最終就一定能夠得到一個入口頁目錄，這時在查詢數(shù)據(jù)時就可以從入口頁目錄開始不斷查詢頁目錄，最終找到目標數(shù)據(jù)所在的Page，然后再在該Page內部找到目標數(shù)據(jù)。
最終構建出來的結構如下：

說明一下：

??最終構建出來的實際就是一棵B+樹，這棵B+樹就是InnoDB的索引結構，其中每一層Page的作用就是加速它的下一層的查找效率。
??如果我們創(chuàng)建表時設置了主鍵，那么MySQL在底層就會自動將這張表中的的數(shù)據(jù)以B+樹的形式組織起來，保存在Buffer Pool當中，當我們查詢數(shù)據(jù)時就可以通過查詢這棵B+樹來提高查詢效率。
??MySQL中可能同時有大量的表正在被處理，因此Buffer Pool中可能會存在多個索引結構，也就是同時存在多個B+樹結構，當我們查詢表時訪問的就是這張表對應的B+樹結構。

注：具有主鍵的表，一個表就是一個B+樹。如果表中沒有主鍵，我們可以認為該表中所有的數(shù)據(jù)是線性組織的（但其實表中沒有主鍵，mysql會自動形成隱藏主鍵，因此即使表中沒有主鍵其實其本質也是一個B+樹）。

B+樹中的Page結點是否需要全量加入到Buffer Pool中：

??當對MySQL中的某張表進行增刪查改操作時，不需要將其對應B+樹的所有結點全量加入到Buffer Pool中，甚至在剛開始時只需要將B+樹的根結點加入到Buffer Pool中。
??當后續(xù)訪問表中的數(shù)據(jù)時，再將該數(shù)據(jù)對應路徑上的結點加入到Buffer Pool中即可，對于其他不需要的結點根本不用加入到Buffer Pool中，這一點和操作系統(tǒng)中的頁表是很像的。
??此外，在刷新數(shù)據(jù)時也不需要將B+樹中所有的結點都進行刷新，在Page結構體中有一個標記位用來標記當前Page是否被修改過，如果被修改過則說明這是一個臟數(shù)據(jù)，在刷新數(shù)據(jù)時只有臟數(shù)據(jù)才需要被刷新到磁盤上。
??由于B+樹中的結點都是16KB大小的Page，因此無論是刷新數(shù)據(jù)到磁盤函數(shù)從磁盤加載數(shù)據(jù)到Buffer Pool，都是以Page為單位進行的，這也就是所謂的MySQL與磁盤交互的基本單位是Page。
如果把這棵B+樹逆時針旋轉90度，就會發(fā)現(xiàn)這其實就是操作系統(tǒng)中的頁表結構，本質操作系統(tǒng)中的頁表也是B+樹結構。如下：

以32位平臺為例，頁表將一個虛擬地址轉換成物理地址的過程如下：

1.選擇虛擬地址的前10個比特位在頁目錄當中進行查找，找到對應的頁表。
2.再繼續(xù)選擇虛擬地址后續(xù)的10個比特位在對應的頁表當中進行查找，找到物理內存中對應頁框的起始地址。
3.最后選擇虛擬地址中剩下的12個比特位作為偏移量，從對應頁框的起始地址處向后進行偏移，最終得到的就是轉換后的物理地址。
說明一下：

12個比特位有種取值，而字節(jié)對應就是4KB，所以物理內存中一個頁框的大小就是4KB，這也就是為什么操作系統(tǒng)與磁盤交互的基本單位是4KB的原因。
此外，頁表中的各個B+樹結點也不需要全量加入到內存中，而只需要加入訪問到的結點即可，所以頁表占用的內存大小實際是可控的，這也就是為什么二級頁表可行的原因。

5.3.索引結構可以采用哪些數(shù)據(jù)結構

索引結構可以采用哪些數(shù)據(jù)結構：

除了InnoDB存儲引擎所采用的B+樹結構，索引結構還可以采用哪些數(shù)據(jù)結構呢？

??鏈表：查找時是線性遍歷，效率太低。
? 普通二叉搜索樹：可能退化成線性結構，這時查找還是線性遍歷。
? AVL樹和紅黑樹：雖然保證了二叉樹是絕對或近似平衡的，不會退化成線性結構，但AVL樹和紅黑樹都是二叉樹結構，這就意味著樹的層高會比較高，而查詢數(shù)據(jù)時都是從根結點開始向下進行查找的，這也就意味著在查詢過程中需要遍歷更多結點，如果這些結點還沒有被加載到Buffer Pool中，這時就需要進行更多次的IO操作，所以最終沒有選擇其作為索引結構。
??哈希表：官方的索引實現(xiàn)方式中MySQL是支持HASH的，只不過InnoDB和MyISAM存儲引擎并不支持。哈希表的優(yōu)點就是它的時間復雜度是O(1)的，但哈希表也有一個缺點就是不利于進行數(shù)據(jù)的范圍查找。
下面是幾個常見的存儲引擎，與其所支持的索引類型：

B樹 VS B+樹：

目前對于B樹和B+樹，對我們最有意義的區(qū)別是：

（1） B樹節(jié)點，既有數(shù)據(jù)，又有 Page 指針，而 B+ ，只有葉子節(jié)點有數(shù)據(jù)，其他目錄頁，只有鍵值和 Page 指針。

（2） B+葉子節(jié)點，全部相連，而B 沒有。

B+樹是B樹的一種變形結構，那為什么我們沒有采用普通的B樹作為索引結構呢？

??首先，普通B樹中的所有結點中都同時包括索引信息和數(shù)據(jù)信息，由于一個Page的大小是固定的，因此非葉子結點中如果包含了數(shù)據(jù)信息，那么這些結點中能夠存儲的索引信息一定會變少，這時這棵樹形結構一定會變得更高更瘦，當查詢數(shù)據(jù)時就可能需要與磁盤進行更多次的IO操作。
??其次，普通B樹中的各個葉子結點之間沒有連接起來，這將不利于進行數(shù)據(jù)的范圍查找，而B+樹的各個葉子結點之間是連接起來的，當我們進行范圍查找時，直接先找到第一個數(shù)據(jù)然后繼續(xù)向后遍歷找到之后的數(shù)據(jù)即可，因此將各個葉子結點連接起來更有利于進行數(shù)據(jù)的范圍查找。

5.4.聚簇索引 VS 非聚簇索引

MyISAM存儲引擎 - 主鍵索引結構：

??之前推導的主鍵索引結構是InnoDB存儲引擎的主鍵索引結構，而MyISAM存儲引擎同樣采用B+樹作為索引的基本數(shù)據(jù)結構。
??與InnoDB存儲引擎的B+樹不同的是，MyISAM存儲引擎的B+樹的葉子結點存放的不是數(shù)據(jù)記錄，而是數(shù)據(jù)記錄對應的地址。
比如下圖為MyISAM存儲引擎的主鍵索引結構，其中Col1為主鍵。如下：

MyISAM存儲引擎 - 普通索引結構：

??MyISAM存儲引擎的普通索引采用的也是B+樹結構，與主鍵索引唯一不同的地方就是普通索引的B+樹中的鍵值可以重復。
??因此一張表可能會同時存在多個B+樹結構，但由于MyISAM存儲引擎的B+樹葉子結點中，存儲的是對應的數(shù)據(jù)記錄的地址，因此有效數(shù)據(jù)只會存儲一份。
比如下圖為MyISAM存儲引擎的普通索引結構，其中Col2為索引列。如下：

InnoDB存儲引擎 - 普通索引結構：

??InnoDB存儲引擎的普通索引采用的也是B+樹結構，但普通索引的B+樹中的鍵值可以重復，并且B+樹的葉子結點中存儲的不是數(shù)據(jù)記錄，而是對應數(shù)據(jù)記錄的主鍵值。
??當根據(jù)普通索引查詢數(shù)據(jù)時，會先查找普通索引對應的B+樹找到目標記錄的主鍵值，然后再查找主鍵索引對應的B+樹找到目標記錄，這個過程就叫做回表查詢。
比如下圖為InnoDB存儲引擎的普通索引結構，其中Col3為索引列。如下：

說明一下：

??InnoDB存儲引擎的普通索引的B+樹葉子結點中沒有保存整條數(shù)據(jù)記錄，是為了節(jié)省空間，因為同一張表可能會創(chuàng)建多個普通索引，每個普通索引的B+樹中都保存一份數(shù)據(jù)會造成數(shù)據(jù)冗余，所以通過回表查詢主鍵索引對應的B+來獲取整個數(shù)據(jù)記錄，該做法本質一種以時間換取空間的做法。
??當根據(jù)普通索引查詢數(shù)據(jù)時，其實也不一定需要進行回表查詢，因為有可能我們要查詢的就是這條記錄對應的主鍵值，因此查詢完普通索引對應B+樹后即可完成查詢。
??采用InnoDB存儲引擎建立的每張表都會有一個主鍵，就算用戶沒有設置，InnoDB也會自動幫你創(chuàng)建一個不可見的主鍵，因為完整數(shù)據(jù)記錄只會存儲在主鍵索引對應的B+樹中的，因此采用InnoDB存儲引擎建立的表必須有主鍵。

聚簇索引 VS 非聚簇索引：

??聚簇索引：像InnoDB存儲引擎這種，將數(shù)據(jù)記錄與索引結構放在一起的索引方案（數(shù)據(jù)在葉子節(jié)點中），叫做聚簇索引。

??非聚簇索引：像MyISAM存儲引擎這種，將數(shù)據(jù)記錄與索引結構分離的索引方案（數(shù)據(jù)不在葉子節(jié)點中），叫做非聚簇索引。

當采用InnoDB存儲引擎創(chuàng)建表時，在數(shù)據(jù)庫對應的目錄下會新增兩個文件。如下：

當采用MyISAM存儲引擎創(chuàng)建表時，在數(shù)據(jù)庫對應的目錄下會新增三個文件。如下：

說明一下：

??采用InnoDB和MyISAM存儲引擎創(chuàng)建表時都會生成xxx.frm文件，該文件中存儲的是表結構相關的信息（表中有幾列，列的名稱，列的屬性等信息）。
??采用InnoDB存儲引擎創(chuàng)建表時會生成一個xxx.ibd文件，該文件中存儲的是索引和數(shù)據(jù)相關的信息，這就是所謂的聚簇索引，索引和數(shù)據(jù)是存儲在同一個文件中的（將整個B+樹存儲在一個文件中）。
??采用MyISAM存儲引擎創(chuàng)建表時會生成一個xxx.MYD文件和一個xxx.MYI文件，其中xxx.MYD文件中存儲的是數(shù)據(jù)相關的信息，而xxx.MYI文件中存儲的是索引相關的信息，這就是所謂的非聚簇索引，索引和數(shù)據(jù)是分開存儲的（將B+樹中的索引和數(shù)據(jù)分別存儲在兩個文件中）。

注：無論是InnoDB還是MyISAM存儲引擎，都要將索引和數(shù)據(jù)存儲在磁盤中（將整個B+樹存儲在磁盤中）。正是因為整個B+樹被持久化存儲在了磁盤中，這就支持了后續(xù)在訪問表中數(shù)據(jù)的時候，只需要將該數(shù)據(jù)對應路徑上的結點從磁盤中載入到內存Buffer Pool中即可。

6.索引操作

6.1.創(chuàng)建主鍵索引

方式一：

創(chuàng)建表時，直接在對應的字段名后指定primary key。如下：

方式二：

在創(chuàng)建表的最后，指定某列或某幾列為主鍵索引。如下：

方式三：

創(chuàng)建表后，使用alter命令給指定字段添加主鍵索引。如下：

主鍵索引的特點：

主鍵索引的特點如下：

??一個表中，最多只能有一個主鍵索引，一個主鍵可以由多個列同時承擔。

??主鍵索引的查詢效率高。

??創(chuàng)建主鍵索引的列，其列值不能為NULL，且不能重復。

??主鍵索引的列一般是數(shù)字類型。

6.2.創(chuàng)建唯一索引

唯一索引其實就是普通索引，只不過其是通過唯一鍵創(chuàng)建的。

方式一：

在創(chuàng)建表時，直接在對應的字段名后指定unique。如下：

方式二：

在創(chuàng)建表的最后，指定某列或某幾列為唯一索引。如下：

方式三：

創(chuàng)建表后，使用alter命令給指定字段添加唯一索引。如下：

唯一索引的特點：

唯一索引的特點如下：

??一個表中，可以有多個唯一索引，一個唯一鍵可以由多個列同時承擔。

??唯一索引的查詢效率高。

??創(chuàng)建唯一索引的列，其列值可以為NULL，但是不能重復。

??如果給唯一索引設置NOT NULL屬性，則等價于主鍵索引。

6.3.創(chuàng)建普通索引

方式一：

在創(chuàng)建表的最后，指定某列或某幾列為普通索引。如下：

方式二：

創(chuàng)建表后，使用alter命令給指定字段添加普通索引。如下：

方式三：

創(chuàng)建表后，使用create命令給指定字段創(chuàng)建普通索引，并指定索引名。如下：

注：索引也是有索引名的，創(chuàng)建主鍵索引時默認主鍵索引名為primary（如果主鍵是多列復合鍵，則其索引名仍為primary），創(chuàng)建唯一鍵和普通鍵時默認將對應列名設置為索引名（如果唯一鍵和普通鍵是多列復合鍵，則其索引名為創(chuàng)建符合鍵時第一列的列明）。這里普通索引方式三的創(chuàng)建方法是在創(chuàng)建時手動指定了普通索引名為idx_name，因此最終該普通索引名為idx_name，如下圖所示。

普通索引的特點：

普通索引的特點如下：

??一個表中，可以有多個普通索引，一個普通索引可以由多個列同時承擔。

??創(chuàng)建普通索引的列，其列值可以為NULL，也可以重復。

6.4.創(chuàng)建全文索引

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

全文索引案例：

全文索引比較常見的案例就是對文章中的詞進行搜索，比如下面創(chuàng)建一個文章表，表當中包含文章的id、文章名稱、文章內容，并在創(chuàng)建表的最后通過fulltext給title和body列創(chuàng)建全文索引。如下：

下面向表當中插入一些測試數(shù)據(jù)。如下：

如果要查詢哪些文章中包含database關鍵字，我們可以通過模糊匹配進行查找。如下：

但實際這種查找方式并沒有用到全文索引，在SQL語句前面加上explain，可以看到key對應的值為NULL，表示這條SQL在執(zhí)行過程中沒有用到任何索引。如下：

如果要通過全文索引來查詢，需要使用match against進行搜索。如下：

在這條SQL語句前面加上explain，可以看到key對應的值為title，表示這條SQL在執(zhí)行過程中用到了索引名為title的索引。如下：

說明一下：

??MyISAM存儲引擎是支持全文索引的，而InnoDB存儲引擎是在5.6以后才開始支持全文索引的。默認的全文索引支持英文，不支持中文。如果對中文進行全文檢索，可以使用sphinx的中文版(coreseek)。
??同時使用title和body建立全文索引時，相當于建立了一個復合索引，默認會選擇fulltext中的第一個列名作為這個復合索引的索引名，所以這里explain中key對應的索引名為title。
??由于是title和body共同建立的全文索引，所以如果文章當中沒有出現(xiàn)關鍵字，但文章名稱中出現(xiàn)了關鍵字則也會被篩選出來（當前示例沒有體現(xiàn)出來）。

6.5.查詢索引

方式一：

使用?show keys from 表名命令查詢，比如查詢articles表中的索引信息。如下：

說明一下：

??Table：表示創(chuàng)建索引的表的名稱。
??Non_unique：表示該索引是否是唯一索引，如果是則為0，如果不是則為1。
??Key_name：表示索引的名稱（主鍵索引的索引名默認為primary，唯一鍵索引和普通索引的索引名默認為對應列明）（如果主鍵是多列復合鍵，則其索引名仍為primary）（如果唯一鍵和普通鍵是多列復合鍵，則其索引名為創(chuàng)建符合鍵時第一列的列明）。
??Seq_in_index：表示該列在索引中的位置，如果索引是單列的，則該列的值為1，如果索引是復合索引，則該列的值為每列在索引定義中的順序。
??Column_name：表示定義索引的列字段。
??Collation：表示列以何種順序存儲在索引中，“A”表示升序，NULL表示無分類。
??Cardinality：索引中唯一值數(shù)目的估計值。基數(shù)根據(jù)被存儲為整數(shù)的統(tǒng)計數(shù)據(jù)計數(shù)，所以即使對于小型表，該值也沒有必要是精確的?；鶖?shù)越大，當進行聯(lián)合時，MySQL使用該索引的機會就越大。
??Sub_part：表示列中被編入索引的字符的數(shù)量，若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目，若整列被編入索引，則該列的值為NULL。
??Packed：指示關鍵字如何被壓縮。若沒有被壓縮，則值為NULL。
??Null：用于顯示索引列中是否包含NULL，若包含則為YES，若不包含則為NO。
??Index_type：顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。
??Comment：顯示評注。

方式二：

使用 show index from 表名命令查詢，比如查詢articles表中的索引信息。如下：

方式三：

使用?desc 表名命令查詢（信息比較簡略），比如查詢articles表中的索引信息。如下：

6.6.刪除索引

創(chuàng)建測試表：

創(chuàng)建一個用戶表用于測試索引的刪除，表中包含用戶的id、姓名和郵箱，并將這三列分別設置為主鍵索引、唯一索引和普通索引。如下：

刪除主鍵索引：

使用?alter table 表名 drop primary key 命令即可刪除主鍵索引。如下：

刪除非主鍵索引：

使用?alter table 表名 drop index 索引名命令即可刪除指定的非主鍵索引。如下：

此外，也可以使用?drop index 索引名 on 表名命令也可以刪除指定的非主鍵索引。如下：

說明一下：

??一個表只有一個主鍵索引，所以在刪除主鍵索引的時候不用指明索引名，而一個表中可能有多個非主鍵索引，所以在刪除非主鍵索引時需要指明索引名。

6.7.索引創(chuàng)建原則

索引創(chuàng)建的原則如下：

??比較頻繁作為查詢條件的字段應該創(chuàng)建索引。

??唯一性太差的字段不適合單獨創(chuàng)建索引，即使頻繁作為查詢條件。

??更新非常頻繁的字段不適合創(chuàng)建索引。

??不會出現(xiàn)在where子句中的字段不應該創(chuàng)建索引。

時刻要記住，創(chuàng)建索引的目的就是為了提高查詢的效率。文章來源地址http://www.zghlxwxcb.cn/news/detail-501079.html