MySQL系列

前言

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

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

本篇文章以主流的InnoDB引擎為例，展開介紹

案例

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

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

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

一、認(rèn)識MySQL與磁盤

1.1 MySQL與存儲

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

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

1.2 MySQL 與磁盤交互基本單位

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

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

 SHOW GLOBAL STATUS LIKE 'innodb_page_size';

使用這個sql語句可以查找引擎頁大小。

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

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

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

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

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

三、索引的理解

3.1 測試案例

建立測試表
create table if not exists user (
id int primary key, --一定要添加主鍵哦，只有這樣才會默認(rèn)生成主鍵索引
age int not null,
name varchar(16) not null
);
插入多條記錄，注意此處數(shù)據(jù)的主鍵順序
insert into user (id, age, name) values(3, 18, '楊過');
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, '歐陽鋒');

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

具體形式后面介紹

3.2 page

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

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

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

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

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

3.3 頁目錄

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

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

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

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

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

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

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

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

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

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

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

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

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

四、聚簇索引 VS 非聚簇索引

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

其中，MyISAM最大的特點是，將索引Page和數(shù)據(jù)Page分離，也就是葉子節(jié)點沒有數(shù)據(jù)，只有對應(yīng)數(shù)據(jù)的地址相較于InnoDB索引，InnoDB是將索引和數(shù)據(jù)放在一起的。
現(xià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 這種用戶數(shù)據(jù)與索引數(shù)據(jù)分離的索引方案，叫做非聚簇索引，InnoDB 這種用戶數(shù)據(jù)與索引數(shù)據(jù)在一起索引方案，叫做聚簇索引。

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

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

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

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

五、索引操作

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

// 在創(chuàng)建表的時候，直接在字段名后指定 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);

主鍵索引的特點：

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

這些特點，在介紹主鍵時都說過，只是當(dāng)時沒有提出索引的概念

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

//在表定義時，在某列后直接指定unique唯一屬性。
create table user4(id int primary key, name varchar(30) unique);
//創(chuàng)建表時，在表的后面指定某列或某幾列為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);

唯一索引的特點：

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

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)建一個索引名為 idx_name 的索引
create table user10(id int primary key, name varchar(20),
 email varchar(30));
create index idx_name on user10(name);

普通索引的特點：

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

5.4 查詢索引

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

5.5 刪除索引

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

余下指令你自己測試吧

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

最新評論