MySQL InnoDB和MyISAM索引结构简析与对比
B+树
我们这里关注B+树的两个特性:
- 叶子节点包含数据data(data并不特指数据库中的某一行数据,也可以是某个数值,指针等)
-
叶子节点均在同一层,且每个节点均可以直接找到上一个或者下一个节点(双向指针,比常规的B+树多了一个指向上一个的指针)
4阶 B+树
InnoDB 聚簇索引
聚簇索引:行数据与键值(主键)紧凑地存储在一起;
InnoDB中表现为:B+树叶子节点的data用于存放行数据(包含主键值、其他列数据、回滚指针、事务id等),物理上索引数据与行数据都放在同一个文件中(.ibd
)
以用户表为例,id
为主键,另外name存在索引idx_name
:
CREATE TABLE `t_user` (
`id` bigint,
`name` varchar(10),
`age` int,
PRIMARY KEY (`id`),
KEY `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聚簇索引,注:数据库中B+树的阶数要比这个大得多最好避免使用随机的主键(比如UUID)
InnoDB中磁盘管理的最小单位为页(InnoDB page,默认16KB),一页能存放的数据记录数量是有限的,根据B+树的特性,需要保证页内数据是按主键排序存储的。
当主键随机插入时,如果新的记录可能需要插入之前记录的中间,导致需要强制移动之前的记录;如果需要插入一个已经满了的页中时,会导致页分裂(新建两个页并把原数据复制过去,成本很高)。
InnoDB 二级索引
二级索引,或者叫非聚簇索引;与聚簇索引最大的不同是:B+树的data存放的并不是行数据,而是主键值;
以上面用户表为例,当select * from t_user where name='n1'
时,会先通过idx_name
索引找到n2
对应的主键的值(id=7),再通过主键值找到行数据 [7,n1,70] ,故称二级索引。
覆盖索引:当select的列的已经在二级索引的树中时,并不需要再通过主键值找到整行的数据即可返回。比如
select id,name from t_user where name='n2'
,name和id均可在idx_name
索引的叶子节点上,故不用主键回查聚簇索引了。像这种二级索引覆盖到所有查询列数据的情况叫覆盖索引。使用explain
这个查询时可以看到Extra
中包含Using index
MyISAM 索引
与InnoDB不同,MyISAM并不使用聚簇索引,MyISAM的索引数据和行数据是分开的,物理上分别为.myi
索引数据文件和.myd
行数据文件(InnoDB 索引和行数据均在.idb
文件中)
MyISAM中,主键索引和其他的一般索引在数据结构上并没有什么区别,B+树的data存放的均是数据行地址。
id主键索引:
name一般索引: