雷达智富

首页 > 内容 > 程序笔记 > 正文

程序笔记

数据库系列:MySQL引擎MyISAM和InnoDB的比较

2024-09-25 17

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是更优的选择。

更新于:17天前
赞一波!

文章评论

评论问答