MySQL系列

前言

上一篇：Mysql數(shù)據(jù)庫(kù)事務(wù)概念、操作與隔離級(jí)別全解析

在MySQL數(shù)據(jù)庫(kù)中，索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，它與表中數(shù)據(jù)關(guān)聯(lián)，就像書(shū)籍的目錄與正文的關(guān)系——目錄通過(guò)章節(jié)標(biāo)題和頁(yè)碼快速定位內(nèi)容，而索引則通過(guò)存儲(chǔ)數(shù)據(jù)的關(guān)鍵列值及其對(duì)應(yīng)物理位置，幫助數(shù)據(jù)庫(kù)快速定位目標(biāo)數(shù)據(jù)。

本篇文章以主流的InnoDB引擎為例，展開(kāi)介紹

案例

在MySQL中操作存儲(chǔ)大量數(shù)據(jù)的表時(shí)，我們往往會(huì)面臨處理數(shù)據(jù)慢，性能低下等問(wèn)題，這時(shí)只需要建立索引就可以將這種問(wèn)題優(yōu)化。

從操作執(zhí)行時(shí)間不難看出，建立索引給我們操作帶來(lái)的巨大提升。

在學(xué)習(xí)索引是如何優(yōu)化MySQL的性能之前，需要先知道MySQL為什么存在性能方面的問(wèn)題。

一、認(rèn)識(shí)MySQL與磁盤(pán)

1.1 MySQL與存儲(chǔ)

MySQL 給用戶(hù)提供數(shù)據(jù)存儲(chǔ)服務(wù)的，早在之前我就介紹過(guò)，MySQL使用的庫(kù)、表對(duì)數(shù)據(jù)存儲(chǔ)，在Linux下都表現(xiàn)為特殊結(jié)構(gòu)的文件，要想對(duì)數(shù)據(jù)進(jìn)行持久化保存，這些文件最終都要存儲(chǔ)在磁盤(pán)中，所有MySQL下數(shù)據(jù)存儲(chǔ)在磁盤(pán)這個(gè)外設(shè)當(dāng)中，相比于計(jì)算機(jī)其他電子元件，磁盤(pán)效率是比較低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一個(gè)重要話(huà)題。

Linux文件系統(tǒng)
這篇文章中詳細(xì)介紹了系統(tǒng)對(duì)磁盤(pán)的訪(fǎng)問(wèn)，強(qiáng)烈建議看一下,這里就不介紹了

1.2 MySQL 與磁盤(pán)交互基本單位

MySQL 作為一款應(yīng)用軟件，可以想象成一種特殊的文件系統(tǒng)，它有著更多的IO需求，而IO操作會(huì)大大影響執(zhí)行效率，所以，為了提高基本的IO效率， MySQL 進(jìn)行IO的基本單位是 16KB 。

磁盤(pán)這個(gè)硬件設(shè)備的基本單位是 512 字節(jié)，操作系統(tǒng)在和磁盤(pán)交互時(shí)以4KB為單位，而 MySQL InnoDB引擎使用 16KB 和內(nèi)存進(jìn)行IO交互。所以可以理解為， MySQL 和磁盤(pán)進(jìn)行數(shù)據(jù)交互的基本單位是 16KB 。這個(gè)基本數(shù)據(jù)單元，在 MySQL 這里叫做page（注意和系統(tǒng)的page區(qū)分）。

 SHOW GLOBAL STATUS LIKE 'innodb_page_size';

使用這個(gè)sql語(yǔ)句可以查找引擎頁(yè)大小。

二、 MySQL 數(shù)據(jù)交互核心：BufferPool 與 IO 優(yōu)化機(jī)制

通過(guò)前文介紹可知，MySQL 中的數(shù)據(jù)文件在磁盤(pán)上以 page（16KB） 為單位存儲(chǔ)。當(dāng)執(zhí)行 CURD（增刪改查）操作時(shí)，MySQL 需先通過(guò)計(jì)算定位目標(biāo)數(shù)據(jù)的位置——這一過(guò)程依賴(lài) CPU 參與，而 CPU 只能直接操作內(nèi)存數(shù)據(jù)。因此，數(shù)據(jù)必須先從磁盤(pán)臨時(shí)加載到內(nèi)存中，形成“磁盤(pán)一份、內(nèi)存一份”的臨時(shí)狀態(tài)。待內(nèi)存中的數(shù)據(jù)操作完成后，再通過(guò)特定策略將更新同步回磁盤(pán)，這一過(guò)程即涉及磁盤(pán)與內(nèi)存的交互（IO），而 IO 的基本單位正是 page。

