如何手动建立MySQL快取 [英] How to manually build mysql cache

问题描述

我有一个超过150,000行的表，其中大多数每天都会更新.我已打开mysql缓存，因此页面加载速度更快，但是每次数据库更新时，页面加载速度都会再次变慢，我认为这是缓存再次建立了自己.

I have a table of over 150,000 rows of which most would be updated daily. I have mysql caching turned on so the pages load faster however everytime the database is updated the pages load slow again, which I assume is the cache building itself again.

因此，目前我已经在服务器上执行了wget -m --delete-after http://localhost/，但是这大约需要4个小时才能完成，并且需要进行约13个演出.

So at the moment I have resorted to doing a wget -m --delete-after http://localhost/ on the server however this takes about 4 hours to complete and moves something like 13 gig.

有更好的方法吗?

推荐答案

MySQL缓存的工作方式有所不同，具体取决于您使用MyISAM存储引擎还是InnoDB存储引擎.

MySQL cache works differently depending on whether you use the MyISAM storage engine or the InnoDB storage engine.

MyISAM仅缓存索引，而不缓存数据.您可以使用 LOAD INDEX INTO CACHE 来预加载MyISAM索引到密钥缓冲区缓存中.但是，如果使用InnoDB，则没有等效声明.

MyISAM caches indexes only, not data. You can use LOAD INDEX INTO CACHE to preload MyISAM indexes into the key buffer cache. But there is no equivalent statement if you use InnoDB.

InnoDB缓存数据页和索引页.没有专门的命令来预热缓存缓冲区，但是您可以执行一些SQL语句来执行完整的表扫描和完整的索引扫描，以将它们加载到缓冲区中.您应该能够使用服务器上的脚本来执行此操作，而无需诉诸wget！

InnoDB caches both data and index pages. There's no specialized command to warm up the cache buffers, but you can execute a few SQL statements that do full table-scans and full index-scans to load them into the buffers. You should be able to do this using a script on the server, without resorting to wget!

我同意@code_burgar的回答:15万行足够小，以至于在缓存预热期间您不会注意到太多的性能损失.

I agree with the answer from @code_burgar: 150k rows is small enough that you shouldn't notice much performance penalty while the cache is warming up.

如果您正在谈论预热查询缓存，这是一个不同的问题.您必须使用特定的SQL查询来预热查询缓存，因为该缓存会保持与这些SQL查询相关联的结果集的逐字记录.您的wget解决方案效率低下，可能会重复很多工作.您应该能够通过在服务器上运行脚本来启动查询缓存，该脚本执行您要一次缓存的每个查询.

If you're talking about warming up the Query Cache, that's a different issue. You'll have to warm up the Query Cache using specific SQL queries, since that cache keeps result sets associated with those SQL queries verbatim. Your wget solution is inefficient and probably duplicates a lot of work. You should be able to prime the Query Cache by running a script on the server that executes each query that you want to cache once.

但是您可能需要进行代码审查以找出那些查询，并在代码更改时定期更新缓存预加载脚本.

But you may need to do a code review to figure out what those queries are, and periodically update your cache preload script if your code changes.

这篇关于如何手动建立MySQL快取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！