Mysql学习笔记---ShardingSphere之数据分片

Mysql学习笔记—ShardingSphere之数据分片

1. 表概念

1. 真实表：数据库中真实存在的物理表。例如b_order0、b_order1
2. 逻辑表：在分片之后，同一类表结构的名称（总成）。例如b_order。
3. 数据节点：在分片之后，由数据源和数据表组成。例如ds0.b_order1
4. 绑定表：指的是分片规则一致的关系表（主表、子表），例如b_order和b_order_item，均按照order_id分片，则此两个表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，可以提升关联查询效率。
   b_order（逻辑表）：b_order0（真实表）、b_order1 （真实表）
   b_order_item （逻辑表）：b_order_item0 （真实表）、b_order_item1 （真实表）

   select * from b_order o join b_order_item i on(o.order_id=i.order_id) where o.order_id in (10,11);
   

5. 如上所示，如果不配置绑定表关系，采用笛卡尔积关联，会生成4个SQL

   select * from b_order0 o join b_order_item0 i on(o.order_id=i.order_id) where o.order_id in (10,11); 
   select * from b_order0 o join b_order_item1 i on(o.order_id=i.order_id) where o.order_id in (10,11); 
   select * from b_order1 o join b_order_item0 i on(o.order_id=i.order_id) where o.order_id in (10,11); 
   select * from b_order1 o join b_order_item1 i on(o.order_id=i.order_id) where o.order_id in (10,11);
   

6. 如果配置绑定表关系，生成2个SQL

   select * from b_order0 o join b_order_item0 i on(o.order_id=i.order_id) where o.order_id in (10,11); 
   select * from b_order1 o join b_order_item1 i on(o.order_id=i.order_id) where o.order_id in (10,11);
   

2.流程剖析: ShardingJDBC中的SQL处理流程

1. ShardingSphere 的3个产品的数据分片功能主要流程是完全一致的
2. SQL解析
   1. SQL解析分为词法解析和语法解析。 先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。
3. 查询优化：负责合并和优化分片条件，如OR等。
4. SQL路由：根据解析上下文匹配用户配置的分片策略，并生成路由路径,定位到不同的数据源或数据表。目前支持分片路由和广播路由。
5. SQL改写：将SQL改写为在真实数据库中可以正确执行的语句。SQL改写分为正确性改写和优化改写。
6. SQL执行：通过多线程执行器异步执行SQL。
7. 结果归并：将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式。

3.SQL使用规范： 支持项

1. 路由至单数据节点时，目前MySQL数据库100%全兼容，其他数据库完善中。
2. 路由至多数据节点时，全面支持DQL、DML、DDL、DCL、TCL。支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）。
3. 以下用最为复杂的查询为例：

   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}]
   

4.SQL使用规范： 不支持项（路由至多数据节点）

1. 不支持CASE WHEN、HAVING、UNION (ALL)

2. 支持分页子查询，但其他子查询有限支持，无论嵌套多少层，只能解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。

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

   SELECT COUNT(*) FROM (SELECT * FROM b_order o)
   

4. 以下子查询不支持：

   SELECT COUNT(*) FROM (SELECT * FROM b_order o WHERE o.id 
   										IN (SELECT id FROM b_order WHERE status = ?))
   

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

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

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

8. 当分片键处于运算表达式或函数中的SQL时，将采用全路由的形式获取结果，执行效率会特别的低。例如下面SQL，create_time为分片键：

   SELECT * FROM b_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2020- 05-05';
   

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

5. SQL 使用规范不支持示例

INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …) //VALUES语句不支持运算表达式

INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? //INSERT .. SELECT 

SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ? //HAVING 

SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 //UNION 

SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 //UNION ALL 

SELECT * FROM ds.tbl_name1 //包含schema 

SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name //同时使用普通聚合函数 和DISTINCT,把SUM(DISTINCT col1)去掉是没问题的

SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ? //可以执行，但会导致全路由，效率低

