快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

灵活的MariaDB服务器查询缓存

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

MariaDB服务器查询缓存

服务器查询缓存(QC)是MariaDB服务器的一项众所周知的功能,它可以缓存SQL语句和相应的结果集。如果随后的查询与缓存的SQL语句匹配,则该查询可以重用结果集。如果例如通过UPDATE修改与缓存查询相对应的表,则查询缓存还知道会使查询缓存无效。有关更多信息,请参见MariaDB知识库中的查询缓存

灵活的MariaDB服务器查询缓存

在过去的几十年中,RAM数量有限,磁盘速度很慢,CPU具有一个内核和一个线程。如果您想要更多的内核,则成本限制为最多两台CPU机器要花几年的薪水。在那些日子里,查询缓存是个好主意。数据位于有限大小的磁盘缓存中的可能性很小。从磁盘获取数据是一个坏主意,因为这意味着应对磁介质的缓慢性能。今天,我们拥有更快的固态磁盘和更多的RAM,因此查询缓存已不再那么重要。减少对缓存的依赖可减轻其负面影响,例如互斥锁。

互斥对象(mutex)是一个编程对象,它允许多个程序线程共享资源(例如文件夹),但不能同时共享。当不再需要数据或例程结束时,将互斥锁设置为解锁。互斥体会产生瓶颈效应。阻塞意味着一次只有一个查询可以查看查询缓存,而其他查询则必须等待。必须等待查找缓存才发现不在缓存中的查询将变慢而不是被加速。

您可能也有兴趣:使用查询分析来提高MariaDB性能

查询缓存标志

查询缓存有三种模式:ONOFF,和DEMAND。在OFF模式下,将不缓存任何内容。在ONDEMAND模式中,标志可用于确定要缓存的语句。

DEMAND模式下,仅缓存指定的语句。要指定应缓存一条语句,请SQL_CACHE在一条SELECT语句中设置标志:

的SQL
1个
SELECT SQL_CACHE id,名称FROM产品;

SELECT语句仅缓存指定语句的SQL文本和set的相应结果。这个假设出现了三个问题:

  1. 应用程序需要修改为适用SELECT于使用此标志的任何内容。
  2. 如果语句中SQL_CACHE缺少SELECT,则它将不符合资格包含在缓存中,并且不会检查任何先前的缓存语句;因此,它将永远不会使用查询缓存。
  3. 的DML,如INSERTUPDATEDELETE语句,需要检查用于无效,这将在查询缓存互斥的阻断作用招致。

如果查询缓存为ON,则可以通过包含以下SQL_NO_CACHE标志来避免语句被缓存或在缓存中查找:

的SQL

1个
SELECT SQL_NO_CACHE order_id,line_id,产品,amt FROM order_item;

查询缓存问题

请记住,在过去的几十年中,只有一个或两个同时线程。当您只有一个CPU和一个线程时,Query Cache互斥锁并不重要,因为另一个线程并发运行的可能性很小。查询缓存互斥锁并不被认为是一件坏事。您刚刚注意到,打开查询缓存时,您的网站突然运行速度提高了十倍。

今天,如果您运行具有16个线程的现代硬件,即使没有查询缓存,您的网站运行速度也会快上百倍。如果打开了查询缓存,则互斥锁将其速度降低到与以前几十年相当的速度。啊。

MaxScale查询过滤器

MariaDB MaxScale数据库代理提供了另一个查询缓存作为缓存过滤器的一部分。见缓存中MariaDB的知识库。缓存过滤器克服了MariaDB Server内部查询缓存的局限性,但增加了一些障碍。

MaxScale缓存过滤器消除了互斥锁问题;与MariaDB服务器查询缓存相比,它更具可配置性。您可以指定一组灵活的规则来影响哪些表,数据库,列等。

如果我们不希望缓存所有的表,那么可以使用指定表的选项很有用,但这会使缓存过滤器的配置更加困难。像这样的复杂性,无效如何在查询过滤器中起作用?

我们可以检查MariaDB MaxScale处理的所有DML,然后使相关表无效。使用此方法将不起作用,因为MaxScale无法看到MariaDB Server内部修改表的任何处理,其中包括存储过程,函数和触发器。

使用高速缓存过滤器时,每个语句都有生存时间(TTL)。语句超时时,需要刷新缓存。调优很困难,因为我们想要尽可能长的缓存时间,所以我们不必重新访问服务器即可刷新,但这意味着返回的数据可能已过时。

查询过滤器具有高级的运行时配置,可以处理调整问题。如果您想这样做,MariaDB MaxScale很有用,但是与在MariaDB Server中使用查询缓存相比,它的设置和配置更加困难。

Mutex解决

已尝试寻找一种解决查询缓存中互斥的方法。不幸的是,由于互斥锁对于内部处理至关重要,因此仍然无法解决。如果我们至少可以指定在MariaDB服务器查询缓存中缓存的表和SQL语句怎么办?高速缓存失效语句仍然存在问题,但是至少高速缓存集中在合理使用表的地方,例如具有静态内容的表以及SELECTS频繁出现的表(例如网站上的产品)。

