MySQL索引中的頁(yè)及索引的分類(lèi)及使用
更新時(shí)間：2025年07月21日 10:22:05 作者：?喜歡做夢(mèng)
文章介紹了InnoDB存儲(chǔ)引擎的頁(yè)結(jié)構(gòu)（16KB邏輯單位,含頁(yè)頭、頁(yè)尾、用戶(hù)記錄等）及索引分類(lèi),包括普通索引（MUL）、唯一索引（UNI）、主鍵索引（PRI）等,強(qiáng)調(diào)主鍵索引作為聚簇索引的特性,以及索引覆蓋優(yōu)化查詢(xún)效率的原理,感興趣的朋友跟隨小編一起看看吧
??一、什么是頁(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è)單向鏈表。
- 用戶(hù)記錄（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)線(xiàn)性查找：每個(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,那么訪(fǎng)問(wèn)右子樹(shù)，到索引頁(yè)3；
- 在索引頁(yè)3中判斷id大小，找到與4相同的索引記錄，最后加載對(duì)應(yīng)的數(shù)據(jù)頁(yè)。
??二、索引的分類(lèi)及使用
按功能特性分類(lèi)：
??普通索引
1.定義
最基本的索引，沒(méi)有唯一性的限制，只是為了提高查詢(xún)效率而創(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_表名_列名[列名]；
索引的查詢(xún)
語(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ù)查詢(xún)和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)遞增作為聚簇索引的鍵。
- 按主鍵查詢(xún)時(shí)，直接獲取數(shù)據(jù)，效率極高。
非聚簇索引：
- 索引與數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值。
- 查詢(xún)時(shí)需要通過(guò)非聚簇索引找到葉子節(jié)點(diǎn)的索引記錄，通過(guò)記錄中的主鍵值在通過(guò)聚簇索引獲取完整數(shù)據(jù)。
- 除聚簇索引外，都是聚簇索引。有唯一索引、普通索引、組合索引等。
索引覆蓋：當(dāng)一個(gè)select語(yǔ)句使用了普通索引且查詢(xún)列表中的列剛好是創(chuàng)建索引時(shí)的所有或部分列，這是就可以直接返回，而不用回表查詢(xún)。
到此這篇關(guān)于MySQL索引中的頁(yè)及索引的分類(lèi)及使用的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行
在MySQL中,字符串分割是一個(gè)常見(jiàn)的操作,本文主要介紹了Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行,具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12
MYSQL基礎(chǔ)之連接MYSQL、修改密碼、添加用戶(hù)
在這篇文章中我們就從連接MYSQL、修改密碼、增加用戶(hù)等方面來(lái)學(xué)習(xí)一些MYSQL的常用命令。2008-08-08
MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法
本文主要介紹了MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
MYSQL主從數(shù)據(jù)庫(kù)同步備份配置的方法
這篇文章主要介紹了的相關(guān)資料,需要的朋友可以參考下2015-10-10
MySQL 使用 Performance Schema 定位和解決慢
本文介紹了如何使用MySQL的PerformanceSchema來(lái)定位和解決慢SQL查詢(xún)問(wèn)題,通過(guò)啟用PerformanceSchema并分析相關(guān)的系統(tǒng)表,可以收集到詳細(xì)的性能數(shù)據(jù),從而識(shí)別出影響性能的SQL語(yǔ)句,優(yōu)化策略包括優(yōu)化查詢(xún)語(yǔ)句、調(diào)整數(shù)據(jù)庫(kù)配置等2025-02-02
本篇文章是對(duì)數(shù)據(jù)庫(kù)通用的連接方式進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
mysql是一種常用的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),它提供了多種函數(shù)來(lái)處理和操作數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于MySQL中如何將字符串替換的相關(guān)資料,需要的朋友可以參考下2024-07-07
windows下MySQL 5.7.3.0安裝配置圖解教程(安裝版)
這篇文章主要介紹了windows下MySQL 5.7.3.0安裝配置圖解教程(安裝版),需要的朋友可以參考下2016-04-04
MySQL遞歸查詢(xún)的3種實(shí)現(xiàn)方式實(shí)例
在項(xiàng)目中會(huì)遇到同一個(gè)表中保存著父子關(guān)系的數(shù)據(jù),最常見(jiàn)的就是處理樹(shù)形結(jié)構(gòu)資源,下面這篇文章主要給大家介紹了關(guān)于MySQL遞歸查詢(xún)的3種實(shí)現(xiàn)方式,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
windows和linux安裝mysql后啟用日志管理功能的方法
在默認(rèn)情況下，mysql安裝后是沒(méi)有啟用日志管理功能的，這給維護(hù)帶來(lái)很多不便的地方,下面介紹windows和linux安裝mysql后啟用日志管理功能的方法2014-01-01