Informix 数据库 SQL 语句高速缓存技术应用实践

简介： SQL 语句高速缓存技术是提高数据库应用程序性能的一种重要的手段，特别是当有大量并发用户执行相同的查询时，系统性能会有显著的提高。本文主要给大家介绍在 Informix 数据库中 SQL 语句高速缓存技术的使用方法，希望能够使大家有一个比较全面的了解。

正如我们所知，Informix 数据库优化器采用基于“成本”优化方式，当接收到用户发出的 SQL 语句后，Informix 优化器要对 SQL 语句进行编译，根据成本最低的原则生成最终的执行计划进行执行。对于每一个 SQL 语句，都要进行编译工作，为了提高系统的性能，特别是对具有大量相同 SQL 语句的系统，Informix 从 9.2 版本开始，就提供了 SQL 语句高速缓存机制 SQL Statement Cache (SSC)，将已分析并优化的 SQL 语句缓存到内存中，以使执行相同 SQL 语句的多个用户能够实现以下性能改进：

响应时间缩短，原因是绕过了分析和优化步骤
占用的内存量减少，原因是数据库服务器在用户间共享查询数据结构

通过采用 SQL 语句高速缓存机制，可以大大提高拥有大量相同 SQL 语句系统的性能。

下图显示数据库服务器如何对多个用户访问 SQL 语句高速缓存。

图 1. 使用 SQL 语句高速缓存时的数据库服务器操作

当数据库服务器第一次为用户 1 执行 SQL 语句时，数据库服务器检查完全一样的 SQL 语句是否在 SQL 语句高速缓存中。如果不在高速缓存中，那么数据库服务器分析该语句、确定最优的查询计划并执行该语句。
当用户 2 执行完全一样的 SQL 语句时，数据库服务器在 SQL 语句高速缓存中查找该语句而不需要分析和优化该语句。
同样，如果用户 3 和用户 4 执行完全一样的 SQL 语句，数据库服务器不必分析和优化该语句。相反，它使用内存中 SQL 语句高速缓存内的分析信息和查询计划。

启用 SQL 语句高速缓存

STMT_CACHE 配置参数为 0（缺省值）时，数据库服务器将不使用 SQL 语句高速缓存。

使用以下方法之一可更改该 STMT_CACHE 的缺省值：

更新 ONCONFIG 文件以指定 STMT_CACHE 配置参数，并重新启动数据库服务器。

如果将 STMT_CACHE 配置参数设置为 1，那么数据库服务器将在单个用户将 STMT_CACHE 环境变量设置为 1 或在应用程序中执行 SET STATEMENT CACHE ON 语句时，为该用户使用 SQL 语句高速缓存。

STMT_CACHE 1

如果 STMT_CACHE 配置参数为 2，那么数据库服务器将所有用户的 SQL 语句存储到 SQL 语句高速缓存中，除非单个用户使用 STMT_CACHE 环境变量或 SET STATEMENT CACHE OFF 语句关闭该功能。

STMT_CACHE 2

使用 onmode -e 命令动态地覆盖 STMT_CACHE 配置参数。

如果使用 enable 关键字，那么在单个用户将 STMT_CACHE 环境变量设置为 1 或在应用程序中执行 SET STATEMENT CACHE ON 语句时，数据库服务器将为该用户使用 SQL 语句高速缓存。

onmode -e enable

如果使用 on 关键字，数据库服务器将所有用户的 SQL 语句存储到 SQL 语句高速缓存，除非单个用户通过 STMT_CACHE 环境变量或 SET STATEMENT CACHE OFF 语句关闭该项功能。

onmode -e on

根据 STMT_CACHE 配置参数的设置（或 onmode -e 的执行），下表为用户总结了 SQL 语句高速缓存的用法，以及在 STMT_ CACHE 环境变量的应用程序和 SET STATEMENT CACHE 语句中的用法。

STMT_ CACHE 配置参数或 onmode -e	STMT_CACHE 环境变量	SET STATEMENT CACHE 语句	导致的行为
0（缺省值）	不适用	不适用	不使用语句高速缓存
1	0（或未设置）	OFF	不使用语句高速缓存
1	1	OFF	不使用语句高速缓存
1	0（或未设置）	ON	使用语句高速缓存
1	1	ON	使用语句高速缓存
1	1	未执行	使用语句高速缓存
1	0	未执行	不使用语句高速缓存
2	1（或未设置）	ON	使用语句高速缓存
2	1（或未设置）	OFF	不使用语句高速缓存
2	0	ON	使用语句高速缓存
2	0	OFF	用户不使用语句高速缓存
2	0	未执行	用户不使用语句高速缓存
2	1（或未设置）	未执行	用户使用语句高速缓存

SQL 语句高速缓存配置

下图显示数据库服务器如何对 SQL 语句高速缓存使用相关配置参数的值。

图 2. 影响 SQL 语句高速缓存的配置参数

当数据库服务器为用户使用 SQL 语句高速缓存时，意味着数据库服务器执行以下操作：

首先检查 SQL 语句高速缓存中是否有与用户正在执行的 SQL 语句匹配的语句
如果 SQL 语句与某条目匹配，那么使用 SQL 语句高速缓存中的查询内存结构执行该语句（图 2 中的用户 2）
如果 SQL 语句没有匹配的条目，那么数据库服务器检查其是否符合高速缓存的条件。
如果 SQL 语句满足条件，那么向高速缓存插入一个条目用于该语句的后续执行。

以下参数影响数据库服务器是否将 SQL 语句插入高速缓存（图 2 中的用户 1）：

STMT_CACHE_HITS 指定使用高速缓存中的条目执行该语句的次数（称为命中数）。数据库服务器根据命中数插入以下条目之一：
- 如果 STMT_CACHE_HITS 的值为 0，那么插入完全高速缓存的条目，其中包含 SQL 语句的文本以及查询内存结构。
- 如果 STMT_CACHE_HITS 的值不为 0 且该语句不存在于高速缓存中，那么插入包含 SQL 语句的文本的唯一关键字条目。 SQL 语句的后续执行会增加命中数。
- 如果 STMT_CACHE_HITS 的值等于唯一关键字条目的命中数，那么添加查询内存结构使该条目变为完全高速缓存的条目。
STMT_CACHE_SIZE 指定 SQL 语句高速缓存的大小
STMT_CACHE_NOLIMIT 指定是否将高速缓存的内存限制为 STMT_CACHE_SIZE 的值。如果您不指定 STMT_CACHE_SIZE 参数，那么它将为缺省值 524288（512 * 1024）字节。
STMT_CACHE_NOLIMIT 的缺省值为 1，这表示数据库服务器将把条目插入 SQL 语句高速缓存中，即使内存总量可能超过 STMT_CACHE_SIZE 的值。
当 STMT_CACHE_NOLIMIT 设置为 0 时，如果高速缓存的当前大小不会超过内存限制，那么数据库服务器将 SQL 语句插入高速缓存。

使用 onstat -g ssc 监视 SSC 上的命中数

当启用了 SQL 语句高速缓存时，缺省情况下数据库服务器会将满足条件的 SQL 语句及其内存结构立即插入 SQL 语句高速缓存中。如果工作负载的特定查询数不成比例，那么在数据库服务器在语句高速缓存中置入完全高速缓存的条目之前，使用 STMT_CACHE_HITS 配置参数来指定执行 SQL 语句的次数。

当 STMT_CACHE_HITS 配置参数大于 0 且 SQL 语句已执行的次数少于 STMT_CACHE_HITS，那么数据库服务器将唯一关键字条目插入高速缓存。此规范最大程度地降低了未共享的内存结构所占用的语句高速缓存量，从而为应用程序常用的 SQL 语句留出了更多内存空间。

监视 SQL 语句高速缓存上的命中数，确定工作负载是否正有效使用此高速缓存。

onstat -g ssc 选项显示 SQL 语句高速缓存中完全高速缓存的条目。下边显示 onstat -g ssc 的样本输出。

onstat -g ssc 
 Statement Cache Summary: 
 #lrus   currsize  maxsize   Poolsize   #hits    nolimit 
 4       49456     524288    57344      0        1 
 Statement Cache Entries: 
 lru hash ref_cnt  hits  flag heap_ptr      database           user 
 ----------------- ---- ------------------------------------------------ 
  0  153       0     0    -F  a7e4690      vjp_stores        virginia 
 SELECT * FROM customer, orders 
   WHERE customer.customer_num = orders.customer_num 
     AND order_date > "01/01/07" 
  1  259       0     0     -F  aa58c20     vjp_stores        virginia 
 SELECT * FROM customer, orders 
   WHERE customer.customer_num = orders.customer_num 
     AND order_date > "01/01/2007" 
  2  232       0     1     DF  aa3d020     vjp_stores        virginia 
 SELECT C.customer_num, O.order_num 
 FROM customer C, orders O, items I 
 WHERE C.customer_num = O.customer_num 
     AND O.order_num = I.order_num 
  3  232       1     1     -F  aa8b020     vjp_stores        virginia 
 SELECT C.customer_num, O.order_num 
 FROM customer C, orders O, items I 
 WHERE C.customer_num = O.customer_num 
 AND O.order_num = I.order_num 
 Total number of entries: 4.

要监视数据库服务器在高速缓存内读取 SQL 语句的次数，请查看以下输出列：

在 onstat -g ssc 输出的 Statement Cache Summary 部分中， #hits 列是 SQL_STMT_HITS 配置参数的值。
在上面例子中，输出的 Statement Cache Summary 部分中的 #hits 列具有 0 值，这是 STMT_CACHE_HITS 配置参数的缺省值。
在 onstat -g ssc 输出的 Statement Cache Entries 部分中，hits 列显示数据库服务器执行高速缓存中的每个单独的 SQL 语句的次数。换言之，列显示数据库服务器使用高速缓存中的内存结构而不是分析和优化语句以再次生成语句的次数。
数据库服务器第一次将语句插入到高速缓存中时， hits 值为 0 。
- 上面例子中的前两个 SQL 语句 hits 列值为 0，这表示每个语句都已插入高速缓存中但尚未从高速缓存执行过。
- 上面例子中的最后两个 SQL 语句 hits 列值为 1，这表示两个语句都已从高速缓存执行过一次。

各个条目的 hits 值指示内存结构的共享程度。 hits 列中较高的值表示 SQL 语句高速缓存对于改善性能和内存使用率的帮助较大。

使用 onstat -g ssc all

使用 onstat -g ssc all 选项可确定高速缓存中存在的非共享条目的数量。 onstat -g ssc all 选项除显示 SQL 语句高速缓存中已完全高速缓存的条目外，还显示唯一关键字条目。

确定高速缓存中非共享条目的数量

将 onstat -g ssc all 输出与 onstat -g ssc 输出相比较。
如果这两个输出之间的差别显示 SQL 语句高速缓存中存在的许多非共享条目，那么应提高 STMT_CACHE_HITS 配置参数的值以允许更多共享语句驻留在高速缓存中，从而减少 SQL 语句高速缓存的管理开销。

您可以使用以下一种方法来更改 STMT_CACHE_HITS 参数值：

更新 ONCONFIG 文件以指定 STMT_CACHE_HITS 配置参数。必须重新启动数据库服务器以使新值生效。
使用 onmode -W 命令在数据库服务器运行时动态地增加 STMT_CACHE_HITS 配置参数。

onmode -W STMT_CACHE_HITS 2

如果重新启动数据库服务器，那么该值将恢复为 ONCONFIG 文件中的值。因此，如果希望以后重新启动时保持使用该设置，那么修改 ONCONFIG 文件。

监视和调整 SQL 语句高速缓存的大小

如果 SQL 语句高速缓存过小，那么可能发生以下性能问题：

频繁执行的 SQL 语句不在高速缓存中

最常使用的语句应该保留在 SQL 语句高速缓存中。如果 SQL 语句高速缓存不够大，那么当其他语句进入高速缓存时，数据库服务器可能没有足够的空间来保留这些语句。关于后续执行，数据库服务器必须重新分析、重新优化并将 SQL 语句重新插入高速缓存中。尝试增加 STMT_CACHE_SIZE 。

数据库服务器花费大量时间清除 SQL 语句高速缓存

数据库服务器尝试通过使用阈值（STMT_CACHE_SIZE 参数的 70%）来确定何时从 SQL 语句高速缓存中除去条目，从而防止 SQL 语句高速缓存分配大量内存。如果新条目引起 SQL 语句高速缓存的大小超过阈值，那么数据库服务器在插入新条目前除去最久未使用的条目（当前未使用）。

但是，如果后续查询需要使用已除去的内存结构，那么数据库服务器必须重新分析并重新优化 SQL 语句。重新生成这些内存结构所需要的额外处理时间会使查询的总响应时间增加。

您可以使用 STMT_CACHE_SIZE 配置参数来设置内存中 SQL 语句高速缓存的大小。该参数的值是以千字节为单位表示的大小。如果未设置 STMT_CACHE_SIZE，那么缺省值为 512 千字节。

onstat -g ssc 输出显示 maxsize 列中 STMT_CACHE_SIZE 的值。在上面例子中，该 maxsize 列值为 524288，即缺省值（512 * 1024 = 524288）。

使用 onstat -gssc 和 onstat -g ssc all 选项监视 SQL 语句高速缓存的大小的有效性。如果在高速缓存中看不到应用程序最常用的 SQL 语句的条目，SQL 语句高速缓存可能过小或者有过多的非共享 SQL 语句占用了高速缓存。以下各节描述如何确定这些情况。

更改过小的 SQL 语句高速缓存

如果 SQL 语句高速缓存过小，那么您可以更改它。

要确定 SQL 语句高速缓存是否过小：

运行 onstat -g ssc all 可确定高速缓存是否过小。
查看 onstat -g ssc all 输出的“语句高速缓存条目”部分中以下输出列的值：
- flags 列显示高速缓存中 SQL 语句的当前状态。

第二个位置中的 F 值指示该语句当前已完全高速缓存。

第二个位置中的值 - 指示只有语句文本（唯一关键字条目）位于高速缓存中。第二个位置中带有此 - 值的条目出现在 onstat -gssc all 输出中，但不出现在 onstat -gssc 输出中。

hits 列显示 SQL 语句已经执行的次数，不包括插入高速缓存时的第一次执行。

如果在高速缓存中看不到应用程序最常用的语句的完全高速缓存的条目，且 hits 列中的值相对于占用高速缓存的条目来说很大，那么说明 SQL 语句高速缓存过小。

要更改 SQL 语句高速缓存的大小：

更新 STMT_CACHE_SIZE 配置参数的值。
重新启动数据库服务器以使新值生效。

SQL 语句高速缓存中一次性查询语句过多

当数据库服务器将许多只使用一次的查询放入高速缓存中时，它们可能会取代其他应用程序常用的语句。

查看 onstat -g ssc all 输出的语句高速缓存条目中以下输出列的值。如果看到大量具有以下两个值的条目，那么说明过多的非共享 SQL 语句占用了高速缓存：

flags 列第二个位置中的值为 F
第二个位置中的 F 值指示该语句当前已完全高速缓存。
hits 列值为 0 或 1
hits 列显示 SQL 语句已经执行的次数，不包括插入高速缓存时的第一次执行。

增加 STMT_CACHE_HITS 配置参数的值以防止未共享的 SQL 语句被完全高速缓存。

内存限制和大小

尽管数据库服务器尝试清除 SQL 语句高速缓存，但是有时候由于当前整使用条目而不能除去它们。在这种情况下，SQL 语句高速缓存的大小可能会超过 STMT_CACHE_SIZE 的值。

STMT_CACHE_NOLIMIT 配置参数的缺省值为 1，这表示即使高速缓存的当前大小可能大于 STMT_CACHE_SIZE 参数的值，数据库服务器也会插入该语句。

如果 STMT_CACHE_NOLIMIT 配置参数的值为 0，那么当大小超过 STMT_CACHE_SIZE0 的值时，数据库服务器不会将完全限定的条目或唯一关键字条目插入 SQL 语句高速缓存中。

