行存和列存自动分流的方案说明 - 云原生数据库 PolarDB

如果您的业务中，OLAP类型的查询与OLTP类型的查询是通过同一个应用程序访问数据库，此时如果要实现分流，您需要设置集群地址的读写模式，并开启自动分流功能。自动分流功能开启后，数据库代理基于业务流入的SQL语句的预估执行代价来实现自动分流，从而实现SQL查询的最大性能。当SQL语句的预估执行代价超过一定值时，数据库代理可自动将该请求分流至只读列存节点处理，反之该请求则由行存只读节点或主节点来处理。

自动分流方案说明

PolarDB MySQL版的数据库代理功能支持将SQL语句的预估执行代价作为判断指标，通过判断预估执行代价是否会超过特定阈值（该阈值可配置），决定将该SQL请求分流到行存或是列存节点上处理，从而发挥出行存和列存的最佳性能。

分流规则：

OLTP类业务：一般包含读和写的请求。写请求统一由主节点处理，读请求由只读行存节点或主节点处理。
OLAP类业务：一般仅包含读请求。读请求统一由只读列存节点处理。

自动分流方案：

主节点和只读列存节点分流：由于主节点也属于行存节点，因此也可处理OLTP类读请求。该方案下，写请求以及OLTP类读请求分流至主节点，OLAP类读请求分流至只读列存节点。
只读行存节点和只读列存节点分流：该方案下，写请求依旧分流至主节点，OLTP类读请求分流至只读行存节点或主节点，OLAP类读请求分流至只读列存节点。

混合下分流

使用限制

服务节点至少包含一个只读列存节点和一个行存节点。

步骤1：开启列存和行存自动引流功能

登录PolarDB控制台。
在左上角，选择集群所在地域。
找到目标集群，单击集群ID。
在集群基本信息页的数据库代理企业版区域，找到目标集群地址，单击目标集群地址名称右侧的配置。
根据实际情况选择合适的读写模式。
- 集群地址的读写模式设置为可读可写（自动读写分离）。
- 集群地址的读写模式设置为只读，并且集群地址的负载均衡策略设置为基于活跃请求数负载均衡。
在服务节点中，选择主节点和需要参与处理请求的行存和列存只读节点。并在右侧HTAP优化类别中，选择开启行存/列存自动引流。单击确定。
说明
服务节点中需要至少选择一个只读列存节点。
示例1：在下图中，除了主节点外，服务节点还包含1个只读节点（即只读行存节点）和2个只读列存节点。若此时开启自动引流功能，则：
- 写请求将引流至主节点。
- OLAP类读请求引流至只读列存节点。
- OLTP类读请求引流至只读行存节点。如果负载均衡设置中将主库是否接受读设置为是时，也可能会引流至主节点。
示例2：在下图中，服务节点包括主节点和1个只读列存节点。若此时开启自动引流功能后，写请求将引流至主节点，OLAP类读请求将引流至只读列存节点，OLTP类读请求将引流至主节点。
说明
可读可写（自动读写分离）模式下，无论主节点是否已被添加在服务节点中，所有写请求只会发往主节点。

步骤2：配置自动引流阈值

开启了列存和行存自动引流后，您需要设置SQL语句的预估执行代价阈值。阈值配置完成后，数据库代理将此阈值作为判断依据。若业务侧请求的SQL语句的预估执行代价大于该阈值，则将该请求引流至列存节点执行；若小于该阈值，则将该请求引流至行存节点执行。

当前SQL语句的预估执行代价阈值由下表中的参数决定，您可以在集群的参数配置页面，根据自身业务情况修改参数的值，从而调整自动分流效果。

参数名称	描述
loose_imci_ap_threshold	分发至列存节点的SQL语句的预估执行代价阈值。默认值：50000。说明开启列存和行存自动引流功能后，如果SQL语句的预估执行代价阈值大于50000，则路由至列存节点。重要 PolarDB MySQL版8.0.1.1.39与8.0.2.2.23及之后的版本，该参数被弃用，分流参数统一使用`loose_cost_threshold_for_imci`。
loose_cost_threshold_for_imci	列存节点内部当前SQL语句的预估执行代价阈值。默认值：50000。说明开启列存和行存自动引流功能后，如果SQL语句的预估执行代价阈值大于50000，则选择列存执行计划。否则，选择行存执行计划。

您可以通过SHOW STATUS LIKE 'Last_query_cost'命令精确查询上一条SQL语句的预估执行代价，从而判断如何调整上表中的参数值。

说明

如果使用集群地址连接数据库，您需要在SHOW STATUS LIKE 'Last_query_cost'命令前添加HINT语法/* ROUTE_TO_LAST_USED*/，以确保能够在正确的节点查询到上一条语句的预估执行代价。如下：

/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';

例如，执行以下语句，可查看上一条SQL语句的预估执行代价：

SHOW STATUS LIKE 'Last_query_cost';

查询结果如下：

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Last_query_cost      | 2     |
+----------------------+-------+
1 row in set (0.01 sec)

从以上查询结果可以看出，该SQL语句的预估执行代价为2。

此时若要实现将该SQL查询分流至列存节点的列存计划执行，则需要设置的参数值如下：

PolarDB MySQL版8.0.1.1.38与8.0.2.2.22及之前的版本
需要将loose_imci_ap_threshold和loose_cost_threshold_for_imci参数的值分别设置为1。
PolarDB MySQL版8.0.1.1.39与8.0.2.2.23及之后的版本
需要将参数loose_cost_threshold_for_imci的值设置为1。

通过`HINT`语法强制执行行存或列存执行计划

如果使用行存和列存自动分流没有达到预期效果，您可以使用HINT语法强制执行行存或列存执行计划。

说明

HINT语法仅对指定的SQL语句生效，对其它连接或同一个连接下的其它SQL语句没有影响。
如果在5.7.7版本之前的MySQL客户端执行HINT语法，则在连接数据库引擎时需要添加--comments选项。您可以使用mysql --version命令来查看MySQL客户端版本。

强制执行列存执行计划。
- PolarDB MySQL版8.0.1.1.38与8.0.2.2.22及之前的版本
  使用数据库代理进行行存和列存自动分流时，还可以通过HINT语法使得数据库代理将SQL语句强制分发至列存节点执行，而不受loose_imci_ap_threshold影响。具体方式为：在SQL关键字前添加/* FORCE_IMCI_NODES */。例如：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
  路由至列存节点的SQL语句依然会受loose_cost_threshold_for_imci影响，如果需要强制SQL语句选择列存执行计划，可以通过HINT语法降低loose_cost_threshold_for_imci的值。例如：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
- PolarDB MySQL版8.0.1.1.39与8.0.2.2.23及之后的版本
  只需要通过HINT语法将参数loose_cost_threshold_for_imci设置为0即可。
```
EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
说明
通过/*+SET_VAR()*/修改阈值时，需要删掉参数前缀loose_ ，否则HINT语句不生效。
强制执行行存执行计划。
您可以通过HINT语法将USE_IMCI_ENGINE的值修改为OFF，来强制SQL语句使用行存执行计划。示例如下：
```
EXPLAIN SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```

自动分流方案说明

使用限制

步骤1：开启列存和行存自动引流功能

步骤2：配置自动引流阈值

通过HINT语法强制执行行存或列存执行计划

通过`HINT`语法强制执行行存或列存执行计划