MySQL数据库基础（11）：索引

雨落在了我的手上个人主页个人仓库Gitee仓库❄️个人专栏JaveSe C语言 C语言数据结构Java数据结构MySQL数据库基础 人生格言无人扶我青云志我自踏雪至山巅博主简介目录1. 本节目标2. 简介2.1 索引是什么2.2 为什么要使用索引3. 索引应该选择哪种数据结构3.1 HASH3.2 二叉搜索树3.3 N叉树3.4 B树3.4.1 简介3.4.2 B树的特点3.4.3 B树与B树的对比4. MySQL中的页4.1 为什么要使用页4.2 页文件头和页文件尾4.3 页主体4.4 页目录4.5 数据页头5. B在MySQL索引中的应用5.1 计算三层树高的B树可以存放多少条记录6. 索引分类6.1 主键索引6.2 普通索引6.3 唯一索引6.4 全文索引6.5 聚集索引6.6 非聚集索引6.7 索引覆盖7. 使用索引7.1 自动创建7.2 手动创建7.2.1 主键索引7.2.2 唯一索引7.2.3 普通索引7.3 创建复合索引7.4 查看索引7.5 删除索引7.5.1 主键索引7.5.2 其他索引7.6 创建索引的注意事项内容大纲本文系统讲解MySQL索引机制涵盖索引概念、数据结构选择重点分析B树优势、页结构与存储原理以及主键、唯一、普通、复合等索引类型的应用与创建方法。通过B树的层级结构与页管理机制揭示其高效支持范围查询与减少磁盘I/O的原理并结合实际操作说明索引的创建、查看、删除及优化注意事项帮助读者深入理解索引在数据库性能优化中的核心作用。1. 本节目标了解什么是索引了解除索引使用的数据结构掌握B树在索引中的应用掌握索引分类和使用2. 简介2.1 索引是什么MySQL的索引是一种数据结构它可以帮助数据库高效地查询、更新数据表中的数据。索引通过一定的规则排列数据表中的记录使得对表的查询可以通过对索引的搜索来加快速度。MySQL 索引类似于书籍的目录通过指向数据行的位置可以快速定位和访问表中的数据比如汉语字典的目录索引页我们可以按笔画、偏旁部首、拼音等排序的目录索引快速查找到需要的字。笔画索引偏旁部首索引拼音索引2.2 为什么要使用索引显而易见使用索引的目的只有一个就是提升数据检索的效率在应用程序的运行过程中查询操作的频率远远高于增删改的频率。3. 索引应该选择哪种数据结构3.1 HASH时间复杂度是O(1) 查询速度非常快但是MySQL并没有选择HASH做为索引的默认数据结构主要原因是HASH不支持范围查找3.2 二叉搜索树二叉搜索树的中序遍历是一个有序数组但有几个问题导致它不适合用作索引的数据结构最坏情况下时间复杂度为O(N)节点个数过多无法保证树高AVL和红黑树虽然是平衡或者近似平衡但是毕竟是二叉结构在检索数据时每次访问某个节点的子节点时都会发生一次磁盘IO而在整个数据库系统中IO是性能的瓶颈减少IO次数可以有效的提升性能3.3 N叉树为了解决树高的问题可以使用N叉树通过观察相同数据量的情况下N叉树的树高可以得到有效的控制也就意味着在相同数据量的情况下可以减少IO的次数从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好。3.4 B树3.4.1 简介B树是一种经常用于数据库和文件系统等场合的平衡查找树MySQL索引采用的数据结构以4阶B树为例如下图所示3.4.2 B树的特点能够保持数据稳定有序插入与修改有较稳定的时间复杂度非叶子节点仅具有索引作用不存储数据所有叶子节点保真实数据所有叶子节点构成一个有序链表可以按照key排序的次序依次遍历全部数据3.4.3 B树与B树的对比叶子节点中的数据是连续的且相互链接便于区间查找和搜索。非叶子节点的值都包含在叶子节点中对于B树而言在相同树高的情况下查找任一元素的时间复杂度都一样性能均衡。4. MySQL中的页4.1 为什么要使用页在.ibd 文件中最重要的结构体就是Page(页)页是内存与磁盘交互的最小单元默认大小为16KB每次内存与磁盘的交互至少读取一页所以在磁盘中每个页内部的地址都是连续的之所以这样做是因为在使用数据的过程中根据局部性原理将来要使用的数据大概率与当前访问的数据在空间上是临近的所以一次从磁盘中读取一页的数据放入内存中当下次查询的数据还在这个页中时就可以从内存中直接读取从而减少磁盘I/O提高性能局部性原理是指程序在执行时呈现出局部性规律在一段时间内整个程序的执行仅限于程序中的某一部分。相应地执行所访问的存储空间也局限于某个内存区域局部性通常有两种形式时间局部性和空间局部性。时间局部性Temporal Locality如果一个信息项正在被访问那么在近期它很可能还会被再次访问。空间局部性Spatial Locality将来要用到的信息大概率与正在使用的信息在空间地址上是临近的。每一个页中即使没有数据也会使用16KB 的存储空间同时与索引的B树中的节点对应后续在专题七索引中详细讲解B树的内容查看页的大小可以通过系统变量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.04 sec)在MySQL中有多种不同类型的页最常用的就是用来存储数据和索引的索引页也叫做数据页但不论哪种类型的页都会包含页头(File Header)和页尾(File Trailer)页的主体信息使用数据行进行填充数据页的基本结构如下图所示4.2 页文件头和页文件尾页文件头和页文件尾中包含的信息如下图所示这里我们只关注上一页页号和下一页页号通过这两个属性可以把页与页之间链接起来形成一个双向链表。4.3 页主体页主体部分是保存真实数据的主要区域每当创建一个新页都会自动分配两个行一个是页内最小行Infimun 另一个是页内最大行Supremun 这两个行并不存储任何真实信息而是做为数据行链表的头和尾第一个数据行有一个记录下一行的地址偏移量的区域next_record 将页内所有数据行组成了一个单向链表此时新页的结构如下所示当向一个新页插入数据时将Infimun 连接第一个数据行最后一行真实数据行连接Supremun 这样数据行就构建成了一个单向链表更多的行数据插入后会按照主键从小到大的顺序进行链接如下图所示4.4 页目录当按主键或索引查找某条数据时最直接简单的方法就是从头行infimun 开始沿着链表顺序逐个比对查找但一个页有16KB通常会存在数百行数据每次都要遍历数百行无法满足高效查询为了提高查询效率InnoDB采用二分查找来解决查询效率问题具体实现方式是在每一个页中加入一个叫做页目录Page Directory 的结构将页内包括头行、尾行在内的所有行进行分组约定头行单独为一组其他每个组最多8条数据同时把每个组最后一行在页中的地址按主键从小到大的顺序记录在页目录中在页目录中的每一个位置称为一个槽每个槽都对应了一个分组一旦分组中的数据行超过分组的上限8个时就会分裂出一个新的分组后续在查询某行时就可以通过二分查找先找到对应的槽然后在槽内最多8个数据行中进行遍历即可从而大幅提高了查询效率这时一个页的核心结构就完成了例如要查找主键为6的行先比对槽中记录的主键值定位到最后一个槽2再从最后一个槽中的第一条记录遍历第二条记录就是我们要查询的目标行。4.5 数据页头数据页头记录了当前页保存数据相关的信息如下图所示5. B在MySQL索引中的应用非叶子节点保存索引数据叶子节点保存真实数据如下图所示以查找id为5的记录完整的检索过程如下首先判断B树的根节点中的索引记录此时5 7 应访问左孩子节点找到索引页2在索引页2中判断id的大小找到与5相等的记录命中加载对应的数据页以上的IO过程加载索引页1 -- 加载索引页2 -- 加载数据页35.1 计算三层树高的B树可以存放多少条记录假设一条用户数据大小为1KB在忽略数据页中数据页自身属性空间占用的情况下一页可以存16条数据索引页一条数据的大小为主键用BIGINT类型占8Byte下一页地址6Byte一共是14Byte一个索引页可以保存16*1024/14 1170 条索引记录如果只有三层树高的情况综合只保存索引的根节点和二级节点的索引页以及保存真实数据的数据页那么一共可以保存1170*1170*16 21,902,400 条记录也就是说在两千多万条数据的表中可以通过三次IO就完成数据的检索6. 索引分类6.1 主键索引当在一个表上定义一个主键PRIMARY KEY 时InnoDB使用它作为聚集索引。推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键则添加一个自增列。6.2 普通索引最基本的索引类型没有唯一性的限制。可能为多列创建组合索引称为复合索引或组全索引6.3 唯一索引当在一个表上定义一个唯一键UNQUE 时自动创建唯一索引。与普通索引类似但区别在于唯一索引的列不允许有重复值。6.4 全文索引基于文本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和DML操作用于全文搜索仅MyISAM和InnoDB引擎支持。6.5 聚集索引与主键索引是同义词如果没有为表定义PRIMARY KEY, InnoDB使用第一个UNIQUE 和NOT NULL 的列作为聚集索引。如果表中没有PRIMARY KEY 或合适的UNIQUE 索引InnoDB会为新插入的行生成一个行号并用6字节的ROW_ID 字段记录ROW_ID 单调递增并使用ROW_ID 做为索引。6.6 非聚集索引聚集索引以外的索引称为非聚集索引或二级索引二级索引中的每条记录都包含该行的主键列以及二级索引指定的列。InnoDB使用这个主键值来搜索聚集索引中的行这个过程称为回表查询6.7 索引覆盖当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列这时就可以直接返回数据而不用回表查询这样的现象称为索引覆盖7. 使用索引7.1 自动创建当我们为一张表加主键约束(Primary key)外键约束(Foreign Key)唯一约束(Unique)时MySQL会为对应的的列自动创建一个索引如果表不指定任何约束时MySQL会自动为每一列生成一个索引并用ROW_ID 进行标识7.2 手动创建7.2.1 主键索引# 方式一创建表时创建主键 create table t_test_pk ( id bigint primary key auto_increment, name varchar(20) ); # 方式二创建表时单独指定主键列 create table t_test_pk1 ( id bigint auto_increment, name varchar(20), primary key (id) ); # 方式三修改表中的列为主键索引 create table t_test_pk2 ( id bigint, name varchar(20) ); alter table t_test_pk2 add primary key (id) ; alter table t_test_pk2 modify id bigint auto_increment;7.2.2 唯一索引# 方式一创建表时创建唯一键 create table t_test_uk ( id bigint primary key auto_increment, name varchar(20) unique ); # 方式二创建表时单独指定唯一列 create table t_test_uk1 ( id bigint primary key auto_increment, name varchar(20), unique (name) ); # 方式三修改表中的列为唯一索引 create table t_test_uk2 ( id bigint primary key auto_increment, name varchar(20) ); alter table t_test_uk2 add unique (name) ;7.2.3 普通索引# 方式一创建表时指定索引列 create table t_test_index ( id bigint primary key auto_increment, name varchar(20) unique sno varchar(10), index(sno) ); # 方式二修改表中的列为普通索引 create table t_test_index1 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); alter table t_test_index1 add index (sno) ; # 方式三单独创建索引并指定索引名 create table t_test_index2 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); create index index_name on t_test_index2(sno);7.3 创建复合索引创建语法与创建普通索引相同只不过指定多个列列与列之间用逗号隔开# 方式一创建表时指定索引列 create table t_test_index4 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), class_id bigint, index (sno, class_id) ); # 方式二修改表中的列为复合索引 create table t_test_index5 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), class_id bigint ); alter table t_test_index5 add index (sno, class_id); # 方式三单独创建索引并指定索引名 create table t_test_index6 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), class_id bigint ); create index index_name on t_test_index6 (sno, class_id);7.4 查看索引# 方式一show keys from 表名 mysql show keys from t_test_index6\G *************************** 1. row *************************** Table: t_test_index6 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: t_test_index6 Non_unique: 1 Key_name: index_name Seq_in_index: 1 Column_name: sno Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 3. row *************************** Table: t_test_index6 Non_unique: 1 Key_name: index_name Seq_in_index: 2 Column_name: class_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 3 rows in set (0.00 sec) # 方式二 show index from t_test_index6; # 方式三简要信息desc 表名; desc t_test_index6;7.5 删除索引7.5.1 主键索引# 语法 alter table 表名 drop primary key; # 示例删除t_test_index6表中的主键 mysql alter table t_test_index6 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 t_test_index6 modify id bigint; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 # 重新删除主键 mysql alter table t_test_index6 drop primary key; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查看结果 mysql show keys from t_test_index6\G *************************** 1. row *************************** Table: t_test_index6 Non_unique: 1 Key_name: index_name Seq_in_index: 1 Column_name: sno Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: t_test_index6 Non_unique: 1 Key_name: index_name Seq_in_index: 2 Column_name: class_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.00 sec)7.5.2 其他索引# 语法 alter table 表名 drop index 索引名 # 示例删除t_test_index6表中名为index_name的索引 mysql alter table t_test_index6 drop index index_name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查看结果 mysql show keys from t_test_index6\G Empty set (0.00 sec)7.6 创建索引的注意事项索引应该创建在高频查询的列上索引需要占用额外的存储空间对表进行插入、更新和删除操作时同时也会修索引可能会影响性能创建过多或不合理的索引会导致性能下降需要谨慎选择和规划索引