使用 onstat -g ssc 选项监视 SQL 语句高速缓存的当前大小。查看 onstat -g ssc 输出的以下输出列中的值：

currsize 列显示 SQL 语句高速缓存中当前分配的字节数。
在上面例子中， currsize 列值为 11264 。
maxsize 列显示 STMT_CACHE_SIZE 的值。
在上面例子中， maxsize 列值为 524288，即缺省值（512 * 1024 = 524288）。

当 SQL 语句高速缓存已满且用户当前正在执行其中的所有语句时，用户执行的任何新 SQL 语句都会导致 SQL 语句高速缓存增长超过 STMT_CACHE_SIZE 指定的大小。数据库服务器不再使用 SQL 语句高速缓存内的 SQL 语句之后，该服务器将释放 SQL 语句高速缓存中的内存直到大小达到 STMT_CACHE_SIZE 阈值为止。但是，如果数以千计的用户同时执行几个特定查询，那么 SQL 查询高速缓存会在除去任何语句之前迅猛增长。在这种情况下，可采取以下操作之一：

将 STMT_CACHE_NOLIMIT 参数设置为 0 可防止在高速缓存大小超过 STMT_CACHE_SIZE 参数的值时执行任何插入操作。
将 STMT_CACHE_HITS 参数设置为大于 0 的值以防止高速缓存未共享的 SQL 语句。

您可以使用以下一种方法来更改 STMT_CACHE_NOLIMIT 参数值：

更新 ONCONFIG 文件以指定 STMT_CACHE_NOLIMIT 配置参数。必须重新启动数据库服务器以使新值生效。
使用 onmode -W 命令在数据库服务器运行时动态地重设 STMT_CACHE_NOLIMIT 配置参数。

onmode -W STMT_CACHE_NOLIMIT 0

如果重新启动数据库服务器，那么该值将恢复为 ONCONFIG 文件中的值。因此，如果希望以后重新启动时保持使用该设置，那么修改 ONCONFIG 文件。

SQL 语句高速缓存 onstat 选项的输出描述

onstat -g ssc 选项为 SQL 语句高速缓存列出以下摘要信息。

列描述：

#lrus LRU 队列的数目。使用多个 LRU 队列便于实现同时查找和插入高速缓存条目。

currsize 当前分配给 SQL 语句高速缓存中的条目的字节数

maxsize STMT_CACHE_SIZE 配置参数中指定的字节数

poolsize SQL 语句高速缓存中所有池的累积字节数。使用 onstat -g ssc pool 选项可监视单个池使用情况。

#hits STMT_CACHE_HITS 配置参数的当前设置，它指定在查询插入高速缓存中之前执行的次数

nolimit STMT_CACHE_NOLIMIT 配置参数的 Current® 设置。

onstat -g ssc 选项为高速缓存中每个完全高速缓存的条目列出以下信息。

onstat -g ssc all 为完全高速缓存的条目和唯一关键字条目列出以下信息。

列描述：

lru LRU 标识符

hash 散列存储区标识符

ref_cnt 当前使用此语句的会话数

hits 用户从高速缓存读取查询的次数（不包括该语句第一次进入高速缓存时）

flags 位置 1 的标志代码：

D	指示语句已经被删除一个语句的某一相关性更改时，会从高速缓存中将该语句删除（不再使用）。例如，运行表的 UPDATE STATISTICS 时，优化程序统计信息可能会发生更改，从而导致高速缓存中 SQL 语句的查询计划过时。在此情况下，数据库服务器在下次尝试使用该语句时将其标记为已删除。
-	指示语句未被删除
位置 2 的标志代码：
F	指示查询高速缓存条目已完全高速缓存并包含该查询的内存结构
-	指示未将语句完全高速缓存当执行语句的次数少于 STMT_CACHE_HITS 配置参数的值时，该语句不能完全高速缓存。在第二个位置中带有此值的条目出现在 onstat -g ssc all 输出中，而不是 onstat -g ssc 输出中。