為什么要有索引？

MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。

打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。

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

常見索引：

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

案例：構建一個海量數(shù)據(jù)表，來驗證索引帶來的查詢差異性

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

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

-- 產(chǎn)生隨機字符串
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)生隨機數(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)建了test_index數(shù)據(jù)庫，test_index中有一個含有8000000條記錄的EMP表，select * from EMP limit 10 查看部分數(shù)據(jù)：

desc EMP;查看表結構，EMP表沒有創(chuàng)建任何索引：

嘗試查詢EMP表的記錄：

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

為EMP表建立索引：

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

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

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

認識磁盤

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

磁盤的結構

磁盤的盤片結構

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

定位扇區(qū)

• 柱面(磁道): 多盤磁盤，每盤都是雙面，大小完全相等。那么同半徑的磁道，整體上便構成了一個柱面
• 每個盤面都有一個磁頭，那么磁頭和盤面的對應關系便是1對1的

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

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

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

磁盤隨機訪問 (Random Access)與連續(xù)訪問 (Sequential Access)

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

因此盡管相鄰的兩次IO操作在同一時刻發(fā)出，但如果它們的請求的扇區(qū)地址相差很大的話也只能稱為隨機訪問，而非連續(xù)訪問，因為連續(xù)訪問的連續(xù)指的是物理上的連續(xù)，而不是時間上的連續(xù)。磁盤是通過機械運動進行尋址的，連續(xù)訪問不需要過多的定位，故效率比較高。

MySQL與磁盤交互

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

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

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

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

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

簡化圖：

索引的理解

測試主鍵索引

建立測試表：

插入多條記錄：

查看插入結果：

索引的原理

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

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

偽代碼：

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

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

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

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

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

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

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

總結：

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

索引結構是否可以使用其他數(shù)據(jù)結構

? InnoDB 在建立索引結構來管理數(shù)據(jù)的時候，其他數(shù)據(jù)結構為何不行

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

B樹 vs B+樹

B樹：

B+樹：

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

? 為什么選擇B+樹

• B+樹的結點中只有葉子結點存儲數(shù)據(jù)，而B樹的全部結點都存儲數(shù)據(jù)，這樣一來，B+樹的高度比B樹的高度要低，查找的次數(shù)也會減少
• B+樹的結點以鏈表的形式相連，B樹沒有，在范圍查找的時候，B+樹的效率比B樹高

聚簇索引 vs 非聚簇索引

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

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

下圖是InnoDB索引結構，以Col3為主鍵：

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

測試：

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

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

總結

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

最新評論