6. 数据分片分页出现的问题

1. 完全支持MySQL和Oracle的分页查询，SQLServer由于分页查询较为复杂，仅部分支持.
2. 性能瓶颈：查询偏移量过大的分页会导致数据库获取数据性能低下，以MySQL为例，如下所示：

   SELECT * FROM b_order ORDER BY id LIMIT 1000000, 10
   

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

   SELECT * FROM b_order ORDER BY id LIMIT 0, 1000010
   

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

7. 数据分片分页优化方案

1. ShardingSphere的优化：ShardingSphere进行了以下2个方面的优化。
   1. 首先，采用流式处理 + 归并排序的方式来避免内存的过量占用。
   2. 其次，ShardingSphere对仅落至单节点的查询进行进一步优化。
2. 分页方案优化：
   1. 由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案

      SELECT * FROM b_order WHERE id > 1000000 AND id <= 1000010 ORDER BY id
      

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

       SELECT * FROM b_order WHERE id > 1000000 LIMIT 10
      

8. 数据分片分页优化方案

1. InlineShardingStrategy：采用Inline行表达式进行分片的配置。
2. Inline是可以简化数据节点和分片算法配置信息。主要是解决配置简化、配置一体化。
3. 语法格式：行表达式的使用非常直观，只需要在配置中使用$ { expression }或$- >{ expression }标识行表达式即可。

   ${begin..end} 表示范围区间 
   ${[unit1, unit2, unit_x]} 表示枚举值
   

4. 行表达式中如果出现多个$或$->{}表达式，整个表达式结果会将每个子表达式结果进行笛卡尔(积)组合。例如，以下行表达式：

   ${['online', 'offline']}_table${1..3} 
   $->{['online', 'offline']}_table$->{1..3}
   

5. 上面的行表达式最终会被解析成如下：

   online_table1, online_table2, online_table3, 
   offline_table1, offline_table2, offline_table3
   

6. 数据节点均匀配置：

   db0
   ├── b_order2 
   └── b_order1 db1
   db1
   ├── b_order2 
   └── b_order1
   

7. 上面的表达式如下

   db${0..1}.b_order${1..2} 
   或者
   db$->{0..1}.b_order$->{1..2}
   

8. 数据节点非 均匀配置：

   db0
   ├── b_order0 
   └── b_order1
   db1
   ├── b_order2 
   └── b_order3
   └── b_order4
   

9. 上面的数据节点用行表达式可以简化为：

   db0.b_order${0..1},db1.b_order${2..4}
   

10. 分片算法配置：行表达式内部的表达式本质上是一段Groovy代码，可以根据分片键进行计算的方式，返回相应的真实数据源或真实表名称。

    ds${id % 10} 
    或者
    ds$->{id % 10}
    

11. 结果为：ds0、ds1、ds2… ds9

9. 数据分片主键生成原理

1. 分布式主键：ShardingSphere不仅提供了内置的分布式主键生成器，例如UUID、SNOWFLAKE，还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器。
2. 内置主键生成器：
   1. UUID：采用UUID.randomUUID()的方式产生分布式主键。
   2. SNOWFLAKE：在分片规则配置模块可配置每个表的主键生成策略，默认使用雪花算法，生成64bit的长整型数据。
3. 自定义主键生成器：
   1. 自定义主键类，实现ShardingKeyGenerator接口
   2. 按SPI规范配置自定义主键类：
      1. 在Apache ShardingSphere中，很多功能实现类的加载方式是通过SPI注入的方式完成的。
      2. 注意：在resources目录下新建META-INF文件夹，再新建services文件夹，然后新建文件的名字为org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator，打开文件，复制自定义主键类全路径到文件中保存。
   3. 自定义主键类应用配置：

       #对应主键字段名 
       spring.shardingsphere.sharding.tables.t_book.key-generator.column=id
       #对应主键类getType返回内容 
       spring.shardingsphere.sharding.tables.t_book.key- generator.type=LAGOUKEY