為什么要有索引？

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。

打個(gè)比方，如果合理的設(shè)計(jì)且使用索引的MySQL是一輛蘭博基尼的話(huà)，那么沒(méi)有設(shè)計(jì)和使用索引的MySQL就是一個(gè)人力三輪車(chē)。

索引的引入，使得查詢(xún)速度的提高，這種提高是以插入、更新、刪除的速度為代價(jià)的，這些寫(xiě)操作，增加了大量的IO。所以它的價(jià)值，在于提高一個(gè)海量數(shù)據(jù)的檢索速度。

常見(jiàn)索引：

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

案例：構(gòu)建一個(gè)海量數(shù)據(jù)表，來(lái)驗(yàn)證索引帶來(lái)的查詢(xún)差異性

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

-- 構(gòu)建一個(gè)8000000條記錄的數(shù)據(jù)

-- 產(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 '部門(mén)編號(hào)'
);


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

上面的sql創(chuàng)建了test_index數(shù)據(jù)庫(kù)，test_index中有一個(gè)含有8000000條記錄的EMP表，select * from EMP limit 10 查看部分?jǐn)?shù)據(jù)：

desc EMP;查看表結(jié)構(gòu)，EMP表沒(méi)有創(chuàng)建任何索引：

嘗試查詢(xún)EMP表的記錄：

可以發(fā)現(xiàn)查詢(xún)EMP表的記錄，由于數(shù)據(jù)量很大而且EMP沒(méi)有建立任何索引，每次都需要較長(zhǎng)的時(shí)間進(jìn)行查詢(xún)。

為EMP表建立索引：

由于數(shù)據(jù)量很大，EMP表在創(chuàng)建索引需要花費(fèi)較長(zhǎng)的時(shí)間。

創(chuàng)建索引后嘗試查詢(xún)：

可以發(fā)現(xiàn)，索引大大提高了數(shù)據(jù)庫(kù)表的查詢(xún)速度。

認(rèn)識(shí)磁盤(pán)

MySQL 給用戶(hù)提供存儲(chǔ)服務(wù)，而存儲(chǔ)的都是數(shù)據(jù)，數(shù)據(jù)在磁盤(pán)這個(gè)外設(shè)當(dāng)中。 磁盤(pán)是計(jì)算機(jī)中的一個(gè)機(jī)械設(shè)備，相比于計(jì)算機(jī)其他電子元件，磁盤(pán)效率是比較低的，在加上IO本身的特征，如何提高效率，是 MySQL 的一個(gè)重要話(huà)題。

磁盤(pán)的結(jié)構(gòu)

磁盤(pán)的盤(pán)片結(jié)構(gòu)

在MySQL中創(chuàng)建數(shù)據(jù)庫(kù)，本質(zhì)上是在Linux下創(chuàng)建特定目錄，在MySQL中創(chuàng)建數(shù)據(jù)庫(kù)表，本質(zhì)上是在特定的目錄下創(chuàng)建特定的文件。數(shù)據(jù)庫(kù)文件，本質(zhì)上就是保存在磁盤(pán)的盤(pán)片中，也就是上圖的一個(gè)個(gè)小格子中，即扇區(qū)。所以找到一個(gè)數(shù)據(jù)庫(kù)文件，本質(zhì)上就是在磁盤(pán)上找到對(duì)應(yīng)的扇區(qū)，就需要能夠定位某個(gè)盤(pán)片中的某些扇區(qū)。

定位扇區(qū)

• 柱面(磁道): 多盤(pán)磁盤(pán)，每盤(pán)都是雙面，大小完全相等。那么同半徑的磁道，整體上便構(gòu)成了一個(gè)柱面
• 每個(gè)盤(pán)面都有一個(gè)磁頭，那么磁頭和盤(pán)面的對(duì)應(yīng)關(guān)系便是1對(duì)1的

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

? 在硬件層面上，我們已經(jīng)可以定位某一個(gè)扇區(qū)，那么系統(tǒng)軟件和磁盤(pán)進(jìn)行IO交互也是按照扇區(qū)（512KB）來(lái)進(jìn)行的嗎

