MyISAM 与 InnoDB [英] MyISAM versus InnoDB

问题描述

我正在处理一个涉及大量数据库写入的项目，我会说(70% 插入和 30% 读取).这个比率还包括我认为是一次读取和一次写入的更新.读取可能很脏(例如，我在读取时不需要 100% 准确的信息).
有问题的任务将每小时处理超过 100 万次数据库事务.

我在网上阅读了很多关于 MyISAM 和 InnoDB 之间差异的内容，对于我将用于此任务的特定数据库/表，MyISAM 似乎是我的明显选择.从我似乎正在阅读的内容来看，如果需要事务，InnoDB 是很好的，因为支持行级锁定.

有人对这种类型的负载(或更高)有任何经验吗?MyISAM 是要走的路吗?

解决方案

我有简要的 在表格中讨论了这个问题，以便您可以决定是使用 InnoDB 还是 MyISAM.

以下是您应该在哪种情况下使用哪个数据库存储引擎的小概述:

<前>MyISAM InnoDB----------------------------------------------------------------需要全文检索 是 5.6.4----------------------------------------------------------------需要交易 是----------------------------------------------------------------频繁选择查询 是----------------------------------------------------------------频繁插入、更新、删除 是----------------------------------------------------------------行锁定(单表多处理) 是----------------------------------------------------------------关系基础设计 是

总结

• 几乎在所有情况下，InnoDB 都是最好的选择
• 但是，经常阅读，几乎不写，使用MyISAM
• 在 MySQL <= 5.5 中进行全文搜索，使用 MyISAM

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read).
The task in question will be doing over 1 million database transactions an hour.

I've read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I'll be using for this task. From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)? Is MyISAM the way to go?

解决方案

I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM.

Here is a small overview of which db storage engine you should use in which situation:

                                                 MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                                      Yes
----------------------------------------------------------------
Frequent select queries                          Yes      
----------------------------------------------------------------
Frequent insert, update, delete                           Yes
----------------------------------------------------------------
Row locking (multi processing on single table)            Yes
----------------------------------------------------------------
Relational base design                                    Yes

Summary

• In almost all circumstances, InnoDB is the best way to go
• But, frequent reading, almost no writing, use MyISAM
• Full-text search in MySQL <= 5.5, use MyISAM

这篇关于MyISAM 与 InnoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！