This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 78b9346 Update shadow sql docs (#13035)
78b9346 is described below
commit 78b9346f76cc87cfe8187af6d53cdf3747883be6
Author: gin <[email protected]>
AuthorDate: Thu Oct 14 20:25:14 2021 +0800
Update shadow sql docs (#13035)
* Add shadow sql docs.
* Remove FIXME comment.
* Add shadow use example docs.
* Optimize shadow use example docs.
---
.../content/features/shadow/principle.cn.md | 137 +++++++++++++--------
.../content/features/shadow/principle.en.md | 8 ++
.../content/features/shadow/use-norms.cn.md | 48 +++++++-
.../configuration/built-in-algorithm/shadow.cn.md | 2 +-
.../dml/ShadowDeleteStatementRoutingEngine.java | 1 -
.../dml/ShadowInsertStatementRoutingEngine.java | 1 -
.../dml/ShadowSelectStatementRoutingEngine.java | 1 -
.../dml/ShadowUpdateStatementRoutingEngine.java | 1 -
8 files changed, 134 insertions(+), 65 deletions(-)
diff --git a/docs/document/content/features/shadow/principle.cn.md
b/docs/document/content/features/shadow/principle.cn.md
index fed3288..2e46ad9 100644
--- a/docs/document/content/features/shadow/principle.cn.md
+++ b/docs/document/content/features/shadow/principle.cn.md
@@ -16,6 +16,14 @@ Apache ShardingSphere 通过解析 SQL，对传入的 SQL 进行影子判定，
+**影子库开关**：影子库功能开关，默认值 `false`。可选值 `true`/`false`
+
+**影子库映射**：生产数据源名称和影子数据源名称映射关系。
+
+**影子表**：压测相关的表，影子库中必须包含影子表。影子表需要指定对应的影子库映射和影子算法。
+
+**影子算法**：SQL 路由影子算法。
+
## 路由过程
以 INSERT 语句为例，在写入数据时，Apache ShardingSphere 会对 SQL
进行解析，再根据配置文件中的规则，构造一条路由链。在当前版本的功能中，
@@ -30,38 +38,24 @@ Apache ShardingSphere 通过解析 SQL，对传入的 SQL 进行影子判定，
支持两种算法。影子判定会首先判断执行 SQL
关联的表是否和影子表有交集。如果有交集，对交集部分影子表关联的影子算法依次判定。如果影子表关联影子算法有任何一个判定成功。SQL 语句路由到影子库。
没有交集或者影子算法判定不成功，SQL 语句路由到生产库。
-举例说明，一个电商网站要对下单业务进行压测，对订单表 `t_order`
进行压测。生产数据执行到生产库，即：ds。测试数据执行到影子库，即：ds-shadow。
+### DDL 语句
-1. 使用列影子算法
+仅支持影子算法。一般不会对 DDL 语句的压力测试。主要做为影子库环境的初始化或者影子表调整时执行。
-假设 `t_order` 表中包含字段下单用户ID的 `user_id`。 如果实现的效果，当用户ID为 `0`
的用户创建订单产生的数据，即：`INSERT INTO t_order (order_id, user_id, ...) VALUES (xxx..., 0,
...)` 会执行到影子库，其他数据执行到生产库。
+影子判定会首先判断执行 SQL 是否包含注解，如果包含注解对影子规则中的注解影子算法依次判定。如果注解影子算法有任何一个判定成功。SQL 语句路由到影子库。
+没有 SQL 不包含注解或者注解影子算法判定不成功，路由到生产库。
-建议配置如下（YAML 格式展示）：
+## 影子算法
-```yaml
-enable: true
- data-sources:
- shadow-data-source:
- source-data-source-name: ds
- shadow-data-source-name: ds-shadow
-tables:
- t_order:
- data-source-names: shadow-data-source
- shadow-algorithm-names:
- - user-id-match-algorithm
-shadow-algorithms:
- user-id-match-algorithm:
- type: COLUMN_REGEX_MATCH
- props:
- operation: insert
- column: user_id
- regex: "[0]"
-```
-无需修改任何 SQL 或者代码，只需要对压力测试的数据进行控制就可以实现在线的压力测试。
+影子算法详情，请参见[内置影子算法列表](/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/shadow)。
-2. 使用注解影子算法
+## 使用案例
+
+### 场景需求
-假设 `t_order` 表中没有存储可以对值进行控制的列。或者控制的值不包含在执行 SQL 的中。可以添加一条注解到执行的 SQL 中，即：`SELECT
* FROM t_order WHERE order_id = xxx /*shadow:true,foo:bar,...*/` 会执行到影子库。
+假设一个电商网站要对下单业务进行压测，对订单表 `t_order` 进行压测。生产数据执行到生产库，即：ds。测试数据执行到影子库，即：ds-shadow。
+
+### 影子库配置
建议配置如下（YAML 格式展示）：
@@ -76,36 +70,54 @@ tables:
data-source-names: shadow-data-source
shadow-algorithm-names:
- simple-note-algorithm
+ - user-id-match-algorithm
shadow-algorithms:
simple-note-algorithm:
type: SIMPLE_NOTE
props:
shadow: true
foo: bar
+ user-id-match-algorithm:
+ type: COLUMN_REGEX_MATCH
+ props:
+ operation: insert
+ column: user_id
+ regex: "[0]"
props:
sql-comment-parse-enabled: true
```
-注意：使用注解影子算法，需配合开起 SQL 注解解析。
-3. 混用模式
+**注意**：
+- 如果使用注解影子算法，需要打开解析 SQL 注释配置项 `sql-comment-parse-enabled: true`。默认为关闭状态。
+ 请参考[属性配置](
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/props/)
-假设对 `t_order` 表压测以上两种场景都需要覆盖。即，`INSERT INTO t_order (order_id, user_id, ...)
VALUES (xxx..., 0, ...)` 和 `SELECT * FROM t_order WHERE order_id = xxx
/*shadow:true,foo:bar,...*/` 都执行到影子库。
-建议配置如下（YAML 格式展示）：
+### 影子库环境准备
+
+* 创建影子库 `ds-shadow`。
+
+* 创建压测相关影子表，影子表结构与生产环境对应表结构必须一致。假设需要在影子库创建 `t_order` 表。创建表语句需要添加 SQL 注释
`/*shadow:true,foo:bar,...*/`。即：
+```sql
+CREATE TABLE t_order (order_id INT(11) primary key, user_id int(11) not null,
...) /*shadow:true,foo:bar,...*/
+```
+执行到影子库。
+
+### 影子算法使用
+
+1. 列影子算法使用
+
+假设 `t_order` 表中包含字段下单用户ID的 `user_id`。 如果实现的效果，当用户ID为 `0` 的用户创建订单产生的数据。 即：
+```sql
+INSERT INTO t_order (order_id, user_id, ...) VALUES (xxx..., 0, ...)
+```
+会执行到影子库，其他数据执行到生产库。
+
+无需修改任何 SQL 或者代码，只需要对压力测试的数据进行控制就可以实现在线的压力测试。
+
+算法配置如下（YAML 格式展示）：
```yaml
-enable: true
- data-sources:
- shadow-data-source:
- source-data-source-name: ds
- shadow-data-source-name: ds-shadow
-tables:
- t_order:
- data-source-names: shadow-data-source
- shadow-algorithm-names:
- - user-id-match-algorithm
- - simple-note-algorithm
shadow-algorithms:
user-id-match-algorithm:
type: COLUMN_REGEX_MATCH
@@ -113,36 +125,53 @@ shadow-algorithms:
operation: insert
column: user_id
regex: "[0]"
+```
+
+**注意**：影子表使用列影子算法时，相同类型操作（INSERT, UPDATE, DELETE, SELECT）目前仅支持单个字段。
+
+2. 使用注解影子算法
+
+假设 `t_order` 表中没有存储可以对值进行控制的列。或者控制的值不包含在执行 SQL 的中。可以添加一条注解到执行的 SQL 中，即：
+```sql
+SELECT * FROM t_order WHERE order_id = xxx /*shadow:true,foo:bar,...*/
+```
+会执行到影子库。
+
+算法配置如下（YAML 格式展示）：
+
+```yaml
+shadow-algorithms:
simple-note-algorithm:
type: SIMPLE_NOTE
props:
shadow: true
foo: bar
-
-props:
- sql-comment-parse-enabled: true
```
-对复杂场景压力测试支持度高。
-### DDL 语句
+3. 混合使用影子模式
-仅支持影子算法。一般不会对 DDL 语句的压力测试。主要为影子库环境的初始化或者影子表调整时执行。
+假设对 `t_order` 表压测以上两种场景都需要覆盖。 即，
-影子判定会首先判断执行 SQL 是否包含注解，如果包含注解对影子规则中的注解影子算法依次判定。如果注解影子算法有任何一个判定成功。SQL 语句路由到影子库。
-没有 SQL 不包含注解或者注解影子算法判定不成功，路由到生产库。
+```sql
+INSERT INTO t_order (order_id, user_id, ...) VALUES (xxx..., 0, ...);
-假设需要在影子库创建 `t_order` 表。即：`CREATE TABLE t_order (order_id INT(11) primary key,
user_id int(11) not null, ...) /*shadow:true,foo:bar,...*/` 执行到影子库。
+SELECT * FROM t_order WHERE order_id = xxx /*shadow:true,foo:bar,...*/;
+```
+满足对复杂场景压力测试支持。
-影子规则配置只需包含（YAML 格式展示）：
+算法配置如下（YAML 格式展示）：
```yaml
shadow-algorithms:
+ user-id-match-algorithm:
+ type: COLUMN_REGEX_MATCH
+ props:
+ operation: insert
+ column: user_id
+ regex: "[0]"
simple-note-algorithm:
type: SIMPLE_NOTE
props:
shadow: true
foo: bar
-
-props:
- sql-comment-parse-enabled: true
```
diff --git a/docs/document/content/features/shadow/principle.en.md
b/docs/document/content/features/shadow/principle.en.md
index 3dbe189..10b2612 100644
--- a/docs/document/content/features/shadow/principle.en.md
+++ b/docs/document/content/features/shadow/principle.en.md
@@ -25,3 +25,11 @@ TODO
### DDL Statement
TODO
+
+## Shadow Algorithm
+
+TODO
+
+## Use Example
+
+TODO
diff --git a/docs/document/content/features/shadow/use-norms.cn.md
b/docs/document/content/features/shadow/use-norms.cn.md
index 865fa17..7d73de8 100644
--- a/docs/document/content/features/shadow/use-norms.cn.md
+++ b/docs/document/content/features/shadow/use-norms.cn.md
@@ -4,14 +4,50 @@ title = "使用规范"
weight = 3
+++
-## 支持项
+## 影子数据库
+
+### 支持项
* 后端数据库为 MySQL、Oracle、PostgreSQL、SQLServer；
-* 支持MDL，DDL语句；
-* 兼容所有常用SQL；
-## 不支持项
+### 不支持项
+
+* NoSQL 数据库；
+
+## 影子算法
+
+### 支持项
-* 影子字段无法支持范围值匹配操作，如：BETWEEN、HAVING、subQuery等；
+* 注解影子算法支持 MDL，DDL 语句；
+* 列影子算法基本支持常用的 MDL 语句；
+
+### 不支持项
+
+* 列影子算法不支持 DDL 语句。
+* 列影子算法不支持范围值匹配操作，比如：子查询、BETWEEN、GROUP BY ... HAVING等；
* 使用影子库功能+分库分表功能，部分特殊SQL不支持，请参考[SQL使用规范](
https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/)；
-* DDL语句不支持列影子算法；
+
+## 列影子算法 DML 语句支持度列表
+
+* INSERT 语句
+
+对 `INSERT` 插入的字段和插入的值判定
+
+| *操作类型* | *SQL语句* | *是否支持* |
+| -------- | --------- | --------- |
+| INSERT | INSERT INTO table (column,...) VALUES (value,...) | 支持 |
+| INSERT | INSERT INTO table (column,...) VALUES (value,...),(value,...),...
| 支持 |
+| INSERT | INSERT INTO table (column,...) SELECT column1 from table1 where
column1 = value1 | 不支持 |
+
+* SELECT，UPDATE，DELETE语句
+
+对 `WHERE` 条件中包含的字段和值进行判定
+
+| *条件类型* | *SQL语句* | *是否支持* |
+| -------- | --------- | --------- |
+| = | SELECT/UPDATE/DELETE ... WHERE column = value | 支持 |
+| LIKE/NOT LIKE | SELECT/UPDATE/DELETE ... WHERE column LIKE/NOT LIKE value |
支持 |
+| IN/NOT IN | SELECT/UPDATE/DELETE ... WHERE column IN/NOT IN
(value1,value2,...) | 支持 |
+| BETWEEN | SELECT/UPDATE/DELETE ... WHERE column BETWEEN value1 AND value2 |
不支持 |
+| GROUP BY ... HAVING... | SELECT/UPDATE/DELETE ... WHERE ... GROUP BY column
HAVING column > value; | 不支持 |
+| 子查询 | SELECT/UPDATE/DELETE ... WHERE column = (SELECT column FROM table
WHERE column = value) | 不支持 |
diff --git
a/docs/document/content/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/shadow.cn.md
b/docs/document/content/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/shadow.cn.md
index f316933..cbf333d 100644
---
a/docs/document/content/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/shadow.cn.md
+++
b/docs/document/content/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/shadow.cn.md
@@ -14,7 +14,7 @@ weight = 5
| *属性名称* | *数据类型* | *说明* |
| -------------- | --------- | ------- |
| column | String | 匹配列 |
-| operation | String | SQL操作类型（insert, update, delete, select） |
+| operation | String | SQL操作类型（INSERT, UPDATE, DELETE, SELECT） |
| regex | String | 匹配正则表达式 |
## 注解影子算法
diff --git
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowDeleteStatementRoutingEngine.java
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowDeleteStatementRoutingEngine.java
index b3507b9..2ca0fd7 100644
---
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowDeleteStatementRoutingEngine.java
+++
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowDeleteStatementRoutingEngine.java
@@ -79,7 +79,6 @@ public final class ShadowDeleteStatementRoutingEngine extends
AbstractShadowDMLS
return deleteStatementContext.getAllTables();
}
- // FIXME refactor the method when sql parses the note and puts it in the
statement context
@Override
protected Optional<Collection<String>> parseSqlNotes() {
Collection<String> result = new LinkedList<>();
diff --git
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowInsertStatementRoutingEngine.java
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowInsertStatementRoutingEngine.java
index 7355a96..e666141 100644
---
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowInsertStatementRoutingEngine.java
+++
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowInsertStatementRoutingEngine.java
@@ -82,7 +82,6 @@ public final class ShadowInsertStatementRoutingEngine extends
AbstractShadowDMLS
return insertStatementContext.getAllTables();
}
- // FIXME refactor the method when sql parses the note and puts it in the
statement context
@Override
protected Optional<Collection<String>> parseSqlNotes() {
Collection<String> result = new LinkedList<>();
diff --git
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowSelectStatementRoutingEngine.java
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowSelectStatementRoutingEngine.java
index 8cc3283..a24d9c9 100644
---
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowSelectStatementRoutingEngine.java
+++
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowSelectStatementRoutingEngine.java
@@ -86,7 +86,6 @@ public final class ShadowSelectStatementRoutingEngine extends
AbstractShadowDMLS
return selectStatementContext.getAllTables();
}
- // FIXME refactor the method when sql parses the note and puts it in the
statement context
@Override
protected Optional<Collection<String>> parseSqlNotes() {
Collection<String> result = new LinkedList<>();
diff --git
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowUpdateStatementRoutingEngine.java
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowUpdateStatementRoutingEngine.java
index 1f1f236..3043245 100644
---
a/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowUpdateStatementRoutingEngine.java
+++
b/shardingsphere-features/shardingsphere-shadow/shardingsphere-shadow-core/src/main/java/org/apache/shardingsphere/shadow/route/engine/dml/ShadowUpdateStatementRoutingEngine.java
@@ -79,7 +79,6 @@ public final class ShadowUpdateStatementRoutingEngine extends
AbstractShadowDMLS
return updateStatementContext.getAllTables();
}
- // FIXME refactor the method when sql parses the note and puts it in the
statement context
@Override
protected Optional<Collection<String>> parseSqlNotes() {
Collection<String> result = new LinkedList<>();
