在同时处理高并发事务（OLTP）和复杂分析（OLAP）的混合负载场景下，将不同类型的查询分发到合适的存储引擎是优化性能的关键。PolarDB分布式版提供了基于规则的行列路由功能，允许您通过配置账户、SQL模板或关键词规则，将查询精确路由到行存节点或列存只读实例，从而在不修改业务代码的情况下实现业务分流，提升集群整体性能。
功能简介
PolarDB分布式版通过以下两种方式使用列存只读实例进行业务分流：
直连列存只读实例：业务应用直接连接列存只读实例的地址，将分析类查询显式地发送至该实例。尽管此方式较为直接，但需要根据OLTP和OLAP类业务进行手动分类，并对业务代码进行相应修改。
连接主实例：业务应用统一连接主实例地址，由数据库优化器基于代价估算自动将复杂查询路由到列存只读实例。此方式对业务透明，但可能因代价估算不准而导致路由错误。
基于规则的行列路由功能结合了上述两种方式的优点。它允许您通过主实例的连接地址，根据SQL的来源账户、特定模板或关键词来定义精确的路由策略，既避免了修改业务代码，又保证了路由的准确性，是混合负载场景下实现业务分流的理想选择。
适用范围
实例的计算节点（CN）版本需为5.4.20或更高版本。
注意事项
性能与资源：访问列存索引会消耗大量计算资源，且并发承载能力有限。建议为列存查询添加独立的列存只读实例，以避免影响主实例的OLTP业务。此功能仅适用于低并发、重扫描或重聚合的OLAP查询，高QPS的查询不应路由到列存只读实例。
数据同步延迟：列存数据与行存数据存在秒级延迟，对数据实时性要求高的业务不应将查询路由到列存只读实例。
功能限制：规则路由仅对
SELECT查询生效。事务内的查询无法通过规则路由到列存只读实例。语法说明：路由规则中定义的所有字段（如用户名、关键词）均不区分大小写。
操作步骤
步骤一：环境准备
配置主实例的连接地址。
说明此操作是将列存只读实例关联到主实例的连接地址，使得通过主实例地址发起的查询能够被路由到该列存只读实例。
前往PolarDB分布式版控制台，在实例列表页面的PolarDB-X 2.0页签中，单击目标主实例ID，进入基本信息页面。
在连接信息区域，单击配置管理。
在配置管理对话框的资源配置区域，将目标列存只读实例从可选实例移动到已选实例列表。
步骤二：创建路由规则
您可以通过高权限账号连接主实例，执行CREATE ROUTING_RULE命令创建路由规则。
CREATE ROUTING_RULE {IF NOT EXISTS} 'rule_name' TO 'user_name'
{FILTER BY TEMPLATE('template_id') | FILTER BY KEYWORD('kw1','kw2')}
WITH TYPE=[ROW | COLUMNAR];参数说明
参数 | 说明 |
| 规则名称，全局唯一。 |
| 数据库账号名。 |
| 可选。过滤条件，用于指定模板路由或关键词路由。如果省略，则为账号路由。
说明
|
| 路由目标。
|
路由优先级
当一条SQL语句可能匹配多个规则时，系统将遵循以下优先级顺序：
不同规则类型，按模式优先级排序：模板路由 > 关键词路由 > 账号路由。
相同规则类型，按账号范围优先级排序：指定账号的规则 > 应用于所有账号（
%）的规则。相同规则类型与账号范围，按路由类型优先级排序：行存（
ROW）规则 > 列存（COLUMNAR）规则。
示例
创建示例
orders表，包含列存索引orders_col_index。CREATE TABLE `orders` ( `o_orderkey` int(11) NOT NULL, `o_custkey` int(11) NOT NULL, `o_orderstatus` varchar(1) NOT NULL, `o_totalprice` decimal(15, 2) NOT NULL, `o_orderdate` date NOT NULL, `o_orderpriority` varchar(15) NOT NULL, `o_clerk` varchar(15) NOT NULL, `o_shippriority` int(11) NOT NULL, `o_comment` varchar(79) NOT NULL, PRIMARY KEY (`o_orderkey`), CLUSTERED COLUMNAR INDEX `orders_col_index` (`o_orderdate`, `o_orderkey`) PARTITION BY HASH(`o_orderkey`) PARTITIONS 64 ) ENGINE = InnoDB DEFAULT CHARSET = latin1 PARTITION BY KEY(`o_orderkey`) PARTITIONS 16配置路由规则。
账号路由
将来自
test0账号的所有查询默认路由到列存。CREATE ROUTING_RULE 'test0_col_rule' TO 'test0' WITH TYPE=COLUMNAR;测试效果
test0账号执行任意SELECT查询，通过EXPLAIN可以看到查询计划将访问列存索引（如orders_col_index）。EXPLAIN SELECT COUNT(*) FROM orders;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(COUNT(*)="SUM(COUNT(*))") | | Exchange(distribution=single, collation=[]) | | PartialHashAgg(COUNT(*)="COUNT()") | | OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554`") | | HitCache:false | | Source:PLAN_CACHE | | TemplateId: bc92bbe3 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.04 sec)其他账号执行
SELECT查询，则不会访问列存索引。EXPLAIN SELECT COUNT(*) FROM orders;+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(count(*)="SUM(count(*))") | | Gather(concurrent=true) | | LogicalView(tables="orders[p1,p2,p3,...p16]", shardCount=16, sql="SELECT COUNT(*) AS `count(*)` FROM `orders` AS `orders`", physicalPlan="[{table:orders,selectType:SIMPLE,type:index,key:PRIMARY,rows:1,filtered:100.0,extra:Scan rows(1, 1); Using index}]") | | HitCache:false | | Source:PLAN_CACHE | | TemplateId: b856efe3 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.09 sec)
模板路由
将
test0账号下特定类型的查询强制路由到行存。获取SQL模板ID。
假设目标SQL为
SELECT COUNT(*) FROM orders WHERE o_orderkey > ?;，则可使用EXPLAIN查看查询计划的模板IDTemplateId。EXPLAIN SELECT COUNT(*) FROM orders WHERE o_orderkey > 10;输出结果如下，模板ID
TemplateId为24dd9dde。+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(COUNT(*)="SUM(COUNT(*))") | | Exchange(distribution=single, collation=[]) | | PartialHashAgg(COUNT(*)="COUNT()") | | OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554` WHERE (`o_orderkey` > ?)") | | HitCache:true | | Source:PLAN_CACHE | | TemplateId: 24dd9dde | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.01 sec)创建模板路由规则。
CREATE ROUTING_RULE 'test0_row_rule' TO 'test0' FILTER BY TEMPLATE('24dd9dde') WITH TYPE=ROW;
测试效果
test0账号执行符合该模板的查询（如... WHERE o_orderkey > 1000），查询将被路由到行存（orders表）。EXPLAIN SELECT COUNT(*) FROM orders WHERE o_orderkey > 1000;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(COUNT(*)="SUM(COUNT(*))") | | Gather(concurrent=true) | | LogicalView(tables="orders[p1,p2,p3,...p16]", shardCount=16, sql="SELECT COUNT(*) AS `COUNT(*)` FROM `orders` AS `orders` WHERE (`o_orderkey` > ?)", physicalPlan="[{table:orders,selectType:SIMPLE,type:index,key:PRIMARY,rows:1,filtered:100.0,extra:Scan rows(1, 1); Using where; Using index}]") | | HitCache:true | | Source:PLAN_CACHE | | TemplateId: 24dd9dde | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.01 sec)将
test0账号按照账号路由的方式，设置所有查询默认路由到列存索引。此时，执行其他SELECT查询，则会访问列存索引，而非行存。说明根据路由优先级，模板路由优先于账号路由。因此，非模板的查询将依据账号路由规则访问列存索引。
EXPLAIN SELECT COUNT(*) FROM orders;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(COUNT(*)="SUM(COUNT(*))") | | Exchange(distribution=single, collation=[]) | | PartialHashAgg(COUNT(*)="COUNT()") | | OSSTableScan(tables="orders_col_index_$9554[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$9554` AS `orders_col_index_$9554`") | | HitCache:true | | Source:PLAN_CACHE | | TemplateId: bc92bbe3 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec)
关键词路由
将所有账号下包含特定关键词序列的查询路由到列存。
获取SQL的关键词列表。你可通过使用
EXPLAIN KEYWORD获取查询对应的关键词并选择合适的关键词列表。EXPLAIN KEYWORD SELECT COUNT(1) AS cnt FROM orders WHERE o_clerk LIKE '%xx%'; +--------------------------------------------------------------------------------------------------+ | KEYWORDS_LIST | +--------------------------------------------------------------------------------------------------+ | 'select', 'count', '(', '1', ')', 'as', 'cnt', 'from', 'orders', 'where', 'o_clerk', 'like', '?' | +--------------------------------------------------------------------------------------------------+选择一组能唯一定位该类查询的关键词，创建规则。
CREATE ROUTING_RULE 'keyword_columnar' TO '%' FILTER BY KEYWORD('select', 'count', 'as', 'cnt', 'from', 'orders', 'where', 'o_clerk', 'like') WITH TYPE="columnar";任意用户执行匹配该关键词序列的查询时，都将被路由到列存索引。
测试效果
通过
EXPLAIN执行符合条件的SQL可以看到查询计划将访问列存索引（如orders_col_index）。EXPLAIN SELECT COUNT(1) AS cnt FROM orders WHERE o_clerk LIKE '%xx%';+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashAgg(cnt="SUM(cnt)") | | Exchange(distribution=single, collation=[]) | | PartialHashAgg(cnt="COUNT()") | | OSSTableScan(tables="orders_col_index_$6ef7[p1,p2,p3,...p64]", shardCount=64, partition=[remote], sql="SELECT `o_orderkey` FROM `orders_col_index_$6ef7` AS `orders_col_index_$6ef7` WHERE (`o_clerk` LIKE ?)") | | HitCache:false | | Source:PLAN_CACHE | | TemplateId: e1b2617d | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.03 sec)
步骤三：查看路由规则
查看所有规则
使用SHOW ROUTING_RULES或查询INFORMATION_SCHEMA.ROUTING_RULES视图。
SHOW ROUTING_RULES [WHERE expr] [ORDER BY expr] [LIMIT expr]示例
SHOW ROUTING_RULES ORDER BY HIT_COUNT DESC;
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| ID | RULE_NAME | USER_NAME | TEMPLATE_ID | KEYWORDS | ROUTING_TYPE | CREATE_TIME | HIT_COUNT |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| 19 | keyword_columnar | % | NULL | [select, count, as, cnt, from, orders, where, o_clerk, like] | columnar | 2025-09-12 11:46:49 | 1 |
| 18 | test0_row_rule | test0 | 24dd9dde | [] | row | 2025-09-12 11:46:12 | 0 |
| 17 | test0_col_rule | test0 | NULL | [] | columnar | 2025-09-12 11:45:50 | 0 |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
3 rows in set (0.02 sec)
SHOW * FROM information_schema.routing_rules ORDER BY HIT_COUNT DESC;
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| ID | RULE_NAME | USER_NAME | TEMPLATE_ID | KEYWORDS | ROUTING_TYPE | CREATE_TIME | HIT_COUNT |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
| 19 | keyword_columnar | % | NULL | [select, count, as, cnt, from, orders, where, o_clerk, like] | columnar | 2025-09-12 11:46:49 | 1 |
| 18 | test0_row_rule | test0 | 24dd9dde | [] | row | 2025-09-12 11:46:12 | 0 |
| 17 | test0_col_rule | test0 | NULL | [] | columnar | 2025-09-12 11:45:50 | 0 |
+------+------------------+-----------+-------------+--------------------------------------------------------------+--------------+---------------------+-----------+
3 rows in set (0.01 sec)查看单条SQL的路由过程
使用EXPLAIN ROUTING命令查看指定SQL的详细路由决策过程。
EXPLAIN ROUTING <SQL>示例
EXPLAIN ROUTING SELECT COUNT(*) FROM orders;+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ROUTING TYPE | CANDIDATE OPTIMIZER TYPES | WORKLOAD TYPE | OPTIMIZER TYPE | PLAN TYPE | DETAIL TRACE |
+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COLUMNAR | NULL | AP | COLUMNAR | COLUMNAR |
determine routing type:
current user: test0
routing type: COLUMNAR, caused by determined by user:test0
cached plan from plan cache:
routing type: COLUMNAR, caused by plan cache
candidate optimizer types: {null}, caused by plan cache doesn't record
workload type: AP, caused by plan cache
optimizer type: COLUMNAR, caused by plan cache
plan type: COLUMNAR, caused by plan cache
plan type: COLUMNAR, caused by setting from planner context
|
+--------------+---------------------------+---------------+----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)路由类型
ROUTING TYPE：COLUMNAR。可选的优化器类型
CANDIDATE OPTIMIZER TYPES：NULL。负载类型
WORKLOAD TYPE：AP。使用的优化器类型
OPTIMIZER TYPE：COLUMNAR。计划类型为
PLAN TYPE：COLUMNAR。细节
DETAIL TRACE：说明了上述类型决定性因素。
步骤四：删除路由规则
使用DROP ROUTING_RULE命令删除一个或多个规则。
DROP ROUTING_RULE {IF EXISTS} ruleName1{,...,ruleNameN};说明
删除路由规则需要使用高权限账号。
示例
DROP ROUTING_RULE test0_col_rule,test0_row_rule,keyword_columnar;常见问题
相关内容
该文章对您有帮助吗？