学习笔记：MySQL索引（二）

最新推荐文章于 2022-06-30 18:34:01 发布

_秋牧

最新推荐文章于 2022-06-30 18:34:01 发布

阅读量231

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42001592/article/details/123492530

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL的存储引擎

这里主要了解一下InnoDB和MyISAM，这两种引擎的索引都是使用B+树的结构来存储的。

InnoDB

InnoDB中有两种索引：主键索引（聚簇索引、聚集索引）、辅助索引（非聚簇索引、非聚集索引）

主键索引

每张表只有一个主键索引，B+树结构，叶子结点存储了主键的值以及完整的行记录，其他结点只存储主键的值。

如果定义了主键，InnoDB会自动使用主键来创建聚簇索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

注：聚簇索引的叶子结点称为数据页

辅助索引

每张表可以有多个辅助索引，B+树结构，叶子结点存储了索引字段的值以及主键的值，其他结点只存储索引字段的值。

MyISAM

MyISAM使用的是非聚簇索引，两颗B+树除了存储内容不同，结点结构完全一致。表数据存储在独立的地方，这两颗B+树的叶子结点都使用一个地址指向真正的表数据。

对于表结构来说，这两个索引是没有任何差别的，两颗B+树也是完全独立的，通过辅助键去检索也无需访问主键的索引树。

注：MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

检索过程图例

我们假设表存有4条数据，其中id为主键索引，name为辅助索引。如下图（图源自网络），可以看出聚簇索引与非聚簇索引的区别。

InnoDB数据检索过程

1、如果需要查询id=14的数据，只需要在主键索引中检索就可以了。

2、如果需要搜索name='Ellison’的数据，则需要分两步：

先在辅助索引中检索到name='Ellison’的数据，获取id为14

再到主键索引中检索id为14的记录

注：辅助索引这个查询过程在mysql中叫做回表。

MyISAM数据检索过程

1、在索引中找到对应的关键字，获取关键字对应的记录的地址；

2、通过记录的地址查找到对应的数据记录。

MyISAM和InnoDB的区别

	InnoDB	MyISAM
B+树索引	聚簇索引	非聚簇索引
事务支持	支持事务（这是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一）	不支持事务
外键支持	支持外键（一个包含外键的InnoDB表转MyISAM会失败）	不支持外键
表锁差异	最小的锁粒度是行锁，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁	最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，并发访问受限。（这也是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一）
表主键	如果没有定义主键或者非空唯一索引，会自动生成一个隐式主键（用户不可见）	允许没有任何索引和主键的表存在，索引都是保存行的地址
表的总行数	没有保存表的总行数，如果执行*select count() from table;**会遍历整张表	保存有表的总行数，如果执行*select count() from table;会直接取出该值（如果加了WHERE**条件，MyISAM和InnoDB的处理方式都一样）
CRUD操作	如果你的数据需要执行大量的INSERT或UPDATE，出于性能方面考虑，应该使用InnoDB	如果你的数据要执行大量的SELECT操作，MyISAM是不错的选择
全文索引	不支持FULLTEXT类型的全文索引，但是可以使用sphinx插件支持全文索引	支持FULLTEXT类型的全文索引
存储结构	所有的表都保存于同一个数据文件中（共享表空间）或者独立的表空间文件	每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件(.frm)、数据文件(.MYD)、索引文件(.MYI)。

注：支持事务和行级锁，是InnoDB的最大特色

页结构

MySQL中页是InnoDB存储数据的基本单位，也是MySQL中磁盘与内存交互的基本单位（管理数据的最小单位），默认是16KB。MySQL中采用B+树来存储数据，页相当于B+树中的一个结点。

页结构分为Page头部、Page主体、Page尾部，如下图（图源自网络）

页结构

Page头部详细信息如下图（图源自网络）

Page头部

Page头部中保存了两个指针，分别指向前一个Page和后一个Page，形成了一个双向链表的结构，如下图（图源自网络）

双向链表

Page主体部分如下图（图源自网络）

Page主体

行数据和索引都位于Page的User Records部分，占据了Page的大部分空间。字符串形式的"Infimum"代表开头，"Supremum"代表结尾，与User Records组成了一个单向链表的结构。（最初的数据是按照插入的先后顺序排列的，但是随着数据的插入和删除，数据物理顺序会变得混乱，但数据会通过链表的方式保持着逻辑上的先后顺序）

