学习笔记：MySQL索引（二）

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的最大特色

页结构

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

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

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

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

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

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

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

将上述内容结合起来看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;

查看索引

# 查看表中的索引
show index from 表名;

# 示例
show index from user_info;

show index语法的列解释