[MySQL]MySQL索引

1. 索引的概念

索引：提高數(shù)據(jù)庫(kù)的性能，索引是物美價(jià)廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行 正確的 create index ，查詢速度就可能提高成百上千倍。但是天下沒(méi)有免費(fèi)的午餐，查詢速度的提高 是以插入、更新、刪除的速度為代價(jià)的，這些寫操作，增加了大量的IO。所以它的價(jià)值，在于提高一個(gè) 海量數(shù)據(jù)的檢索速度。

• MySQL的服務(wù)器本質(zhì)是在內(nèi)存中的，因此MySQL的CRUD操作也在內(nèi)存當(dāng)中，索引也是如此。
• 索引提高效率的方式：對(duì)組織數(shù)據(jù)的方式進(jìn)行重構(gòu)。

常見(jiàn)索引分為：

• 主鍵索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)–解決中子文索引問(wèn)題。

示例： 先整一個(gè)海量表，在查詢的時(shí)候，看看沒(méi)有索引時(shí)有什么問(wèn)題？

使用如下SQL語(yǔ)句創(chuàng)建一個(gè)擁有海量數(shù)據(jù)的表：

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;

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

-- 產(chǎn)生隨機(jī)字符串
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 ;

-- 產(chǎn)生隨機(jī)數(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)建存儲(chǔ)過(guò)程，向雇員表添加海量數(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 '雇員編號(hào)',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號(hào)'
);

-- 執(zhí)行存儲(chǔ)過(guò)程，添加8000000條記錄
call insert_emp(100001, 8000000);

首先將以上SQL語(yǔ)句導(dǎo)入到一個(gè)本地的文本文件中，然后在Linux系統(tǒng)中使用rz命令將該文件導(dǎo)入：

然后在MySQL中使用source執(zhí)行導(dǎo)入的SQL語(yǔ)句文件：

由于服務(wù)器配置的限制，數(shù)據(jù)量太大可能會(huì)導(dǎo)致MySQL卡住，需要耐心等待：

最后執(zhí)行完成，用時(shí)大概7分鐘11.79秒：

完成后，會(huì)新增一個(gè)index_demon數(shù)據(jù)庫(kù)：

數(shù)據(jù)庫(kù)中有一個(gè)EMP表：

查看表結(jié)構(gòu)：

由于表中數(shù)據(jù)量很大，我們只查看一下前5行數(shù)據(jù)：

經(jīng)過(guò)多次測(cè)試發(fā)現(xiàn)正常查詢某條記錄大概需要4.2秒左右：

然后對(duì)表添加索引，花費(fèi)了大概20.88秒：

有了索引之后，查找的效率大大提高了：

2. 認(rèn)識(shí)磁盤

MySQL 給用戶提供存儲(chǔ)服務(wù)，而存儲(chǔ)的都是數(shù)據(jù)，數(shù)據(jù)在磁盤這個(gè)外設(shè)當(dāng)中。

磁盤是計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比于計(jì)算機(jī)其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，要想更好的理解MySQL的效率問(wèn)題，我們必須要了解磁盤的結(jié)構(gòu)和工作原理。

磁盤的內(nèi)部結(jié)構(gòu)

• 磁盤 – 由很多盤片組成，用于存儲(chǔ)數(shù)據(jù)。
• 主軸 – 能夠?qū)⒋疟P進(jìn)行高速旋轉(zhuǎn)。
• 磁頭 – 磁盤的每個(gè)盤片都有一個(gè)對(duì)應(yīng)的磁頭，用于讀取對(duì)應(yīng)盤片中數(shù)據(jù)，不與盤面接觸。

磁盤中的一個(gè)盤片結(jié)構(gòu)

磁盤由多個(gè)盤片組成，盤片具有磁性，使用磁性特點(diǎn)來(lái)記錄對(duì)應(yīng)的二進(jìn)制數(shù)據(jù)。

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