• 系統(tǒng)軟件和磁盤(pán)進(jìn)行IO交互不是按照扇區(qū)（512KB）進(jìn)行交互的
• 如果系統(tǒng)直接使用硬件提供的數(shù)據(jù)大小進(jìn)行交互，那么系統(tǒng)的IO代碼就和硬件強(qiáng)相關(guān)，如今硬件的發(fā)展日新月異，換言之，如果硬件發(fā)生變化，系統(tǒng)代碼就必須大規(guī)模更改，維護(hù)成本大
• 512byte作為單次IO的大小太小了，這就意味著系統(tǒng)需要重復(fù)讀取相同大小的數(shù)據(jù)，需要多次訪問(wèn)磁盤(pán)，效率較低
• 文件系統(tǒng)中，物理內(nèi)存實(shí)際上是被分為一個(gè)個(gè)4KB的數(shù)據(jù)塊的，文件系統(tǒng)讀取磁盤(pán)的基本單位，不是扇區(qū)，而是數(shù)據(jù)塊，基本單位是4KB

磁盤(pán)隨機(jī)訪問(wèn) (Random Access)與連續(xù)訪問(wèn) (Sequential Access)

• 隨機(jī)訪問(wèn)：本次IO所給出的扇區(qū)地址和上次IO給出扇區(qū)地址不連續(xù)，這樣的話(huà)磁頭在兩次IO操作之間需要作比較大的移動(dòng)動(dòng)作才能重新開(kāi)始讀/寫(xiě)數(shù)據(jù)。
• 連續(xù)訪問(wèn)：如果當(dāng)次IO給出的扇區(qū)地址與上次IO結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開(kāi)始這次IO操作，這樣的多個(gè)IO操作稱(chēng)為連續(xù)訪問(wèn)。

因此盡管相鄰的兩次IO操作在同一時(shí)刻發(fā)出，但如果它們的請(qǐng)求的扇區(qū)地址相差很大的話(huà)也只能稱(chēng)為隨機(jī)訪問(wèn)，而非連續(xù)訪問(wèn)，因?yàn)檫B續(xù)訪問(wèn)的連續(xù)指的是物理上的連續(xù)，而不是時(shí)間上的連續(xù)。磁盤(pán)是通過(guò)機(jī)械運(yùn)動(dòng)進(jìn)行尋址的，連續(xù)訪問(wèn)不需要過(guò)多的定位，故效率比較高。

MySQL與磁盤(pán)交互

MySQL作為一款應(yīng)用軟件，可以想象成是一種特殊的文件系統(tǒng)，它有著更高頻的IO場(chǎng)景，因此為了提高基本的IO效率，MySQL與磁盤(pán)交互的基本單位是16KB，這個(gè)基本數(shù)據(jù)單元在MySQL這里也叫做Page

show global status like 'innodb_page_size查看page大小：

在MySQL進(jìn)行CRUD時(shí)，是需要計(jì)算數(shù)據(jù)的位置的，涉及到計(jì)算就需要CPU的參與，根據(jù)馮諾依曼體系結(jié)構(gòu)，CPU只和內(nèi)存打交道，因此MySQL訪問(wèn)數(shù)據(jù)，不可能直接和磁盤(pán)交互，全部需要加載到內(nèi)存進(jìn)行訪問(wèn)。

數(shù)據(jù)庫(kù)的數(shù)據(jù)是可能同時(shí)存在于內(nèi)存和磁盤(pán)中的，數(shù)據(jù)在進(jìn)行CRUD之后發(fā)生更改，就需要有對(duì)應(yīng)的刷新策略將數(shù)據(jù)刷新到磁盤(pán)，這就說(shuō)明MySQL需要較高頻次的進(jìn)行IO操作，為了提高效率，MySQL服務(wù)器會(huì)在內(nèi)存中預(yù)先開(kāi)辟一大塊空間進(jìn)行數(shù)據(jù)緩存，這塊空間叫做buffer pool，磁盤(pán)的數(shù)據(jù)會(huì)預(yù)先加載到buffer pool中，刷新磁盤(pán)的數(shù)據(jù)也是從buffer pool中將數(shù)據(jù)刷新到磁盤(pán)。

