持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第10天，点击查看活动详情

1、MyISAM索引

MyISAM的数据文件和索引文件是分开存储的。索引保存在索引文件user.MYI中，数据文件保存在数据文件user.MYD中。

MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

创建表user：

CREATE TABLE `user_myisam` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) USING BTREE, 
  KEY `idx_age` (`age`) USING BTREE 
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

此时，id列为主键，创建主键索引。age为普通索引。

1.1、主键索引

等值查询：

select * from user where id = 30;

在主键树中从根节点开始检索，将根节点加载到内存，比较30<56,走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项。（1次磁盘IO）
从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）
将记录返给客户端。 磁盘IO次数：3+1次。

范围查询：

select * from user where id between 30 and 49;

先将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。
根据磁盘地址从数据文件中获取行记录缓存到结果集中。（2次磁盘IO）
我们的查询语句是范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（2次磁盘IO）
最后得到两条符合筛选条件，将查询结果集返给客户端。 磁盘IO次数：2+检索叶子节点数量+记录数。

MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存。

1.2、辅助索引

在普通字段上创建的索引。 MyISAM中的辅助索引和主键索引的结构是一样的。

主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，在查到值以后依然需要按照范围查询的方式继续检索。

2、InnoDB索引

CREATE TABLE `user_innodb` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `name` varchar(20) DEFAULT NULL, 
    `age` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`) USING BTREE, 
    KEY `idx_age` (`age`) USING BTREE 
) ENGINE=InnoDB;

2.1、InnoDB索引简介

InnoDB创建索引的具体规则如下：

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

每个InnoDB表都有一个聚簇索引，使用B+树构建。 叶子节点存储的数据是整行记录。

一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

辅助索引的叶子节点存储的数据是该行的主键值，在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

InnoDB的数据和索引存储在一个文件user.ibd中。InnoDB的数据组织方式，是聚簇索引。

2.2、主键索引

主键索引的叶子节点会存储数据行, InnoDB要求表必须有一个主键索引

等值查询：

select * from user_innodb where id=30;

先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO） 磁盘IO次数：3次。

范围查询：

select * from user_innodb where id between 30 and 49;

先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。获取行数据缓存到结果集中。（1次磁盘IO）
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，获取行数据缓存到结果集中。（1次磁盘IO）
最后得到2条符合筛选条件，将查询结果集返给客户端。 磁盘IO次数：2次+检索叶子节点数量。

2.3、辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

age索引的索引结果如下图:
底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

等值查询：

select * from user_innodb where age=22;

先将根节点加载到内存，比较22<77，走左路。（1次磁盘IO）
将左子树节点加载到内存中，比较22<34，向下检索。（1次磁盘IO）
检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）
1. 第一项5：5<22不符合要求，丢弃。
2. 第二项22：等于22，符合要求，获取主键id=18，去主键索引树中检索id=18的数据放入结果集中。（回表：3次磁盘IO）。
3. 第三项22：等于22，符合要求，获取主键id=49，去主键索引树中检索id=49的数据放入结果集中。（回表：3次磁盘IO）
  22=22,22=22。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘IO）
第一项34：34>22不符合要求，丢弃。查询结束。
最后得到2条符合筛选条件，将查询结果集返给客户端。 磁盘IO次数：2次+检索叶子节点数量+记录数*3。

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询， 范围查询:

select * from user_innodb where age between 30 and 49;

辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。

检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。最后将查询结果返回客户端。

Mysql索引