我們?cè)谑褂肔inux，所看到的大部分目錄或者文件，其實(shí)就是保存在硬盤當(dāng)中的，所以，最基本的，找到一個(gè)文件的全部，本質(zhì)，就是在磁盤找到所有保存文件的扇區(qū)，而我們能夠定位任何一個(gè)扇區(qū)，那么便能找到所有扇區(qū)，因?yàn)椴檎曳绞绞且粯拥摹?/p>

MySQL中的數(shù)據(jù)庫(kù)和表也是通過(guò)文件形式存儲(chǔ)在硬盤中的：

因此知道如何在磁盤中如何定位扇區(qū)，找到對(duì)應(yīng)的文件，才能知道MySQL中的數(shù)據(jù)如何找到。

定位扇區(qū)

定位扇區(qū)的步驟：

• 定位數(shù)據(jù)所在的是哪一個(gè)盤面 – 也就是選擇哪一個(gè)對(duì)應(yīng)的磁頭。
• 定位數(shù)據(jù)在所在盤面的哪一個(gè)的磁道。
• 定位數(shù)據(jù)在所在磁道中的哪一個(gè)扇區(qū)。

磁頭（Heads）、柱面(Cylinder)(等價(jià)于磁道)、扇區(qū)(Sector)對(duì)應(yīng)的編號(hào)。即可在磁盤上定位所要訪問(wèn)的扇區(qū)。這種磁盤數(shù)據(jù)定位方式叫做 CHS 。不過(guò)實(shí)際系統(tǒng)軟件使用 的并不是 CHS （但是硬件是），而是 LBA ，一種線性地址，可以想象成虛擬地址與物理地址。系統(tǒng) 將 LBA 地址最后會(huì)轉(zhuǎn)化成為 CHS ，交給磁盤去進(jìn)行數(shù)據(jù)讀取。

我們現(xiàn)在已經(jīng)能夠在硬件層面定位，任何一個(gè)基本數(shù)據(jù)塊了(扇區(qū))。但是在系統(tǒng)軟件上，不是直接按照扇區(qū)(512字節(jié)),進(jìn)行IO交互，原因如下：

• 如果操作系統(tǒng)直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，那么系統(tǒng)的IO代碼，就和硬件強(qiáng)相關(guān)，換言之，如果硬件發(fā)生變化，系統(tǒng)必須跟著變化
• 從目前來(lái)看，單次IO 512字節(jié)，還是太小了。IO單位小，意味著讀取同樣的數(shù)據(jù)內(nèi)容，需要進(jìn)行多次磁盤訪問(wèn)，會(huì)帶來(lái)效率的降低。
• 之前學(xué)習(xí)文件系統(tǒng)，就是在磁盤的基本結(jié)構(gòu)下建立的，文件系統(tǒng)讀取基本單位，就不是扇區(qū)，而是數(shù)據(jù)塊。

綜上原因，系統(tǒng)讀取磁盤，是以塊為單位的，基本單位是 4KB 。

磁盤隨機(jī)訪問(wèn)與連續(xù)訪問(wèn)

• 隨機(jī)訪問(wèn)：本次IO所給出的扇區(qū)地址和上次IO給出扇區(qū)地址不連續(xù)，這樣的話磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開(kāi)始讀/寫數(shù)據(jù)。
• 連續(xù)訪問(wèn)：如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開(kāi)始這次IO操作，這樣的多個(gè)IO操作稱為連續(xù)訪問(wèn)。
• 因此盡管相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話也只能稱為隨機(jī)訪問(wèn)，而非連續(xù)訪問(wèn)。
• 磁盤是通過(guò)機(jī)械運(yùn)動(dòng)進(jìn)行尋址的，連續(xù)訪問(wèn)不需要過(guò)多的定位，故效率比較高。

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

而 MySQL 作為一款應(yīng)用軟件，可以想象成一種特殊的文件系統(tǒng)。它有著更高的IO場(chǎng)景，所以，為了提高基本的IO效率， MySQL進(jìn)行IO的基本單位是 16KB (后面統(tǒng)一使用 InnoDB 存儲(chǔ)引擎講解)。這個(gè)基本數(shù)據(jù)單元，在 MySQL 這里叫做page（注意和系統(tǒng)的page區(qū)分）。

