c# - 什么时候应该使用 MySqlCommand.EnableCaching 属性？

Question

EnableCaching 属性在做什么？我在 official documentation 中找不到有关此属性的任何文档.

MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.EnableCaching = [true | false];
// other codes...

感谢任何有用的来源、评论和回答。非常感谢。

Answer 1

TLDR:按您认为合适的方式使用它，但它需要对客户端和服务器端进行大量调整才能掌握所需的结果。

---

摘自 Mysql 手册页 Query Cache Configuration:

Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:

mysql> SET SESSION query_cache_type = OFF;

诚然，以上内容适用于命令行实用程序，但手册对概念的介绍并不知道您使用的是哪种连接器(Java、.NET 等)。这就是它运行的那个。 您的 连接器将 .NET 的 command.EnableCaching 传递到 mysql 连接的 query_cache_type。这是在逐个连接的基础上进行的，它决定了您是否希望缓存提供您的结果，假设它们甚至被缓存了。这是一个过于宽泛的问题，甚至无法提出。

我尽量缩小这个答案的范围。你应该什么时候使用它？这取决于您的应用程序和您自己。并且取决于您的特定测试尝试是否被缓存，它会因情况而异。

服务器端

有几件事要看，例如大小设置和是否启用缓存。此外，mysql 守护程序是否具有发送给它的缓存的临时运行时设置，以及在服务器重新启动时这些设置仍然存在的内容。所以这不是一个简单的答案。

为此，我找到了一篇很好的“文章二”，它补充或超越了上述引用文献中的文章。后者确实提供了服务器信息。

第二条是Speed Up Your Web Site With MySQL Query Caching。我将其全部包含在内，因为经过半小时的搜索，它似乎是最好的信息，我希望它不会消失。

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.

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.

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.

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.

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

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…

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%

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_type – This value must be ON or 1 for query caching to be enabled by default.

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

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.

SET GLOBAL query_cache_size = 8388608;

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

SET GLOBAL query_cache_limit = 1048576; 
SET GLOBAL query_cache_type = 1;

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.

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.

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.

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.

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.