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字段两边的字段都要创建索引。
mysql教程书籍,一本搞懂mysql事务淘宝¥74.78¥购买已下架