• MySQL作為一款應(yīng)用層軟件無(wú)法直接與磁盤內(nèi)的數(shù)據(jù)進(jìn)行訪問(wèn)，只能通過(guò)操作系統(tǒng)來(lái)訪問(wèn)。
• 由于操作系統(tǒng)是按4KB大小的數(shù)據(jù)塊和磁盤進(jìn)行訪存，而MySQL訪存的基本單位是16KB。
• 操作系統(tǒng)為MySQL設(shè)置一個(gè)緩沖區(qū)，一次性從磁盤訪存4次數(shù)據(jù)給MySQL使用。
• MySQL從操作系統(tǒng)為其準(zhǔn)備的緩沖區(qū)中取數(shù)據(jù)到自身的緩沖區(qū)進(jìn)行操作。

查看MySQL訪存基本單位：

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

4. 建立共識(shí)

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

查看MySQL的配置文件，可以看到Buffer Pool的默認(rèn)大?。?/p>

5. 索引的理解

創(chuàng)建測(cè)試表進(jìn)行測(cè)試

創(chuàng)建了一個(gè)主鍵為序號(hào), 包含年齡、姓名屬性的用戶表：

用戶表的結(jié)構(gòu)如下：

向表中插入一些無(wú)序的數(shù)據(jù)：

查看表中數(shù)據(jù)：

無(wú)序插入的數(shù)據(jù)，在表中按照主鍵的順序出現(xiàn)。

為什么IO交互要使用page：

如上面的5條記錄，如果MySQL要查找id=2的記錄，第一次加載id=1，第二次加載id=2，一次一條記錄，那 么就需要2次IO。如果要找id=5，那么就需要5次IO。 但，如果這5條(或者更多)都被保存在一個(gè)Page中(16KB，能保存很多記錄),那么第一次IO查找id=2的時(shí) 候，整個(gè)Page會(huì)被加載到MySQL的Buffer Pool中，這里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要進(jìn)行IO了，而是直接在內(nèi)存中進(jìn)行了。所以，就在單Page里面，大大減少了IO的次數(shù)。 我們不能嚴(yán)格保證，但是有很大概率，因?yàn)橛芯植啃栽怼?往往IO效率低下的最主要矛盾不是IO單次數(shù)據(jù)量的大小，而是IO的次數(shù)。

理解單個(gè)page

MySQL 中要管理很多數(shù)據(jù)表文件，而要管理好這些文件，就需要 先描述，在組織 ,而一個(gè)個(gè)獨(dú)立文件是由一個(gè)或者多個(gè)Page構(gòu)成的。

• Page 在 MySQL 中，都是 16KB 。
• 單個(gè)page文件的結(jié)構(gòu)由兩個(gè)指針（ prev 和 next ）和數(shù)據(jù)構(gòu)成。
• page文件中的數(shù)據(jù)由鏈表結(jié)構(gòu)組織起來(lái)，因此在page頁(yè)中增刪數(shù)據(jù)的速度很快，但是查詢的速度很慢。
• 因?yàn)橛兄麈I的問(wèn)題， MySQL 會(huì)默認(rèn)按照主鍵給我們的數(shù)據(jù)進(jìn)行排序。

理解多個(gè)page

page通過(guò)一次加載16KB的數(shù)據(jù)的方式，減少IO，提高了了效率，但是數(shù)據(jù)庫(kù)中不只是有一個(gè)page頁(yè)：

• 多個(gè)page頁(yè)是由prev 和 next 兩個(gè)指針構(gòu)成雙向鏈表。
• 由于多個(gè)page頁(yè)是用鏈表結(jié)構(gòu)組織起來(lái)的，page頁(yè)內(nèi)的數(shù)據(jù)也是由鏈表結(jié)構(gòu)組織起來(lái)的，因此查找數(shù)據(jù)時(shí)需要先遍歷page頁(yè)鏈表結(jié)構(gòu)找到對(duì)應(yīng)的page頁(yè)，然后在對(duì)應(yīng)的page頁(yè)中遍歷數(shù)據(jù)鏈表結(jié)構(gòu)，因此效率很低。

頁(yè)目錄

