资讯专栏INFORMATION COLUMN

MySQL中InnoDB和MyISAM的存储引擎区别

PingCAP / 3065人阅读

摘要:作为,我们应该深刻的认识存储引擎。今天介绍两种最常见的存储引擎和它们的区别和。存储引擎存储引擎不支持事务表锁设计,支持全文索引,主要面向联机分析处理应用,适用于数据仓库等查询频繁的场景。从版本,版本后,两者都支持全文索引。

MySQL数据库区别于其他数据库的很重要的一个特点就是其插件式的表存储引擎,其基于表,而不是数据库。由于每个存储引擎都有其特点,因此我们可以针对每一张表来挑选最合适的存储引擎。

作为DBA,我们应该深刻的认识存储引擎。今天介绍两种最常见的存储引擎和它们的区别:InnoDBMyISAM

InnoDB存储引擎

InnoDB存储引擎支持事务,其设计目标主要就是面向OLTP(On Line Transaction Processing 在线事务处理)的应用。特点为行锁设计、支持外键,并支持非锁定读。从5.5.8版本开始,InnoDB成为了MySQL的默认存储引擎。

InnoDB存储引擎采用聚集索引(clustered)的方式来存储数据,因此每个表都是按照主键的顺序进行存放,如果没有指定主键,InnoDB会为每行自动生成一个6字节的ROWID作为主键。

MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计,支持全文索引,主要面向OLAP(On Line Analytical Processing 联机分析处理)应用,适用于数据仓库等查询频繁的场景。在5.5.8版本之前,MyISAMMySQL的默认存储引擎。该引擎代表着对海量数据进行查询和分析的需求。它强调性能,因此在查询的执行速度比InnoDB更快。

InnoDBMyISAM的区别 事务

为了数据库操作的原子性,我们需要事务。保证一组操作要么都成功,要么都失败,比如转账的功能。我们通常将多条SQL语句放在begincommit之间,组成一个事务。

InnoDB支持,MyISAM不支持。

主键

由于InnoDB的聚集索引,其如果没有指定主键,就会自动生成主键。
MyISAM支持没有主键的表存在。

外键

为了解决复杂逻辑的依赖,我们需要外键。比如高考成绩的录入,必须归属于某位同学,我们就需要高考成绩数据库里有准考证号的外键。

InnoDB支持,MyISAM不支持。

索引

为了优化查询的速度,进行排序和匹配查找,我们需要索引。比如所有人的姓名从a-z首字母进行顺序存储,当我们查找zhangsan或者第44位的时候就可以很快的定位到我们想要的位置进行查找。

InnoDB是聚集索引,数据和主键的聚集索引绑定在一起,通过主键索引效率很高。如果通过其他列的辅助索引来进行查找,需要先查找到聚集索引,再查询到所有数据,需要两次查询。

MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据的指针。

InnoDB 1.2.x版本,MySQL5.6版本后,两者都支持全文索引。

auto_increment自增

对于自增数的字段,InnoDB要求该列必须是索引,同时必须是索引的第一个列,否则会报错:

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

(b,a)顺序替换为(a,b)即可。

MyISAM可以将该字段与其他字段随意顺序组成成联合索引。

表行数

很常见的需求是看表中有多少条数据,此时我们需要select count(*) from table_name

InnoDB不保存表行数,需要进行全表扫描。MyISAM用一个变量保存,直接读取该值,更快。当时当带有where查询的时候,两者一样。

存储

数据库的文件都是需要在磁盘中进行存储,当应用需要时再读取到内存中。一般包含数据文件、索引文件。

InnoDB分为:

.frm表结构文件

.ibdata1共享表空间

.ibd表独占空间

.redo日志文件

MyISAM分为三个文件:

.frm存储表定义

.MYD存储表数据

.MYI存储表索引

执行速度

如果你的操作是大量的查询操作,如SELECT,使用MyISAM性能会更好。
如果大部分是删除和更改的操作,使用InnoDB

InnoDBMyISAM的索引都是B+树索引,通过索引可以查询到数据的主键,不熟悉B+树的可以查看MySQL InnoDB索引原理和算法。两者的性能区别主要在于查询到数据主键后两者的处理方式却不同。

InnoDB会缓存索引和数据文件,一般以16KB为一个最小单元(数据页大小)和磁盘进行交互,InnoDB在查询到索引数据后实际得到的是主键的ID,它需要在内存中的数据页中查找该行的全部数据,但如果该数据不是加载过的热数据,还需要进行数据页的查找和替换,这其中可能牵涉到多次I/O操作和内存中数据查找,导致耗时较高。

MyISAM存储引擎只缓存索引文件,不缓存数据文件,其数据文件的缓存直接使用操作系统的缓存,这点非常独特。此时相同的空间能够加载更多的索引,因此当缓存空间有限时,MyISAM的索引数据页替换次数会更少。根据前面我们知道MyISAM的文件分为MYIMYD,当我们通过MYI查找到主键ID时,其实得到是MYD数据文件的offset偏移量,查找数据比InnoDB寻址映射要快的多。

