理解Mysql常见的两种存储引擎：MyISAM和InnoDB |
您所在的位置：网站首页 › myisam是什么意思 › 理解Mysql常见的两种存储引擎：MyISAM和InnoDB |
|
📖前言
知道的越多不知道的就越多 编程开发这条路上的知识是无穷无尽的，就像以前你敢说精通Java，到后来学到越来越多只想写了解Java，过了几年现在可能想说懂一点点Java。当视野和格局的扩大，会让我们越来越发现原来的看法是多么浅显，这就像站在地球看地球和站在宇宙看地球一样。但正因为胸怀和眼界的提升让我们有了更多的认识，也逐渐学会了更多的技能。虽然不知道的越来越多，但也因此给自己填充了更多的技术栈，让自己越来越强大。 🚀Mysql 常见的存储引擎！主要存储引擎以及功能如下： 功能MylSAMMEMORYInnoDBArchive存储限制256TBRAM64TBNone支持事务NoNoYesNo支持全文索引YesNoYesNo支持树索引YesYesYesNo支持哈希索引NoYesYesNo支持数据缓存NoN/AYesNo支持外键NoNoYesNoInnoDB 支持的哈希索引是自适应的，InnoDB 会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。 MySQL 5.6 开始 InnoDB 支持全文索引。 🎎MyISAM 和 InnoDBMyISAM 是 MySQL 的默认数据库引擎（5.5版之前），由早期的 ISAM（Indexed Sequential Access Method ：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL 也导入了 InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代 MyISAM。 InnoDB ，是 MySQL 的数据库引擎之一，为 MySQL AB 发布 binary 的标准之一。InnoDB 由 Innobase Oy公司 所开发，2006年五月时由甲骨文公司并购。与传统的 ISAM与MyISAM 相比，InnoDB 的最大特色就是 支持了ACID兼容的事务（Transaction） 功能，类似于 PostgreSQL。目前 InnoDB 采用 双轨制授权，一是 GPL授权，另一是 专有软件授权。 🤳MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件 存储表定义。数据文件的扩展名为 .MYD (MYData)。 MyISAM 表格可以被压缩，而且它们支持全文搜索。不支持事务，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。在进行 updata 时进行表锁，并发量相对较小。如果执行大量的 SELECT，MyISAM 是更好的选择。 MyISAM 的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而 Innodb 是索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 体积庞大不小 MyISAM 缓存在内存的是索引，不是数据。而 InnoDB 缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大。 优点：查询数据相对较快，适合大量的 select ，可以全文索引。缺点：不支持事务，不支持外键，并发量较小，不适合大量 update 🐱🏍InnoDB：这种类型是事务安全的。.它与BDB类型具有相同的特性,它们还支持外键。InnoDB 表格速度很快。具有比 BDB 还丰富的特性,因此如果需要一个事务安全的存储引擎，建议使用它。在 update 时表进行行锁，并发量相对较大。如果你的数据执行大量的 INSERT或UPDATE ，出于性能方面的考虑，应该使用 InnoDB 表。 优点：支持事务，支持外键，并发量较大，适合大量 update缺点：查询数据相对较快，不适合大量的 select对于支持事物的 InnoDB 类型的表，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动 Commit ，严重影响了速度。可以在 执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。 基本的差别为：MyISAM 类型不支持事务处理等高级处理，而 InnoDB类型支持。MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务支持，而 InnoDB 提供事务支持已经外部键等高级数据库功能。 🎶其他比较：MyIASM 是 IASM 表的新版本，有如下扩展： 二进制层次的可移植性。NULL 列索引。对变长行比 ISAM表 有更少的碎片。支持大文件。更好的索引压缩。更好的键吗统计分布。更好和更快的 auto_increment 处理。以下是一些细节和具体实现的差别： InnoDB 不支持 FULLTEXT 类型的索引。InnoDB 中不保存表的具体行数，也就是说，执行 select count(*) from table 时，InnoDB 要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数 即可。注意的是，当 count(*) 语句包含 where 条件时，两种表的操作是一样的。对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中，可以和其他字段一起建立联合索引。DELETE FROM table时，InnoDB 不会重新建立表，而是一行一行的删除。LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的，解决方法是首先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但是对于使用的额外的 InnoDB特性（例如外键） 的表不适用。另外，InnoDB 表的行锁也不是绝对的，如果在执行一个SQL语句时 MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表，例如 update table set num=1 where name like “%aaa%” 🚀MyISAM 和 InnoDB 两者的应用场景？在使用中应该根据以下的原则来选择 MySQL 存储引擎： 如果你的应用程序一定要使用事务，毫无疑问你要选择 INNODB 引擎。但要注意，INNODB 的行级锁是有条件的。在 where 条件没有使用主键时，照样会锁全表。比如 DELETE FROM mytable 这样的删除语句。如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。MyISAM 管理非事务表。它提供 高速存储和检索，以及 全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那 MyISAM 是更好的选择。InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID事务支持。如果应用中需要执行大量INSERT或UPDAT操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。 🎊在实际场景要考虑的：针对具体问题具体分析，一般而言可以遵循以下几个问题： 数据库是否有外键是否需要事务支持是否需要全文索引数据库经常使用什么样的查询模式？在写多读少的应用中还是 InnoDB 插入性能更稳定，在并发情况下也能基本，如是对读取速度要求比较快的应用还是选 MyISAM。数据库的数据有多大？ 大尺寸倾向于 InnoDB，因为事务日志，故障恢复。注意：使用哪一种引擎要根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。 💕InnoDB 和 MylSAM 比较和理解 InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一； 📖InnoDB 和 MylSAM对比表 MyISAMInnoDB构成上的区别：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB事务处理上方面 :MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能SELECT UPDATE,INSERT ， Delete 操作如果执行大量的SELECT，MyISAM是更好的选择1. 如果你的数据执行大量的INSERT 或 UPDATE，出于性能方面的考虑，应该使用InnoDB表 2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用对 AUTO_INCREMENT 的操作每表一个AUTO_INCREMEN列的内部处理。 MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 更好和更快的auto_increment处理如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上 关于该计算器的算法实现，请参考 AUTO_INCREMENT 列在 InnoDB 里如何工作表的具体行数select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行锁表锁提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%” 🐱🏍《MySQL高性能》一书上面有一句话这样写到:不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中， InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。 一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。 PS：兄弟们多看书总没错，哪怕没有实际用过也能说出来个一二三四，当你真的使用了你的理解会更加深刻，深沉就像 “初恋一般”。 🎉总结如果你的应用程序对查询性能要求较高，就要使用 MYISAM 了。MYISAM 索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于 INNODB 。压缩后的索引也能节约一些磁盘空间。MYISAM 拥有全文索引的功能，这可以极大地优化 LIKE 查询的效率。 有人说 MYISAM 只能用于小型应用，其实这只是一种偏见。如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，而不是单纯地依赖存储引擎。 任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。 对于 MyISAM 和 InnoDB 的选择只能说根据需要走吧，仁者见仁智者见智只有在实际中用到了才能阐述更多。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |