我应该使用 MyISAM 还是 InnoDB（或其他）？
我有一个在线游戏，它是 PHP 和 PHP 的。基于 MySQL（PHP 5.2.9 和 MySQL 5.0.91）。最近，当我删除表中的数千行旧数据时，我遇到了整个网站有时会在特定时间冻结的问题。我还遇到了一些问题，当查询必须等待我假设的表锁并且游戏无法按预期运行时，会发生奇怪的事情。
我的所有表都是 MyISAM，它每秒运行超过 900 个查询。在整个数据库（约 150 个表）上，88% 的查询是读取，只有 12% 是写入，但少数表接近 50/50，并且每秒从不同客户端多次读取和写入大量数据（这是一个多人游戏）。这些表还存储 1M-5M 行。
我知道 MyISAM 应该读取速度更快，但 InnoDB 不必在写入时锁定整个表。我已经在这里和其他网站上浏览了很多主题，但我仍然不确定如何解决这些问题。
I have an online game that is PHP & MySQL based (PHP 5.2.9 & MySQL 5.0.91). Lately I've been having issues where the entire site freezes sometimes during specific times when I'm deleting several thousand rows of old data in tables. I have also been having issues where strange things happen when queries have to wait for what I assume is a table lock and the game doesn't function as expected.
All of my tables are MyISAM and it runs over 900 queries per second. On the entire database (~150 tables), 88% of queries are reads and only 12% writes, but a few of the tables are closer to 50/50 and have a lot of data read and written multiple times per second from various clients (this is a multiplayer game). These tables also store anywhere from 1M-5M rows.
I know that MyISAM is supposed to read faster, but InnoDB doesn't have to lock the entire table on writes. I've gone through a bunch of topics on here and other sites, but I'm still not sure what to do to solve these issues.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MyISAM 的最大问题是它对 UPDATE 和 DELETE 命令使用表级锁定，因此如果您要从数据库中删除一条记录，则在该查询运行时，所有其他更新或删除都会阻塞。
InnoDB 使用行级锁定，因此只有正在更新的记录才会阻塞，因为显而易见的原因应该如此。
InnoDB 还具有许多有用的功能，例如对事务和外键约束的支持，这使其成为我自己和其他开发人员的首选引擎。
就读取速度而言，InnoDB 按主键排序存储其记录，这使得检索通过 PK 搜索的记录的速度更快（例如 SELECT foo FROM bar WHERE id = baz; ）。 MyISAM 或多或少按照添加到数据库的顺序存储其记录，这意味着如果您主要按添加记录的日期进行搜索，例如财务跟踪之类的内容，它可能会更快您可能想要获取某一天发生的所有交易。
Wikipedia 详细介绍了这两个引擎之间的许多主要差异，但足以说明这一点在 99% 的情况下，您可能想使用 InnoDB。
我能想到的唯一情况是 MyISAM 明显获胜，如果您需要支持全文搜索。 MyISAM 支持 FTS，但 InnoDB 不支持。即便如此，您可能最好使用第三方系统（例如 Sphinx），而不是使用 MyISAM。
The biggest problem with MyISAM is that it uses table level locking for UPDATE and DELETE commands, so if you're deleting a record from your database, every other update or delete will block while that query runs.
InnoDB uses row-level locking, so only the record being updated will block, as it should be for obvious reasons.
InnoDB also has a lot of useful features like support for transacions and foreign key constraints, which makes it the engine of choice for myself and other developers.
As far as read speeds are concerned, InnoDB stores its record ordered by the primary key, which makes it a lot faster to retrieve records where you're searching by the PK (things like
SELECT foo FROM bar WHERE id = baz;). MyISAM stores its records more or less in the order they are added to the database, which means it can be faster if you're mostly searching by the date records are added, for example things like financial tracking where you could want to grab all transactions which occurred on a certain day.Wikipedia details a lot of the major differences between the two engines, but suffice it to say that in 99% of cases, you probably want to use InnoDB.
The only circumstance I can think of here MyISAM has a clear victory is if you need support for Full-Text Search. MyISAM supports FTS, but InnoDB does not. Even then, you're probably better off using a third party system, like Sphinx, rather than use MyISAM.
如果您有任何类型的复杂数据模型，并且您不得不使用 MySQL 或其众多分支之一，那么请使用支持外键引用的事务存储引擎。否则，您将不得不重新发明数据完整性“轮子”，这并不有趣，而且容易出错（特别是当您发现数据到处都存在完整性问题，并且您缺少正确的信息来解决问题时）。 如果您能够这样
做，我建议您使用 PostgreSQL 之类的工具，因为如果您的应用程序增长到所需的容量，您仍然可以平衡服务器之间的负载。 。
If you have any sort of complex data model, and you are compelled to use MySQL or one of its great many forks, then use a transactional storage engine that supports foreign key references. Otherwise you will have to reinvent the data integrity "wheels" all over again, and that is not fun and is error prone (especially when you find that your data has integrity problems all over the place, and you're missing the proper information to put it back together.
I'd suggest using something like PostgreSQL, if you are able to do so, because such robustness features are built-in. You can still balance load between servers if your application grows to such a volume that it is required.