由于page結(jié)構(gòu)的限制，只能線性遍歷搜素?cái)?shù)據(jù)，效率很低，我們引入了頁(yè)目錄的概念。

頁(yè)目錄用于記錄對(duì)應(yīng)數(shù)據(jù)以及對(duì)應(yīng)數(shù)據(jù)所在位置的結(jié)構(gòu)。

這里的頁(yè)目錄就和我們平?？磿哪夸涱愃?，我們想找到書的某一部分時(shí)，可以通過(guò)查看目錄找到該部分所在章節(jié)，然后從該章節(jié)對(duì)應(yīng)的頁(yè)數(shù)在章節(jié)內(nèi)尋找，大大提高了效率。于是我們?cè)贛ySQL中的page頁(yè)結(jié)構(gòu)中也引入了頁(yè)目錄。

單表情況：

• page頁(yè)中不僅存有prev、next指針和數(shù)據(jù)還有頁(yè)目錄。
• 查找數(shù)據(jù)時(shí)，通過(guò)頁(yè)目錄篩選數(shù)據(jù)可能在的位置，減少不必要的記錄的遍歷。
• page頁(yè)的大小是固定的，引入頁(yè)目錄本質(zhì)上是一種空間換時(shí)間的策略。

引入頁(yè)目錄后，比如我們要查找圖中主鍵為4的數(shù)據(jù)，可以先遍歷頁(yè)目錄發(fā)現(xiàn)主鍵大于目錄2記錄的主鍵數(shù)據(jù)3，目錄1到目錄2之間不可能存在，于是就減少了不必要記錄的遍歷，然后我們發(fā)現(xiàn)要找的記錄會(huì)在目錄2指向的位置往后，于是根據(jù)目錄2指向的位置往后尋找。由于這種查找的方式是需要主鍵有序的，因此我們可以理解為什么在測(cè)試表中發(fā)現(xiàn)無(wú)序的插入數(shù)據(jù)，結(jié)果卻是有序的。

多表情況：

在單個(gè)page頁(yè)內(nèi)引入了頁(yè)目錄提高了page頁(yè)內(nèi)鏈?zhǔn)浇Y(jié)構(gòu)的數(shù)據(jù)的查找效率，但是多個(gè)page頁(yè)間仍然是鏈?zhǔn)浇Y(jié)構(gòu)的，導(dǎo)致了如果要查找特定page頁(yè)，就要從page頁(yè)鏈表的表頭開(kāi)始線性查找，但是一次只從磁盤中加載一個(gè)page頁(yè)，尋找特定page頁(yè)就要多次IO加載page頁(yè)影響效率。因此將頁(yè)目錄的結(jié)構(gòu)也使用在page頁(yè)的查找上：

• 用一些page頁(yè)專門記錄頁(yè)目錄，page頁(yè)內(nèi)只存有頁(yè)目錄。
• 專門做頁(yè)目錄的page頁(yè)中的頁(yè)目錄用于記錄page頁(yè)的編號(hào)，和指向?qū)?yīng)page頁(yè)的指針。
• 通過(guò)專門做頁(yè)目錄的page頁(yè)查找對(duì)應(yīng)page頁(yè)，提高多個(gè)page頁(yè)的查找效率。

同樣的專門記錄頁(yè)目錄的page頁(yè)也是鏈?zhǔn)浇Y(jié)構(gòu)的，因此如果專門記錄頁(yè)目錄的page頁(yè)過(guò)多，page頁(yè)的查找效率也會(huì)很低，因此我們需要一些page頁(yè)記錄這些page頁(yè)的目錄：

如此以來(lái)，我們建立了由上到下的結(jié)構(gòu)用于提高查找效率，如果數(shù)據(jù)量增大，我們只需要不斷增加向上增加page頁(yè)就可以提高效率。在后續(xù)的查找過(guò)程中由上到下通過(guò)頁(yè)目錄篩選、除去不必要的page頁(yè)不記錄即可大大的提升查找效率。由于一個(gè)page頁(yè)的大小為16KB，能夠存儲(chǔ)大量的頁(yè)目錄，因此不用擔(dān)心，page頁(yè)的層數(shù)過(guò)多。