為高效管理內(nèi)存中的數(shù)據(jù)、減少頻繁的磁盤(pán) IO，MySQL 服務(wù)器在啟動(dòng)時(shí)會(huì)在內(nèi)存中申請(qǐng)一塊專(zhuān)用的大內(nèi)存區(qū)域，稱(chēng)為 BufferPool（緩沖池）。它的核心作用是：

• 緩存熱點(diǎn)數(shù)據(jù)：將頻繁訪(fǎng)問(wèn)的 page 臨時(shí)存儲(chǔ)在內(nèi)存中，避免每次操作都直接讀寫(xiě)磁盤(pán)（局部性原理：當(dāng)你對(duì)某一塊數(shù)據(jù)操作時(shí)，你的下一次操將有很大概率，會(huì)使用后面的數(shù)據(jù)）。
• 優(yōu)化 IO 效率：所有數(shù)據(jù)操作先在 BufferPool 中完成，操作完成后同步到磁盤(pán)，大幅減少磁盤(pán) IO 次數(shù)（不可以操作一行，就獲取一行）。

因此，減少系統(tǒng)與磁盤(pán)的 IO 次數(shù)是提升 MySQL 效率的核心原則，而 BufferPool 正是實(shí)現(xiàn)這一目標(biāo)的關(guān)鍵機(jī)制——它通過(guò)內(nèi)存緩存降低了磁盤(pán) IO 對(duì)性能的影響。

三、索引的理解

3.1 測(cè)試案例

建立測(cè)試表
create table if not exists user (
id int primary key, --一定要添加主鍵哦，只有這樣才會(huì)默認(rèn)生成主鍵索引
age int not null,
name varchar(16) not null
);
插入多條記錄，注意此處數(shù)據(jù)的主鍵順序
insert into user (id, age, name) values(3, 18, '楊過(guò)');
insert into user (id, age, name) values(4, 16, '小龍女');
insert into user (id, age, name) values(2, 26, '黃蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '歐陽(yáng)鋒');

可以發(fā)現(xiàn)MySQL會(huì)將插入的數(shù)據(jù)默認(rèn)變?yōu)橛行?，那么這樣做有什么好處呢？
排序插入是為了優(yōu)化查詢(xún)效率

具體形式后面介紹

3.2 page

我們目前可以簡(jiǎn)單理解一個(gè)獨(dú)立表文件是由一個(gè)或者多個(gè)Page構(gòu)成的，那么這個(gè)表該如何管理這些page呢？

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 構(gòu)成雙向鏈表（像這種結(jié)構(gòu)在學(xué)習(xí)Linux時(shí)，我們經(jīng)常遇到），因?yàn)橛兄麈I的問(wèn)題， MySQL 會(huì)默認(rèn)按照主鍵給我們的數(shù)據(jù)進(jìn)行排序，從上面的Page內(nèi)數(shù)據(jù)記錄可以看出，數(shù)據(jù)是有序且彼此關(guān)聯(lián)的。

頁(yè)內(nèi)部存放數(shù)據(jù)的模塊，實(shí)質(zhì)上是一個(gè)鏈表的結(jié)構(gòu)，鏈表的特點(diǎn)也就是增刪快，查詢(xún)修改慢，所以?xún)?yōu)化查詢(xún)的效率是必須的，正是因?yàn)橛行?，在查找的時(shí)候，從頭到后都是有效查找，沒(méi)有任何一個(gè)查找是浪費(fèi)的（這一點(diǎn)在后面感受）

通過(guò)頁(yè)模式，MySQL 查詢(xún)時(shí)會(huì)將一整頁(yè)數(shù)據(jù)（16KB）加載到內(nèi)存，以此減少硬盤(pán) IO 次數(shù)、提升性能。

