mysql 查询缓存使用率_使用MySQL查询缓存来加速您的网站

mysql 查询缓存使用率

One of the best ways to speed up your web application is to enable query caching in your database, which caches commonly used SQL queries in memory for virtually instant access by the next page that makes the same request.

加快Web应用程序速度的最佳方法之一是在数据库中启用查询缓存，该数据库将常用SQL查询缓存在内存中，以便下一个发出相同请求的页面几乎可以即时访问。

The reason this method is so powerful is that you don’t have to make any changes to your web application, you just have to sacrifice a little bit of memory. This isn’t going to fix all of your problems, but it definitely can’t hurt.

这种方法之所以如此强大，是因为您不必对Web应用程序进行任何更改，而只需要牺牲一点内存即可。 这不会解决您的所有问题，但绝对不会造成伤害。

Note: if your application updates tables frequently, then the query cache will be constantly purged and you won’t get much or any benefit from this. This is ideal for an application that mostly does reads against the database, such as a WordPress blog. This also won’t work if you are running on shared hosting.

注意：如果您的应用程序频繁更新表，那么查询缓存将不断清除，您将不会从中获得任何好处。 这是最适合对数据库进行读取的应用程序的理想选择，例如WordPress博客。 如果您在共享主机上运行，​​这也将不起作用。

Enable Caching with Server Running

在服务器运行时启用缓存

The first thing you’ll want to do is make sure that your installation of MySQL actually has query caching support available. Most distributions do, but you should check anyway.

您要做的第一件事是确保您安装MySQL实际上具有可用的查询缓存支持。 大多数发行版都可以，但是您还是应该检查一下。

You’ll want to run this command from your MySQL console, which will tell you if query caching is available.

您需要从MySQL控制台运行此命令，它将告诉您查询缓存是否可用。

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Don’t mistake this as meaning that query caching is actually enabled, because most hosting providers aren’t going to enable this by default. Oddly enough, my Ubuntu Feisty installation already had it enabled…

不要误认为这实际上是启用了查询缓存，因为大多数托管提供程序默认不会启用该功能。 奇怪的是，我的Ubuntu Feisty安装已经启用了它…

Next we’ll need to check and see if query caching is enabled. We’ll need to check more than one variable, so we may as well do it all at once by checking for the variable query%

接下来，我们需要检查并查看是否启用了查询缓存。 我们将需要检查多个变量，因此我们最好通过检查变量query％一次完成所有操作

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Here’s the important items in the list and what they mean:

以下是列表中的重要项目及其含义：

• query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.

  query_cache_size –这是缓存的大小(以字节为单位)。 将此值设置为0将有效地禁用缓存。

• query_cache_type – This value must be ON or 1 for query caching to be enabled by default.

  query_cache_type –该值必须为ON或1，默认情况下启用查询缓存。

• query_cache_limit – This is the maximum size query (in bytes) that will be cached.

  query_cache_limit –这是将要缓存的最大查询大小(以字节为单位)。

If the query_cache_size value is set to 0 or you just want to change it, you’ll need to run the following command, keeping in mind that the value is in bytes. For instance, if you wanted to allocate 8MB to the cache we’d use 1024 * 1024 * 8 = 8388608 as the value.

如果将query_cache_size值设置为0或只想更改它，则需要运行以下命令，请记住该值以字节为单位。 例如，如果您想为高速缓存分配8MB，我们将使用1024 * 1024 * 8 = 8388608作为值。

SET GLOBAL query_cache_size = 8388608;

SET GLOBAL query_cache_size = 8388608;

Similarly, the other options can be set with the same syntax:

同样，可以使用相同的语法设置其他选项：

SET GLOBAL query_cache_limit = 1048576;

query_cache_type

query_cache_type

Now how do we tell if it’s actually working? You can use the SHOW STATUS command to pull all the variables that start with “Qc” to take a look at what is going on under the hood.

现在我们如何确定它是否确实在工作？ 您可以使用SHOW STATUS命令提取所有以“ Qc”开头的变量，以了解幕后情况。

mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 65     | 
| Qcache_free_memory      | 201440 | 
| Qcache_hits             | 18868  | 
| Qcache_inserts          | 2940   | 
| Qcache_lowmem_prunes    | 665    | 
| Qcache_not_cached       | 246    | 
| Qcache_queries_in_cache | 492    | 
| Qcache_total_blocks     | 1430   | 
+-------------------------+--------+
8 rows in set (0.00 sec)

You’ll notice in the stats that I have plenty of free memory left. If your server shows a lot of lowmem prunes, you might need to consider increasing this value, but I wouldn’t spend too much memory on query caching for a web server… you need to leave memory available for apache, php, ruby, or whatever you are using.

您会在统计数据中注意到，我还有足够的可用内存。 如果您的服务器显示大量低级修剪，则可能需要考虑增加此值，但我不会在Web服务器的查询缓存上花费过多的内存…您需要保留可用于apache，php，ruby或无论您使用什么。

Enable in Config File

在配置文件中启用

If you want these changes to survive a reboot or restart of the mysql server, you’ll need to add them into your /etc/mysql/my.cnf configuration file for MySQL. Note that it might be in a different location on your installation.

如果希望这些更改在mysql服务器重新启动或重新启动后仍然存在，则需要将它们添加到MySQL的/etc/mysql/my.cnf配置文件中。 请注意，它可能在安装中的其他位置。

Open up the file using a text editor in sudo or root mode, and then add these values if they don’t already exist in the file. If they do exist, just uncomment them.

使用sudo或root模式的文本编辑器打开文件，然后添加这些值(如果文件中尚不存在这些值)。 如果确实存在，则取消注释。

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

Query caching can significantly improve the speed of your web application, especially if your application does mostly reads. Monitor the status using the methods above and see how it works over time.

查询缓存可以显着提高Web应用程序的速度，尤其是在您的应用程序确实主要读取的情况下。 使用上面的方法监视状态，并查看其工作状态。

翻译自: https://www.howtogeek.com/howto/programming/speed-up-your-web-site-with-mysql-query-caching/

mysql 查询缓存使用率