mysql - 是否值得将数据复制到 MEMORY 表中使用？

Question

我正在构建一个需要一些查找表的网络服务。这些存储在 MariaDB 中。我预计主表一开始会有几百个值，但随着时间的推移，一些值将变得不活动，而另一些值将被添加。服务只需要事件值，其余的用于审计目的。

因此，我正在考虑在 MEMORY 引擎中保存表的副本，只加载所需的记录子集，并根据更改定期刷新它。

这看起来是减少 Web 服务的查询开销和检索时间的明智途径吗？还有其他更合适的潜在解决方案吗？

Answer 1

@N.Bs 评论:

InnoDB keeps working data-set in memory by default. It's controlled via innodb_buffer_pool variable setting. Short answer - no, it's not worth the hassle. MySQL tries to do lots of query optimization for you out of the box, you just have to know what you're using (which engine) and what to configure for better performance. InnoDB will be fast enough, I have a medium-sized project that has around 600 million records in one table, InnoDB is selecting the values just fine and quick.