数据库系列:MySQL引擎MyISAM和InnoDB的比较
1 比较和分析
MyISAM和InnoDB是两种不同的数据库存储引擎,它们在数据存储结构、事务支持、锁的支持、外键支持、主键观念、性能和优化方式等方面都存在明显的差异。
- 数据存储结构:MyISAM在磁盘上存储了三个文件,包括表的定义文件(.frm)、数据文件(.MYD)和索引文件(.MYI)。而InnoDB在磁盘上存储了两个文件,包括表的定义文件(.frm) 和 数据+索引文件(.ibd)。
- 事务支持:MyISAM不支持事务,而InnoDB支持事务,且有完善的锁、事务控制机制等功能。如需要执行大量的INSERT、UPDATE操作,InnoDB可以保证数据的一致性和完整性,提高多用户并发操作的性能。
- 锁的支持:MyISAM只支持表锁,效率相对较低。而InnoDB支持行锁,效率会高一些。执行大量SELECT查询时,MyISAM具有更好的性能,因它支持不加锁读取。在执行修改数据操作(UPDATE、DELETE)时,InnoDB行级锁可以减少锁定时间,提高并发性能。
- 主键观念:MyISAM可以不定义主键,如果定义了主键,则会是主键索引。而InnoDB必须要有主键,就算没有定义,那么会自动创建一个隐藏的6byte的int型的索引。
- 性能和优化:MyISAM适合执行大量的select操作,如阅读查询密集型的系统。而InnoDB适合执行大量的insert、update操作,如写入查询密集型的网站。
- 存储空间:MyISAM存储空间较小,而InnoDB需要更多的内存来存储。
- 对外键的支持:InnoDB支持外键,而MyISAM不支持。外键用于关联两个表的数据,使得两个表之间的关系更加清晰,同时也提高了数据的完整性。
总的来说,选择哪种存储引擎需要根据具体的应用场景来决定。
2 InnoDB在实践中的几点比较
2.1 数据计量:count(*)
MyISAM: MyISAM在执行COUNT(*)操作时,会扫描整个表,计算所有行的数量。由于MyISAM不支持事务和行级锁,因此在高并发环境下,多个客户端同时执行COUNT(*)操作可能会导致性能问题。但是如果查询中使用了索引,会使用索引来加速计算行数。
InnoDB: InnoDB在执行COUNT(*)操作时,会使用统计信息来估计行数,而不是扫描整个表。InnoDB支持事务和行级锁,通过在统计信息中维护行数统计,可以更快地执行COUNT(*)操作,提高性能。
如果查询中使用了索引,也不会使用索引来计算行数。这是因为统计信息是基于表数据,而不是基于索引。但是索引还是有益的,InnoDB会使用索引来加速满足查询条件的行的查找。
当加了where条件后,两种存储引擎的处理方式类似,都是根据条件来按照行扫描。
例如:
select count(*) from tb_userinfo where sex=0 and age > 22 ;
按照条件进行用户查询,两种存储引擎的处理方式类似,都是根据条件按照索引进行查询。
所以无论哪种存储引擎,建议都尽量建立好索引,并适当的通过where条件进行数据过滤。
2.2 全文索引
InnoDB5.6之前不支持全文索引,如果需要使用全文索引,可以使用Sphinx等搜索引擎。而MyISAM支持全文索引,这使得在文本搜索方面MyISAM具有更好的性能。
但是MySQL数据库本身不是为了全文检索而设计的,所以在数据量大并发量大的情况下,都不应该使用数据库自带的全文索引,会导致大量数据库资源和内存内损耗,更推荐的是使用类似 es、Sphinx等专业的全文检索引擎或组件。
2.3 事务机制
nnoDB支持事务,而MyISAM不支持。事务是一组数据库操作命令组成的程序逻辑单元,可以保证这组命令在执行过程中符合ACID特性(原子性、一致性、隔离性和持久性)。如果应用中需要执行大量的INSERT或UPDATE操作,使用InnoDB可以保证数据的一致性和完整性,提高多用户并发操作的性能
2.4 外键
InnoDB支持外键,而MyISAM不支持。外键用于关联两个表的数据,使得两个表之间的关系更加清晰,同时也提高了数据的完整性。
但是在数据量大并发量大的情况下,使用外键可能会导致性能瓶颈和死锁问题。
因此,为了提高性能和并发性,避免使用外键可能是一个更好的选择。在处理高度并发的场景时,可以考虑使用其他方法来管理数据的一致性和引用完整性,例如使用应用程序级别的逻辑或缓存等。
2.5 行锁与表锁
MyISAM只支持表级锁,而InnoDB支持行级锁。表级锁是加锁时对整张表进行加锁,行级锁是对表中的某一行进行加锁。因此,在执行大量SELECT查询时,MyISAM具有更好的性能,因为它支持不加锁读取。
然而,在执行需要修改数据的操作(如UPDATE、DELETE)的需求时,InnoDB的行级锁可以减少锁定时间和锁定的影响范围,提高并发性能。
我们在实际的业务场景中,绝大部分是读写混合的(基本都是读多写少),数据量和并发量只要上去了,都对性能有一定要求,所以还是推荐使用InnoDB。
需要注意的点是:尽量创建合适的索引,因为InnoDB的行锁是实现在索引上的,如果没有命中索引,就退化为表锁,那对并发性能反而有所降低。
举例如下:
# 表设计tb_userinfo(id, username, age, sex, tel) innodb;id PK # 主键默认建立索引# 命中索引,执行行锁update tb_userinfo set sex=0 where id=10086;# 未命中索引,退化为表锁update tb_userinfo set sex=0 where username='brand';
所以综上,InnoDB尽可能创建好合适的索引,否则锁粒度变成表锁,并发性能会受到很大的影响。
3 总结
总的来说,选择哪种存储引擎需要根据具体的应用场景来决定。如果需要执行大量的SELECT查询,且不需要事务支持,那么MyISAM可能一个选择。如果需要执行大量的INSERT或UPDATE操作,且需要事务支持、行级锁和外键支持,那么InnoDB可能是一个更好的选择。
在现有的互联网场景下,对大数据的处理要求很频繁,对高并发性能要求也很高,所以InnoDB是更优的选择。
相关文章
- 主流数据库中间件介绍和对比
- MyISAM 和 InnoDB 的区别
- mysql 数据库设计三大范式
- 数据库管理工具DBeaver 支持多种数据库
- 支持多种不同类型的数据库管理工具分享
- 海洋seacms数据库去重并禁止添加同名影片
- ThinkTemplate 模板引擎
- navicat 数据库结构同步
- 数据库ORM框架原理和实现
- .NET架构师技术要求:掌握.NET平台和架构设计能力
- MySQL 数据库备份和还原数据库 mysqldump、source
- 游戏引擎Unity与Godot的对比分析
- .NET C# EntityFrameworkCore(EF)连接PostgreSQL数据库
- .NET C#连接使用PostgreSQL数据库
- .NET EF连接MySQL数据库
- 在 Python 中集成一个 Hermite 系列
- 在 Python 中将一个 Legendre 系列添加到另一个 Legendre 系列
- 如何使用Python备份MySQL数据库?
- 如何使用Python连接到驻留在内存中的SQLite数据库?
- 数据库死锁及解决死锁