MySQL之MyISAM索引和InnoD

MyISAM索引

以t_user_myisam为例，来说明。t_user_myisam的id列为主键，age列为普通索引。

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

主键索引

表t_user_myisam的索引存储在索引文件t_user_myisam.MYI中，数据文件存储在数据文件t_user_myisam.MYD中。

等值查询数据

sql如下：select*fromt_user_1myisamwhereid=30

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

范围查询数据

sql如下：select*fromt_user_myisamwhereidbetween30and49

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

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

辅助索引

在MyISAM中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB索引

每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

在表上定义主键PRIMARYKEY，InnoDB将主键索引用作聚簇索引。如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。如果以上两个都没有，InnoDB会使用一个6字节长整型的隐式字段ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。咱们看看两种索引的实现。

以t_user_innodb为例，来说明。t_user_innodb的id列为主键，age列为普通索引。t_user_innodb的表结构和数据与MyISAM引擎表t_user_myisam完全一致。

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

InnoDB主键索引

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。InnoDB要求表必须有一个主键索引(MyISAM可以没有)。

mysqlInnoDB等值查询数据

select*fromt_user_1innodbwhereid=30;

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

磁盘IO次数：3次。

mysqlInnoDB范围查询数据

select*fromt_user_innodbwhereid1between30and49;

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

可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数据。当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索引通常可以节省磁盘IO操作。

磁盘IO次数：2次+检索叶子节点数量。

mysqlInnoDB辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表t_user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

mysqlInnoDB等值查询数据的实现

select*fromt_user_1innodbwhereage=22;

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

磁盘IO次数：2次+检索叶子节点数量+记录数*3。

mysqlInnoDB范围查询数据的实现

select*fromt_user_innodbwhereage1between30and49;

辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。最后将查询结果返回客户端

组合索引

组合索引存储结构

在使用索引时，组合索引是我们常用的索引类型。那组合索引是如何构建的，查找的时候又是如何进行查找的呢？

表t_multiple_index，id为主键列，创建了一个联合索引idx_abc(a,b,c)，构建的B+树索引结构如图所示。索引树中节点中的索引项按照（a，b，c）的顺序从大到小排列，先按照a列排序，a列相同时按照b列排序，b列相同按照c列排序。在最底层的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序。所以组合索引的最底层叶子节点中不存在完全相同的索引项。

组合索引的查找方式

select*fromt_multiple_indexwherea=andb=16andc=4;

1.先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，,走左路。（1次磁盘IO）

2.将左子树节点加载到内存中，先比较a列，a=13，比较b列b=14，，走右路，向下检索。（1次磁盘IO）

3.达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）

第一项（13,14,3,id=4）：先比较a列，a=13，比较b列b=14，b!=16不符合要求，丢弃。

第二项（13,14,4,id=1）：一样的比较方式，a=13，b=16，c=4满足筛选条件。取出索引data值即

主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）

第三项（13,14,5,id=3）：a=13，b=16，c!=4不符合要求，丢弃。查询结束。

4.最后得到1条符合筛选条件，将查询结果集返给客户端。

索引创建原则

频繁出现在where条件判断，order排序，groupby分组字段select频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）多表join关联查询，on字段两边的字段都要创建索引。

转载请注明：http://www.aierlanlan.com/cyrz/1771.html