但頁(yè)模式內(nèi)部采用鏈表結(jié)構(gòu)，本質(zhì)上需通過(guò)逐條數(shù)據(jù)比較定位目標(biāo)。若表數(shù)據(jù)量大，且目標(biāo)數(shù)據(jù)位于最后一個(gè) page 的最后一條，仍需遍歷全表，導(dǎo)致查找速度過(guò)慢。

3.3 頁(yè)目錄

在課本中查找知識(shí)點(diǎn)時(shí)，我們會(huì)選擇優(yōu)先查看目錄，找到具體的頁(yè)，再?gòu)捻?yè)中查找知識(shí)點(diǎn)，這樣的查找效率要比從頭開(kāi)始找，高效的多。在這里每一個(gè)獨(dú)立的page或每一行數(shù)據(jù)，都可以視為“頁(yè)”，而我們要做的是，給這些“頁(yè)”添加屬于他們的目錄。
page內(nèi)部：

現(xiàn)在，要在一個(gè)Page內(nèi)部，查找id=3記錄，直接通過(guò)目錄2[3]，直接進(jìn)行定位新的起始位置，提高了效率。現(xiàn)在我們可以再次正式回答上面的問(wèn)題了，為何通過(guò)鍵值 MySQL 會(huì)自動(dòng)排序？可以很方便引入目錄，提高查找效率

圖中是為了迎合上面的數(shù)據(jù)，在實(shí)際情況下目錄間的區(qū)間是很大的，在進(jìn)行目錄查找時(shí)，一次查找可以pass掉很多數(shù)據(jù)。

多page情況：
單表數(shù)據(jù)不斷被插入的情況下， MySQL 會(huì)在容量不足的時(shí)候，自動(dòng)開(kāi)辟新的Page來(lái)保存新的數(shù)據(jù)，然后通過(guò)指針的方式，將所有的Page組織起來(lái)。

上面的方法幫我們提高了表內(nèi)部遍歷數(shù)據(jù)的效率，但是仍需要將每個(gè)page都，加載值內(nèi)存中，為了進(jìn)一步減少IO操作，我們采用頁(yè)目錄的方式

依照這個(gè)思路，我們還可以對(duì)目錄頁(yè)再次添加目錄管理，現(xiàn)在可以得出結(jié)論：

• Page分為目錄頁(yè)和數(shù)據(jù)頁(yè)。目錄頁(yè)只放各個(gè)下級(jí)Page的最小鍵值。
• 查找的時(shí)候，自定向下找，只需要加載部分目錄頁(yè)到內(nèi)存，即可完成算法的整個(gè)查找過(guò)程，大大減少了IO次數(shù)
  這個(gè)結(jié)構(gòu)最終就是一顆B+樹(shù)，整個(gè)過(guò)程中我們所要IO的次數(shù)，就是整個(gè)結(jié)構(gòu)數(shù)的高度

目錄頁(yè)的本質(zhì)也是頁(yè)，普通頁(yè)中存的數(shù)據(jù)是用戶(hù)數(shù)據(jù)，而目錄頁(yè)中存的數(shù)據(jù)是普通頁(yè)的地址。

3.3 對(duì)比其他結(jié)構(gòu)

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

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

B樹(shù)？最值得比較的是 InnoDB 為何不用B樹(shù)作為底層索引？
B樹(shù)節(jié)點(diǎn)，既有數(shù)據(jù)，又有Page指針，而B(niǎo)+，只有葉子節(jié)點(diǎn)有數(shù)據(jù)，其他目錄頁(yè)，只有鍵值和Page指針B+,葉子節(jié)點(diǎn)，全部相連，而B(niǎo)沒(méi)有,為何選擇B+
節(jié)點(diǎn)不存儲(chǔ)data，這樣一個(gè)節(jié)點(diǎn)就可以存儲(chǔ)更多的key?？梢允沟脴?shù)更矮，所以IO操作次數(shù)更少。葉子節(jié)點(diǎn)相連，更便于進(jìn)行范圍查找

具體結(jié)構(gòu)特征，你可以搜點(diǎn)圖片理解