數(shù)據(jù)是不會(huì)直接從內(nèi)存刷新到磁盤(pán)的，它們的交互會(huì)經(jīng)過(guò)操作系統(tǒng)，操作系統(tǒng)有對(duì)應(yīng)的內(nèi)核級(jí)緩沖區(qū)，當(dāng)MySQL需要從磁盤(pán)上加載數(shù)據(jù)時(shí)，數(shù)據(jù)會(huì)先通過(guò)磁盤(pán)和內(nèi)核緩沖區(qū)進(jìn)行每次4KB的IO交互，操作系統(tǒng)再通過(guò)對(duì)應(yīng)刷新策略，數(shù)據(jù)從內(nèi)核緩沖區(qū)以每次16KB的IO交互拷貝到buffer pool中。

簡(jiǎn)化圖：

索引的理解

測(cè)試主鍵索引

建立測(cè)試表：

插入多條記錄：

查看插入結(jié)果：

索引的原理

? 可以發(fā)現(xiàn)，插入數(shù)據(jù)的時(shí)候并沒(méi)有按照主鍵的順序進(jìn)行插入，但是插入多條數(shù)據(jù)后，結(jié)果默認(rèn)就是有序的，這是為什么？

MySQL中需要管理很多的數(shù)據(jù)，管理這些數(shù)據(jù)就需要先描述，再組織，MySQL中有一個(gè)個(gè)的Page結(jié)構(gòu)體，用來(lái)存放數(shù)據(jù)，MySQL中存在很多Page結(jié)構(gòu)體，它們通過(guò)兩個(gè)指針構(gòu)成雙向鏈表。

偽代碼：

struct Page 
{
	struct Page* page_prev;
	struct Page* page_next;
	char buffer[]
};

在插入數(shù)據(jù)時(shí)排序，是為了優(yōu)化鏈表增刪改效率高，查詢(xún)效率低的特點(diǎn)。但是當(dāng)Page內(nèi)的數(shù)據(jù)越來(lái)越多時(shí)，在頁(yè)內(nèi)查找也還是線性查找，于是數(shù)據(jù)庫(kù)在插入時(shí)，進(jìn)行排序，是為了便于建立Page中的目錄。在單個(gè)Page中引入頁(yè)內(nèi)目錄，將Page中數(shù)據(jù)分為若干區(qū)域，目錄中存儲(chǔ)這些區(qū)域中主鍵的最小值。

引入目錄后，MySQL在進(jìn)行查找時(shí)，預(yù)先查找目錄中的內(nèi)容，對(duì)于插入數(shù)據(jù)的主鍵處于目錄的哪一個(gè)區(qū)間，從而到區(qū)間中查找，大大提高了在單個(gè)Page中查找數(shù)據(jù)的效率。

MySQL在單個(gè)Page中引入目錄，大大提高了再單個(gè)Page中的查找效率，但是當(dāng)數(shù)據(jù)量很大時(shí)，MySQL中存在很多Page，這些Page也是通過(guò)鏈表的形式連接起來(lái)的，所以在數(shù)據(jù)量很大時(shí)，在多個(gè)Page中查找也是線性遍歷。

? MySQL是怎么處理這種情況，提高效率的呢

按照單個(gè)Page內(nèi)創(chuàng)建目錄的思路，給多個(gè)Page也帶上目錄，每一個(gè)目錄項(xiàng)的構(gòu)成是 Page中最小主鍵值 和 指向該P(yáng)age的指針，與頁(yè)內(nèi)目錄不同，這個(gè)目錄管理的級(jí)別是Page頁(yè)，頁(yè)內(nèi)目錄管理的級(jí)別是一條記錄

