2023-06-25

数据分片--使用规范

虽然 Apache ShardingSphere 希望能够完全兼容所有的SQL以及单机数据库，但分布式为数据库带来了更加复杂的场景。 Apache ShardingSphere 希望能够优先解决海量数据 OLTP 的问题，OLAP 的相关支持，会一点一点的逐渐完善。

一、SQL

支持项
- 路由至单数据节点
- 路由至多数据节点
不支持项
- 路由至多数据节点
- 对分片键进行操作
示例
- 支持的SQL
- 不支持的SQL
DISTINCT支持情况详细说明
- 支持的SQL
- 不支持的SQL

1.1、支持项

1、路由至单数据节点

100%全兼容（目前仅MySQL，其他数据库完善中）。

2、路由至多数据节点

全面支持DML、DDL、DCL、TCL和部分DAL。支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）。以下用最为复杂的DML举例：

SELECT主语句

SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...]
[WHERE predicates]
[GROUP BY {col_name | position} [ASC | DESC], ...]
[ORDER BY {col_name | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

select_expr

* | 
[DISTINCT] COLUMN_NAME [AS] [alias] | 
(MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] | 
COUNT(* | COLUMN_NAME | alias) [AS] [alias]

table_reference

tbl_name [AS] alias] [index_hint_list]
| table_reference ([INNER] | {LEFT|RIGHT} [OUTER]) JOIN table_factor [JOIN ON conditional_expr | USING (column_list)]

1.2、不支持项

1、路由至多数据节点

部分支持CASE WHEN

CASE WHEN 中包含子查询不支持
CASE WHEN 中使用逻辑表名不支持（请使用表别名）不支持 HAVING、UNION (ALL)

部分支持子查询

子查询中使用WHERE条件时，必须包含分片键，当外层查询中也包含分片键时，子查询和外层查询中的分片键必须保持一致

除了分页子查询的支持之外(详情请参考分页)，也支持同等模式的子查询。无论嵌套多少层，ShardingSphere都可以解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。

例如，以下子查询可以支持：

SELECT COUNT(*) FROM (SELECT * FROM t_order) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1;

以下子查询不支持：

SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o;
SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;```

简单来说，通过子查询进行非功能需求，在大部分情况下是可以支持的。比如分页、统计总数等；而通过子查询实现业务查询当前并不能支持。

由于归并的限制，子查询中包含聚合函数目前无法支持。

不支持包含schema的SQL。因为ShardingSphere的理念是像使用一个数据源一样使用多数据源，因此对SQL的访问都是在同一个逻辑schema之上。

2、对分片键进行操作

运算表达式和函数中的分片键会导致全路由。

假设create_time为分片键，则无法精确路由形如SQL：

SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';

由于ShardingSphere只能通过SQL字面提取用于分片的值，因此当分片键处于运算表达式或函数中时，ShardingSphere无法提前获取分片键位于数据库中的值，从而无法计算出真正的分片值。

当出现此类分片键处于运算表达式或函数中的SQL时，ShardingSphere将采用全路由的形式获取结果。

支持&不支持的 SQL：
https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/

二、分页

分页性能
- 性能瓶颈
- ShardingSphere的优化
分页方案优化
分页子查询

完全支持MySQL、PostgreSQL和Oracle的分页查询，SQLServer由于分页查询较为复杂，仅部分支持。

2.1、分页性能

1、性能瓶颈

查询偏移量过大的分页会导致数据库获取数据性能低下，以MySQL为例：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。而在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010

即将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。因为原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1,000,010 * 2的记录至客户端。

2.2、分页方案优化

由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案：

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询：

SELECT * FROM t_order WHERE id > 100000 LIMIT 10

2.3、分页子查询

Oracle和SQLServer的分页都需要通过子查询来处理，ShardingSphere支持分页相关的子查询。

Oracle
支持使用rownum进行分页：

SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT o.order_id as order_id FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id) row_ WHERE rownum <= ?) WHERE rownum > ?

目前不支持rownum + BETWEEN的分页方式。

SQLServer
支持使用TOP + ROW_NUMBER() OVER配合进行分页：

SELECT * FROM (SELECT TOP (?) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > ? ORDER BY temp.order_id

支持SQLServer 2012之后的OFFSET FETCH的分页方式：

SELECT * FROM t_order o ORDER BY id OFFSET ? ROW FETCH NEXT ? ROWS ONLY

目前不支持使用WITH xxx AS (SELECT …)的方式进行分页。由于Hibernate自动生成的SQLServer分页语句使用了WITH语句，因此目前并不支持基于Hibernate的SQLServer分页。目前也不支持使用两个TOP + 子查询的方式实现分页。

MySQL, PostgreSQL
MySQL和PostgreSQL都支持LIMIT分页，无需子查询：

SELECT * FROM t_order o ORDER BY id LIMIT ? OFFSET ?

三、解析器

特定SQL方言解析器
默认SQL方言解析器
RDL(Rule definition Language)方言解析器

ShardingSphere使用不同解析器支持SQL多种方言。对于未实现解析器的特定SQL方言，默认采用SQL92标准进行解析。

3.1、特定SQL方言解析器

PostgreSQL解析器
MySQL解析器
Oracle解析器
SQLServer解析器

注：MySQL解析器支持的方言包括MySQL、H2和MariaDB。

3.2、默认SQL方言解析器

其他SQL方言，如SQLite、Sybase、DB2和Informix等，默认采用SQL92标准进行解析。

3.3、RDL(Rule definition Language)方言解析器

ShardingSphere独有的RDL方言解析器。该解析器主要解析ShardingSphere内部的RDL方言，即自定义的SQL。请查阅RDL了解详情。

四、RDL

什么是 RDL?
RDL使用实战
- 前置工作
- 启动ShardingProxy
- 创建分布式数据库和分片表
- 注意

4.1、什么是 RDL?

RDL（Rule Definition Language）是ShardingSphere特有的内置SQL语言。用户可以使用RDL语言向SharidngSphere注入数据源资源、创建分片规则等，即向ShardingSphere注入数据库资源信息和分片规则信息。 RDL使得用户抛弃对传统Yaml或其他配置文件的依赖，像使用数据库一样，通过SQL进行资源信息的注入和规则的配置。

当前，RDL主要包括以下SQL内容：

Create DATASOURCES，用于注入数据源信息。

// SQL
CREATE DATASOURCES (
ds_key=host_name:host_port:db_name:user_name:pwd
[, ds_key=host_name:host_port:db_name:user_name:pwd, ...]
)

// Example
CREATE datasources (
ds0=127.0.0.1:3306:demo_ds_0:root:pwd, 
ds1=127.0.0.1:3306:demo_ds_1:root:pwd)

CREATE SHARDINGRULE，用于配置分片规则。

CREATE SHARDINGRULE (
sharding_table_name=sharding_algorithm(algorithm_property[, algothrim_property])
[, sharding_table_name=sharding_algorithm_type(algorithm_property[, algothrim_property]), ...]
)

sharding_algorithm_type: {MOD | HASH_MODE} 
mod_algorithm_properties: sharding_column,shards_amount
mod_hash_algorithm_properties: sharding_column,shards_amount

// Example
CREATE shardingrules (
t_order=hash_mod(order_id, 4), 
t_item=mod(item_id, 2)
)

4.2、RDL使用实战

1、前置工作

Start the service of MySQL instances
Create MySQL databases (Viewed as the resources for ShardingProxy)
Create a role or user with creating privileges for ShardingProxy
Start the service of Zookeeper (For persisting configuration)

2、启动ShardingProxy

Add governance and authentication setting item to the server.yaml (Please refer to the example in this file)
Start the ShardingProxy (Instruction)

3、创建分布式数据库和分片表

连接到ShardingProxy
创建分布式数据库

CREATE DATABASE sharding_db;

使用新创建的数据库

USE sharding_db;

配置数据源信息

CREATE datasources (
ds0=127.0.0.1:3306:demo_ds_2:root:pwd, 
ds1=127.0.0.1:3306:demo_ds_3:root:pwd)

创建分片规则

CREATE shardingrule (
t_order=hash_mod(order_id, 4), 
t_item=mod(item_id, 2)
)

这里的 hash_mode 和 mod是自动分片算法的Key。详情请查阅 auto-sharding-algorithm。

创建切分表

CREATE TABLE `t_order` (
  `order_id` int NOT NULL,
  `user_id` int NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `t_item` (
  `item_id` int NOT NULL,
  `order_id` int NOT NULL,
  `user_id` int NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

删除切分表

DROP TABLE t_order;
DROP TABLE t_item;

删除分布式数据库

DROP DATABASE sharding_db

注意：

当前, DROP DB只会移除逻辑的分布式数据库，不会删除用户真实的数据库 (TODO)。
DROP TABLE会将逻辑分片表和数据库中真实的表全部删除。
CREATE DB只会创建逻辑的分布式数据库，所以需要用户提前创建好真实的数据库（TODO）。
自动分片算法会持续增加，从而覆盖用户各大分片场景 (TODO)。
重构ShardingAlgorithmPropertiesUtil（TODO）。
保证所有客户端完成RDL执行（TODO）。
增加 ALTER DB 和 ALTER TABLE的支持（TODO）。

五、ShardingSphere 配置说明

5.1、配置示例

spring:
  shardingsphere:
    datasource:   #数据源配置
      names:
        ds0,ds1
      # 主数据源
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8
        username: root
        password: root
    sharding:      
      tables:
        tab_user:     #逻辑表
          actual-data-nodes: ds$->{0..1}.tab_user$->{0..1}  #表数量配置  
          database-strategy:
            inline:
              sharding-column: age  #分库分片键
              algorithm-expression: ds$->{age % 2}
          table-strategy: 
            inline:
              sharding-column: id   #分表分片键
              algorithm-expression: tab_user$->{id % 3}
#          key-generator:    #id生成策略
#            column: id
#            type: SNOWFLAKE    
    props:
      # 开启SQL显示，默认false
      sql:
        show: true

tab_user ：是逻辑表名称，分表后创建的表应该是 tab_user_xxx。
actual-data-nodes ：是数据节点由数据源和数据表组成，也就是真实表，使用行表达式 {0..1} 表示有 tab_user0 到 tab_user1 共2张表；shardingsphere 不会自动创建表，需要使用脚本定时或手动提前创建好。
table-strategy ：是分片策略，根据需求实现具体的分片策略，inline 为行表达式， sharding-column 为自定义分片。
algorithm-expression ：是算法表达式，根据 id 取模尾数为 n 的路由到后缀为 n 的表中（tab_usern）。
key-generator ：是主键生成，SNOWFLAKE 是Twitter的分布式 ID 生成算法。

5.2、时间字段分片（如日志表）

sharding:
  tables:
    sys_log: # 逻辑表
      actual-data-nodes: ds0.sys_log_20200116 # 默认表配置
      table-strategy:
        standard:
          sharding-column: log_time # 分片列
          preciseAlgorithmClassName: com.payn.config.SysLogDataTableShardingAlgorithm #精确分片
          rangeAlgorithmClassName:  com.payn.config.SysLogDataTableShardingAlgorithm #范围分片
      key-generator: #  id 生成策略
        column: id
        type: SNOWFLAKE

preciseAlgorithmClassName 和 rangeAlgorithmClassName 为分片支持和实现请查看分片算法，下面是按天分片实现，按月分片只需要改动部分代码即可

5.3、关于查询：

分片后，如果查询 where 条件没有带分片字段的话会去扫描配置的所有真实数据表 = actual-data-nodes，最后将匹配到的数据合并为一个结果集返回，保存数据时找不到对应的表会报表不存在。
复杂SQL或者UNION可能会不支持，具体 SQL使用规范。

暂无