专栏首页MySQL入坑记MySQL - 常用存储引擎区别总结(2020最新版)

MySQL - 常用存储引擎区别总结(2020最新版)

存储引擎是数据库的核心,在MySQL中,存储引擎是以插件的形式运行的。支持的引擎有十几种之多,但我们实战常用到的,大概只有InnoDB、MyISAM 和 Memory 了。术业有专攻,这三种引擎各有特点和适用场景,也算是各自的生存技能了,就像各种编程语言一样。本文就带你来多方了解一下这三种存储引擎。 来不及解释了,快上车!

一、InnoDB

为什么先说InnoDB?InnoDB 从 MySQL5.5(2010年) 版本代替 MyISAM 成为默认引擎,可以说只要玩儿过 MySQL 的,都用过InnoDB,相比MyISAM强调性能,InnoDB 侧重于提供事务支持以及外部键等高级数据库功能。在大厂面试中也频频被问到,那 InnoDB 到底有多牛B呢?先来看看它有哪些特点;

1、支持事务。默认的事务隔离级别为可重复读(REPEATABLE-READ),通过MVCC(并发版本控制)来实现。

2、使用的锁粒度默认为行级锁,可以支持更高的并发;当然,也支持表锁。

其实有这两点就足以奠定InnoDB在存储引擎中的霸主地位了。你知道的,使用场景真的太多了。

3、支持外键约束;外键约束其实降低了表的查询速度,但是增加了表之间的耦合度。

4、可以通过自动增长列,方法是auto_increment。

5、配合一些热备工具可以支持在线热备份;

6、在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度;

7、对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块,都位于B+数的叶子节点上;

8、InnoDB 表的select count(*) 比 MyISAM 慢很多;当执行 select count(*) from t 时,会先把数据读出来,一行一行的累加,最后返回总数量。 是的,真的会很慢。需要注意的是,当count(*) 语句包含 where 条件时,两种表的操作是一样的。

9、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。

InnoDB的存储表和索引有下面两种形式:

  • 共享表空间存储:所有的表和索引存放在同一个表空间中。
  • 多表空间存储:表结构放在.frm文件,数据和索引放在.ibd文件中。分区表的话,每个分区对应单独的.ibd文件,分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。

对于InnoDB来说,最大的优势在于支持事务,当然这是以牺牲效率为代价的。

二、MyISAM

MyISAM:“凭啥 InnoDB 成默认引擎了?他能像我一样支持 Fulltext 全文检索吗?垃圾!” MySQL5.7:“可以啊!” MyISAM:“卧槽你(……长音)!,难道不是亲生的?”

还有好多同学在面试回答 InnoDB 和 MyISAM 区别的时候,依然会带上 InnoDB 不支持全文检索,MyISAM 支持对 BLOB 和 TEXT 的前500个字符索引云云,那是5、6年之前的答案啦。那么MyISAM有哪些特点呢?

1、不支持事务。这也算特点???没错,就是这么不要脸。不支持事务,像是挣脱了枷锁,在读写(Insert、select)效率上,要高于InnoDB不少。场景在:日志记录、调查统计表时,绝对值得一用。对了,不支持事务,自然就不支持锁!

2、体积小,质量大。MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。同时能加载更多索引,而Innodb的索引和数据是紧密捆绑的,没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多。

每张MyISAM表在磁盘上会对应三个文件。

(1).frm文件:存储表的定义数据

(2).MYD文件:存放表具体记录的数据

(3).MYI文件:存储索引

3、从以往经验来说,select count(*) 和 order by 大概是使用最频繁的,大概能占了整个sql总语句的60%以上的操作,而这种操作Innodb其实也是会锁表的,很多人以为Innodb是行级锁,那个只是where对它主键是有效,非主键的还是会锁全表的。

4、常常应用部门需要我给他们定期某些表的数据,MyISAM的话很方便,只要发给他们对应那表的(frm.MYD,MYI)的文件,让他们自己在对应版本的数据库启动就行,而Innodb就需要导出.sql了,因为光给别人文件,受字典数据文件的影响,对方是无法使用的。

5、如果和 MyISAM 比 Insert 写操作的话,Innodb还达不到MyISAM的写性能,如果是针对基于索引的update操作,确实MyISAM会慢与InnoDB,但在并发环境下,从库同步也是个事儿,还不如通过多实例分库分表架构来解决。

6、MyISAM表的select count(*) 是非常快的;在 MyISAM 存储引擎中,把表的总行数(row)存储在磁盘上,当执行 select count(*) from t 时,直接返回总数据。同样,当 count(*) 语句包含 where条件时,两种表的操作是一样的。

7、DELETE FROM table时,MyISAM会先将表结构备份到一张虚拟表中,然后执行drop,最后根据备份重建该表。

三、Memory

你可以将它理解为,临时表。

没错,Memory是将数据直接存在内存中的,特别适合数据量小的表。同时为了提高数据的访问速度,每一个表实际上和一个磁盘文件关联,文件是frm。

1、支持的数据类型有限制,比如:不支持TEXT和BLOB类型。对于字符串类型的数据,只支持固定长度的行,VARCHAR(64)会被自动存储为CHAR(64)类型;

2、只支持表级锁。所以,在访问量比较大时,表级锁会成为MEMORY存储引擎的瓶颈;

3、由于数据是存放在内存中,一旦服务器宕机,数据就会丢失;数据库主从切换的配置要设置好。

4、查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低;

5、默认使用hash索引。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 曾经,我以为我很懂MySQL索引

      在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数...

    陈哈哈
  • Intellij IDEA中如何在右侧展示类中的方法树形结构(类似Eclipse中右侧的Outline)

    当然,也可以直接Alt+F7快捷键,这样默认会把Structure显示在屏幕下方,如下图操作就可以移动到右侧。

    陈哈哈
  • 人工智能 - 语音识别的技术原理是什么

    转自:https://www.zhihu.com/question/20398418/answer/18080841

    陈哈哈
  • MySQL中MyISAM与InnoDB存储的区别

    程序你好
  • MySQL有哪些存储引擎,各自的优缺点,应用场景

    经常面试都会问到MYSQL有哪些存储引擎,以及各自的优缺点。今天主要分享常见的存储引擎:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB...

    Java
  • MySQL MyISAM和InnoDB存储引擎的比较

    MyISAM是MySQL的默认存储引擎,基于传统的ISAM类型,支持全文搜索,但不是事务安全的,而且不支持外键。每张MyISAM表存放在三个文件中:frm 文件...

    用户7657330
  • 初识 MySQL

    MySQL 是一种关系型数据库,是开源免费的,且方便扩展,任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要...

    happyJared
  • 谷歌云平台竞争力或大幅增强 全面下调价格

    长期以来,在面向个人或企业的云平台服务商中,微软和亚马逊一直拥有较大的领先优势,而鉴于云平台对于数据处理的重要性,谷歌也在持续努力打造自己的云端平台来增强竞争力...

    静一
  • H3C AP胖转瘦方法大全

    py3study
  • MySQL 加锁处理分析

    背景 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信...

    java达人

扫码关注云+社区

领取腾讯云代金券