mysql - 准备好的语句，具有 1 亿条记录的 MyISAM 和 MySQL 上的缓存

Question

我有 10 个大型只读表。我做了很多具有相同格式但参数不同的查询，例如:

SELECT 'count' FROM table1 WHERE x='SOME VARIABLE';
SELECT 'count' FROM table1 WHERE x='SOME OTHER VARIABLE';
...
SELECT 'count' FROM table2 WHERE x='' AND y='';
SELECT 'count' FROM table2 WHERE x='' AND y='';
...
SELECT 'count' FROM table3 WHERE x='' AND y='' AND z='';

每个查询使用的变量都不同，所以我几乎从不执行同一个查询两次。 MySQL 端的查询缓存和行缓存会造成浪费并且应该禁用它们是否正确？表缓存似乎是一件好事。

在客户端，我使用的是准备好的语句，我认为这是好的。如果我启用 Prepared statement 缓存(通过 Slick)，那不会损害我的性能，因为参数变化很大吗？我还能做些什么来优化我的表现吗？

是否应该关闭自动提交，因为我只是在做选择，永远不需要回滚？

Answer 1

鉴于您使用的是 MYISAM 引擎并且拥有包含数亿个事件行的表，我将不太关心如何查询缓存(由于您的复杂性较低，这很可能是最少的问题)，但更多地关注数据库中数据的正确组织:

• Prepared Statements 完全没问题。不要一遍又一遍地准备声明可能会有所帮助。相反，只需使用一组新的参数值重用现有的准备好的语句(某些环境甚至允许在客户端存储准备好的语句)。但是，这主要只是节省时间，用于查询缓存。由于您的查询的复杂性非常低，可以假设这不会是最大的时间消耗者。
• > Key Caching (也称为键缓冲)，但是，顾名思义，它是游戏的键!由于缓冲区太小，MySQL 的大多数数据库配置都受到该区域错误值的严重影响。简而言之，键缓存确保可以在主内存中访问对数据的引用(例如在您的索引中)。如果它们不在内存中，则需要从磁盘中检索它们，这很慢。要查看您的 key 缓存是否有效，您应该在系统处于负载状态时观察 key 命中率。 https://dba.stackexchange.com/questions/58182/tuning-key-reads-in-mysql 中详细解释了有关详细信息.
• 如果缓存变大或由于使用其他表而频繁移动，为您的表创建自己的 key 缓存可能会有所帮助。有关详细信息，请参阅 https://dev.mysql.com/doc/refman/5.5/en/cache-index.html
• 如果您总是通过相同的属性访问表的大部分内容，则可以使用 ALTER TABLE ... ORDER BY expr1, expr2, .. 更改磁盘上数据存储的顺序。 .。有关此方法的详细信息，另请参阅 https://dev.mysql.com/doc/refman/5.5/en/optimizing-queries-myisam.html
• 避免使用可变长度的列，例如 VARCHAR、BLOB 或 TEXT。它们可能有助于节省一些空间，但尤其是比较它们的值会变得非常耗时。但是，请注意，已经有一个此类类型的列将 MySQL 切换到 Dynamic 列模式。
• 在大量数据更改后运行 ANALYZE TABLE 以保持统计信息最新。如果您删除了很大的区域，OPTIMIZE TABLE 可能会有所帮助，有助于确保阅读时没有需要跳过的大间隙。
• 使用INSERT DELAYED如果您不需要回复，则异步写入更改。如果在同一时间点附近还有其他 SELECT 语句，这将大大提高您的性能。
• 或者，如果您需要回复，您可以使用INSERT LOW_PRIORITY。那么与 INSERT 相比，并发 SELECT 的执行是首选。这可能有助于减轻一点痛苦，即 MyISAM 仅支持表级锁定。
• 您可以尝试提供Index Hints对您的查询，特别是如果您的表上有多个相互重叠的索引。您应该尝试使用宽度最小但仍涵盖最多属性的索引。但是，请注意，在您的情况下，影响必须非常小:您没有排序/分组或加入，因此查询优化器应该已经非常善于找到最好的。只需在您的 SELECT 语句上使用 EXPLAIN 来检查所用索引的选择是否合理。

总之，Prepared Statements 完全没问题。 key 缓存是关键 - 您还可以做一些其他事情来帮助 MySQL 处理大量数据。