MySQL索引中的頁及索引的分類及使用
更新時間：2025年07月21日 10:22:05 作者：?喜歡做夢
文章介紹了InnoDB存儲引擎的頁結(jié)構(gòu)（16KB邏輯單位,含頁頭、頁尾、用戶記錄等）及索引分類,包括普通索引（MUL）、唯一索引（UNI）、主鍵索引（PRI）等,強調(diào)主鍵索引作為聚簇索引的特性,以及索引覆蓋優(yōu)化查詢效率的原理,感興趣的朋友跟隨小編一起看看吧
??一、什么是頁？
??1.定義
頁是InnoDB存儲引擎在磁盤上存儲數(shù)據(jù)的一種邏輯結(jié)構(gòu)，是管理數(shù)據(jù)和索引的基本單位，相當于一個容器，存放表中的記錄、索引信息等。
- 大?。涸?strong>.ibd文件中最重要的結(jié)構(gòu)體就是Page（頁），頁是內(nèi)存與磁盤交互的最小單元，默認情況下，InnoDB的頁的大小為16KB，即使頁中沒有數(shù)據(jù)也會使用16KB的存儲空間，同時與索引的B+樹中的節(jié)點對應。每次內(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ù)據(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ù)據(jù)連接Supremum，構(gòu)成一個單向鏈表。
- 用戶記錄（User Records）：實際存儲表中的數(shù)據(jù)記錄，按照一定的順序記錄，并且通過指針相互連接；
- 空閑空間（Free Space）:數(shù)據(jù)頁中尚未使用的空間，新插入的記錄從這里分配空間。
- 頁目錄（Page Directory）：數(shù)據(jù)頁（默認16KB）包含成百上千條數(shù)據(jù)，查找數(shù)據(jù)時，如果要每個都掃描過去，那么效率極低。為了提高效率InnoDB采用二分查找的方法。
頁目錄通過分組+槽的方法進行高效的查找：
快速定位記錄組：先通過二分查找找到對應的組；
組內(nèi)線性查找：每個組內(nèi)的數(shù)據(jù)不超過8個，超過8個重新分裂成一個組。
- 頁尾（Page Traider）：包含一些校驗信息，如頁的校驗和頁中記錄的數(shù)量，保證數(shù)據(jù)的完整性和一致性。
??3.B+樹在索引中的應用
查找4為例：
- 判斷B+樹根節(jié)點的索引記錄，3<4,那么訪問右子樹，到索引頁3；
- 在索引頁3中判斷id大小，找到與4相同的索引記錄，最后加載對應的數(shù)據(jù)頁。
??二、索引的分類及使用
按功能特性分類：
??普通索引
1.定義
最基本的索引，沒有唯一性的限制，只是為了提高查詢效率而創(chuàng)建的索引?？赡転槎嗔袆?chuàng)建組合索引，稱為復合索引或者組合索引。創(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)- 普通索引標識：MUL ；
- 普通索引關(guān)鍵字：index；
- 如果表中不指定任何約束，MySQL會自動為每一列生成索引并用ROW_ID進行標識。
- 索引名推薦使用：idx_表名_列名[列名]；
索引的查詢
語法
show keys from 表名；
示例：
他有點長，后面我直接查看表結(jié)構(gòu)來查看
??唯一索引
1.定義
當在一個表中定義一個唯一鍵unique，索引值必須是唯一的，不可以存在重復值。
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ū)別：
-- 方式一只能給單列加主鍵，而方式二支持復合主鍵
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)- 唯一索引標識：UNI
??主鍵索引
1.定義
當在一個表上定義一個主鍵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)建表示單獨創(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)- 主鍵索引標識：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é)點存儲的是主鍵值。
- 查詢時需要通過非聚簇索引找到葉子節(jié)點的索引記錄，通過記錄中的主鍵值在通過聚簇索引獲取完整數(shù)據(jù)。
- 除聚簇索引外，都是聚簇索引。有唯一索引、普通索引、組合索引等。
索引覆蓋：當一個select語句使用了普通索引且查詢列表中的列剛好是創(chuàng)建索引時的所有或部分列，這是就可以直接返回，而不用回表查詢。
到此這篇關(guān)于MySQL索引中的頁及索引的分類及使用的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL 5.7之關(guān)于SQL_MODE的設置方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
Mysql?索引?BTree?與?B+Tree?的區(qū)別(面試)
這篇文章主要介紹了Mysql索引BTree與B+Tree的區(qū)別，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09
下面小編就為大家?guī)硪黄鉀QMYSQL連接端口被占引入文件路徑錯誤的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-08-08
在mysql安裝之后，如何找到自己的mysql數(shù)據(jù)庫的安裝位置，本文將介紹詳細的解決方法，需要的朋友可以參考下2012-12-12
淺談MySQL中float、double、decimal三個浮點類型的區(qū)別與總結(jié)
這篇文章主要介紹了淺談MySQL中float、double、decimal三個浮點類型的區(qū)別與總結(jié),文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
要實現(xiàn)逐行獲取數(shù)據(jù),需要用到MySQL中的游標,一個游標相當于一個for循環(huán),這里需要用到2個游標,如何在MySQL中實現(xiàn)游標雙層循環(huán)呢,下面小編給大家分享MySQL雙層游標嵌套循環(huán)方法,感興趣的朋友跟隨小編一起看看吧2024-05-05
這篇文章主要介紹了mysql存儲過程,結(jié)合圖文與實例形式較為詳細的分析了mysql存儲過程基本概念、原理、用法及相關(guān)操作注意事項,需要的朋友可以參考下2020-04-04
這篇文章主要介紹了導致MySQL做全表掃描的幾種情況，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下2021-03-03
MySQL對varchar類型數(shù)字進行排序的實現(xiàn)方法
這篇文章主要介紹了MySQL對varchar類型數(shù)字進行排序的實現(xiàn)方法,文中用的是CAST方法,MySQL CAST()函數(shù)用于將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種特定數(shù)據(jù)類型,并通過代碼示例講解的非常詳細,需要的朋友可以參考下2024-04-04
在本文中小編給大家整理的是關(guān)于mysql 字符串拼接+設置null值的實例內(nèi)容以及具體方法，需要的朋友們可以學習下。2019-09-09