• page頁(yè)整體的結(jié)構(gòu)是一個(gè)B+樹(shù)的結(jié)構(gòu)。
• 除了B+樹(shù)也夠，也有使用其他結(jié)構(gòu)的存儲(chǔ)引擎，比如哈希結(jié)構(gòu)。
• 除了InnoDB,大部分的存儲(chǔ)引擎都采用B+樹(shù)的結(jié)構(gòu)。
• B+樹(shù)結(jié)構(gòu)只有葉子結(jié)點(diǎn)保存數(shù)據(jù)，并用鏈?zhǔn)浇Y(jié)構(gòu)存儲(chǔ)，非葉子結(jié)點(diǎn)保存目錄，非葉子節(jié)點(diǎn)只存目錄的情況下可以存放大量目錄，因此該B+樹(shù)的整體結(jié)構(gòu)是層數(shù)比較少的“矮胖”結(jié)構(gòu)，順著目錄尋找page，由于層數(shù)少，因此尋找葉子節(jié)點(diǎn)需要加載的page頁(yè)少，IO次數(shù)也就少，提高效率。
• InnooDB存儲(chǔ)引擎下就是這種B+樹(shù)的結(jié)構(gòu)，因此在MySQL中對(duì)數(shù)據(jù)的增刪查改就是在這個(gè)結(jié)構(gòu)內(nèi)進(jìn)行的，如果我們沒(méi)有設(shè)置主鍵，MySQL會(huì)生成隱藏的屬性列作為排序?qū)傩?，存在目錄里用于管理?shù)據(jù)，當(dāng)然我們不設(shè)置主鍵時(shí)，也無(wú)法使用這個(gè)隱藏的屬性列，因此查找數(shù)據(jù)時(shí)只能在葉子節(jié)點(diǎn)中線性遍歷，因此效率很低。

簡(jiǎn)單復(fù)盤一下：

由于存儲(chǔ)數(shù)據(jù)的page是鏈?zhǔn)浇Y(jié)構(gòu)，page內(nèi)的數(shù)據(jù)是鏈?zhǔn)浇Y(jié)構(gòu)因此，查找數(shù)據(jù)只能線性遍歷，效率很低，為此引入了只存儲(chǔ)頁(yè)目錄的page，通過(guò)頁(yè)目錄知道數(shù)據(jù)在那個(gè)范圍內(nèi)，排除不需要遍歷的page，由于只存儲(chǔ)頁(yè)目錄的page的也是鏈?zhǔn)浇Y(jié)構(gòu)，因此需要有只存儲(chǔ)頁(yè)目錄的page用來(lái)查找這些page頁(yè)，最后形成的整體結(jié)構(gòu)是B+樹(shù)結(jié)構(gòu)，通過(guò)這個(gè)結(jié)構(gòu)可以排除大量不需要遍歷的page，page頁(yè)的加載次數(shù)只取決于B+樹(shù)結(jié)構(gòu)的層數(shù)，減少了IO,提升了效率。索引的本質(zhì)就是數(shù)據(jù)結(jié)構(gòu)。

其他的數(shù)據(jù)結(jié)構(gòu)的問(wèn)題

• 鏈表？線性遍歷，效率太低。
• 二叉搜索樹(shù)？退化問(wèn)題，可能退化成為線性結(jié)構(gòu) 。
• AVL &&紅黑樹(shù)？雖然是平衡或者近似平衡，但是畢竟是二叉結(jié)構(gòu)，相比較多階B+，意味著樹(shù)整體過(guò)高，大家都是自頂向下找，層高越低，意味著系統(tǒng)與硬盤更少的IO Page交互。
• Hash？官方的索引實(shí)現(xiàn)方式中， MySQL 是支持HASH的，不過(guò) InnoDB 和 MyISAM 并不支持.Hash跟 進(jìn)其算法特征，決定了雖然有時(shí)候也很快(O(1))，不過(guò)，在面對(duì)范圍查找就效率很低。

下面是幾個(gè)常見(jiàn)的存儲(chǔ)引擎，與其所支持的索引類型：