當(dāng)?shù)诙拥腜age逐漸增多時(shí)，可以再添加一層Page管理下層Page，依次類(lèi)推，就構(gòu)成了B+樹(shù)的結(jié)構(gòu)。通過(guò)B+樹(shù)的結(jié)構(gòu)，可以提高查找的效率，減少將過(guò)多Page加載到內(nèi)存中，減少和磁盤(pán)的IO次數(shù)。

總結(jié)：

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

索引結(jié)構(gòu)是否可以使用其他數(shù)據(jù)結(jié)構(gòu)

? InnoDB 在建立索引結(jié)構(gòu)來(lái)管理數(shù)據(jù)的時(shí)候，其他數(shù)據(jù)結(jié)構(gòu)為何不行

• 鏈表：查找是線性遍歷
• 二叉搜索樹(shù)：可能退化成鏈表的線性結(jié)構(gòu)，查找是線性遍歷
• AVL數(shù)和紅黑樹(shù)：雖然樹(shù)形結(jié)構(gòu)是平衡或者近似平衡的，但是該結(jié)構(gòu)還是二叉樹(shù)結(jié)構(gòu)，這就意味著AVL樹(shù)和紅黑樹(shù)的結(jié)構(gòu)會(huì)比較高，查詢(xún)數(shù)據(jù)是自頂向下查找，這就意味著要遍歷更多的結(jié)點(diǎn)，就需要經(jīng)歷多次IO

B樹(shù) vs B+樹(shù)

B樹(shù)：

B+樹(shù)：

• B樹(shù)節(jié)點(diǎn)，既有數(shù)據(jù)，又有Page指針，而B(niǎo)+只有葉子節(jié)點(diǎn)有數(shù)據(jù)，其他目錄頁(yè)，只有鍵值和Page指針
• B+樹(shù)葉子節(jié)點(diǎn)是以鏈表連接起來(lái)的，而B(niǎo)樹(shù)沒(méi)有相連

? 為什么選擇B+樹(shù)

• B+樹(shù)的結(jié)點(diǎn)中只有葉子結(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，而B(niǎo)樹(shù)的全部結(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，這樣一來(lái)，B+樹(shù)的高度比B樹(shù)的高度要低，查找的次數(shù)也會(huì)減少
• B+樹(shù)的結(jié)點(diǎn)以鏈表的形式相連，B樹(shù)沒(méi)有，在范圍查找的時(shí)候，B+樹(shù)的效率比B樹(shù)高

聚簇索引 vs 非聚簇索引

MyISAM 引擎同樣使用B+樹(shù)作為索引結(jié)果，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。下圖為 MyISAM 表的主索引，Col1 為主鍵。

其中， MyISAM 最大的特點(diǎn)是，將索引Page和數(shù)據(jù)Page分離，也就是葉子節(jié)點(diǎn)沒(méi)有數(shù)據(jù)，只有對(duì)應(yīng)數(shù)據(jù)的地址。相較于 InnoDB 索引， InnoDB 是將索引和數(shù)據(jù)放在一起的

下圖是InnoDB索引結(jié)構(gòu)，以Col3為主鍵：

其中， InnoDB 這種用戶(hù)數(shù)據(jù)與索引數(shù)據(jù)在一起索引方案，叫做聚簇索引，MyISAM 這種用戶(hù)數(shù)據(jù)與索引數(shù)據(jù)分離的索引方案，叫做非聚簇索引。

測(cè)試：

• innodb_test.frm: 存放的是表結(jié)構(gòu)數(shù)據(jù)
• innodb_test.ibd: 存放的是索引和用戶(hù)數(shù)據(jù)

• myisam_test.frm: 存放的是表結(jié)構(gòu)數(shù)據(jù)
• myisam_test.MYD: 存放的是表的用戶(hù)數(shù)據(jù)
• myisam_test.MYI: 存放的是表的索引數(shù)據(jù)

總結(jié)

到此這篇關(guān)于MySQL索引特性的文章就介紹到這了,更多相關(guān)MySQL索引特性?xún)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論