1.索引简介MySQL的索引是⼀种数据结构它可以帮助数据库高效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录使得对表的查询可以通过对索引的搜索来加快速度。MySQL 索引类似于书籍的目录通过指向数据行的位置可以快速定位和访问表中的数据比如 汉语字典的目录索引页我们可以按笔画、偏旁部首、拼音等排序的目录索引快速查找到需要的字。使用索引的目的只有⼀个就是提升数据检索的效率(MySQL实现的两个关键目标安全和效率)在应⽤程序的运行过程中查询操作的频率远远高于增删改的频率。那么索引应该选择使用哪种数据结构呢HASH时间复杂度是O(1)查询的速度非常快但是MySQL并没有选择HASH做为索引的默认数据结构主要原因是HASH不支持范围查找。二叉搜索树二叉搜索树的中序遍历是⼀个有序数组支持范围查找但有几个问题导致它不适合用作索引的数据结构最坏情况下时间复杂度是O(n)二叉搜索树可能退化成一棵单边树。节点个数过多无法保证树高数据库中的数据是在磁盘上保存的在检索数据时每次访问某个节点的子节点时都会发生⼀次磁盘IO而磁盘IO是制约数据库的主要因素。N叉树为了解决树高的问题可以使用N叉树——每个节点可以有超过两个的子节点。时间复杂度是O(logn)也就意味着在相同数据量的情况下可以有效控制树高减少IO的次数找到目标节点从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好。2.B树B树是⼀种经常用于数据库和文件系统等场合的平衡查找树MySQL索引采用的数据结构。以4阶 B树为例如下图所示(阶也叫度每个节点最多有多少个子节点一般子节点个数小于度的值)B 树B Tree它是一种有序的多路搜索树满足 “左边节点值 右边节点值” 的有序性。是一种特殊的有序 N 叉树。B 树 的核心设计规则节点内有序每个节点里的关键字都按从小到大排列比如根节点 0080, 0140 是递增的。子树范围有序根节点 0080 指向的子树所有值都小于 00800080 和 0140 之间的子树所有值都在 (0080, 0140) 之间0140 指向的子树所有值都 大于 0140。叶子节点有序且链表相连(双向链表)最底层的叶子节点 0010,0020,0030...0200 是完整的有序链表保证了全局有序。只有叶子节点会被连成有序链表这是为了遍历和范围查询。非叶子节点中间层、根节点 不需要兄弟指针它们只负责索引导航数据都存在叶子节点里。2.1 B 树的特点能够保持数据稳定有序插⼊与修改有较稳定的时间复杂度。非叶⼦节点仅具有索引作⽤不存储数据所有叶子节点保真实数据。所有叶子节点构成⼀个有序链表可以按照key排序的次序依次遍历全部数据。2.2 B 树与 B 树的对比叶⼦节点中的数据是连续的且叶子节点相互链接可通过叶子节点找到它相邻的兄弟节点便于区间查找和搜索 (MySQL在组织叶子节点时使用的是双向链表)。非叶子节点的值都包含在叶子节点中MySQL非叶子节点只保存了对子节点的引用没有保存真实的数据所有真实数据都保存在叶子节点中。对于B树而言在相同树高的情况下查找任⼀元素的时间复杂度(O(logn))都⼀样性能均衡。———————————————————————————————————————————到这里我们已经了解了MySQL索引底层所使用的数据结构——B 树。那么索引在整个数据检索的过程中是如何工作的就要从MySQL的存储结构说起。MySQL 的数据最终落地在磁盘文件中不同存储引擎InnoDB/MyISAM结构差异很大其中InnoDB 是默认且最核心的引擎而InnoDB中的核心文件结构之一是表空间文件(.ibd)它的作用是存储表数据 索引一张数据表对应一个 .ibd 文件默认所有数据都在 B 树里。.idb 是表空间文件的后缀。而在 .ibd 文件中最重要的结构体就是Page(页)。3.MySQL中的页3.1为什么使用页在 .ibd 文件中最重要的结构体就是Page(页)。页是内存与磁盘交互的最小单元默认大小为16KB。可以理解为 B 树的节点就是一个默认大小16KB的页。B 树的叶子节点页存储完整的行数据B 树的非叶子节点页只存储索引键 指向子页的指针导航用每次内存与磁盘的交互⾄少读取⼀页所以在磁盘中每个页内部的地址都是连续的之所以这样做是因为在使⽤数据的过程中根据局部性原理将来要使用的数据大概率与当前访问的 数据在空间上是临近的所以⼀次从磁盘中读取⼀页的数据放⼊内存中当下次查询的数据还在这个页中时就可以从内存中直接读取从而减少磁盘I/O提高性能。每⼀个页中即使没有数据也会使用 16KB 的存储空间。查看页的大小可以通过系统变量innodb_page_size查看5. 在MySQL中有多种不同类型的页最常用的就是用来存储数据和索引的索引页也叫做数据 页。但不论哪种类型的页都会包含页头(File Header)和页尾(File Trailer)页的主体信息使用数 据行进行填充数据页的基本结构如下图所示前面说过一张表对应一个 .ibd文件而一个 .idb文件中包含许多页也就是说一个 .ibd文件包含许多B树。即一个 .ibd 文件 一大堆 16KB 页 一大堆 B 树节点.ibd 里有大量数据页的根本原因是单页16KB存储容量有限而一张表的实际数据量几乎都会超过这个上限因此一页无法容纳整张表的所有数据数据越多数据页就越多。还有就是B 树的 “分裂机制” 强制生成新页还有就是小页结构能最大化磁盘 I/O 和内存缓存的效率。了解完页之后我们继续回到数据页(叶子节点页)的基本结构了解页头页尾页主体页目录以及数据页头。3.2页文件头和页文件尾页文件头和页文件尾中包含的信息如下图所示这里我们只关注上⼀页页号和下⼀页页号通过这两个属性可以把页与页之间链接起来形成⼀个双向链表。通过页号和页大小可以计算出下一页和上一页在磁盘上的偏移量3.3页主体页主体部分是保存真实数据的主要区域每当创建⼀个新页都会自动分配两个行一个是页内最 小行 Infimun另⼀个是页内最大行 Supremun这两个行并不存储任何真实信息而是做为数据行链表的头和尾第⼀个数据行有⼀个记录下⼀行的地址偏移量的区域next_record将页内所有数据行组成了⼀个单向链表此时新页的结构如下所示当向⼀个新页插⼊数据时将 Infimun 连接第⼀个数据行最后⼀行真实数据行连接 Supremun 这样数据行就构建成了⼀个单向链表更多的行数据插入后会按照主键从小到大的顺序进行链接如下图所示事实上最小行和最大行在未插入数据行时是挨在一起的当有数据插入时都会插入在它们的中间即页一初始化就先把这两条写死让它们挨在一起形成一个空链表框架当有数据行时就往中间插入。注意最小行Infimum和最大行Supremum在物理存储位置上依然是紧挨着页头的Page Header 之后(如下图是最大行和最小行真正的位置)只是在逻辑链表关系上它们中间被插入了用户记录(如上面所画的图)。总结数据页初始化时最小行与最大行会物理上固定相邻形成空链表。无数据时二者直接相连插入用户记录时记录会被插入到二者的逻辑链表之间但最小行和最大行的物理位置始终保持相邻不变。3.4页目录当按主键或索引查找某条数据时最直接简单的方法就是从头行 infimun 开始沿着链表顺序逐个比对查找但⼀个页有16KB通常会存在数百行数据每次都要遍历数百行无法满足高效查 询为了提高查询效率InnoDB采用⼆分查找来解决查询效率问题具体实现方式是在每⼀个页中加入⼀个叫做页目录 Page Directory的结构将页内包括头行、尾行在内的所有行进行分组约定头行单独为⼀组其他每个组最多8条数据同时把每个组最后⼀行在页中的地址按主键从小到大的顺序记录在页目录中页目录中的每⼀个位置称为⼀ 个槽每个槽都对应了⼀个分组⼀旦分组中的数据行超过分组的上限8个时就会分裂出⼀个新的分组后续在查询某行时就可以通过⼆分查找先找到对应的槽然后在槽内最多8个数据行中进行遍 历即可从而大幅提高了查询效率这时⼀个页的核心结构就完成了例如要查找主键为6的行先比对槽中记录的主键值定位到最后⼀个槽2再从最后⼀个槽中的第 ⼀条记录遍历第⼆条记录就是我们要查询的目标行。3.5数据页头数据页头记录了当前页保存数据相关的信息如下图所示——————————————————————————————————————————4.B树在MySQL索引中的应用非叶子节点保存索引数据(索引页保存的是主键值和子节点的引用/指针)叶子节点保存真实数据(数据页保存的是具体数据页与页之间通过页号建立关联关系叶子节点最终形成一个双向循环链表)如下图所示以查找id为5的记录完整的检索过程如下首先判断B树的根节点中的索引记录此时 5 7 应访问左孩子节点找到索引页2所有关于页的访问都是在内存中进行的在索引页2中判断id的大小找到与5相等的记录命中加载对应的数据页以上的IO过程加载索引页1-- 加载索引页2-- 加载数据页34.1计算三层树高的B树可以存放多少条记录假设⼀条用户数据大小为1KB在忽略数据页中数据页自身属性空间占用的情况下⼀页可以存16条数据。索引页⼀条数据的大小为主键用BIGINT类型占 8 Byte下⼀页地址 6 Byte⼀共是14 Byte⼀个索引页可以保存16*1024/14 1170条索引记录。如果只有三层树高的情况综合只保存索引的根节点和⼆级节点的索引页以及保存真实数据的数据 页那么⼀共可以保存1170*1170*16 21,902,400条记录也就是说在两千多万条数据的表中可以通过三次IO就完成数据的检索。5.索引分类5.1主键索引当在⼀个表上定义⼀个主键 PRIMARY KEY时InnoDB使用它作为聚集索引(聚簇索引)。推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且非空的列或列集可以使用主键则添加⼀个⾃ 增列。一张表只能有一个聚集 / 聚簇索引也就是一张表只能有一个主键列。5.2普通索引最基本的索引类型没有唯⼀性的限制。可能为多列创建组合索引称为复合索引或组全索引。5.3唯一索引当在⼀个表上定义⼀个唯⼀键 UNQUE时自动创建唯⼀索引。与普通索引类似但区别在于唯⼀索引的列不允许有重复值。5.4全文索引基于文本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和DML操作。用于全文搜索仅MyISAM和InnoDB引擎支持。5.5聚集索引与主键索引是同义词。如果没有为表定义 PRIMARY KEY, InnoDB使用第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索引。如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引InnoDB会为新插入的行生成⼀个行号并 用6字节的 ROW_ID 字段(数据行中的隐藏列/隐式字段)记录ROW_ID 单调递增并使用 ROW_ID 做为索引。聚集索引和主键索引的区别先抛出核心结论主键索引 ≠ 聚集索引但在 InnoDB 里主键索引默认就是聚集索引是 “默认绑定”而非 “天生等同”。两个概念的本质InnoDB 引擎规定如果表定义了主键那么这个主键索引会被自动设为聚集索引。在日常情况下主键索引几乎就是聚集索引只有一种情况能看出 主键索引≠聚集索引区别表没有显式主键时InnoDB 会选其他字段做聚集索引但这个字段不是主键。示例create table user ( id int auto_increment, username varchar(50) not null unique, age int );一个形象的例子总结本质区别主键索引是 “字段维度” 的索引建在主键上聚集索引是 “存储维度” 的索引数据与索引物理绑定默认关联InnoDB 中主键索引默认就是聚集索引这是最常见的情况特殊情况无显式主键时InnoDB 会选唯一非空索引 / 隐式 row_id 作为聚集索引此时聚集索引≠主键索引。5.6非聚集索引聚集索引以外的索引称为⾮聚集索引或⼆级索引也就是说主键索引 聚集索引普通索引唯一索引等都是非聚集索引。聚集索引主键专属存整行数据即叶子节点存储完整数据非聚集索引其他所有索引只存索引 主键即叶子节点不存完整数据查询时需要回表。回表查询当你用「非聚集索引」普通 / 唯一 / 复合索引查询数据时因为非聚集索引的叶子节点只存「索引值 主键值」没有完整数据所以需要拿着主键值再去「聚集索引」里查完整数据—— 这个 “二次查询” 的过程就是回表。简单说回表 「非聚集索引查主键」 「聚集索引查完整数据」。它们之间的引用关系非聚集索引树的叶子节点 → 存主键 → 指向聚集索引树。我们可以再进行一个更加完善的总结一张表对应一个 .ibd 文件 → .ibd 文件由大量 16KB 的页数据页、索引页等组成 → 这些页按照所属的索引被划分为多个组每组页分别属于一棵逻辑上独立的 B 树1 棵聚集索引树 N 棵非聚集索引树即每棵树独占一部分页页与页之间不混用 → 最终一个 .ibd 文件 众多 16KB 页的集合是存储了 1N 棵独立 B 树的物理容器。回到非聚集索引我们知道在一棵非聚集索引树中无法查询到完整的数据需要回表查询到聚集索引树中查询数据信息但是如果使用索引覆盖那么就可以直接在非聚集索引树中获取数据而无需回表。5.7索引覆盖索引覆盖 是指查询语句中需要获取的所有字段SELECT 列 WHERE 条件列都能从某一棵非聚集索引的 B 树中直接获取无需回表查询聚集索引。简单说索引 “覆盖” 了查询的所有需求不用再去查数据本体聚集索引。核心判定条件查询的所有字段如 id、age、name都包含在目标非聚集索引中查询条件字段是该索引的列或复合索引的最左前缀全程仅操作非聚集索引的 B 树不依赖聚集索引。示例用户表主键 id非聚集索引 idx_age(age)CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, -- 聚集索引叶子节点存 idnameage...完整数据 name VARCHAR(50), age INT, INDEX idx_age (age) -- 非聚集索引叶子节点只存 age id );-- 普通查询回表2 次 IO SELECT id, name FROM user WHERE age 20; -- 索引覆盖查询不回表1 次 IO SELECT id, age FROM user WHERE age 20;记住回表查询的核心流程是先遍历非聚集索引树拿主键 → 再遍历聚集索引树补全数据。那么普通索引的回表流程而索引覆盖的例子分析虽然聚集索引也有主键值但是你要查的是 “age20”不是 “id5”—— 聚集索引里没有按 age 排序的结构无法快速找到 age20 的行必须靠非聚集索引先找到对应的 id非聚集索引如 idx_age是按条件字段age排序的能快速找到目标数据对应的聚合索引的主键从而找到 name。回表必要流程查age20 → 非聚集索引按age排序→ 拿ID5 → 聚集索引按ID排序→ 拿name到这里我们对索引的了解全部结束接下来我们要来使用索引。6.使用索引6.1自动创建当我们为⼀张表加主键约束(Primary key)外键约束(Foreign Key)唯⼀约束(Unique)时 MySQL会为对应的的列自动创建⼀个索引。如果表不指定任何约束时MySQL会自动为每⼀列生成⼀个索引并用 ROW_ID 进行标识。6.2手动创建6.2.1主键索引方式一创建表时创建主键create table t_pk1 ( id bigint primary key auto_increment, name varchar(20) );如上述的例子我们在创建表时就加了一个主键约束即此时这个表的主键列对应有一个主键索引那么可以通过show index from 表名;这条SQL语句查看指定表的索引信息Table索引所属的表名。Non_unique是否唯一索引0 表示唯一1 表示可以重复。Key_name索引名称主键索引默认为 PRIMARY。Seq_in_index索引中列的顺序/序号从 1 开始。复合索引中用于标识列的位置(复合主键列谁先谁后)。Column_name索引的列名。Collation列的排序方式A 表示升序NULL 表示不排序。Cardinality索引中唯一值的估计数量用于优化器判断索引效率。Sub_part如果只索引列的前缀部分显示前缀长度如对 varchar(255) 只索引前 10 个字符则显示 10。Packed索引是否被压缩NULL 表示未压缩。Null列是否允许 NULL 值YES 表示允许。Index_type索引类型BTREE、HASH、FULLTEXT 等InnoDB 通常为 BTREE。Comment索引的注释信息。Index_comment创建索引时指定的注释。方式二创建表时单独指定主键列create table t_pk2 ( id bigint auto_increment, name varchar(20), primary key(id) );方式三修改表中的列为主键索引create table t_pk3 ( id bigint, name varchar(20), ); alter table t_pk3 add primary key(id); -- 为t_pk3的表添加主键并指定列 alter table t_pk3 modify id bigint auto_increment; -- 把t_pk3表中id列修改为自增列6.2.2唯一索引方式一创建表时创建唯一键create table t_uq1 ( id bigint primary key auto_increment, name varchar(20) unique );查看表索引信息方式二创建表时单独指定唯一列create table t_uq2 ( id bigint primary key auto_increment, name varchar(20), unique (name) );方式三修改表中的列为唯一索引create table t_uq3 ( id bigint primary key auto_increment, name varchar(20), ); alter table t_uq3 add unique (name);6.2.3普通索引普通索引创建的时机创建表的时候明确知道某些列频繁查询就创建好(当表中数据过少时全表扫描可能效率比索引高)。随着业务不断发展在版本迭代过程中添加索引。创建普通索引语法index是创建索引关键字。index (索引列列名);方式一创建表时指定索引列create table t_index1 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), index (sno) );查看索引信息方式二修改表中的列为普通索引create table t_index2 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), ); alter table t_index2 add index (sno);方式三单独创建索引并指定索引名(推荐使用)语法create index 索引名 on 表名(列名[,列名]);create table t_index3 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), ); -- 为 t_index3 表创建普通索引索引列为sno create index sno_index3 on t_index3(sno);查看索引信息可以看到当我们单独去创建一个索引时可以指定索引名而不是默认是将作为索引的列的列名作为索引名6.2.4复合索引创建语法与创建普通索引相同只不过指定多个列(索引中包含多个列)列与列之间⽤逗号隔开。方式一创建表时指定索引列create table t_index4 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint, index (sno,class_id) );查看索引信息方式二修改表中的列为复合索引create table t_index5 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint ); alter table t_index5 add index (sno,class_id);方式三单独创建索引并指定索引名(推荐使用)create table t_index6 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint ); -- 为t_index6表创建复合索引并指定索引列 create index sno_class_id on t_index6(sno,class_id);查看索引信息6.3查看索引方式一show keys from 表名;示例方式二show index from 表名;示例方式三desc 表名; —— 查看的是简要信息示例6.4删除索引6.4.1删除主键索引语法alter table 表名 drop primary key;示例删除 t_index6 表中的主键此时发现此条语句运行失败原因是该表中的主键列设置为了自增列需要先删除自增属性然后再删除主键查看索引信息主键索引已经被删除只剩下一个复合索引6.4.2删除其他索引这里的其他索引指的是除了主键索引之外的索引例如唯一索引复合索引普通索引等。语法alter table 表名 drop index 索引名;示例删除 t_index6 表中名为 sno_class_id 的索引查看索引信息此时的表中已经没有索引是空表——————————————————————————————————7.如何查看自己写的SQL走没走索引可以看执行计划explain示例有一个stu表先为这个表创建一个复合索引查看索引信息查看表中的数据1.查看执行计划以下是不加条件即查询所有2.使用主键查询3.子查询中使用索引4.唯一索引查询5.复合/普通索引查询以上的查询语句都是需要回表查询的(主键索引除外)除了在子查询的时候使用了索引覆盖。再举一个索引覆盖的例子用复合索引 sno_class_id 举例Extra——执行情况的说明和描述。Using index表示使用索引如果只有Using index 说明没有查询到数据表只用到了索引表就完成了这个查询这个叫做索引覆盖。Using where表示条件查询如果不读取表的所有数据或不是仅仅通过索引就可以获取所有需要的数据则会出现Using where。Using where; Using index表示查询使用了覆盖索引但索引返回的行还需要经过 WHERE 条件进一步过滤虽然列都在索引中但索引本身可能无法直接排除所有行仍需在索引层面筛选总结只要查询条件中使用了索引包含的索引列就会走索引和顺序无关。8.创建索引的注意事项索引应该创建在高频查询的列上索引需要占用额外的存储空间对表进行插入、更新和删除操作时同时也会修索引可能会影响性能创建过多或不合理的索引会导致性能下降需要谨慎选择和规划索引
【MySQL】索引
发布时间：2026/7/4 15:20:28
1.索引简介MySQL的索引是⼀种数据结构它可以帮助数据库高效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录使得对表的查询可以通过对索引的搜索来加快速度。MySQL 索引类似于书籍的目录通过指向数据行的位置可以快速定位和访问表中的数据比如 汉语字典的目录索引页我们可以按笔画、偏旁部首、拼音等排序的目录索引快速查找到需要的字。使用索引的目的只有⼀个就是提升数据检索的效率(MySQL实现的两个关键目标安全和效率)在应⽤程序的运行过程中查询操作的频率远远高于增删改的频率。那么索引应该选择使用哪种数据结构呢HASH时间复杂度是O(1)查询的速度非常快但是MySQL并没有选择HASH做为索引的默认数据结构主要原因是HASH不支持范围查找。二叉搜索树二叉搜索树的中序遍历是⼀个有序数组支持范围查找但有几个问题导致它不适合用作索引的数据结构最坏情况下时间复杂度是O(n)二叉搜索树可能退化成一棵单边树。节点个数过多无法保证树高数据库中的数据是在磁盘上保存的在检索数据时每次访问某个节点的子节点时都会发生⼀次磁盘IO而磁盘IO是制约数据库的主要因素。N叉树为了解决树高的问题可以使用N叉树——每个节点可以有超过两个的子节点。时间复杂度是O(logn)也就意味着在相同数据量的情况下可以有效控制树高减少IO的次数找到目标节点从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好。2.B树B树是⼀种经常用于数据库和文件系统等场合的平衡查找树MySQL索引采用的数据结构。以4阶 B树为例如下图所示(阶也叫度每个节点最多有多少个子节点一般子节点个数小于度的值)B 树B Tree它是一种有序的多路搜索树满足 “左边节点值 右边节点值” 的有序性。是一种特殊的有序 N 叉树。B 树 的核心设计规则节点内有序每个节点里的关键字都按从小到大排列比如根节点 0080, 0140 是递增的。子树范围有序根节点 0080 指向的子树所有值都小于 00800080 和 0140 之间的子树所有值都在 (0080, 0140) 之间0140 指向的子树所有值都 大于 0140。叶子节点有序且链表相连(双向链表)最底层的叶子节点 0010,0020,0030...0200 是完整的有序链表保证了全局有序。只有叶子节点会被连成有序链表这是为了遍历和范围查询。非叶子节点中间层、根节点 不需要兄弟指针它们只负责索引导航数据都存在叶子节点里。2.1 B 树的特点能够保持数据稳定有序插⼊与修改有较稳定的时间复杂度。非叶⼦节点仅具有索引作⽤不存储数据所有叶子节点保真实数据。所有叶子节点构成⼀个有序链表可以按照key排序的次序依次遍历全部数据。2.2 B 树与 B 树的对比叶⼦节点中的数据是连续的且叶子节点相互链接可通过叶子节点找到它相邻的兄弟节点便于区间查找和搜索 (MySQL在组织叶子节点时使用的是双向链表)。非叶子节点的值都包含在叶子节点中MySQL非叶子节点只保存了对子节点的引用没有保存真实的数据所有真实数据都保存在叶子节点中。对于B树而言在相同树高的情况下查找任⼀元素的时间复杂度(O(logn))都⼀样性能均衡。———————————————————————————————————————————到这里我们已经了解了MySQL索引底层所使用的数据结构——B 树。那么索引在整个数据检索的过程中是如何工作的就要从MySQL的存储结构说起。MySQL 的数据最终落地在磁盘文件中不同存储引擎InnoDB/MyISAM结构差异很大其中InnoDB 是默认且最核心的引擎而InnoDB中的核心文件结构之一是表空间文件(.ibd)它的作用是存储表数据 索引一张数据表对应一个 .ibd 文件默认所有数据都在 B 树里。.idb 是表空间文件的后缀。而在 .ibd 文件中最重要的结构体就是Page(页)。3.MySQL中的页3.1为什么使用页在 .ibd 文件中最重要的结构体就是Page(页)。页是内存与磁盘交互的最小单元默认大小为16KB。可以理解为 B 树的节点就是一个默认大小16KB的页。B 树的叶子节点页存储完整的行数据B 树的非叶子节点页只存储索引键 指向子页的指针导航用每次内存与磁盘的交互⾄少读取⼀页所以在磁盘中每个页内部的地址都是连续的之所以这样做是因为在使⽤数据的过程中根据局部性原理将来要使用的数据大概率与当前访问的 数据在空间上是临近的所以⼀次从磁盘中读取⼀页的数据放⼊内存中当下次查询的数据还在这个页中时就可以从内存中直接读取从而减少磁盘I/O提高性能。每⼀个页中即使没有数据也会使用 16KB 的存储空间。查看页的大小可以通过系统变量innodb_page_size查看5. 在MySQL中有多种不同类型的页最常用的就是用来存储数据和索引的索引页也叫做数据 页。但不论哪种类型的页都会包含页头(File Header)和页尾(File Trailer)页的主体信息使用数 据行进行填充数据页的基本结构如下图所示前面说过一张表对应一个 .ibd文件而一个 .idb文件中包含许多页也就是说一个 .ibd文件包含许多B树。即一个 .ibd 文件 一大堆 16KB 页 一大堆 B 树节点.ibd 里有大量数据页的根本原因是单页16KB存储容量有限而一张表的实际数据量几乎都会超过这个上限因此一页无法容纳整张表的所有数据数据越多数据页就越多。还有就是B 树的 “分裂机制” 强制生成新页还有就是小页结构能最大化磁盘 I/O 和内存缓存的效率。了解完页之后我们继续回到数据页(叶子节点页)的基本结构了解页头页尾页主体页目录以及数据页头。3.2页文件头和页文件尾页文件头和页文件尾中包含的信息如下图所示这里我们只关注上⼀页页号和下⼀页页号通过这两个属性可以把页与页之间链接起来形成⼀个双向链表。通过页号和页大小可以计算出下一页和上一页在磁盘上的偏移量3.3页主体页主体部分是保存真实数据的主要区域每当创建⼀个新页都会自动分配两个行一个是页内最 小行 Infimun另⼀个是页内最大行 Supremun这两个行并不存储任何真实信息而是做为数据行链表的头和尾第⼀个数据行有⼀个记录下⼀行的地址偏移量的区域next_record将页内所有数据行组成了⼀个单向链表此时新页的结构如下所示当向⼀个新页插⼊数据时将 Infimun 连接第⼀个数据行最后⼀行真实数据行连接 Supremun 这样数据行就构建成了⼀个单向链表更多的行数据插入后会按照主键从小到大的顺序进行链接如下图所示事实上最小行和最大行在未插入数据行时是挨在一起的当有数据插入时都会插入在它们的中间即页一初始化就先把这两条写死让它们挨在一起形成一个空链表框架当有数据行时就往中间插入。注意最小行Infimum和最大行Supremum在物理存储位置上依然是紧挨着页头的Page Header 之后(如下图是最大行和最小行真正的位置)只是在逻辑链表关系上它们中间被插入了用户记录(如上面所画的图)。总结数据页初始化时最小行与最大行会物理上固定相邻形成空链表。无数据时二者直接相连插入用户记录时记录会被插入到二者的逻辑链表之间但最小行和最大行的物理位置始终保持相邻不变。3.4页目录当按主键或索引查找某条数据时最直接简单的方法就是从头行 infimun 开始沿着链表顺序逐个比对查找但⼀个页有16KB通常会存在数百行数据每次都要遍历数百行无法满足高效查 询为了提高查询效率InnoDB采用⼆分查找来解决查询效率问题具体实现方式是在每⼀个页中加入⼀个叫做页目录 Page Directory的结构将页内包括头行、尾行在内的所有行进行分组约定头行单独为⼀组其他每个组最多8条数据同时把每个组最后⼀行在页中的地址按主键从小到大的顺序记录在页目录中页目录中的每⼀个位置称为⼀ 个槽每个槽都对应了⼀个分组⼀旦分组中的数据行超过分组的上限8个时就会分裂出⼀个新的分组后续在查询某行时就可以通过⼆分查找先找到对应的槽然后在槽内最多8个数据行中进行遍 历即可从而大幅提高了查询效率这时⼀个页的核心结构就完成了例如要查找主键为6的行先比对槽中记录的主键值定位到最后⼀个槽2再从最后⼀个槽中的第 ⼀条记录遍历第⼆条记录就是我们要查询的目标行。3.5数据页头数据页头记录了当前页保存数据相关的信息如下图所示——————————————————————————————————————————4.B树在MySQL索引中的应用非叶子节点保存索引数据(索引页保存的是主键值和子节点的引用/指针)叶子节点保存真实数据(数据页保存的是具体数据页与页之间通过页号建立关联关系叶子节点最终形成一个双向循环链表)如下图所示以查找id为5的记录完整的检索过程如下首先判断B树的根节点中的索引记录此时 5 7 应访问左孩子节点找到索引页2所有关于页的访问都是在内存中进行的在索引页2中判断id的大小找到与5相等的记录命中加载对应的数据页以上的IO过程加载索引页1-- 加载索引页2-- 加载数据页34.1计算三层树高的B树可以存放多少条记录假设⼀条用户数据大小为1KB在忽略数据页中数据页自身属性空间占用的情况下⼀页可以存16条数据。索引页⼀条数据的大小为主键用BIGINT类型占 8 Byte下⼀页地址 6 Byte⼀共是14 Byte⼀个索引页可以保存16*1024/14 1170条索引记录。如果只有三层树高的情况综合只保存索引的根节点和⼆级节点的索引页以及保存真实数据的数据 页那么⼀共可以保存1170*1170*16 21,902,400条记录也就是说在两千多万条数据的表中可以通过三次IO就完成数据的检索。5.索引分类5.1主键索引当在⼀个表上定义⼀个主键 PRIMARY KEY时InnoDB使用它作为聚集索引(聚簇索引)。推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且非空的列或列集可以使用主键则添加⼀个⾃ 增列。一张表只能有一个聚集 / 聚簇索引也就是一张表只能有一个主键列。5.2普通索引最基本的索引类型没有唯⼀性的限制。可能为多列创建组合索引称为复合索引或组全索引。5.3唯一索引当在⼀个表上定义⼀个唯⼀键 UNQUE时自动创建唯⼀索引。与普通索引类似但区别在于唯⼀索引的列不允许有重复值。5.4全文索引基于文本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和DML操作。用于全文搜索仅MyISAM和InnoDB引擎支持。5.5聚集索引与主键索引是同义词。如果没有为表定义 PRIMARY KEY, InnoDB使用第⼀个 UNIQUE 和 NOT NULL 的列作为聚集索引。如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引InnoDB会为新插入的行生成⼀个行号并 用6字节的 ROW_ID 字段(数据行中的隐藏列/隐式字段)记录ROW_ID 单调递增并使用 ROW_ID 做为索引。聚集索引和主键索引的区别先抛出核心结论主键索引 ≠ 聚集索引但在 InnoDB 里主键索引默认就是聚集索引是 “默认绑定”而非 “天生等同”。两个概念的本质InnoDB 引擎规定如果表定义了主键那么这个主键索引会被自动设为聚集索引。在日常情况下主键索引几乎就是聚集索引只有一种情况能看出 主键索引≠聚集索引区别表没有显式主键时InnoDB 会选其他字段做聚集索引但这个字段不是主键。示例create table user ( id int auto_increment, username varchar(50) not null unique, age int );一个形象的例子总结本质区别主键索引是 “字段维度” 的索引建在主键上聚集索引是 “存储维度” 的索引数据与索引物理绑定默认关联InnoDB 中主键索引默认就是聚集索引这是最常见的情况特殊情况无显式主键时InnoDB 会选唯一非空索引 / 隐式 row_id 作为聚集索引此时聚集索引≠主键索引。5.6非聚集索引聚集索引以外的索引称为⾮聚集索引或⼆级索引也就是说主键索引 聚集索引普通索引唯一索引等都是非聚集索引。聚集索引主键专属存整行数据即叶子节点存储完整数据非聚集索引其他所有索引只存索引 主键即叶子节点不存完整数据查询时需要回表。回表查询当你用「非聚集索引」普通 / 唯一 / 复合索引查询数据时因为非聚集索引的叶子节点只存「索引值 主键值」没有完整数据所以需要拿着主键值再去「聚集索引」里查完整数据—— 这个 “二次查询” 的过程就是回表。简单说回表 「非聚集索引查主键」 「聚集索引查完整数据」。它们之间的引用关系非聚集索引树的叶子节点 → 存主键 → 指向聚集索引树。我们可以再进行一个更加完善的总结一张表对应一个 .ibd 文件 → .ibd 文件由大量 16KB 的页数据页、索引页等组成 → 这些页按照所属的索引被划分为多个组每组页分别属于一棵逻辑上独立的 B 树1 棵聚集索引树 N 棵非聚集索引树即每棵树独占一部分页页与页之间不混用 → 最终一个 .ibd 文件 众多 16KB 页的集合是存储了 1N 棵独立 B 树的物理容器。回到非聚集索引我们知道在一棵非聚集索引树中无法查询到完整的数据需要回表查询到聚集索引树中查询数据信息但是如果使用索引覆盖那么就可以直接在非聚集索引树中获取数据而无需回表。5.7索引覆盖索引覆盖 是指查询语句中需要获取的所有字段SELECT 列 WHERE 条件列都能从某一棵非聚集索引的 B 树中直接获取无需回表查询聚集索引。简单说索引 “覆盖” 了查询的所有需求不用再去查数据本体聚集索引。核心判定条件查询的所有字段如 id、age、name都包含在目标非聚集索引中查询条件字段是该索引的列或复合索引的最左前缀全程仅操作非聚集索引的 B 树不依赖聚集索引。示例用户表主键 id非聚集索引 idx_age(age)CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, -- 聚集索引叶子节点存 idnameage...完整数据 name VARCHAR(50), age INT, INDEX idx_age (age) -- 非聚集索引叶子节点只存 age id );-- 普通查询回表2 次 IO SELECT id, name FROM user WHERE age 20; -- 索引覆盖查询不回表1 次 IO SELECT id, age FROM user WHERE age 20;记住回表查询的核心流程是先遍历非聚集索引树拿主键 → 再遍历聚集索引树补全数据。那么普通索引的回表流程而索引覆盖的例子分析虽然聚集索引也有主键值但是你要查的是 “age20”不是 “id5”—— 聚集索引里没有按 age 排序的结构无法快速找到 age20 的行必须靠非聚集索引先找到对应的 id非聚集索引如 idx_age是按条件字段age排序的能快速找到目标数据对应的聚合索引的主键从而找到 name。回表必要流程查age20 → 非聚集索引按age排序→ 拿ID5 → 聚集索引按ID排序→ 拿name到这里我们对索引的了解全部结束接下来我们要来使用索引。6.使用索引6.1自动创建当我们为⼀张表加主键约束(Primary key)外键约束(Foreign Key)唯⼀约束(Unique)时 MySQL会为对应的的列自动创建⼀个索引。如果表不指定任何约束时MySQL会自动为每⼀列生成⼀个索引并用 ROW_ID 进行标识。6.2手动创建6.2.1主键索引方式一创建表时创建主键create table t_pk1 ( id bigint primary key auto_increment, name varchar(20) );如上述的例子我们在创建表时就加了一个主键约束即此时这个表的主键列对应有一个主键索引那么可以通过show index from 表名;这条SQL语句查看指定表的索引信息Table索引所属的表名。Non_unique是否唯一索引0 表示唯一1 表示可以重复。Key_name索引名称主键索引默认为 PRIMARY。Seq_in_index索引中列的顺序/序号从 1 开始。复合索引中用于标识列的位置(复合主键列谁先谁后)。Column_name索引的列名。Collation列的排序方式A 表示升序NULL 表示不排序。Cardinality索引中唯一值的估计数量用于优化器判断索引效率。Sub_part如果只索引列的前缀部分显示前缀长度如对 varchar(255) 只索引前 10 个字符则显示 10。Packed索引是否被压缩NULL 表示未压缩。Null列是否允许 NULL 值YES 表示允许。Index_type索引类型BTREE、HASH、FULLTEXT 等InnoDB 通常为 BTREE。Comment索引的注释信息。Index_comment创建索引时指定的注释。方式二创建表时单独指定主键列create table t_pk2 ( id bigint auto_increment, name varchar(20), primary key(id) );方式三修改表中的列为主键索引create table t_pk3 ( id bigint, name varchar(20), ); alter table t_pk3 add primary key(id); -- 为t_pk3的表添加主键并指定列 alter table t_pk3 modify id bigint auto_increment; -- 把t_pk3表中id列修改为自增列6.2.2唯一索引方式一创建表时创建唯一键create table t_uq1 ( id bigint primary key auto_increment, name varchar(20) unique );查看表索引信息方式二创建表时单独指定唯一列create table t_uq2 ( id bigint primary key auto_increment, name varchar(20), unique (name) );方式三修改表中的列为唯一索引create table t_uq3 ( id bigint primary key auto_increment, name varchar(20), ); alter table t_uq3 add unique (name);6.2.3普通索引普通索引创建的时机创建表的时候明确知道某些列频繁查询就创建好(当表中数据过少时全表扫描可能效率比索引高)。随着业务不断发展在版本迭代过程中添加索引。创建普通索引语法index是创建索引关键字。index (索引列列名);方式一创建表时指定索引列create table t_index1 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), index (sno) );查看索引信息方式二修改表中的列为普通索引create table t_index2 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), ); alter table t_index2 add index (sno);方式三单独创建索引并指定索引名(推荐使用)语法create index 索引名 on 表名(列名[,列名]);create table t_index3 ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(20), ); -- 为 t_index3 表创建普通索引索引列为sno create index sno_index3 on t_index3(sno);查看索引信息可以看到当我们单独去创建一个索引时可以指定索引名而不是默认是将作为索引的列的列名作为索引名6.2.4复合索引创建语法与创建普通索引相同只不过指定多个列(索引中包含多个列)列与列之间⽤逗号隔开。方式一创建表时指定索引列create table t_index4 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint, index (sno,class_id) );查看索引信息方式二修改表中的列为复合索引create table t_index5 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint ); alter table t_index5 add index (sno,class_id);方式三单独创建索引并指定索引名(推荐使用)create table t_index6 ( id bigint primary key auto_increment, name varchar(20), sno varchar(20), class_id bigint ); -- 为t_index6表创建复合索引并指定索引列 create index sno_class_id on t_index6(sno,class_id);查看索引信息6.3查看索引方式一show keys from 表名;示例方式二show index from 表名;示例方式三desc 表名; —— 查看的是简要信息示例6.4删除索引6.4.1删除主键索引语法alter table 表名 drop primary key;示例删除 t_index6 表中的主键此时发现此条语句运行失败原因是该表中的主键列设置为了自增列需要先删除自增属性然后再删除主键查看索引信息主键索引已经被删除只剩下一个复合索引6.4.2删除其他索引这里的其他索引指的是除了主键索引之外的索引例如唯一索引复合索引普通索引等。语法alter table 表名 drop index 索引名;示例删除 t_index6 表中名为 sno_class_id 的索引查看索引信息此时的表中已经没有索引是空表——————————————————————————————————7.如何查看自己写的SQL走没走索引可以看执行计划explain示例有一个stu表先为这个表创建一个复合索引查看索引信息查看表中的数据1.查看执行计划以下是不加条件即查询所有2.使用主键查询3.子查询中使用索引4.唯一索引查询5.复合/普通索引查询以上的查询语句都是需要回表查询的(主键索引除外)除了在子查询的时候使用了索引覆盖。再举一个索引覆盖的例子用复合索引 sno_class_id 举例Extra——执行情况的说明和描述。Using index表示使用索引如果只有Using index 说明没有查询到数据表只用到了索引表就完成了这个查询这个叫做索引覆盖。Using where表示条件查询如果不读取表的所有数据或不是仅仅通过索引就可以获取所有需要的数据则会出现Using where。Using where; Using index表示查询使用了覆盖索引但索引返回的行还需要经过 WHERE 条件进一步过滤虽然列都在索引中但索引本身可能无法直接排除所有行仍需在索引层面筛选总结只要查询条件中使用了索引包含的索引列就会走索引和顺序无关。8.创建索引的注意事项索引应该创建在高频查询的列上索引需要占用额外的存储空间对表进行插入、更新和删除操作时同时也会修索引可能会影响性能创建过多或不合理的索引会导致性能下降需要谨慎选择和规划索引