• 這里的BTREE指的是B+樹(shù)。

B樹(shù)和B+樹(shù)

B+樹(shù)是B樹(shù)的一種變形結(jié)構(gòu)，那為什么我們沒(méi)有采用普通的B樹(shù)作為索引結(jié)構(gòu)呢？

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

聚簇索引和非聚簇索引

• 聚簇索引 – 數(shù)據(jù)與索引數(shù)據(jù)在一起索引方案。
• 非聚簇索引 – 數(shù)據(jù)與索引數(shù)據(jù)不在一起索引方案。
• InnoDB存儲(chǔ)引擎采用的是聚簇索引。
• MyISAM采用的是非聚簇索引。

這個(gè)聚簇索引的結(jié)構(gòu)，就是前文提到的只有葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)的B+樹(shù)結(jié)構(gòu)。

MyISAM索引結(jié)構(gòu)：

MyISAM存儲(chǔ)引擎同樣采用的是B+樹(shù)索引結(jié)構(gòu)，不同的是葉子結(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，只存儲(chǔ)數(shù)據(jù)的指針。

用存儲(chǔ)文件來(lái)驗(yàn)證聚簇索引和非聚簇索引的結(jié)構(gòu)：

InnoDB存儲(chǔ)引擎采用聚簇索引：

MyISAM存儲(chǔ)引擎采用非聚簇索引：

MyISAM主鍵索引結(jié)構(gòu)：

• 和InnoDB的結(jié)構(gòu)一樣都是B+樹(shù)結(jié)構(gòu)，只是葉子結(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)而是數(shù)據(jù)指針。
• 通過(guò)主鍵進(jìn)行索引從上倒下查找到對(duì)應(yīng)葉子結(jié)點(diǎn)，通過(guò)葉子節(jié)點(diǎn)存儲(chǔ)的指針找到對(duì)應(yīng)數(shù)據(jù)。

MyISAM普通索引結(jié)構(gòu)：

• MyISAM普通索引和MyISAM主鍵索引結(jié)構(gòu)一樣。
• MyISAM普通索引和MyISAM主鍵索引的區(qū)別是值不能重復(fù)。

InnoDB普通索引結(jié)構(gòu)：

• InnoDB普通索引和InnoDB主鍵索引的結(jié)構(gòu)一樣。
• InnoDB普通索引最后會(huì)索引到InnoDB主鍵索引對(duì)應(yīng)的葉子結(jié)點(diǎn)，也就是從普通數(shù)據(jù)索引到對(duì)應(yīng)主鍵數(shù)據(jù)，由于主鍵索引中存儲(chǔ)了整條記錄，因此找到對(duì)應(yīng)主鍵數(shù)據(jù)，就找到了整條記錄。
• 通過(guò)輔助（普通）索引，找到目標(biāo)記錄，需要兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這種過(guò)程，就叫做回表查詢。

一條結(jié)論：

由于具有主鍵索引和普通索引，因此一張表關(guān)聯(lián)的B+樹(shù)不止一個(gè)。

6. 索引操作

查看索引結(jié)構(gòu)

• 方式一

show keys from table_name;

查看索引結(jié)構(gòu)示例：

• Table – 表名
• Non_unique – 0表示唯一索引
• Key_name – 索引名
• Column_name – 索引屬性
• Index_type – 索引結(jié)構(gòu)

其中BTREE就是B+樹(shù)。

表中不存在索引：

• 方式二

show index from table_name;

查看索引結(jié)構(gòu)示例：

• 方式三

desc table_name;

查看索引結(jié)構(gòu)示例：

• 這種查看方式顯示信息太簡(jiǎn)略，不推薦。
• MySQL默認(rèn)為主鍵添加索引。

創(chuàng)建主鍵索引

• 方式一

在創(chuàng)建表的時(shí)候，直接在字段名后指定 primary key。

• 方式二

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

• 方式三

創(chuàng)建表以后再添加主鍵

主鍵索引的特點(diǎn)：

• 由于存儲(chǔ)引擎管理和存儲(chǔ)數(shù)據(jù)方式，添加主鍵就相當(dāng)于添加了索引。
• 在已創(chuàng)建的表中添加索引，就會(huì)在存儲(chǔ)結(jié)構(gòu)中添加一顆新的B+樹(shù)結(jié)構(gòu)存儲(chǔ)索引。
• 一個(gè)表中，最多有一個(gè)主鍵索引，當(dāng)然可以使復(fù)合主鍵。
• 主鍵索引的效率高（主鍵不可重復(fù)）。
• 創(chuàng)建主鍵索引的列，它的值不能為null，且不能重復(fù) 。
• 主鍵索引的列基本上是int。

創(chuàng)建唯一鍵索引

• 方式一

在定義時(shí)，在某列后直接指定unique唯一屬性。

• 方式二

創(chuàng)建表時(shí)，在表的后面指定某列或某幾列為unique。

• 方式三

創(chuàng)建表以后再添加唯一鍵。

唯一索引的特點(diǎn)：

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

創(chuàng)建普通索引

• 方式一

在表的定義最后，指定某列為索引。

• 方式二

創(chuàng)建完表以后指定某列為普通索引。

• 方式三

創(chuàng)建一個(gè)自定義索引名為索引在表的屬性上。

注意： 這里的索引名為自定義的my_index。

普通索引的特點(diǎn)：

• 一個(gè)表中可以有多個(gè)普通索引，普通索引在實(shí)際開(kāi)發(fā)中用的比較多。
• 如果某列需要?jiǎng)?chuàng)建索引，但是該列有重復(fù)的值，那么我們就應(yīng)該使用普通索引。