产品表中进行选择将很快,因为我们可以缓存许多查询的结果。我们不会看到太多的缓存失效,因为产品集很少更改。我们可以用现在可用的内容修复它吗?是的,让我们看看。

修复查询缓存

当查询缓存类型设置为时,在由该标志明确指定时DEMANDSELECT语句将使用查询缓存SQL_CACHE。这是在 [mysqld]部分中的MariaDB服务器配置文件/etc/my.cnf.d/server.cnf 中完成的,我们在其中添加:

的SQL

1个
query_cache_type = DEMAND

现在,我们不想重写所有应用程序以有选择地使用该SQL_CACHE标志,而是使用MariaDB MaxScale为我们完成此操作。

这次使用正则表达式过滤器,因为它不是查询过滤器。正则表达式过滤器很直观,可以搜索和替换正在处理的SQL语句。在这种情况下,SQL_CACHE被添加到选定的语句中。下面的示例显示了一个简单的网站表,其中始终有两个表(产品和客户)。

的SQL


1个
创建  `products`(
2
`id` int11NOT  NULL
3
`category` int11NOT  NULL
4
`name` varchar255)默认为NULL
5
主键(`id`)
6
)ENGINE = InnoDB;
7

8
创建  `customers`(
9
`id` int11NOT  NULL
10
`name` varchar255)默认为NULL
11
主键(`id`)
12
)ENGINE = InnoDB;

1.我们可以配置MaxScale正则表达式过滤器以将SQL_CACHE插入任何语句,例如:

的SQL

1个
SELECT ID,名称FROM产品WHERE类= 1 ;

要么

的SQL

1个
客户那里选择名称,ID = 42 ;

2.此正则表达式过滤器的配置使用PCRE2语法进行匹配:

的SQL


1个
 [QcOnDemand]
2
类型=过滤器
3
module = regexfilter
4
match =(?i)^([[[:space:]] * select)([[:space:]] +。* [[:space:]] + from [[:space:]] +(产品|客户)([[:: space:]] | $))
5
replace = $ 1 SQL_CACHE $ 2

PCRE2正则表达式语法复杂但功能强大。以下是上述正则表达式的逐元素说明:

灵活的MariaDB服务器查询缓存

3.以下配置设置告诉MariaDB MaxScale路由器使用创建的过滤器:

属性文件


1个
 [DefaultService]
2
类型 = 服务
3
路由器 = readwritesplit
4
服务器 = server1
5
过滤器 = QcOnDemand

4.完成配置后,重新启动MariaDB Server和MariaDB MaxScale。我们现在准备测试。

测试固定查询缓存

要测试已配置的查询缓存,请通过您选择的MariaDB MaxScale端口(此示例使用端口4004)连接到MariaDB。

1.连接到数据库。

爪哇


1个
$  mysql  - h  mydbhost  - P  4004  - usomeuser  - psomepassword  测试
2
  信息  完成     
3
 可以  关闭 这个 功能 ,以 获得 一个 更快的 启动  - 一个
4

5
欢迎   MariaDB的 显示器。  命令  ; 结尾  ; \ g
6
您的 MariaDB  连接 ID   3
7
服务器 版本10.46 - MariaDB的- 日志 MariaDB的 服务器
8

9
版权所有c ^20002018甲骨文MariaDB的 公司 抗体  其他人
10

11
输入 “帮助;”   '\ h'  寻求 帮助键入 “\ C”  ,以 清除  当前 输入 的语句
12

13
MariaDB [ 测试 ] >

2.查看查询缓存中当前包含的内容。

爪哇


1个
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
2
+ ------------------------- + --------- +
3
|  变量名           |  价值   |
4
+ ------------------------- + --------- +
5
|  Qcache_free_blocks       |  1        |
6
|  Qcache_free_memory       |  1031320  |
7
|  Qcache_hits              |  0        |
8
|  Qcache_inserts           |  0        |
9
|  Qcache_lowmem_prunes     |  0        |
10
|  Qcache_not_cached        |  265      |
11
|  Qcache_queries_in_cache  |  0        |
12
|  Qcache_total_blocks      |  1        |
13
+ ------------------------- + --------- +

3.请注意,表orderorder_line不应与表productscustomers不同地进行缓存。

爪哇


1个
MariaDB [ 测试 ] >  订单中选择 *  ;
2
+ ---------- + ------------- +
3
|  order_id  |  customer_id  |
4
+ ---------- + ------------- +
5
|         1  |            1  |
6
|         2  |            7  |
7
|         9  |            2  |
8
+ ---------- + ------------- +

这些表是否命中了查询缓存?查看以下内容,注意没有任何内容插入到查询缓存中,并且没有命中。

爪哇


1个
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
2
+ ------------------------- + --------- +
3
|  变量名           |  价值   |
4
+ ------------------------- + --------- +
5
|  Qcache_free_blocks       |  1        |
6
|  Qcache_free_memory       |  1031320  |
7
|  Qcache_hits              |  0        |
8
|  Qcache_inserts           |  0        |
9
|  Qcache_lowmem_prunes     |  0        |
10
|  Qcache_not_cached        |  485      |
11
|  Qcache_queries_in_cache  |  0        |
12
|  Qcache_total_blocks      |  1        |
13
+ ------------------------- + --------- +

接下来,让我们检查一下products表上的查询。

爪哇


1个
MariaDB [ 测试 ] >  category = 1的产品选择 idcategoryname  ;
2
+ ---- + ---------- + ----------------- +
3
|  id  |  类别 |  名称            |
4
+ ---- + ---------- + ----------------- +
5
|   1  |         1  |  数据库 系统 |
6
|   2  |         1  |  文字 处理器  |
7
+ ---- + ---------- + ----------------- +

我们可以在表中看到产品。让我们再次看一下查询缓存的状态。

爪哇


1个
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
2
+ ------------------------- + --------- +
3
|  变量名           |  价值   |
4
+ ------------------------- + --------- +
5
|  Qcache_free_blocks       |  1        |
6
|  Qcache_free_memory       |  1029784  |
7
|  Qcache_hits              |  0        |
8
|  Qcache_inserts           |  1        |
9
|  Qcache_lowmem_prunes     |  0        |
10
|  Qcache_not_cached        |  737      |
11
|  Qcache_queries_in_cache  |  1        |
12
|  Qcache_total_blocks      |  4        |
13
+ ------------------------- + --------- +

插入到查询缓存中。当我们再次运行相同的查询时,我们将得到相同的结果吗?

爪哇


1个
MariaDB [ 测试 ] >  category = 1的产品选择 idcategoryname  ;
2
+ ---- + ---------- + ----------------- +
3
|  id  |  类别 |  名称            |
4
+ ---- + ---------- + ----------------- +
5
|   1  |         1  |  数据库 系统 |
6
|   2  |         1  |  文字 处理器  |
7
+ ---- + ---------- + ----------------- +
8

9
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
10
+ ------------------------- + --------- +
11
|  变量名           |  价值   |
12
+ ------------------------- + --------- +
13
|  Qcache_free_blocks       |  1        |
14
|  Qcache_free_memory       |  1029784  |
15
|  Qcache_hits              |  1        |
16
|  Qcache_inserts           |  1        |
17
|  Qcache_lowmem_prunes     |  0        |
18
|  Qcache_not_cached        |  899      |
19
|  Qcache_queries_in_cache  |  1        |
20
|  Qcache_total_blocks      |  4        |
21
+ ------------------------- + --------- +

它按照预期的方式工作。查询缓存中有一个插入,一个命中。
接下来,让我们尝试使缓存无效。

爪哇


1个
MariaDB的 [ 测试 ] >  插入  产品 的值51'电子表格');
2