把User Records的组织形式与若干个Page组合起来，如下图（图源自网络）

双向链表和单向链表

将上述内容结合起来看B+树的结构，如下图（图源自网络）

B+树页结构

页目录（Page Directory）

页目录在靠近页尾部的地方，记录着每组记录中最后一条记录的地址偏移量，每个偏移量被放在目录槽（slot）中

分组

最小记录（Infimum）所在的分组只能有1条记录，最大记录（Supremum）所在的分组拥有的记录条数取值范围只能在[1,8]，其余分组的记录条数取值范围只能在[4,8]，并且每个分组中只有最大的那条记录的n_owned才会有值，其余的记录n_owned值为0，如下图（图源自网络）

页目录

n_owned 表示当前分组中一共有几条记录

数据检索过程

查询数据时，先通过B+树定位到数据所在的页，然后将页整体加载到内存中，再通过二分法在Page Directory中检索数据，缩小范围。比如在上图中检索记录 7：

通过二分法查找到 7 位于 slot3 和 slot2 之间；然后从 slot3 向右一个一个找，就可以找到记录 7。

走到 slot2 指向的记录 8 结束，如果还没有找到记录 7，则说明不存在记录 7。

页结构特点总结

1、B+树的叶子页之间是用双向链表连接的，能够实现范围查询；

2、页内部的记录之间是用单向链表连接的，便于访问下一条数据；

3、InnoDB为了加快页内部记录的查询，对页内记录加了个有序的稀疏索引（页目录）

注：稠密索引：对每一条记录都有索引项与之对应（占用空间大，检索快）
稀疏索引：索引项只对应部分记录（占用空间小，检索相对于稠密索引慢）

索引分类

分为聚簇索引和非聚簇索引

聚簇索引（主键索引）

每个表一定会有一个聚簇索引，整张表的数据存储在聚簇索引中，MySQL中采用B+树结构存储，其中非叶子结点只存储主键的值，叶子结点存储主键的值以及对应的行记录数据。

非聚簇索引（辅助索引）

每张表可以有多个非聚簇索引。也是B+树结构存储，其中非叶子结点只存储索引字段的值，叶子结点存储索引字段的值以及对应行记录数据的主键值。在MySQL中，非聚簇索引又分为：

单列索引：只包含一个列的索引
复合索引（联合索引）：包含多个列的索引
唯一索引：索引列的值必须唯一，允许有一个空值

索引管理

创建索引

# 方式一
create [unique] index 索引名称 on 表名(列名[(length)],列名[(length)]...);

# 方式二
alter table 表名 add [unique] index 索引名称 (列名[(length)],列名[(length)]...);

# 示例
create index idx_name on user_info(name(20));
alter table user_info add index idx_name_age (name(20),age);

如果字段是char、varchar类型，length可以小于实际字段的长度；如果是blob，text等长文本类型的，必须指定length。（实际开发中根据实际文本区分度决定索引长度，通过减小索引长度来减小索引文件的大小）

[unique]：可以省略，加上了unique代表创建唯一索引。

列名处如果只写一个字段，就是单列索引，写多个字段，就是复合索引，多个字段用逗号隔开。

删除索引

drop index 索引名称 on 表名;

# 示例
drop index idx_name on user_info;

查看索引

# 查看表中的suo'y
show index from 表名;

# 示例
show index from user_info;

show index语法的列解释

show index

列名	列解释
Table	表名
Non_unique	唯一索引。不能包括重复值则为0，否则为1
Key_name	索引名称，名称相同表示的是同一个索引，即联合索引
Seq_in_index	索引中的列序列号，从1开始（如图中的3，4条记录），能根据这个值推断出联合索引中索引的前后顺序
Column_name	索引的列名
Collation	列以何种方式存储在索引中，在MySQL中，有值 ‘A’ (升序)或 NULL (无分类)
Cardinality	索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 可以更新。值越大，当进行联合时，MySQL使用该索引的机会就越大（一般根据该值来判断该字段是否适合建立索引）
Sub_part	前置索引。如果列只是部分编入索引，该值就是编入索引的字符的数目。如果整列编入索引，则该值为NULL
Packed	指示关键字如何被压缩。如果没被压缩则为NULL
Null	如果列允许为空，则为YES
Index_type	索引方法。主要有（BTREE、HASH）
Comment Index_comment	备注