創(chuàng)建復(fù)合索引

復(fù)合索引就是將多個(gè)屬性作為索引的普通索引。

• 方式一

在表的定義最后，指定某列為索引。

注意： 由于是多個(gè)屬性作為一個(gè)索引，因此多個(gè)屬性所屬索引名相同。

• 方式二

創(chuàng)建完表以后指定某列為普通索引。

• 方式三

創(chuàng)建一個(gè)自定義索引名為索引在表的屬性上。

創(chuàng)建全文索引

當(dāng)對(duì)文章字段或有大量文字的字段進(jìn)行檢索時(shí)，會(huì)使用到全文索引。MySQL提供全文索引機(jī)制，但是有 要求，要求表的存儲(chǔ)引擎必須是MyISAM，而且默認(rèn)的全文索引支持英文，不支持中文。如果對(duì)中文進(jìn)行全文檢索，可以使用sphinx的中文版(coreseek)。

創(chuàng)建測(cè)試表：

創(chuàng)建測(cè)試表的代碼如下：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;
-- 插入數(shù)據(jù)
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

測(cè)試表的結(jié)構(gòu)和數(shù)據(jù)如下：

查看索引結(jié)構(gòu)：

嘗試查詢某條記錄：

用explain工具看一下，是否使用到索引：

• type : ALL --采用的是線性遍歷的查找方式。
• key : NULL – 沒(méi)有使用索引來(lái)查找。

嘗試使用全文索引查詢某條記錄：

用explain工具看一下，是否使用到索引：

• type : fulltext – 使用了全文索引。
• key : title – 使用title索引查詢的數(shù)據(jù)。

刪除索引

• 方式一 – 刪除主鍵索引

alter table table_name drop primary key;

• 方式二 – 刪除其他索引

alter table table_name drop index index_name; 

• 索引名(index_name)就是查看表結(jié)構(gòu)時(shí)中的 Key_name 字段。

注意： 索引名(index_name)不是屬性名，刪除其他索引時(shí)使用的是索引名。

注意： 使用的是索引名，Key_name對(duì)應(yīng)的字段，不是屬性名(Column_name對(duì)應(yīng)的字段)：

• 方式三 – 刪除自定義名索引

drop index index_name on table_name;

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

索引創(chuàng)建原則：文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-571989.html

• 比較頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引。
• 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件。
• 更新非常頻繁的字段不適合作創(chuàng)建索引。
• 不會(huì)出現(xiàn)在where子句中的字段不該創(chuàng)建索引。

到了這里，關(guān)于[MySQL]MySQL索引的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！