MySQL索引中的頁(yè)及索引的分類(lèi)及使用
更新時(shí)間：2025年07月21日 10:22:05 作者：?喜歡做夢(mèng)
文章介紹了InnoDB存儲(chǔ)引擎的頁(yè)結(jié)構(gòu)（16KB邏輯單位,含頁(yè)頭、頁(yè)尾、用戶記錄等）及索引分類(lèi),包括普通索引（MUL）、唯一索引（UNI）、主鍵索引（PRI）等,強(qiáng)調(diào)主鍵索引作為聚簇索引的特性,以及索引覆蓋優(yōu)化查詢效率的原理,感興趣的朋友跟隨小編一起看看吧
??一、什么是頁(yè)？
??1.定義
頁(yè)是InnoDB存儲(chǔ)引擎在磁盤(pán)上存儲(chǔ)數(shù)據(jù)的一種邏輯結(jié)構(gòu)，是管理數(shù)據(jù)和索引的基本單位，相當(dāng)于一個(gè)容器，存放表中的記錄、索引信息等。
- 大小：在.ibd文件中最重要的結(jié)構(gòu)體就是Page（頁(yè)），頁(yè)是內(nèi)存與磁盤(pán)交互的最小單元，默認(rèn)情況下，InnoDB的頁(yè)的大小為16KB，即使頁(yè)中沒(méi)有數(shù)據(jù)也會(huì)使用16KB的存儲(chǔ)空間，同時(shí)與索引的B+樹(shù)中的節(jié)點(diǎn)對(duì)應(yīng)。每次內(nèi)存與磁盤(pán)的交互至少讀取一頁(yè)，所以在磁盤(pán)中每個(gè)頁(yè)內(nèi)部的地址都是連續(xù)的。
- 查看頁(yè)的大?。?nbsp;show variables like 'innodb_page_size'；
mysql> show variables like 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | 16KB +------------------+-------+ 1 row in set, 1 warning (0.02 sec)
- 頁(yè)的數(shù)據(jù)類(lèi)型有多種，最常用的就是“索引頁(yè)”，也稱(chēng)為數(shù)據(jù)頁(yè)，用于存儲(chǔ)表中的實(shí)際數(shù)據(jù)記錄。不論是那種類(lèi)型的頁(yè)都包含頁(yè)頭和頁(yè)尾，頁(yè)的主題信息使用數(shù)據(jù)“行”來(lái)填充。
??2.頁(yè)的結(jié)構(gòu)
以數(shù)據(jù)頁(yè)的結(jié)構(gòu)為例：
- 文件頭（File Header）：包含頁(yè)的通用信息，如頁(yè)號(hào)、上一頁(yè)頁(yè)號(hào)和下一頁(yè)頁(yè)號(hào)，頁(yè)類(lèi)型等，其中上一頁(yè)頁(yè)號(hào)與下一頁(yè)頁(yè)號(hào)，將各個(gè)也連接起來(lái)，組成一個(gè)雙向鏈表。
- 頁(yè)頭（Page Header）：記錄數(shù)據(jù)頁(yè)的一些狀態(tài)信息，比如已使用的空間、空閑空間的位置，插入記錄的順序，這些信息對(duì)于數(shù)據(jù)的插入、更新、刪除至關(guān)重要。
- Infimum和Supremum記錄：這是兩個(gè)虛擬記錄，Onfimum記錄代表了數(shù)據(jù)頁(yè)中最小的記錄，Supremum記錄代表了數(shù)據(jù)頁(yè)中最大的記錄，作為數(shù)據(jù)行鏈表的頭和尾。Infimum連接第一個(gè)數(shù)據(jù)行，最后一行真實(shí)數(shù)據(jù)連接Supremum，構(gòu)成一個(gè)單向鏈表。
- 用戶記錄（User Records）：實(shí)際存儲(chǔ)表中的數(shù)據(jù)記錄，按照一定的順序記錄，并且通過(guò)指針相互連接；
- 空閑空間（Free Space）:數(shù)據(jù)頁(yè)中尚未使用的空間，新插入的記錄從這里分配空間。
- 頁(yè)目錄（Page Directory）：數(shù)據(jù)頁(yè)（默認(rèn)16KB）包含成百上千條數(shù)據(jù)，查找數(shù)據(jù)時(shí)，如果要每個(gè)都掃描過(guò)去，那么效率極低。為了提高效率InnoDB采用二分查找的方法。
頁(yè)目錄通過(guò)分組+槽的方法進(jìn)行高效的查找：
快速定位記錄組：先通過(guò)二分查找找到對(duì)應(yīng)的組；
組內(nèi)線性查找：每個(gè)組內(nèi)的數(shù)據(jù)不超過(guò)8個(gè)，超過(guò)8個(gè)重新分裂成一個(gè)組。
- 頁(yè)尾（Page Traider）：包含一些校驗(yàn)信息，如頁(yè)的校驗(yàn)和頁(yè)中記錄的數(shù)量，保證數(shù)據(jù)的完整性和一致性。
??3.B+樹(shù)在索引中的應(yīng)用
查找4為例：
- 判斷B+樹(shù)根節(jié)點(diǎn)的索引記錄，3<4,那么訪問(wèn)右子樹(shù)，到索引頁(yè)3；
- 在索引頁(yè)3中判斷id大小，找到與4相同的索引記錄，最后加載對(duì)應(yīng)的數(shù)據(jù)頁(yè)。
??二、索引的分類(lèi)及使用
按功能特性分類(lèi)：
??普通索引
1.定義
最基本的索引，沒(méi)有唯一性的限制，只是為了提高查詢效率而創(chuàng)建的索引?？赡転槎嗔袆?chuàng)建組合索引，稱(chēng)為復(fù)合索引或者組合索引。創(chuàng)建之后都會(huì)生成一顆索引樹(shù)，創(chuàng)建多少索引生成多少棵索引樹(shù)。
2.使用
-- 方式一：創(chuàng)建表時(shí)創(chuàng)建普通索引
mysql> create table class4(
-> id bigint,
-> name varchar(20),
-> index(name));
Query OK, 0 rows affected (0.09 sec)
mysql> desc class4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 方式二：修改為普通索引
mysql> create table class5(
-> id bigint,
-> name varchar(20));
Query OK, 0 rows affected (0.15 sec)
mysql> alter table class5 add index(name);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc class5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 方式三：創(chuàng)建索引并指定索引名：一定要指定索引名，否則創(chuàng)建失
mysql> create index idx_class5_id on class5(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc class5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | MUL | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)- 普通索引標(biāo)識(shí)：MUL ；
- 普通索引關(guān)鍵字：index；
- 如果表中不指定任何約束，MySQL會(huì)自動(dòng)為每一列生成索引并用ROW_ID進(jìn)行標(biāo)識(shí)。
- 索引名推薦使用：idx_表名_列名[列名]；
索引的查詢
語(yǔ)法
show keys from 表名；
示例：
他有點(diǎn)長(zhǎng)，后面我直接查看表結(jié)構(gòu)來(lái)查看
??唯一索引
1.定義
當(dāng)在一個(gè)表中定義一個(gè)唯一鍵unique，索引值必須是唯一的，不可以存在重復(fù)值。
2.使用
方式一：創(chuàng)建時(shí)創(chuàng)建唯一鍵
mysql> create table class1(
-> id bigint unique,
-> name varchar(20));
Query OK, 0 rows affected (0.13 sec)
mysql> desc class1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
方式二：創(chuàng)建時(shí)指定唯一列
mysql> create table class2(
-> id bigint,
-> name varchar(20),
-> unique (id,name));
Query OK, 0 rows affected (0.06 sec)
-- 方式一和方式二的區(qū)別：
-- 方式一只能給單列加主鍵，而方式二支持復(fù)合主鍵
mysql> desc class2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
方式三：修改為唯一索引
mysql> create table class(
-> id bigint,
-> name varchar(20));
Query OK, 0 rows affected (0.10 sec)
-- 修改方式1：
mysql> alter table class modify id bigint unique;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table class3(
-> id bigint,
-> name varchar(20));
Query OK, 0 rows affected (0.09 sec)
-- 修改方式2：
mysql> alter table class3 add unique(id,name);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc class3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | bigint(20) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)- 唯一索引標(biāo)識(shí)：UNI
??主鍵索引
1.定義
當(dāng)在一個(gè)表上定義一個(gè)主鍵primary key時(shí)，自動(dòng)創(chuàng)建索引，索引的值是主鍵列的值，主鍵的索引的列值不能為空且必須唯一，InnoDB使用它作為聚簇索引。
2.使用
-- 方式一：創(chuàng)建表時(shí)直接創(chuàng)建主鍵
mysql> create table class1(
-> id bigint primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
-- 方式二：創(chuàng)建表示單獨(dú)創(chuàng)建主鍵列
mysql> create table class3(
-> id bigint auto_increment,
-> name varchar(20),
-> primary key(id,name));
Query OK, 0 rows affected (0.11 sec)
-- 方式三：修改表中的列為主鍵值
mysql> create table class5(
-> id bigint,
-> name varchar(20));
Query OK, 0 rows affected (0.09 sec)
-- 修改方式1:
mysql> alter table class5 modify id bigint primary key auto_increment;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表結(jié)構(gòu)
mysql> desc class5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
mysql> create table class6(
-> id bigint,
-> name varchar(20));
Query OK, 0 rows affected (0.10 sec)
-- 修改方式1：
mysql> alter table class6 add primary key(id,name);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table class6 modify id bigint auto_increment;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表結(jié)構(gòu)
mysql> desc class6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)- 主鍵索引標(biāo)識(shí)：PRI
??三、索引的刪除
語(yǔ)法
alter table class6 drop 索引名;
示例：
-- 刪除主鍵索引 -- 刪除主鍵前，如果有自增屬性，要先刪除自增屬性，否則刪除主鍵失敗 mysql> alter table class6 drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- 刪除自增屬性：將自增屬性修改為非自增屬性 mysql> alter table class6 modify id bigint; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 刪除自增屬性 mysql> alter table class6 drop primary key; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看刪除后的表結(jié)構(gòu) mysql> desc class6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | bigint(20) | NO | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
??四、其他索引
全文索引：基于文本列（char、varchar、text）上創(chuàng)建，加快對(duì)些列中包含的數(shù)據(jù)查詢和DML操作。
聚簇索引：
- 如果表定義了主鍵，聚簇索引就是主鍵索引。
- 如果表沒(méi)有主鍵，InnoDB會(huì)選擇第一個(gè)非空的唯一索引（UNIQUE NOT NULL）作為聚簇索引的鍵。
- 如果沒(méi)有主鍵也沒(méi)有合適的唯一索引：InnoDB會(huì)自動(dòng)生成一個(gè)隱藏的6字節(jié)的ROW_ID字段記錄，ROW_ID單調(diào)遞增作為聚簇索引的鍵。
- 按主鍵查詢時(shí)，直接獲取數(shù)據(jù)，效率極高。
非聚簇索引：
- 索引與數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值。
- 查詢時(shí)需要通過(guò)非聚簇索引找到葉子節(jié)點(diǎn)的索引記錄，通過(guò)記錄中的主鍵值在通過(guò)聚簇索引獲取完整數(shù)據(jù)。
- 除聚簇索引外，都是聚簇索引。有唯一索引、普通索引、組合索引等。
索引覆蓋：當(dāng)一個(gè)select語(yǔ)句使用了普通索引且查詢列表中的列剛好是創(chuàng)建索引時(shí)的所有或部分列，這是就可以直接返回，而不用回表查詢。
到此這篇關(guān)于MySQL索引中的頁(yè)及索引的分類(lèi)及使用的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL 5.7之關(guān)于SQL_MODE的設(shè)置
這篇文章主要介紹了MySQL 5.7之關(guān)于SQL_MODE的設(shè)置方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
Mysql?索引?BTree?與?B+Tree?的區(qū)別(面試)
這篇文章主要介紹了Mysql索引BTree與B+Tree的區(qū)別，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-09-09
解決MYSQL連接端口被占引入文件路徑錯(cuò)誤的問(wèn)題
下面小編就為大家?guī)?lái)一篇解決MYSQL連接端口被占引入文件路徑錯(cuò)誤的問(wèn)題。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-08-08
在mysql安裝之后，如何找到自己的mysql數(shù)據(jù)庫(kù)的安裝位置，本文將介紹詳細(xì)的解決方法，需要的朋友可以參考下2012-12-12
淺談MySQL中float、double、decimal三個(gè)浮點(diǎn)類(lèi)型的區(qū)別與總結(jié)
這篇文章主要介紹了淺談MySQL中float、double、decimal三個(gè)浮點(diǎn)類(lèi)型的區(qū)別與總結(jié),文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
MySQL雙層游標(biāo)嵌套循環(huán)實(shí)現(xiàn)方法
要實(shí)現(xiàn)逐行獲取數(shù)據(jù),需要用到MySQL中的游標(biāo),一個(gè)游標(biāo)相當(dāng)于一個(gè)for循環(huán),這里需要用到2個(gè)游標(biāo),如何在MySQL中實(shí)現(xiàn)游標(biāo)雙層循環(huán)呢,下面小編給大家分享MySQL雙層游標(biāo)嵌套循環(huán)方法,感興趣的朋友跟隨小編一起看看吧2024-05-05
這篇文章主要介紹了mysql存儲(chǔ)過(guò)程,結(jié)合圖文與實(shí)例形式較為詳細(xì)的分析了mysql存儲(chǔ)過(guò)程基本概念、原理、用法及相關(guān)操作注意事項(xiàng),需要的朋友可以參考下2020-04-04
這篇文章主要介紹了導(dǎo)致MySQL做全表掃描的幾種情況，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
MySQL對(duì)varchar類(lèi)型數(shù)字進(jìn)行排序的實(shí)現(xiàn)方法
這篇文章主要介紹了MySQL對(duì)varchar類(lèi)型數(shù)字進(jìn)行排序的實(shí)現(xiàn)方法,文中用的是CAST方法,MySQL CAST()函數(shù)用于將值從一種數(shù)據(jù)類(lèi)型轉(zhuǎn)換為另一種特定數(shù)據(jù)類(lèi)型,并通過(guò)代碼示例講解的非常詳細(xì),需要的朋友可以參考下2024-04-04
mysql字符串拼接并設(shè)置null值的實(shí)例方法
在本文中小編給大家整理的是關(guān)于mysql 字符串拼接+設(shè)置null值的實(shí)例內(nèi)容以及具體方法，需要的朋友們可以學(xué)習(xí)下。2019-09-09