ProxySQL官档翻译__18_Query_Cache

18_Query_Cache

备注：文章编写时间201904-201905期间，后续官方在github的更新没有被写入

~ ~ 查询缓存[Query Cache]

一、前言[Preface]

从历史上看，在MySQL环境中有两种使用缓存的方法： 1）启用MySQL的查询缓存：嵌入在MySQL服务器本身，没有外部依赖。但它会成为任何写入密集型环境的瓶颈，因为当相关表收到写入时，缓存条目无效。 2）使用外部缓存：允许很多灵活性，但也需要应用程序做出大量的逻辑更改，因为应用程序必须连接到数据库和缓存系统，并负责保持更新。 虽然外部缓存非常有效，但它需要开发一定量的投入，而DBA无法控制数据流。

二、在线上缓存[Caching on the wire]

ProxySQL引入了一种新的范例来查询缓存。根据相关配置（下面会做详细说明），当执行查询后结果集会被缓存在线上，并将结果集返回给应用程序。 如果应用程序将重新执行相同的查询，则结果集将由内部的查询缓存来返回。

识别由非最佳SELECT语句引起的数据库负载是一种非常常见的情况，并应对这些语句的结果集进行数秒的缓存。然而，实现应用程序的代码更改可能是一个漫长的过程（开发人员应编写新代码，构建代码，测试分段，然后在生产中进行部署），这在紧急情况下通常不是合适的选择。由于数据库代理层（在本例中为ProxySQL）的配置是属于DBA的责任，因此DBA启用缓存是不需要开发人员对应用程序进行更改。

因此，这是一个赋予DBA权力的功能。

三、定义需要缓存的流量[Define traffic that needs to be cached]

如果想要对指定的(查询)流量进行结构集缓存，我们需要在定义匹配传入流量的查询规则时为其定义cache_ttl值。 如文档(《09_ProxySQL配置之系统库_01_main库MEMORY层表和RUNTIME层表.txt》)中所述，有许多方法可以定义对传入流量的匹配。我们缓存结果集所需要做的就是定义匹配条件和超时。

缓存示例 说明如何配置缓存的最佳方法是使用示例。 假设我们使用一个非常小的表对 ProxySQL 运行sysbench(1.0.14)：

$ sysbench oltp_common --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=1  prepare

$ sysbench oltp_read_only --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=1 \
--skip_trx=on --point_selects=100 --simple_ranges=1 --sum_ranges=1 --order_ranges=1 --distinct_ranges=1 \
--time=120 --histogram --report-interval=10 --db-ps-mode=disable run

备注: 如果设置了--db-ps-mode=disable，则效果为每个线程始终在一个Session中执行所有SQL；如果不设置，则为每次执行都新建连接。

结果是：