3
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
4
+ ------------------------- + --------- +
5
|  变量名           |  价值   |
6
+ ------------------------- + --------- +
7
|  Qcache_free_blocks       |  1        |
8
|  Qcache_free_memory       |  1031320  |
9
|  Qcache_hits              |  1        |
10
|  Qcache_inserts           |  1        |
11
|  Qcache_lowmem_prunes     |  0        |
12
|  Qcache_not_cached        |  1112     |
13
|  Qcache_queries_in_cache  |  0        |
14
|  Qcache_total_blocks      |  1        |
15
+ ------------------------- + --------- +

缓存中的查询数为0,因为缓存无效。让我们再尝试一次使缓存无效的时间。

爪哇


1个
MariaDB [ 测试 ] >  category = 1的产品选择 idcategoryname  ;
2
+ ---- + ---------- + ----------------- +
3
|  id  |  类别 |  名称            |
4
+ ---- + ---------- + ----------------- +
5
|   1  |         1  |  数据库 系统 |
6
|   2  |         1  |  文字 处理器  |
7
|   5  |         1  |  电子表格     |
8
+ ---- + ---------- + ----------------- +
9
3    0.001  
10

11
MariaDB [ 测试 ] >  显示 “ qc%”之全局 状态
12
+ ------------------------- + --------- +
13
|  变量名           |  价值   |
14
+ ------------------------- + --------- +
15
|  Qcache_free_blocks       |  1        |
16
|  Qcache_free_memory       |  1029784  |
17
|  Qcache_hits              |  1        |
18
|  Qcache_inserts           |  2        |
19
|  Qcache_lowmem_prunes     |  0        |
20
|  Qcache_not_cached        |  1232     |
21
|  Qcache_queries_in_cache  |  1        |
22
|  Qcache_total_blocks      |  4        |
23
+ ------------------------- + --------- +

该语句已按预期方式插入到缓存中(Qcache_inserts现在为2)。同一条语句被插入了两次,结果集在两次执行之间无效。

快乐的SQL’ing

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638