查询缓存

MySQL 从版本 4.0.1 开始为基于读取的应用程序提供了查询缓存功能,能够存储 SELECT 查询的结果,直到其相关的表被修改。

查询缓存的配置

查询缓存可以通过 query_cache_% 变量进行配置。最重要的配置项是:

  • query_cache_size:指定缓存大小。
  • query_cache_limit:防止单个查询产生的结果过大,避免占用整个缓存。

值得注意的是,查询缓存块的大小是可变的,最小值由 query_cache_min_res_unit 控制。因此,在完全清空缓存后,理想情况下,空闲的缓存块应该只有一个。较大的 Qcache_free_blocks 值表示缓存碎片较高。

监控的变量

以下变量值得监控:

  • Qcache_free_blocks:如果该值较高,表示缓存碎片较多,但这不一定是坏事。
  • Qcache_not_cached:如果该值较高,可能表示有大量不可缓存的查询(例如使用了如 now()函数),或者 query_cache_limit 的值设置得太低。
  • Qcache_lowmem_prunes:表示因为缓存已满而被清除的旧查询结果,而不是因为基础表被修改。为了降低此值,应增加 query_cache_size

示例:空缓存

查询当前的查询缓存配置:

mysql> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+

示例:已使用缓存

mysql> SHOW VARIABLES LIKE "query_cache_size";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 33554432 |
+------------------+----------+

mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1409     |
| Qcache_free_memory      | 27629552 |
| Qcache_hits             | 7925191  |
| Qcache_inserts          | 3400435  |
| Qcache_lowmem_prunes    | 2946778  |
| Qcache_not_cached       | 71255    |
| Qcache_queries_in_cache | 4546     |
| Qcache_total_blocks     | 10575    |
+-------------------------+----------+

查询缓存的 my.cnf 配置参数为:

query_cache_size = 32M

清空缓存

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

等待锁

Table_locks_% 变量显示了因其他查询锁定表而导致当前查询必须等待的次数。这种情况通常由 LOCK TABLE 语句引起,也可能是由于同时写入同一表造成的。


表缓存

MySQL 需要一定的时间来“打开”表并读取其元数据,如列名等。如果多个线程同时访问同一张表,表会被多次打开。

为了加速这一过程,可以将元数据缓存到 table_cache(自 MySQL 5.1.3 起称为 table_open_cache)中。

对于此设置的良好值是 max_connections 乘以每个 SELECT 查询通常使用的表的数量。

使用 mysqlreport 或查看当前的 Open_tables 和自启动以来的 Opened_tables 以及 Uptime,可以计算出每秒需要打开的表数。


连接和线程

每个客户端连接(即会话)都由 MySQL 在主 mysqld 进程下创建一个单独的线程。对于大站点,每秒有数百个新连接时,创建线程本身就会消耗相当多的时间。为了加速这一过程,可以在客户端断开连接后缓存空闲线程。通常,每秒创建的线程数不应超过一个。

可以通过 thread_cache_size 配置此缓存,并通过 threads_% 变量进行监控。

为了避免过载,MySQL 会阻止新连接,直到当前连接数低于 max_connections。可以通过 max_used_connections 开始监控,并查看在 Aborted_clients 中被拒绝的连接数以及在 Aborted_connections 中超时的连接数。

需要注意的是,使用持久连接的客户端(如 mysql_pconnect())在客户端忘记断开连接时,可能导致服务拒绝。


临时表

MySQL 在排序或分组结果时会创建临时表。如果临时表太大,不能完全放入内存中,它们会被写入磁盘,这会显著降低性能。可以通过监控 Created_tmp_% 变量,特别是写入磁盘的临时表数量。如果该值较高,可能需要调整 max_heap_table_sizetmp_table_size 设置。


延迟写入

对于像写入 Web 服务器访问日志文件等情况,可以通过告知服务器缓存写入请求一段时间,然后一次性写入磁盘来提高性能。然而,所有这些方法都违背了 ACID 合规性,因为在数据写入磁盘之前,客户端会先收到 INSERT 查询的确认,这意味着在停电或服务器崩溃的情况下,数据可能会丢失。

  • MyISAM 表 可以使用 DELAY_KEY_WRITE 选项进行延迟写入。缺点是崩溃后,表会自动标记为损坏,需要检查和修复。
  • InnoDB 可以通过 innodb_flush_log_at_trx_commit 延迟写入。在服务器崩溃的情况下,数据应保持一致,只有索引需要重建。

INSERT DELAYED 在主要存储引擎中按查询基础工作。


进一步阅读


参考资料

最后修改: 2025年01月17日 星期五 19:37