如何更安全地使用MyISAM表？
我喜欢InnoDB的安全性、一致性和自检查性。
但我需要MyISAM的速度和轻量。
如何使 MyISAM 不易因崩溃、错误数据等而损坏？ 检查需要很长时间（CHECK TABLE 或 myisamchk）。
我并不是要求事务安全性——这就是 InnoDB 的用途。 但我确实想要一个可以快速重新启动的数据库，而不是几个小时（或几天！）后。
更新：我不是问如何更快地将数据加载到表中。 我已经对此进行了认真的思考，并确定使用 MyISAM 表来加载数据会更快。 我现在所追求的是降低使用 MyISAM 表的风险。 也就是说，减少损坏的机会，提高恢复速度。
I like InnoDB's safety, consistency, and self-checking.
But I need MyISAM's speed and light weight.
How can I make MyISAM less prone to corruption due to crashes, bad data, etc.? It takes forever to go through a check (either CHECK TABLE or myisamchk).
I'm not asking for transactional security -- that's what InnoDB is for. But I do want a database I can restart quickly rather than hours (or days!) later.
UPDATE: I'm not asking how to load data into tables faster. I've beat my head against that already, and determined that using the MyISAM tables for my LOAD DATA is simply much faster. What I'm after now is mitigating the risks of using MyISAM tables. That is, reducing chances of damage, increasing speed of recovery.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
购买一个好的 UPS，具有良好的电源调节功能。 在稳定且冗余的硬件上运行。
我不相信 MyISAM 表能够在写入过程中发生崩溃，因此我认为最好的选择是减少崩溃（和写入）的发生。
Get a good UPS, with decent power conditioning. Run on stable and redundant hardware.
I don't trust MyISAM tables to ever survive a crash during a write, so I think your best bet is on reducing the occurrence of crashes (and writes).
这实际上很大程度上取决于您如何使用表格。 如果它们写入量很大，那么您可能需要考虑删除索引，这将加快恢复时间。 如果它们的读取量很大，您可能需要考虑使用复制，它将序列化所有对表的写入，从而最大限度地减少崩溃后读取副本的恢复时间。
您可以做的就是写入表的 InnoDB 副本，然后复制到 MyISAM 副本。 无论如何，MyISAM 的性能优势主要是面向读的。
当然，使用复制，读取和写入之间会有延迟时间
This really depends a lot on how your use of the tables. If they are write heavy, then you may want to consider removing indexes, which will speed up the recovery time. If they are read heavy, you may want to consider using replication which will serialise all writes to your tables, minimising the recovery time for your read copy after a crash.
Once thing you could do is write to an InnoDB copy of the table, and then replicate to a MyISAM copy. The performance benefits of MyISAM are mostly read-oriented anyway.
Using replication of course, you will have lag time between reads and writes
你和 MySQL 结婚了吗？ Postgres 符合 ACID（如 innoDB）并且（经过精心调优）几乎与 MyISAM 一样快。
Are you married to MySQL? Postgres is ACID-compliant (like innoDB) and (when well-tuned) nearly as speedy as MyISAM.
你的评论：
建议删除约束和索引，然后在加载后启用/重建它们可能会显着加快速度 - 我假设您尝试过？ 这有改善吗？
Your comment:
suggests dropping constraints and indexes, then enabling / rebuilding them after the load may significantly speed it up- I assume you tried that? Did that improve things?
虽然我同意 innodb 的评论，但我将为您的 MyISAM 问题提供解决方案。
防止损坏和提高速度的一个好方法是使用 MERGE 表
您可以使用 2 个或更多 MyISAM 文件。 一种通常用于备份不经常使用的旧数据，另一种是较新的数据。 然后您的硬盘上将有 2 个 FRM（MyISAM 表文件），其中一个将受到保护。 通常，您压缩旧的MyISAM表，然后它们会挑衅地不会被损坏，因为它们变为只读。
此技术通常用于加速大型 MyISAM 表的速度，但您也可以在这里应用它。
希望对您的问题有所帮助。 虽然我意识到它并没有真正帮助 MyISAM 防崩溃，但它确实提供了相当多的保护。
While I agree with the innodb comments, I will give a solution to your MyISAM problem.
A good way to prevent corruption and increasing speed would be to use MERGE tables
You can use 2 or more MyISAM files. One is usually for backup'd old data that isn't used that often and the other is newer data. Then you will have 2 FRM (the MyISAM table files) on your harddisk and one will be protected. Usually you compress the old MyISAM tables and then they will defiantly not be corrupted, since they become read-only.
This technique is usually used to speed up big MyISAM tables, but you can apply it here as well.
Hope that helped your question. While I realize it didn't really help crash-proof MyISAM, it does give quite a bit of protection.
在正常实践中，你不应该腐败。 如果你遇到了损坏，你需要查看诸如内存故障、硬盘驱动器故障、驱动器控制器故障或者可能是 mysql 错误等问题。
如果你想避开这一切，你可以设置一个复制从站。 当主服务器死亡时，停止从服务器上的复制并使其成为新的主服务器。 清除旧主机上的数据并将其设置为从机。 用户停机时间将仅限于检测主设备死亡并启动从设备所需的时间。
这还有一个额外的好处，那就是成为实现零停机备份的好方法：关闭从属进程并备份从属进程。
in normal practice, you shouldn't get corruption. if you are getting corruption, you need to look at things like bad memory, bad hard drive, bad drive controller, or possibly a mysql bug.
if you want to side-step all that, you could set up a replication slave. when the master dies, stop the replication on the slave and make it your new master. the clear the data off your old master and set it up as a slave. user down-time will be limited to the amount of time it takes to detect that the master died and bring the slave up.
this has the added benefit of being a good way to achieve a zero-downtime backup: shut down the slave process and back up the slave.
在某些情况下，MyISAM 的性能优势实际上非常小； 您需要对自己的应用程序 MyISAM 与 InnoDB 进行基准测试。 专门使用 InnoDB 事务引擎还具有其他好处。
在我的测试中，InnoDB 通常会比 MyISAM 多使用大约 150% 的磁盘空间 - 这是因为它的块结构和缺乏索引压缩。
如果你负担得起的话，就使用 InnoDB 吧。
就回答您的实际问题而言：如果您将表分区为多个 MyISAM 表，则崩溃时所需的修复量会少得多； 如果您的数据很大，出于其他原因这可能是个好主意。
The performance advantages of MyISAM are actually pretty minimal in some cases; you need to benchmark your own application MyISAM vs InnoDB. Using the InnoDB transactional engine exclusively gives other benefits too.
In my testing InnoDB will use up typically about 150% more disc space than MyISAM- this is because of its block structure and lack of index compression.
If you can afford it, just use InnoDB instead.
As far as answering your actual question goes: If you partition your table into multiple MyISAM tables, the amount of repair needed in a crash will be much less; if your data are large, this might be a good idea anyway for other reasons.
MyISAM 所谓的速度优势实际上很快就会消失 - 事实上，它缺乏行级锁定，这意味着小的更新可能会导致大量数据被锁定，查询被阻塞。 正因为如此，我对声称的 MyISAM 速度优势持怀疑态度：开始进行多次更新，每秒的查询量将会下降。
我认为你最好问“如何使 InnoDB 支持的应用程序变得更快？” 然后答案是处理缓存数据，也许是在对象级别，在轻量级缓存中——ACID 是有成本的，而对于 Web 应用程序来说，它并不是真正需要的。
如果更新很少发生（如果不是，MyISAM 就不是一个好的选择），那么您甚至可以使用 MySQL 查询缓存。
memcached (http://www.danga.com/memcached/) 是一个非常受欢迎的选项对象缓存。 根据您的应用程序，您还有其他选项（HTTP 缓存等）
MyISAM's supposed speed benefits can actually go away pretty quickly - the fact that it lacks row-level locking means small updates can cause large amounts of data to be locked, and queries to block. Because of that, I'm skeptical of claimed MyISAM speed benefits: start doing several UPDATEs, and the queries per second will tank.
I think you're better off asking "How can applications backed with InnoDB be made faster?" and the answer then deals with caching data, perhaps at the object level, in lightweight caches - there is a cost for ACID, and for, say, web applications, it's not really needed.
If UPDATEs are rare (if they aren't, MyISAM isn't a good choice) then you can even use the MySQL query cache.
memcached (http://www.danga.com/memcached/) is a very popular option for object caching. Depending on your application you have other options as well (HTTP caches, etc.)