InnoDB和MyISAM索引探索


前言:

InnoDB和MyISAM是Mysql的重要结构,都是基于B+Tree实现的索引结构,本文将简析与对比这2中索引结构的特征,希望对于理解索引能够有所帮助~


索引:

索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构。

  1. MySQL的数据都是以文件的形势存储在磁盘上的。
  2. 磁盘是由一圈一圈的磁道组成。
  3. 磁头移动到不同的磁道,磁盘旋转,这样就可以读到数据。
  4. 磁盘存取原理 1.寻道时间(速度慢,费时)  2.旋转时间(速度较快)
  5. CPU读取数据都是从内存读取,内存去磁盘中读取数据,内存读取磁盘数据大小都是以页的大小单位。一页 = 10kb
总结:
  1. 当磁头移动到另一个磁道读取数据就是我们常说的一次I/O操作,MySQL数据是分布在不同的磁道上的,每次读取数据都要把所有的磁道读取一遍,那我们进行I/O操作的次数就很多了,查询效率就很低。
  2. 索引就是把索引字段数据的地址保存起来,来帮助MySQL直接定位到哪个磁道哪个扇区,这样就减少了I/O的操作次数了,自然查询效率就提高了。

B+树

我们这里关注B+树的两个特性:

  1. 叶子节点包含数据data(data并不特指数据库中的某一行数据,也可以是某个数值,指针等)
  2. 叶子节点均在同一层,且每个节点均可以直接找到上一个或者下一个节点(双向指针,比常规的B+树多了一个指向上一个的指针)

B+Tree


InnoDB 聚簇索引

聚簇索引:行数据与键值(主键)紧凑地存储在一起;

InnoDB中表现为:B+树叶子节点的data用于存放行数据(包含主键值、其他列数据、回滚指针、事务id等),物理上索引数据与行数据都放在同一个文件中(.ibd

以用户表为例,id为主键,另外name存在索引idx_name

CREATE TABLE `t_user` (
  `id` bigint,
  `name` varchar(10),
  `age` int,
  PRIMARY KEY (`id`),
  INDEX `idx_name` (`name`)
);

插入数据:

insert into t_user (id,`name`,age) values
(1,'n7',10),
(2,'n6',20),
(3,'n5',30),
(4,'n4',40),
(5,'n3',50),
(6,'n2',60),
(7,'n1',70)

聚簇索引对应的结构为:

InnoDB聚簇索引

注意:如果没有定义主键,InnoDB会选择一个非空的唯一索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键(DB_ROW_ID)来作为聚簇索引。

最好避免使用随机的主键(比如UUID)
InnoDB中磁盘管理的最小单位为页(InnoDB page,默认16KB),一页能存放的数据记录数量是有限的,根据B+树的特性,需要保证页内数据是按主键排序存储的。
当主键随机插入时,如果新的记录插入到之前记录的中间,则需要逻辑移动之前的记录;如果插入一个已经满了的页中时,会导致页分裂(申请一个新的数据页,复制部分数据过去,代价很高)。


InnoDB 二级索引

二级索引,或者叫非聚簇索引;与聚簇索引最大的不同是:B+树的data存放的并不是行数据,而是主键值
以上面用户表为例,当select * from t_user where name='n1'时,会先通过idx_name索引找到n1对应的主键的值(id=7),再通过主键值找到行数据 [7,n1,70] ,故称二级索引。这个name字段也称为辅助索引。

InnoDB二级索引

覆盖索引:当select的列的已经在二级索引的树中时,并不需要再通过主键值找到整行的数据即可返回。比如select id,name from t_user where name='n1' ,name和id均可在idx_name索引的叶子节点上,故不用主键回查聚簇索引了。像这种二级索引覆盖到所有查询列数据的情况叫覆盖索引。使用explain这个查询时可以看到Extra中包含Using index


MyISAM 索引

与InnoDB不同,MyISAM并不使用聚簇索引,MyISAM的索引数据和行数据是分开的,物理上分别为.myi索引数据文件和.myd行数据文件(InnoDB 索引和行数据均在.idb文件中)

MyISAM中,主键索引和其他的一般索引在数据结构上并没有什么区别,B+树的data存放的均是数据行地址

id主键索引:

id主键索引

name一般索引

name一般索引


文章作者: jackey
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 jackey !
评论
  目录