一文搞懂MySQL索引特性(清晰明了)

更新時間：2023年04月10日 14:32:45 作者：還小給個面子

索引可以提高數(shù)據(jù)庫的性能,提高一個海量數(shù)據(jù)的檢索速度,但是插入,更新,刪除的速度相應(yīng)會降低,下面這篇文章主要給大家介紹了關(guān)于MySQL索引特性的相關(guān)資料,需要的朋友可以參考下

為什么要有索引？

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

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

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

常見索引：

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

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

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

-- 構(gòu)建一個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 '雇員領(lǐng)導編號',
  `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;查看表結(jié)構(gòu)，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 給用戶提供存儲服務(wù)，而存儲的都是數(shù)據(jù)，數(shù)據(jù)在磁盤這個外設(shè)當中。磁盤是計算機中的一個機械設(shè)備，相比于計算機其他電子元件，磁盤效率是比較低的，在加上IO本身的特征，如何提高效率，是 MySQL 的一個重要話題。

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

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

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

定位扇區(qū)

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

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

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

系統(tǒng)軟件和磁盤進行IO交互不是按照扇區(qū)（512KB）進行交互的
如果系統(tǒng)直接使用硬件提供的數(shù)據(jù)大小進行交互，那么系統(tǒng)的IO代碼就和硬件強相關(guān)，如今硬件的發(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結(jié)束的扇區(qū)地址是連續(xù)的，那磁頭就能很快的開始這次IO操作，這樣的多個IO操作稱為連續(xù)訪問。

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

MySQL與磁盤交互

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

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

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

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

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

簡化圖：

索引的理解

測試主鍵索引

建立測試表：

插入多條記錄：

查看插入結(jié)果：

索引的原理

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

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

偽代碼：

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在進行查找時，預(yù)先查找目錄中的內(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也帶上目錄，每一個目錄項的構(gòu)成是 Page中最小主鍵值和指向該Page的指針，與頁內(nèi)目錄不同，這個目錄管理的級別是Page頁，頁內(nèi)目錄管理的級別是一條記錄

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

總結(jié)：

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

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

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

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