大型MySQL表 [英] Large MySQL tables

问题描述

对于正在开发的Web应用程序，我需要存储大量的记录。每个记录将由一个主键和一个（简短的ish）字符串值组成。我希望有大约100GB的存储空间，并希望能够使用它。

For a web application I'm developing, I need to store a large number of records. Each record will consist of a primary key and a single (short-ish) string value. I expect to have about 100GB storage available and would like to be able to use it all.

记录将被插入，删除和读取频繁，我必须使用一个MySQL数据库。数据的完整性并不重要，但性能却是如此。我可能遇到什么问题和陷阱，哪个存储引擎最适合这项任务？

The records will be inserted, deleted and read frequently and I must use a MySQL database. Data integrity is not crucial, but performance is. What issues and pitfalls am I likely to encounter and which storage engine would be best suited to the task?

非常感谢，J

推荐答案

无论您使用什么解决方案，由于您说您的数据库将会变得很重，您需要确保整个表不会被写入锁定。这排除了有些人建议的MyISAM。 MyISAM将锁定表上的更新，删除或插入。这意味着任何想要从表中读取的客户端都必须等待写入完成。 Dunno什么是INSERT LOW PRIORITY做的，可能有一些在桌面锁定： - ）

Whatever solution you use, since you say your database will be write-heavy you need to make sure the whole table doesn't get locked on writes. This rules out MyISAM, which some have suggested. MyISAM will lock the table on an update,delete or insert. That means any client who wants to read from the table will have to wait for the write to finish. Dunno what the INSERT LOW PRIORITY does though, probably some hack around table-locking :-)

如果你只需要使用MySQL，你会想要InnoDB， t锁定写。我不知道MySQL如何使用VACUUM的InnoDB表（InnoDB是像PostgreSQL这样的MVCC，因此需要清理）...但是如果您正在做很多更新或删除，那么您必须考虑到这一点。

If you simply must use MySQL, you'll want InnoDB, which doesn't lock on write. I dunno how MySQL does VACUUM's InnoDB tables (InnoDB is MVCC like PostgreSQL and so needs to clean up)... but you'll have to take that into consideration if you are doing a lot of updates or deletes.

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