MySQL索引中的頁及索引的分類及使用
更新時間：2025年07月21日 10:22:05 作者：?喜歡做夢
文章介紹了InnoDB存儲引擎的頁結(jié)構(gòu)（16KB邏輯單位,含頁頭、頁尾、用戶記錄等）及索引分類,包括普通索引（MUL）、唯一索引（UNI）、主鍵索引（PRI）等,強(qiáng)調(diào)主鍵索引作為聚簇索引的特性,以及索引覆蓋優(yōu)化查詢效率的原理,感興趣的朋友跟隨小編一起看看吧
??一、什么是頁？
??1.定義
頁是InnoDB存儲引擎在磁盤上存儲數(shù)據(jù)的一種邏輯結(jié)構(gòu)，是管理數(shù)據(jù)和索引的基本單位，相當(dāng)于一個容器，存放表中的記錄、索引信息等。
- 大小：在.ibd文件中最重要的結(jié)構(gòu)體就是Page（頁），頁是內(nèi)存與磁盤交互的最小單元，默認(rèn)情況下，InnoDB的頁的大小為16KB，即使頁中沒有數(shù)據(jù)也會使用16KB的存儲空間，同時與索引的B+樹中的節(jié)點(diǎn)對應(yīng)。每次內(nèi)存與磁盤的交互至少讀取一頁，所以在磁盤中每個頁內(nèi)部的地址都是連續(xù)的。
- 查看頁的大?。?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)
- 頁的數(shù)據(jù)類型有多種，最常用的就是“索引頁”，也稱為數(shù)據(jù)頁，用于存儲表中的實(shí)際數(shù)據(jù)記錄。不論是那種類型的頁都包含頁頭和頁尾，頁的主題信息使用數(shù)據(jù)“行”來填充。
??2.頁的結(jié)構(gòu)
以數(shù)據(jù)頁的結(jié)構(gòu)為例：
- 文件頭（File Header）：包含頁的通用信息，如頁號、上一頁頁號和下一頁頁號，頁類型等，其中上一頁頁號與下一頁頁號，將各個也連接起來，組成一個雙向鏈表。
- 頁頭（Page Header）：記錄數(shù)據(jù)頁的一些狀態(tài)信息，比如已使用的空間、空閑空間的位置，插入記錄的順序，這些信息對于數(shù)據(jù)的插入、更新、刪除至關(guān)重要。
- Infimum和Supremum記錄：這是兩個虛擬記錄，Onfimum記錄代表了數(shù)據(jù)頁中最小的記錄，Supremum記錄代表了數(shù)據(jù)頁中最大的記錄，作為數(shù)據(jù)行鏈表的頭和尾。Infimum連接第一個數(shù)據(jù)行，最后一行真實(shí)數(shù)據(jù)連接Supremum，構(gòu)成一個單向鏈表。
- 用戶記錄（User Records）：實(shí)際存儲表中的數(shù)據(jù)記錄，按照一定的順序記錄，并且通過指針相互連接；
- 空閑空間（Free Space）:數(shù)據(jù)頁中尚未使用的空間，新插入的記錄從這里分配空間。
- 頁目錄（Page Directory）：數(shù)據(jù)頁（默認(rèn)16KB）包含成百上千條數(shù)據(jù)，查找數(shù)據(jù)時，如果要每個都掃描過去，那么效率極低。為了提高效率InnoDB采用二分查找的方法。
頁目錄通過分組+槽的方法進(jìn)行高效的查找：
快速定位記錄組：先通過二分查找找到對應(yīng)的組；
組內(nèi)線性查找：每個組內(nèi)的數(shù)據(jù)不超過8個，超過8個重新分裂成一個組。
- 頁尾（Page Traider）：包含一些校驗(yàn)信息，如頁的校驗(yàn)和頁中記錄的數(shù)量，保證數(shù)據(jù)的完整性和一致性。
??3.B+樹在索引中的應(yīng)用
查找4為例：
- 判斷B+樹根節(jié)點(diǎn)的索引記錄，3<4,那么訪問右子樹，到索引頁3；
- 在索引頁3中判斷id大小，找到與4相同的索引記錄，最后加載對應(yīng)的數(shù)據(jù)頁。
??二、索引的分類及使用
按功能特性分類：
??普通索引
1.定義
最基本的索引，沒有唯一性的限制，只是為了提高查詢效率而創(chuàng)建的索引?？赡転槎嗔袆?chuàng)建組合索引，稱為復(fù)合索引或者組合索引。創(chuàng)建之后都會生成一顆索引樹，創(chuàng)建多少索引生成多少棵索引樹。
2.使用
-- 方式一：創(chuàng)建表時創(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)識：MUL ；
- 普通索引關(guān)鍵字：index；
- 如果表中不指定任何約束，MySQL會自動為每一列生成索引并用ROW_ID進(jìn)行標(biāo)識。
- 索引名推薦使用：idx_表名_列名[列名]；
索引的查詢
語法
show keys from 表名；
示例：
他有點(diǎn)長，后面我直接查看表結(jié)構(gòu)來查看
??唯一索引
1.定義
當(dāng)在一個表中定義一個唯一鍵unique，索引值必須是唯一的，不可以存在重復(fù)值。
2.使用
方式一：創(chuàng)建時創(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)建時指定唯一列
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)識：UNI
??主鍵索引
1.定義
當(dāng)在一個表上定義一個主鍵primary key時，自動創(chuàng)建索引，索引的值是主鍵列的值，主鍵的索引的列值不能為空且必須唯一，InnoDB使用它作為聚簇索引。
2.使用
-- 方式一：創(chuàng)建表時直接創(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)識：PRI
??三、索引的刪除
語法
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)建，加快對些列中包含的數(shù)據(jù)查詢和DML操作。
聚簇索引：
- 如果表定義了主鍵，聚簇索引就是主鍵索引。
- 如果表沒有主鍵，InnoDB會選擇第一個非空的唯一索引（UNIQUE NOT NULL）作為聚簇索引的鍵。
- 如果沒有主鍵也沒有合適的唯一索引：InnoDB會自動生成一個隱藏的6字節(jié)的ROW_ID字段記錄，ROW_ID單調(diào)遞增作為聚簇索引的鍵。
- 按主鍵查詢時，直接獲取數(shù)據(jù)，效率極高。
非聚簇索引：
- 索引與數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲的是主鍵值。
- 查詢時需要通過非聚簇索引找到葉子節(jié)點(diǎn)的索引記錄，通過記錄中的主鍵值在通過聚簇索引獲取完整數(shù)據(jù)。
- 除聚簇索引外，都是聚簇索引。有唯一索引、普通索引、組合索引等。
索引覆蓋：當(dāng)一個select語句使用了普通索引且查詢列表中的列剛好是創(chuàng)建索引時的所有或部分列，這是就可以直接返回，而不用回表查詢。
到此這篇關(guān)于MySQL索引中的頁及索引的分類及使用的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行
在MySQL中,字符串分割是一個常見的操作,本文主要介紹了Mysql中substring_index函數(shù)實(shí)現(xiàn)字符分割一行變多行,具有一定的參考價值,感興趣的可以了解一下2023-12-12
MYSQL基礎(chǔ)之連接MYSQL、修改密碼、添加用戶
在這篇文章中我們就從連接MYSQL、修改密碼、增加用戶等方面來學(xué)習(xí)一些MYSQL的常用命令。2008-08-08
MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法
本文主要介紹了MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
這篇文章主要介紹了的相關(guān)資料,需要的朋友可以參考下2015-10-10
MySQL 使用 Performance Schema 定位和解決慢
本文介紹了如何使用MySQL的PerformanceSchema來定位和解決慢SQL查詢問題,通過啟用PerformanceSchema并分析相關(guān)的系統(tǒng)表,可以收集到詳細(xì)的性能數(shù)據(jù),從而識別出影響性能的SQL語句,優(yōu)化策略包括優(yōu)化查詢語句、調(diào)整數(shù)據(jù)庫配置等2025-02-02
本篇文章是對數(shù)據(jù)庫通用的連接方式進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
mysql是一種常用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng),它提供了多種函數(shù)來處理和操作數(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遞歸查詢的3種實(shí)現(xiàn)方式實(shí)例
在項(xiàng)目中會遇到同一個表中保存著父子關(guān)系的數(shù)據(jù),最常見的就是處理樹形結(jié)構(gòu)資源,下面這篇文章主要給大家介紹了關(guān)于MySQL遞歸查詢的3種實(shí)現(xiàn)方式,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
windows和linux安裝mysql后啟用日志管理功能的方法
在默認(rèn)情況下，mysql安裝后是沒有啟用日志管理功能的，這給維護(hù)帶來很多不便的地方,下面介紹windows和linux安裝mysql后啟用日志管理功能的方法2014-01-01