SQL statistics:
    queries performed:
        read:                            1285648
        write:                           0
        other:                           0
        total:                           1285648
    transactions:                        12362  (102.99 per sec.)
    queries:                             1285648 (10710.93 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

在ProxySQL中我们可以看到以下结果：

Admin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
| count_star | sum_time  | hostgroup | digest             | digest_text                                                        |
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
| 1236200    | 394819753 | 10        | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   |
| 12362      | 13846586  | 10        | 0xC19480748AE79B4B | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 12362      | 7419887   | 10        | 0xAC80A5EA0101522E | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 12362      | 5256720   | 10        | 0xDBF868B2AA296BC5 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                |
| 12362      | 5232686   | 10        | 0x290B92FD743826DA | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     |
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
5 rows in set (0.00 sec)

毫无疑问，大多数执行时间来自单一类型的SELECT，执行很多次。让我们缓存它,为它们创建匹配规则。在此示例中，我们将使用摘要(digest)作 为匹配条件，并使用2000ms的cache_ttl :

Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (5,1,'0xBF001A0C13781C1D',2000,1);
Query OK, 1 row affected (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)

让我们重新运行测试基准：

$ sysbench oltp_read_only --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=1 \
--skip_trx=on --point_selects=100 --simple_ranges=1 --sum_ranges=1 --order_ranges=1 --distinct_ranges=1 \
--time=120 --histogram --report-interval=10 --db-ps-mode=disable run

本次结果为:

SQL statistics:
    queries performed:
        read:                            4655664
        write:                           0
        other:                           0
        total:                           4655664
    transactions:                        44766  (373.01 per sec.)
    queries:                             4655664 (38793.21 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

我们可以立即看到，吞吐量大幅增加：从1285648 (10710.93 per sec.)增加到了4655664 (38793.21 per sec.)；因为一些查询是由ProxySQL缓存的。

在ProxySQL中，我们可以看到stats_mysql_query_digest的以下结果：

Admin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
| count_star | sum_time  | hostgroup | digest             | digest_text                                                        |
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
| 282956     | 164908854 | 10        | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   |
| 44766      | 48693121  | 10        | 0xC19480748AE79B4B | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 44766      | 27466809  | 10        | 0xDBF868B2AA296BC5 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                |
| 44766      | 27065916  | 10        | 0xAC80A5EA0101522E | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 44766      | 21306870  | 10        | 0x290B92FD743826DA | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     |
| 4193644    | 0         | -1        | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   |
+------------+-----------+-----------+--------------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)

注意：hostgroup = -1的查询表示直接从查询缓存中获取结果集的流量，而不会命中任何后端。

四、度量值[Metrics]

目前可用的一些指标是在 stats_mysql_query_digest 中使用 hostgroup = -1 报告的指标，如上面的例子中所示的那样。 备注:上面使用的是 stats_mysql_query_digest_reset 表，它是stats_mysql_query_digest的清除模式表，它会清空表中内容。

通过stats表 stats_mysql_global 可以获得与查询缓存相关的其他指标：

Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Query_Cache_Memory_bytes | 6905080        |
| Query_Cache_count_GET    | 16034509       |
| Query_Cache_count_GET_OK | 15035431       |
| Query_Cache_count_SET    | 999069         |
| Query_Cache_bytes_IN     | 194818455      |
| Query_Cache_bytes_OUT    | 2931909045     |
| Query_Cache_Purged       | 997109         |
| Query_Cache_Entries      | 1960           |
+--------------------------+----------------+
8 rows in set (0.00 sec)

它们代表的含义如下： Query_Cache_Memory_bytes ==>存储在查询缓存中的结果集的总大小(单位:byte)。这不包括元数据; Query_Cache_count_GET ==>针对查询缓存执行的GET请求总数; Query_Cache_count_GET_OK ==>针对查询缓存执行成功的GET请求的总数，其中Query Cache中的结果集存在且未过期; Query_Cache_count_SET ==>插入查询缓存的结果集总数; Query_Cache_bytes_IN ==>写入查询缓存的数据量(单位:byte); Query_Cache_bytes_OUT ==>从查询缓存中读取的数据量(单位:byte); Query_Cache_Purged ==>清除的条目数量; Query_Cache_Entries ==>查询缓存中当前的条目数。

五、查询缓存调整[Query Cache tuning]

目前，只能使用变量 mysql-query_cache_size_MB 来调整查询缓存使用的内存总量：

Admin> SHOW VARIABLES LIKE 'mysql-query_cache_size_MB';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| mysql-query_cache_size_MB | 256   |
+---------------------------+-------+
1 row in set (0.00 sec)

重要提示：mysql-query_cache_size_MB 的当前实现并未强加硬限制。而是将它作为触发 purging 线程的参数。

要更改查询缓存使用的内存总量，可以使用如下命令：

Admin> SET mysql-query_cache_size_MB=128; 
Query OK, 1 row affected (0.00 sec)

Admin> SHOW VARIABLES LIKE 'mysql-query_cache_size_MB';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| mysql-query_cache_size_MB | 128   |
+---------------------------+-------+
1 row in set (0.00 sec)

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

与查询缓存非紧密相关但影响其行为的另一个变量是 mysql-threshold_resultset_size。

mysql-threshold_resultset_size ==>它定义了ProxySQL在开始将结果集发送到客户端之前能缓冲的最大大小。

将此变量设置得太低将导致在从后端检索结果集时执行重试查询的失败。 将此变量设置得太高可能会增加内存占用，因为ProxySQL将尝试缓冲更多数据。 由于 mysql-threshold_resultset_size 定义了ProxySQL可以缓冲的最大结果集大小，所以它也就定义了可以存储在查询缓存中的最大结果集大小。

六、实施细节[Implementation details]

查询缓存中的每个元素都有几个与之关联的元数据： 1）key ==>该值唯一标识查询缓存记录：它是从username、schemaname和查询本身派生的一个哈希。通过这些，它确保了用户只访问他们当前所在schema的结果集; 2）value ==>结果集(内容); 3）length ==>结果集的长度; 4）expire_ms ==>定义该结果集的到期时间; 5）access_ms ==>记录上次访问该记录的时间; 6）ref_count ==>用于标识当前正在使用的结果集的引用计数。

1、GET calls

每次GET调用成功时，为了提高性能，会在增加引用指针并释放所有锁之后，执行数据拷贝。当复制完成时，ref_count值便会减少。该策略可以确保在该结 果集仍在使用状态时不会将其从查询缓存中删除条目(即使在次过程中它到期了)。当GET调用找到过期的条目时，该条目将被移动到清除队列。

2、SET calls

SET调用永远不会失败！！ 如果到达 mysql-query_cache_size_MB 指定值，则SET调用不会失败。如果此时发现存在与SET操作的结果集具有相同key的条目，则将已有的条目移动到清除队列中。

七、清除线程[Purging thread]

由Purging线程执行对查询缓存中条目的清除工作。这确保了查询缓存的任何维护都不是由访问它的MySQL线程执行的，而是由后台线程执行，从而提高了性能。 这就是为什么即使达到 mysql-query_cache_size_MB，SET调用也永远不会失败的原因：访问查询缓存的MySQL线程是不负责释放空间的;而是由Purging线程来处理。

Purging线程不仅会负责清除'清除队列'中的条目。它还负责扫描整个查询缓存以查找过期的条目。作为优化，如果当前内存使用率小于 mysql-query_cache_size_MB 的3％，则 Purging 线程不执行任何清除。

八、限制[Limitations]

查询缓存中目前存在多个已知限制。 有些很容易实现，有些则很难。 它们是没有定义优先级的：将根据用户请求定义优先级。

目前已知的限制： 1）除了使用cache_ttl之外，无法使用其他所在来定义查询缓存(条目)的失效; 2）没有提供可以立即清除查询缓存中全部内容的命令; 3）mysql-query_cache_size_MB 不是一个严格的容量限制，它只是使用指标来触发自动清除过期的条目; 4）虽然记录了 access_ms 值，但是当实现 mysql-query_cache_size_MB 时，它不用作使判定到期的度量值; 5）查询缓存不支持MySQL的 PREPARE （预准备）语句。

完毕！