四、聚簇索引 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ù)放在一起的。
現(xiàn)在我們就可以回答，第一篇文章遺留的問(wèn)題了

innodb引擎
create table itest(
id int primary key,
name varchar(11) not null
)engine=InnoDB;
MyISAM引擎
create table mtest(
id int primary key,
name varchar(11) not null
)engine=MyISAM;

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

MySQL 除了默認(rèn)會(huì)建立主鍵索引外，我們用戶(hù)也有可能建立按照其他列信息建立的索引，一般這種索引可以叫做輔助（普通）索引。對(duì)于MyISAM ,建立輔助（普通）索引和主鍵索引沒(méi)有差別，無(wú)非就是主鍵不能重復(fù)，而非主鍵可重復(fù)。
下圖就是基于 MyISAM 的 Col2 建立的索引，和主鍵索引沒(méi)有差別

同時(shí)我們以上表中的 Col3 建立對(duì)應(yīng)的輔助索引如下圖：

MyISAM 的非主鍵索引中葉子節(jié)點(diǎn)并沒(méi)有數(shù)據(jù)，而只有對(duì)應(yīng)記錄的key值。
所以通過(guò)輔助（普通）索引，找到目標(biāo)記錄，需要兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。這種過(guò)程，就叫做回表查詢(xún)?yōu)楹蜯yISAM 針對(duì)這種輔助（普通）索引的場(chǎng)景，不給葉子節(jié)點(diǎn)也附上數(shù)據(jù)呢？原因就是太浪費(fèi)空間了。
那么普通索引為什么要存在呢？
當(dāng)我們以符合間進(jìn)行索引時(shí)如：（姓名，qq），只知道第一個(gè)鍵值，需要查找第二個(gè)鍵值，我們就可以直接查找：

對(duì)于復(fù)合索引，匹配原則是，從做到右的，也就是說(shuō)，我們只需要知道姓名，就可以得到qq，這種普通索引方式，要比主鍵索引更快。

五、索引操作

5.1 創(chuàng)建主鍵索引

// 在創(chuàng)建表的時(shí)候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
//在創(chuàng)建表的最后，指定某列或某幾列為主鍵索引
create table user2(id int, name varchar(30), primary key(id));
//創(chuàng)建表以后再添加主鍵
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

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

• 一個(gè)表中，最多有一個(gè)主鍵索引，當(dāng)然可以使符合主鍵
• 主鍵索引的效率高（主鍵不重復(fù)）
• 創(chuàng)建主鍵索引的列，它的值不能為null，且不能重復(fù)
• 主鍵索引的列基本上是int

這些特點(diǎn)，在介紹主鍵時(shí)都說(shuō)過(guò)，只是當(dāng)時(shí)沒(méi)有提出索引的概念

5.2 唯一索引的創(chuàng)建

//在表定義時(shí)，在某列后直接指定unique唯一屬性。
create table user4(id int primary key, name varchar(30) unique);
//創(chuàng)建表時(shí)，在表的后面指定某列或某幾列為unique
create table user5(id int primary key,
 name varchar(30), unique(name));
//創(chuàng)建表以后再添加
create table user6(id int primary key, name varchar(30)）;
alter table user6 add unique(name);

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

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

5.3 普通索引的創(chuàng)建

 //在表的定義最后，指定某列為索引
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name)
);
//創(chuàng)建完表以后指定某列為普通索引
create table user9(id int primary key, name varchar(20),
 email varchar(30));
alter table user9 add index(name); 
//創(chuàng)建一個(gè)索引名為 idx_name 的索引
create table user10(id int primary key, name varchar(20),
 email varchar(30));
create index idx_name on user10(name);

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

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

5.4 查詢(xún)索引

show keys from 表名;
show index from 表名;

5.5 刪除索引

//刪除主鍵索引 
alter table 表名 drop primary key;
//其他索引的刪除
alter table 表名 drop index 索引名; 
drop index 索引名 on 表名

余下指令你自己測(cè)試吧

到此這篇關(guān)于MySQL 索引結(jié)構(gòu)、對(duì)比與操作實(shí)踐詳細(xì)攻略的文章就介紹到這了,更多相關(guān)MySQL 索引結(jié)構(gòu)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！