数据库系列16:MyISAM与InnoDB的索引对比

相关文章

数据库系列:MySQL慢查询分析和性能优化
数据库系列:MySQL索引优化总结(综合版)
数据库系列:高并发下的数据字段变更
数据库系列:覆盖索引和规避回表
数据库系列:数据库高可用及无损扩容
数据库系列:使用高区分度索引列提升性能
数据库系列:前缀索引和索引长度的取舍
数据库系列:MySQL引擎MyISAM和InnoDB的比较
数据库系列:InnoDB下实现高并发控制
数据库系列:事务的4种隔离级别
数据库系列:RR和RC下,快照读的区别
数据库系列:MySQL InnoDB锁机制介绍
数据库系列:MySQL不同操作分别用什么锁?
数据库系列:业内主流MySQL数据中间件梳理

1 介绍

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现差距和特性对比。

2 MySQL索引实现

MySQL内部索引是由不同的引擎实现的,主要包含InnoDB和MyISAM这两种,并且这两种引擎中的索引都是使用B+树的结构来存储的。

2.1 InnoDB引擎中的索引

Innodb中有2种索引:主键索引(也叫聚集索引 Clustered Index)、辅助索引(也叫非聚集索引 UnClustered Index)。

1. 主键索引: 每个表只有一个主键索引,B+树结构,叶子节点存储主键的值以及对应整条记录的数据,非叶子节点不存储记录的数据,只存储主键的值。
当表中未指定主键时,则第一个非空unique列是聚集索引,如果一个非空unique列都没有,MySQL内部会自动给每条记录添加一个隐藏的rowid字段(默认4个字节)作为主键,用rowid构建聚集索引。聚集索引在MySQL中即主键索引。

2. 辅助索引: 每个表可以有多个辅助索引,b+树结构,非聚集索引叶子节点存储字段(索引字段)的值以及对应记录主键的值,其他节点只存储字段的值(索引字段),这就是与聚集索引不同的地方。每个表可以有多个非聚集索引。

MySQL中非聚集索引进一步区分:

非聚集索引类型 说明
单列索引 一个索引只包含一个列
多列索引(复合索引) 一个索引包含多个列
唯一索引 索引列的值必须唯一,允许有一个空值

3. 索引设计建议

  • 不宜使用较长的字段上做主键,因为所有的辅助索引都会存储主键(参考上面红体字),这样会导致的体积庞大
  • 建议使用有序递增(IDENTITY)的key做主键,避免索引分裂,行记录频繁移动

image

2.2 MyISAM引擎中的索引

也是B+树结构,MyISM使用的是非聚簇索引(UnClustered Index),也就是说索引跟数据分开存储,如下图,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。
由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
image

3 图解数据检索过程

下图更形象说明这两种索引的区别,这边假设了一个存储4行数据的表。Id为主键索引,name作为辅助索引,图中清晰的体现了聚簇索引和非聚簇索引的差异。

image

3.1 InnoDB数据检索过程

上面的表中有2个索引:id作为主键索引,name作为辅助索引
如果需要查询id=9的数据,只需要在左边的主键索引中检索就可以了。
如果需要搜索name='Brand'的数据,需要2步:
1、先在辅助索引中检索到name='Brand'的数据,获取id为16
2、再到主键索引中检索id为16的记录
辅助索引这个查询过程在MySQL中叫做回表,相对于主键索引多了第二步操作。

3.2 MyISAM数据检索过程

1、在索引中找到对应的关键字,获取关键字对应的记录的地址
2、通过记录的地址查找到对应的数据记录

对比发现:innodb中最好是采用主键查询,这样只需要一次索引,如果使用辅助索引检索,涉及多一步的回表操作,比主键查询要耗时一些。
而innodb中辅助索引区别于myisam的是:
表中的数据发生变更的时候,会影响其他记录地址的变化,如果辅助索引中记录数据的地址,此时会受影响,而主键的值一般是很少更新的,当页中的记录发生地址变更的时候,对辅助索引是没有影响的。

4 InnoDB与MyISAM的其它不同

1. 事务支持: MyISAM不支持事务,而InnoDB则支持。事务的支持使得InnoDB在数据一致性、完整性和可靠性方面表现更好。

2. 缓存机制: MyISAM只会缓存索引,不会缓存数据。它可以通过key_buffer_size缓存索引,以减少磁盘I/O,提升访问性能。而InnoDB的缓存机制则更为复杂,它支持提交(commit)和回滚(rollback)事务,并提供了可靠的数据一致性和完整性。

3. 外键: InnoDB支持外键,而MyISAM不支持。外键用于关联两个表的数据,使得两个表之间的关系更加清晰,同时也提高了数据的完整性。
但是在数据量大并发量大的情况下,使用外键可能会导致性能瓶颈和死锁问题。

4. 行锁与表锁: MyISAM只支持表级锁,而InnoDB支持行级锁(记录锁)。表级锁是加锁时对整张表进行加锁,行级锁是对表中的某一行进行加锁。因此,在执行大量SELECT查询时,MyISAM具有更好的性能,因为它支持不加锁读取。不过InnoDB也可以使用快照(snapshoot)的模式进行高效检索。

详细可以扩展阅读作者的这篇文章:MySQL引擎MyISAM和InnoDB的比较

5 总结

总的来说,选择哪种存储引擎需要根据具体的应用场景来决定。如果需要执行大量的SELECT查询,且不需要事务支持,那么MyISAM可能一个选择。如果需要执行大量的INSERT或UPDATE操作,且需要事务支持、行级锁和外键支持,那么InnoDB可能是一个更好的选择。
在现有的互联网场景下,对大数据的处理要求很频繁,对高并发性能要求也很高,所以InnoDB是更优的选择。
其他方面的决策可以参考以下表格综合衡量:

InnoDB MyISAM
事务 支持 不支持,回滚将造成不完全回滚,不具有原子性
mvcc 支持,辅助事务,可用于全库备份 不支持,全库备份需要使用全局锁
默认行锁,也支持表锁 仅支持表锁
外键 支持 不支持外键
文件系统 把数据和索引存放在表空间里面 frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
主键索引 使用聚集索引,主键和列值数据直接在一起,所以建议使用自增bigint 类型 主键 索引数据中存储数据所在地址,主键也不是聚集索引
非主键索引 辅助索引data域存储相应记录主键的值而不是地址 data域保存数据记录的地址
数据恢复 由于其事务日志的使用,更容易从崩溃中恢复 难从崩溃中恢复,可能会丢失数据。
全表count(*) 需要全表扫描计算总行数 每个表维护了一个行数计数器,可以直接返回
posted @ 2024-06-07 09:15  Hello-Brand  阅读(318)  评论(0编辑  收藏  举报