但由于MyISAM是表锁,而InnoDB支持行锁,因此在牵涉到大量写操作时,InnoDB的并发性能比MyISAM好很多。同时InnoDB还通过MVVC多版本控制来提高并发读写性能。

delete删除数据

调用delete from table时,MyISAM会直接重建表,InnoDB会一行一行的删除,但是可以用truncate table代替。参考: mysql清空表数据的两种方式和区别。

MyISAM仅支持表锁,每次操作锁定整张表。
InnoDB支持行锁,每次操作锁住最小数量的行数据。

表锁相比于行锁消耗的资源更少,且不会出现死锁,但同时并发性能差。行锁消耗更多的资源,速度较慢,且可能发生死锁,但是因为锁定的粒度小、数据少,并发性能好。如果InnoDB的一条语句无法确定要扫描的范围,也会锁定整张表。

当行锁发生死锁的时候,会计算每个事务影响的行数,然后回滚行数较少的事务。

数据恢复

MyISAM崩溃后无法快速的安全恢复。InnoDB有一套完善的恢复机制。

数据缓存

MyISAM仅缓存索引数据,通过索引查询数据。InnoDB不仅缓存索引数据,同时缓存数据信息,将数据按页读取到缓存池,按LRU(Latest Rare Use 最近最少使用)算法来进行更新。

如何选择存储引擎

创建表的语句都是相同的,只有最后的type来指定存储引擎。

MyISAM

大量查询总count

查询频繁,插入不频繁

没有事务操作

InnoDB

需要高可用性,或者需要事务

表更新频繁

参考资料

MySQL InnoDB索引原理和算法:https://segmentfault.com/a/11...

《MySQL技术内幕 InnoDB存储引擎》 1.3节

mysql清空表数据的两种方式和区别:https://segmentfault.com/a/11...

Mysql 中 MyISAM 和 InnoDB 的区别有哪些?:https://www.zhihu.com/questio...

MySQL存储引擎MyISAM与InnoDB区别总结整理:https://blog.csdn.net/xlgen15...

MySQL InnoDB的存储文件:https://blog.csdn.net/chenjia...

InnoDB数据页结构:https://juejin.im/post/5cb3e3...

MySQL中MyISAM的查询为什么比InnoDB快:https://blog.csdn.net/xmtblog...

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/49304.html

相关文章

  • MySQL存储引擎--MyISAMInnoDB区别

    摘要:,是的数据库引擎之一,为发布的标准之一。目前采用双轨制授权,一是授权,另一是专有软件授权。文件存储表定义。索引文件的扩展名是。在备份和恢复时可单独针对某个表进行操作。支持事务和行级锁,是的最大特色。 MyISAM是MySQL的默认数据库引擎(5.5版之前),由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良。虽然性能极佳,但...

    tomener 评论0 收藏0
  • MySQL存储引擎MyISAMInnoDB区别

    摘要:基本的差别为类型不支持事务处理等高级处理,而类型支持。对于类型的字段,中必须包含只有该字段的索引,但是在表中,可以和其他字段一起建立联合索引。而不支持所以往往就容易被人认为只适合在小项目中使用。 MyISAM 和InnoDB 讲解  InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理...

    lufficc 评论0 收藏0
  • MySQL性能调优与架构设计(二)—— MySQL存储引擎简介

    摘要:的插件式存储引擎主要包括等。和在严格意义来说,并不能算作一个存储引擎。实现外键实现了外键引用这一数据库的重要特性,使在数据库端控制部分数据的完整性成为可能。 什么是存储引擎 mysql中的数据用各种不同的技术存储在文件(或者内存)中。 这些技术中每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术,你能够获得额外的速度和功能,从而...

    muzhuyu 评论0 收藏0
  • Mysql索引实现原理

    摘要:下图是索引的原理图这里设表一共有三列,假设我们以为主键,则图是一个表的主索引示意。在中,主索引和辅助索引在结构上没有任何区别,只是主索引要求是唯一的,而辅助索引的可以重复。 Mysql的索引讨论 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。 MyISAM索引实现 MyISAM引擎使用...

    Nino 评论0 收藏0
  • MySQL 索引

    摘要:另外,只有字段类型为和的字段才能设置全文索引。在中,主索引和辅助索引在结构上没有任何区别,只是主索引要求是唯一的,而辅助索引的可以重复。这个索引的是数据表的主键,因此表数据文件本身就是主索引。 索引原理 我们知道,MySQL 查询数据是从第一条记录开始依次查找,直到读完整个表或者找到匹配的行。数据库表的数据量越大,MySQL 查询所花费的时间就越多。索引的出现就是为了改善查询性能的。M...

    Salamander 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<