MySQL MEMORY 存储引擎的替代方案
我当前正在对 MyISAM 表运行一些密集的 SELECT 查询。 该表大约有 100 MiB（800,000 行）并且永远不会改变。
我需要提高脚本的性能，因此我正在考虑将表从 MyISAM 移动到 MEMORY 存储引擎，这样我就可以将其完全加载到内存中。
除了 MEMORY 存储引擎之外，我还可以选择哪些选项将 100 MiB 表加载到内存中？
I'm currently running some intensive SELECT queries against a MyISAM table. The table is around 100 MiB (800,000 rows) and it never changes.
I need to increase the performance of my script, so I was thinking on moving the table from MyISAM to the MEMORY storage engine, so I could load it completely into the memory.
Besides the MEMORY storage engine, what are my options to load a 100 MiB table into the memory?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
无论您使用什么存储引擎，具有 800k 行的表对于 mysql 来说都不应该有任何问题。 大小为 100 MB 的完整表（数据和键）应位于内存中（mysql 键缓存、操作系统文件缓存或两者）。
首先，您检查索引。 在大多数情况下，优化索引可以为您带来最佳的性能提升。 除非您非常确定它们状况良好，否则不要做任何其他事情。 使用
EXPLAIN调用查询并观察未使用索引或使用错误索引的情况。 这应该使用真实世界的数据来完成，而不是在具有测试数据的服务器上完成。优化索引后，查询应该会在不到一秒的时间内完成。 如果查询仍然太慢，那么请尝试通过在应用程序中使用缓存（memcached 等）来避免运行它们。 鉴于表中的数据永远不会改变，旧的缓存数据等不应该有任何问题。
A table with 800k rows shouldn't be any problem to mysql, no matter what storage engine you are using. With a size of 100 MB the full table (data and keys) should live in memory (mysql key cache, OS file cache, or propably in both).
First you check the indices. In most cases, optimizing the indices gives you the best performance boost. Never do anything else, unless you are pretty sure they are in shape. Invoke the queries using
EXPLAINand watch for cases where no or the wrong index is used. This should be done with real world data and not on a server with test data.After you optimized your indices the queries should finish by a fraction of a second. If the queries are still too slow then just try to avoid running them by using a cache in your application (memcached, etc.). Given that the data in the table never changes there shouldn't be any problems with old cache data etc.
假设数据很少发生变化，您可以使用 MySql 查询缓存。
Assuming the data rarely changes, you could potentially boost the performance of queries significantly using MySql query caching.
如果您的表被频繁查询，它可能已经在操作系统级别缓存，具体取决于服务器中有多少内存。
MyISAM 还允许使用名为 MyISAM 密钥缓存。 创建密钥缓存后，您可以使用 缓存索引 或 加载索引< /a> 语法。
我假设您已经分析了表和查询并在实际查询后优化了索引？ 否则，在尝试将整个表存储在内存中之前，这确实是您应该做的事情。
If your table is queried a lot it's probably already cached at the operating system level, depending on how much memory is in your server.
MyISAM also allows for preloading MyISAM table indices into memory using a mechanism called the MyISAM Key Cache. After you've created a key cache you can load an index into the cache using the CACHE INDEX or LOAD INDEX syntax.
I assume that you've analyzed your table and queries and optimized your indices after the actual queries? Otherwise that's really something you should do before attempting to store the entire table in memory.
如果你有足够的内存分配给Mysql使用——在Innodb缓冲池中，或者供MyIsam使用，你可以将数据库读入内存（只是一个“SELECT * from tablename”），如果没有理由删除它，它会保留那里。
您还可以更好地使用键，因为 MEMORY 表只执行哈希键，而不是完整的 btree 访问，对于较小的、非唯一的键来说，这可能已经足够了，或者对于这么大的表来说就不够了。
像往常一样，最好对其进行基准测试。
另一个想法是，如果您使用 v5.1，请使用 ARCHIVE 表类型，它可以被压缩，并且如果内容易于压缩，也可以加快对内容的访问速度。 这会交换 CPU 时间来解压缩 IO/内存访问。
If you have enough memory allocated for Mysql's use - in the Innodb buffer pool, or for use by MyIsam, you can read the database into memory (just a 'SELECT * from tablename') and if there's no reason to remove it, it stays there.
You also get better key use, as the MEMORY table only does hash-bashed keys, rather than full btree access, which for smaller, non-unique keys might be fats enough, or not so much with such a large table.
As usual, the best thing to do it to benchmark it.
Another idea is, if you are using v5.1, to use an ARCHIVE table type, which can be compressed, and may also speed access to the contents, if they are easily compressible. This swaps the CPU time to de-compress for IO/memory access.
如果数据永远不会改变，您可以轻松地将表复制到多个数据库服务器上。
通过这种方式，您可以将一些查询卸载到不同的服务器，为主服务器获得一些额外的喘息空间。
速度的提升取决于当前的数据库负载，如果您的数据库负载很低，则不会有任何提升。
PS：
您知道，当数据库重新启动时，MEMORY 表会忘记其内容！
If the data never changes you could easily duplicate the table over several database servers.
This way you could offload some queries to a different server, gaining some extra breathing room for the main server.
The speed improvement depends on the current database load, there will be no improvement if your database load is very low.
PS:
You are aware that MEMORY tables forget their contents when the database restarts!