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

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

时间:2023-10-31 14:13:20浏览次数:36  
标签:并发 数据库 支持 索引 InnoDB MyISAM MySQL

1、数据库核心知识点
数据库系列:MySQL慢查询分析和性能优化
数据库系列:MySQL索引优化总结(综合版)
数据库系列:高并发下的数据字段变更
数据库系列:覆盖索引和规避回表
数据库系列:数据库高可用及无损扩容
数据库系列:使用高区分度索引列提升性能
数据库系列:前缀索引和索引长度的取舍

2、MySQL完整学习
MySQL全面瓦解30篇:合辑地址

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

标签:并发,数据库,支持,索引,InnoDB,MyISAM,MySQL
From: https://www.cnblogs.com/wzh2010/p/17794018.html

相关文章

  • java mysql druid mybatis-plus里使用多表删除出错的一种处理方式
    今天在出来多表删除的时候在mapper.xml用了下面的多个delete语句<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespa......
  • mysql中的is null和空字符串
    相比于oracle,mysql中的isnull和空坑就没那么多,直接写就行。不为空and(usernameisnotnullandusername!='')注:不为空中间用的是and。为空and(usernameisnullorusername!='')注:为空中间用的是or。......
  • mysql处理json格式的字段,一文搞懂mysql解析json数据
    文章目录一、概述1、什么是JSON2、MySQL的JSON3、varchar、text、json类型字段的区别二、JSON类型的创建1、建表指定2、修改字段三、JSON类型的插入1、字符串直接插入2、JSON_ARRAY()函数插入数组3、JSON_OBJECT()函数插入对象4、JSON_ARRAYAGG()和JSON_OBJECTAGG()将查询结果封装......
  • MySQL事务四大特性及其实现原理
    文章目录前言(一)原子性(二)持久性(三)隔离性(四)一致性前言在讨论MySQL事务四大特性的实现原理,需要先掌握事务与MySQL日志相关知识,可先看这两篇文章:MySQL事务【详解-最新的总结】MySQL三大日志(binlog、redolog和undolog)详解先来回顾下,事务的四大特性:原子性(Atomicity):事务的整个过程......
  • java通过ssl连接mysql(linux)
    环境:Os:centos7mysql:5.7.29java运行客户端:windows10 1.查看服务器的证书文件mysql数据data目录下[root@localhostdata]#pwd/opt/mysql57/data[root@localhostdata]#ls-al*.pem-rw-------.1mysqlmysql1676Oct3005:22ca-key.pem-rw-r--r--.1mysqlmysq......
  • MySQL中,当update修改数据与原数据相同时会再次执行吗?
    一、背景本文主要测试MySQL执行update语句时,针对与原数据(即未修改)相同的update语句会在MySQL内部重新执行吗?二、测试环境MySQL5.7.25Centos7.4三、binlog_format为ROW1、参数 2、测试步骤session1session2session13、总结在binlog_format=row和binlog_row_image......
  • 【转载】Mysql8授予某个用户某个数据库的权限
    参考chatgpt3.5环境软件/系统版本说明mysql8.0.28正文要在MySQL中授予某个用户对某个数据库的权限,你可以使用以下SQL语句:GRANT权限列表ON数据库名.表名TO'用户名'@'主机名';下面是这个语句的各个部分的解释:权限列表:这是你要授予用户的权限列表,可......
  • 我应该在MySQL中使用datetime还是timestamp数据类型?
    内容来自DOChttps://q.houxu6.top/?s=我应该在MySQL中使用datetime还是timestamp数据类型?你推荐使用datetime还是timestamp字段,为什么(使用MySQL)?我正在服务器端使用PHP。在MySQL中,时间戳通常用于跟踪记录的更改,并且每次更改记录时通常都会更新。如果您想存储特定值,则应使用......
  • 【技术分享】Amazon RDS MySQL参数说明及性能调优
    在亚马逊云科技的RDS中支持几乎主流的数据库,对于亚马逊云中的数据库的参数设置及性能调优对于我们的日常业务的运行会很有帮助。本篇将对于Amazon RDSMySQL的参数说明及性能调优。RDSMySQL的执行基本架构1.我们都知道,基础设施的潜在异常是常见的,也是不可避免的。Flamingo零售......
  • 【技术分享】Amazon RDS MySQL常见故障分析和排查
    在亚马逊云的RDS中支持几乎主流的数据库,对于亚马逊云中的数据库的问题排查对于我们的日常业务的稳定运行会很有帮助。本篇将对于Amazon RDSMySQL常见故障问题的分析和排查办法。RDS提供了强大的适配工作负载功能对于RDS的责任共担RDS常见连接问题Check:√客户端IP地址是否在D......