MyISAM 与 InnoDB 的 BI/批量查询性能（即_NOT_ OLTP）
当然，对于事务数据库来说，InnoDB 是一个完美的选择。 MyISAM 不支持事务或行级锁定。
但是，如果我想要执行涉及数亿行的大型批量查询该怎么办？
MyISAM 在哪些方面比 InnoDB 具有相对优势？
例如，我知道的一个（次要）...“select count(*) from my_table;” MyISAM 立即知道答案，而 InnoDB 可能需要一分钟或更长时间才能做出决定。
---戴夫
Sure, for a transactional database InnoDB is a slam dunk. MyISAM doesn't support transactions or row-level locking.
But what if I want to do big nasty batch queries that touch hundreds of millions of rows?
Are there areas where MyISAM has relative advantage over InnoDB??
eg, one (minor) one that I know of ... "select count(*) from my_table;" MyISAM knows the answer to this instantly whereas InnoDB may take a minute or more to make up its mind.
--- Dave
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
MyISAM 对于非常大的数据集可以更好地扩展。 InnoDB在很多情况下都优于MyISAM，直到它无法将索引保留在内存中，然后性能急剧下降。
MyISAM 还支持 MERGE 表，这是一种“穷人的”分片。您可以立即添加/删除非常大的数据集。例如，如果每个业务季度有 1 个表，则可以创建最近 4 个季度、特定年份或任何所需范围的合并表。您无需通过导出、删除和导入来移动数据，只需重新声明基础 MERGE 表内容即可。由于表的名称没有更改，因此不需要更改代码。
当您仅向表添加内容时，MyISAM 也更适合日志记录。与 MERGE 表一样，您可以轻松地换出（轮换“日志”）表和/或复制它。
您可以将与MyISAM表关联的DB文件复制到另一台计算机上，然后将它们放在MySQL数据目录中，MySQL会自动将它们添加到可用表中。 InnoDB 无法做到这一点，您需要导出/导入。
这些都是具体情况，但我已经多次利用过每一种情况。
当然，通过复制，您可以同时使用两者。表可以在主服务器上是 InnoDB，在从服务器上可以是 MyISAM。结构必须相同，而不是表类型相同。然后您就可以充分利用两者。 BLACKHOLE 表类型就是这样工作的。
MyISAM scales better with very large datasets. InnoDB outperforms MyISAM in many situations until it can't keep the indexes in memory, then performance drop drastically.
MyISAM also supports MERGE tables, which is sort of a "poor man's" sharding. You can add/remove very large sets of data instantaneously. For example, if you have 1 table per business quarter, you can create a merge table of the last 4 quarters, or a specific year, or any range you want. Rather than exporting, deleting and importing to shift data around, you can just redeclare the underlying MERGE table contents. No code change required since the name of the table doesn't change.
MyISAM is also better suited for logging, when you are only adding to a table. Like MERGE tables, you can easily swap out (rotate "logs") a table and/or copy it.
You can copy the DB files associated with a MyISAM table to another computer and just put them in the MySQL data directory and MySQL will automatically add them to the available tables. You can't do that with InnoDB, you need to export/import.
These are all specific cases, but I've taken advantage of each one a number of times.
Of course, with replication, you could use both. A table can be InnoDB on the master and MyISAM on the slave. The structure has to be the same, not the table type. Then you can get the best of both. The BLACKHOLE table type works this way.
这是一篇很棒的文章，比较了各种性能点 http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ - 你必须评估这个从很多角度，包括您打算如何编写查询以及您的架构是什么样子。这根本不是一个非黑即白的问题。
Here's a great article comparing various performance points http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ - you'll have to evaluate this from quite a few angles, including how you intend to write your queries and what your schema looks like. It's simply not a black and white question.
根据 这篇文章，从v5.6开始，InnoDB已经发展到了在所有场景下都比较优秀的地步。作者可能有点偏见，但它清楚地概述了哪些技术被视为该平台的未来方向。
According to this article, as of v5.6, InnoDB has been developed to the point where it is better in all scenarios. The author is probably a bit biased, but it clearly outlines which tech is seen as the future direction of the platform.