时间戳

首页 > 技术文章 > MySQL深入理解学习笔记 - 二

MySQL深入理解学习笔记 - 二

jerry0612 2021-01-25 19:22 原文

一、分库分表实战及中间件

背景介绍

一开始我们的系统只属于单体应用架构，数据库也只用了单机数据库。随着互联网的快速发展，单体应用架构并不能满足系统请求量。逐渐演化到分布式系统，仅仅只是扩充机器部署，单机数据库也并不能承受住越来越多的用户请求，这时我们开始使用数据库主从架构。用户量再和业务再一次提升后，写请求越来越多，这时我们开始使用分库分表。

  • 垂直拆分

    • 垂直分库
    • 垂直分表

  • 水平拆分

    • 水平分库
    • 水平分表
1.ShardingSphere实战

1.1.ShardingSphere介绍

Apache ShardingSphere是一款开源的分布式数据库中间件组成的生态圈。包括：Sharding-JDBC、Sharding-Proxy、Sharding-Sidecar

Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar三者区别如下：

Sharding-JDBC Sharding-Proxy Sharding-Sidecar
数据库 任意 MySQL MySQL
连接消耗数 高 低 高
异构语言 仅Java 任意 任意
性能 损耗低 损耗略高 损耗低
无中心化 是 否 是
静态入口 无 有 无

1.2.Sharding-JDBC介绍

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 以jar包形式提供服务，无需额外部署和依赖，完全兼容JDBC和各种ORM。

Sharding-JDBC主要功能：

  • 数据分片
    • 分库分表
    • 读写分离
    • 分片策略
    • 分布式主键
  • 分布式事务
    • 标准化事务接口
    • XA强一致性事务
    • 柔性事务
  • 数据库治理
    • 配置动态化
    • 编排和治理
    • 数据脱敏
    • 可视化链路追踪

Sharding-JDBC初始化流程：

  • 根据配置的信息生成Configuration对象
  • 通过Factory会将Configuration对象转化为Rule对象
  • 通过Factory会将Rule对象与DataSource对象封装
  • Sharding-JDBC使用DataSource进行分库分表和读写分离操作

1.3.数据分片实战

核心概念

  • 表概念
    • 真实表
    • 逻辑表
    • 数据节点
    • 绑定表
    • 广播表
  • 分片算法
    • 精确分片算法PreciseShardingAlgorithm
    • 范围分片算法RangeShardingAlgorithm
    • 复合分片算法ComplexKeysShardingAlgorithm
    • Hint分片算法HintShardingAlgorithm
  • 分片策略
    • 标准分片策略StandardShardingStrategy
    • 复合分片策略ComplexShardingStrategy
    • 行表达式分片策略InlineShardingStrategy
    • Hint分片策略HintShardingStrategy
    • 不分片策略NoneShardingStrategy
  • 分片策略配置
    • 数据源分片策略
    • 表分片策略

流程剖析

流程剖析

  • SQL解析
    SQL解析分为词法解析和语法解析。 先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。
    Sharding-JDBC采用不同的解析器对SQL进行解析，解析器类型如下：

    • MySQL解析器
    • Oracle解析器
    • SQLServer解析器
    • PostgreSQL解析器
    • 默认SQL解析器

  • 查询优化
    负责合并和优化分片条件，如OR等。

  • SQL路由
    根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。

  • SQL改写
    将SQL改写为在真实数据库中可以正确执行的语句。SQL改写分为正确性改写和优化改写。

  • SQL执行
    通过多线程执行器异步执行SQL。

  • 结果归并
    将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存

    归并和使用装饰者模式的追加归并这几种方式。

SQL使用规范

  • 支持项
    • MySQL数据库100%全兼容，其他数据库完善中
    • 全面支持DQL、DML、DDL、DCL、TCL
    • 支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）
  • 不支持项
    • 不支持CASE WHEN、HAVING、UNION (ALL)
    • 聚合函数目前无法支持
    • 不支持包含schema的SQL

Inline行表达式

InlineShardingStrategy：采用Inline行表达式进行分片的配置。

语法格式：

${begin..end} 表示范围区间

${[unit1, unit2, unit_x]} 表示枚举值

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

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

最终会解析为：

online_table1, online_table2, online_table3,
offline_table1, offline_table2, offline_table3

分布式主键

内置主键生成器：UUID和SNOWFLAKE

自定义主键生成器：自定义主键类，实现ShardingKeyGenerator接口

1.4.读写分离实战

  • 读写分离

    读写分离

  • 分表+读写分离

    分表+读写分离

  • 分库分表+读写分离

    分库分表+读写分离

ShardingSphere读写分离关注点

提供一主多从的读写分离配置。仅支持单主库，可以支持独立使用

独立使用读写分离，支持SQL透传。不需要SQL改写流程

同一线程且同一数据库连接内，能保证数据一致性。如果有写入操作，后续的读操作均从主
库读取。

基于Hint的强制主库路由。可以强制路由走主库查询实时数据，避免主从同步数据延迟。

主从同步有延迟

不支持多主双写或多写

1.5.强制路由实战

Hint可以指定了强制分片路由，那么SQL将会无视原有的分片逻辑，直接路由至指定的数据节点操作

Hint使用场景

  • 数据分片操作，如果分片键没有在SQL或数据表中，而是在业务逻辑代码中
  • 读写分离操作，如果强制在主库进行某些数据操作

Hint使用过程

  • 编写分库或分表路由策略，实现HintShardingAlgorithm接口

    public class MyHintShardingAlgorithm implements
HintShardingAlgorithm<Integer> {
  @Override
  public Collection<String> doSharding(Collection<String> collection,
HintShardingValue<Integer> hintShardingValue) {
   //添加分库或分表路由逻辑
 }
}

  • 在配置文件指定分库或分表策略

    #强制路由库和表
spring.shardingsphere.sharding.tables.b_order.database-
strategy.hint.algorithm-class-name=com.lagou.hint.MyHintShardingAlgorithm
spring.shardingsphere.sharding.tables.b_order.table-strategy.hint.algorithm-
class-name=com.lagou.hint.MyHintShardingAlgorithm
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds$->
{0..1}.b_order$->{0..1}

  • 在代码执行查询前使用HintManager指定执行策略值

      @Test//路由库和表
  public void test(){
    HintManager hintManager = HintManager.getInstance();
    hintManager.addDatabaseShardingValue("b_order",1);
    hintManager.addTableShardingValue("b_order",1);
    List<Order> list = orderRepository.findAll();
    hintManager.close();
    list.forEach(o -> {
      System.out.println(o.getOrderId()+" "+o.getUserId()+"
"+o.getOrderPrice());
   });
 }

1.6.数据脱敏实战

数据脱敏是指对某些敏感信息通过脱敏规则进行数据的变形，实现敏感隐私数据的可靠保护

ShardingSphere提供的Encrypt-JDBC和业务代码部署在一起。业务方需面向Encrypt-JDBC进行JDBC编程

整体架构

数据脱敏整体架构

Encrypt-JDBC将用户发起的SQL进行拦截，并通过SQL语法解析器进行解析、理解SQL行为，再依据用户传入的脱敏规则，找出需要脱敏的字段和所使用的加解密器对目标字段进行加解密处理后，再与底层数据库进行交互。

脱敏规则

脱敏配置主要分为四部分：数据源配置，加密器配置，脱敏表配置以及查询属性配置，其详情如下图所示：

脱敏规则

  • 数据源配置：指DataSource的配置信息
  • 加密器配置：指使用什么加密策略进行加解密。目前ShardingSphere内置了两种加解密策略：
    AES/MD5
  • 脱敏表配置：指定哪个列用于存储密文数据（cipherColumn）、哪个列用于存储明文数据（plainColumn）以及用户想使用哪个列进行SQL编写（logicColumn）
  • 查询属性的配置：当底层数据库表里同时存储了明文数据、密文数据后，该属性开关用于决定是直接查询数据库表里的明文数据进行返回，还是查询密文数据通过Encrypt-JDBC解密后返回。

脱敏处理流程

脱敏处理流程

脱敏处理流程-2

加密策略解析

ShardingSphere提供了两种加密策略用于数据脱敏，该两种策略分别对应ShardingSphere的两种加解密的接口，即Encryptor和QueryAssistedEncryptor。

Encryptor：通过提供encrypt(), decrypt()两种方法对需要脱敏的数据进行加解密，ShardingSphere针对这种类型的脱敏解决方案提供了两种具体实现类，分别是MD5(不可逆)，AES(可逆)，用户只需配置即可使用这两种内置的方案

QueryAssistedEncryptor：即使是相同的数据，如两个用户的密码相同，它们在数据库里存储的脱敏数据也应当是不一样的

1.7.分布式事务实战

1.7.1.分布式事务理论

  • CAP（强一致性）

    CAP 定理，又被叫作布鲁尔定理。对于共享数据系统，最多只能同时拥有CAP其中的两个，任意两
    个都有其适应的场景。

    分布式理论

  • BASE（最终一致性）

    BASE 是指基本可用（Basically Available）、软状态（ Soft State）、最终一致性（ Eventual Consistency）。它的核心思想是即使无法做到强一致性（CAP 就是强一致性），但应用可以采用适合的方式达到最终一致性。

    • BA指的是基本业务可用性，支持分区失败；
    • S表示柔性状态，也就是允许短时间内不同步；
    • E表示最终一致性，数据最终是一致的，但是实时是不一致的。

1.7.2.分布式事务模式

了解了分布式事务中的强一致性和最终一致性理论，下面介绍几种常见的分布式事务的解决方案。

  • 2PC模式（强一致性）：2PC是Two-Phase Commit缩写，即两阶段提交，就是将事务的提交过程分为两个阶段来进行处理。事务的发起者称协调者，事务的执行者称参与者。协调者统一协调参与者执行

    • 阶段 1：准备阶段

      协调者向所有参与者发送事务内容，询问是否可以提交事务，并等待所有参与者答复。各参与者执行事务操作，但不提交事务，将 undo 和 redo 信息记入事务日志中。如参与者执行成功，给协调者反馈 yes；如执行失败，给协调者反馈 no。

    • 阶段 2：提交阶段

      如果协调者收到了参与者的失败消息或者超时，直接给每个参与者发送回滚(rollback)消息；否则，发送提交(commit)消息。

    2PC 方案实现起来简单，实际项目中使用比较少，主要因为以下问题：

    • 性能问题：所有参与者在事务提交阶段处于同步阻塞状态，占用系统资源，容易导致性能瓶颈。
    • 可靠性问题：如果协调者存在单点故障问题，如果协调者出现故障，参与者将一直处于锁定状态。
    • 数据一致性问题：在阶段 2 中，如果发生局部网络问题，一部分事务参与者收到了提交消息，另一部分事务参与者没收到提交消息，那么就导致了节点之间数据的不一致。

  • 3PC模式（强一致性）：3PC 三阶段提交，是两阶段提交的改进版本，与两阶段提交不同的是，引入超时机制。同时在协调者和参与者中都引入超时机制。三阶段提交将两阶段的准备阶段拆分为 2 个阶段，插入了一个preCommit 阶段，解决了原先在两阶段提交中，参与者在准备之后，由于协调者或参与者发生崩溃或错误，而导致参与者无法知晓处于长时间等待的问题。如果在指定的时间内协调者没有收到参与者的消息则默认失败。

    • 阶段1：canCommit

      协调者向参与者发送 commit 请求，参与者如果可以提交就返回 yes 响应，否则返回 no 响应。

    • 阶段2：preCommit

      协调者根据阶段 1 canCommit 参与者的反应情况执行预提交事务或中断事务操作。

      • 参与者均反馈 yes：协调者向所有参与者发出 preCommit 请求，参与者收到
        preCommit 请求后，执行事务操作，但不提交；将 undo 和 redo 信息记入事务日志中；各参与者向协调者反馈 ack 响应或 no 响应，并等待最终指令。
      • 任何一个参与者反馈 no或等待超时：协调者向所有参与者发出 abort 请求，无论收到协调者发出的 abort 请求，或者在等待协调者请求过程中出现超时，参与者均会中断事务。

    • 阶段3：do Commit

      该阶段进行真正的事务提交，根据阶段 2 preCommit反馈的结果完成事务提交或中断操作。

  • XA（强一致性）：XA是由X/Open组织提出的分布式事务的规范，是基于两阶段提交协议。 XA规范主要定义了全局事务管理器（TM）和局部资源管理器（RM）之间的接口。

    XA模式

  • TCC模式（最终一致性）

    TCC 是服务化的两阶段编程模型，其 Try、Confirm、Cancel 3 个方法均由业务编码实现：

    • Try 操作作为一阶段，负责资源的检查和预留；
    • Confirm 操作作为二阶段提交操作，执行真正的业务；
    • Cancel 是预留资源的取消；

    TCC 模式相比于 XA，解决了如下几个缺点：

    • 解决了协调者单点，由主业务方发起并完成这个业务活动。业务活动管理器可以变成多点，引入集群。
    • 同步阻塞：引入超时机制，超时后进行补偿，并且不会锁定整个资源，将资源转换为业务逻辑形式，粒度变小。
    • 数据一致性，有了补偿机制之后，由业务活动管理器控制一致性。

  • 消息队列模式（最终一致性）

    消息队列模式

    消息队列的方案最初是由 eBay 提出，基于TCC模式，消息中间件可以基于 Kafka、RocketMQ 等消息队列。此方案的核心是将分布式事务拆分成本地事务进行处理，将需要分布式处理的任务通过消息日志的方式来异步执行。消息日志可以存储到本地文本、数据库或MQ中间件，再通过业务规则人工发起重试。

    • 步骤1：事务主动方处理本地事务。
      事务主动方在本地事务中处理业务更新操作和MQ写消息操作。
    • 步骤 2：事务主动方通过消息中间件，通知事务被动方处理事务通知事务待消息。
      事务主动方主动写消息到MQ，事务消费方接收并处理MQ中的消息。
    • 步骤 3：事务被动方通过MQ中间件，通知事务主动方事务已处理的消息，事务主动方根据反馈结果提交或回滚事务。

    为了数据的一致性，当流程中遇到错误需要重试，容错处理规则如下：

    • 当步骤 1 处理出错，事务回滚，相当于什么都没发生。
    • 当步骤 2 处理出错，由于未处理的事务消息还是保存在事务发送方，可以重试或撤销本地业务操作。
    • 如果事务被动方消费消息异常，需要不断重试，业务处理逻辑需要保证幂等。
    • 如果是事务被动方业务上的处理失败，可以通过MQ通知事务主动方进行补偿或者事务回滚。
    • 如果多个事务被动方已经消费消息，事务主动方需要回滚事务时需要通知事务被动方回滚。

  • Saga模式（最终一致性）

    Saga 事务基本协议如下：

    • 每个 Saga 事务由一系列幂等的有序子事务(sub-transaction) Ti 组成。
    • 每个 Ti 都有对应的幂等补偿动作 Ci，补偿动作用于撤销 Ti 造成的结果。

    Saga是一种补偿模式，它定义了两种补偿策略：

    • 向前恢复（forward recovery）：对应于上面第一种执行顺序，发生失败进行重试，适用于必须要成功的场景。

    • 向后恢复（backward recovery）：对应于上面提到的第二种执行顺序，发生错误后撤销掉之前所有成功的子事务，使得整个 Saga 的执行结果撤销。

      Saga模式

1.7.3.Sharding-JDBC整合XA原理

ShardingSphere整合XA事务时，分离了XA事务管理和连接池管理，这样接入XA时，可以做到对业务的零侵入。

支持功能

  • 支持数据分片后的跨库XA事务
  • 两阶段提交保证操作的原子性和数据的强一致性
  • 服务宕机重启后，提交/回滚中的事务可自动恢复
  • SPI机制整合主流的XA事务管理器，默认Atomikos
  • 同时支持XA和非XA的连接池
  • 提供spring-boot和namespace的接入端

Sharding-JDBC整合XA原理

流程

  • Begin（开启XA全局事务）
  • 执行物理SQL
  • Commit/rollback（提交XA事务）

1.7.4.Sharding-JDBC整合Saga原理

ShardingSphere的柔性事务已通过第三方servicecomb-saga组件实现的，通过SPI机制注入使用。

支持功能：

  • 完全支持跨库事务
  • 支持失败SQL重试及最大努力送达
  • 支持反向SQL、自动生成更新快照以及自动补偿
  • 默认使用关系型数据库进行快照及事务日志的持久化，支持使用SPI的方式加载其他类型的持久化

Sharding-JDBC整合Saga原理

流程

  • Init（Saga引擎初始化）
  • Begin（开启Saga全局事务）
  • 执行物理SQL
  • Commit/rollback（提交Saga事务）

1.7.5.Sharding-JDBC整合Seata原理

Seata AT事务作为BASE柔性事务的一种实现，可以无缝接入到ShardingSphere生态中。在数据库资源上，Seata通过对接DataSource接口，让JDBC操作可以同TC进行RPC通信。同样，ShardingSphere也是面向DataSource接口对用户配置的物理DataSource进行了聚合，因此把物理DataSource二次包装为Seata 的DataSource后，就可以把Seata AT事务融入到ShardingSphere的分片中。

Sharding-JDBC整合Seata原理

流程

  • Init（Seata引擎初始化）

  • Begin（开启Seata全局事务）

  • 执行分片物理SQL

  • Commit/rollback（提交Seata事务）

1.7.6.Sharding-JDBC分布式事务实战

  • 引入Maven依赖

    //XA模式
<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-transaction-xa-core</artifactId>
 <version>${shardingsphere.version}</version>
</dependency>
//Saga模式
<dependency>
 <groupId>io.shardingsphere</groupId>
 <artifactId>sharding-transaction-base-saga</artifactId>
 <version>${shardingsphere-spi-impl.version}</version>
</dependency>
//Seata模式
<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-transaction-base-seata-at</artifactId>
 <version>${sharding-sphere.version}</version>
</dependency>

  • JAVA编码方式设置事务类型

    TransactionTypeHolder.set(TransactionType.XA);
TransactionTypeHolder.set(TransactionType.BASE);

  • 参数配置

    ShardingSphere默认的XA事务管理器为Atomikos，通过在项目的classpath中添加jta.properties来定制化Atomikos配置项。具体的配置规则如下：

    #指定是否启动磁盘日志，默认为true。在生产环境下一定要保证为true，否则数据的完整性无法保
证
com.atomikos.icatch.enable_logging=true
#JTA/XA资源是否应该自动注册
com.atomikos.icatch.automatic_resource_registration=true
#JTA事务的默认超时时间，默认为10000ms
com.atomikos.icatch.default_jta_timeout=10000
#事务的最大超时时间，默认为300000ms。这表示事务超时时间由
UserTransaction.setTransactionTimeout()较大者决定。4.x版本之后，指定为0的话则表示
不设置超时时间
com.atomikos.icatch.max_timeout=300000
#指定在两阶段提交时，是否使用不同的线程(意味着并行)。3.7版本之后默认为false，更早的版本
默认为true。如果为false，则提交将按照事务中访问资源的顺序进行。
com.atomikos.icatch.threaded_2pc=false
#指定最多可以同时运行的事务数量，默认值为50，负数表示没有数量限制。在调用
UserTransaction.begin()方法时，可能会抛出一个”Max number of active transactions
reached”异常信息，表示超出最大事务数限制
com.atomikos.icatch.max_actives=50
#是否支持subtransaction，默认为true
com.atomikos.icatch.allow_subtransactions=true
#指定在可能的情况下，否应该join 子事务(subtransactions)，默认值为true。如果设置为
false，对于有关联的不同subtransactions，不会调用XAResource.start(TM_JOIN)
com.atomikos.icatch.serial_jta_transactions=true
#指定JVM关闭时是否强制(force)关闭事务管理器，默认为false
com.atomikos.icatch.force_shutdown_on_vm_exit=false
#在正常关闭(no-force)的情况下，应该等待事务执行完成的时间，默认为Long.MAX_VALUE
com.atomikos.icatch.default_max_wait_time_on_shutdown=9223372036854775807
========= 日志记录配置=======
#事务日志目录，默认为./。
com.atomikos.icatch.log_base_dir=./
#事务日志文件前缀，默认为tmlog。事务日志存储在文件中，文件名包含一个数字后缀，日志文件
以.log为扩展名，如tmlog1.log。遇到checkpoint时，新的事务日志文件会被创建，数字增加。
com.atomikos.icatch.log_base_name=tmlog
#指定两次checkpoint的时间间隔，默认为500
com.atomikos.icatch.checkpoint_interval=500
=========日志恢复配置=============
#指定在多长时间后可以清空无法恢复的事务日志(orphaned)，默认86400000ms
com.atomikos.icatch.forget_orphaned_log_entries_delay=86400000
#指定两次恢复扫描之间的延迟时间。默认值为与com.atomikos.icatch.default_jta_timeout
相同
com.atomikos.icatch.recovery_delay=${com.atomikos.icatch.default_jta_timeout
}
#提交失败时，再抛出一个异常之前，最多可以重试几次，默认值为5
com.atomikos.icatch.oltp_max_retries=5
#提交失败时，每次重试的时间间隔，默认10000ms
com.atomikos.icatch.oltp_retry_interval=10000

1.8.SPI 加载剖析

在Apache ShardingSphere中，很多功能实现类的加载方式是通过SPI注入的方式完成的.

Apache ShardingSphere通过SPI方式载入的功能模块：

  • SQL解析
    SQL解析的接口用于规定用于解析SQL的ANTLR语法文件。
  • 数据库协议
    数据库协议的接口用于Sharding-Proxy解析与适配访问数据库的协议。
  • 数据脱敏
    数据脱敏的接口用于规定加解密器的加密、解密、类型获取、属性设置等方式。
  • 分布式主键
    分布式主键的接口主要用于规定如何生成全局性的自增、类型获取、属性设置等。
  • 分布式事务
    分布式事务的接口主要用于规定如何将分布式事务适配为本地事务接口。
  • XA事务管理器
    XA事务管理器的接口主要用于规定如何将XA事务的实现者适配为统一的XA事务接口。
  • 注册中心
    注册中心的接口主要用于规定注册中心初始化、存取数据、更新数据、监控等行为。

1.9.Sharding-Proxy实战

Sharding-Proxy是ShardingSphere的第二个产品，提供封装了数据库二进制协议的服务端版本，目前只提供MySQL版本

  • 向应用程序完全透明，可直接当做MySQL使用

  • 适用于任何兼容MySQL协议的客户端

Sharding-Proxy使用过程：

  • 下载Sharding-Proxy的最新发行版

  • 解压缩后修改conf/server.yaml和以config-前缀开头的文件，进行分片规则、读写分离规则配置

  • 引入依赖jar

  • Linux操作系统请运行bin/start.sh，Windows操作系统请运行bin/start.bat启动Sharding-Proxy

  • 使用客户端工具连接

注意点：

  • Sharding-Proxy 默认不支持hint，如需支持，请在conf/server.yaml中，将props的属性proxy.hint.enabled设置为true

  • Sharding-Proxy默认使用3307端口，可以通过启动脚本追加参数作为启动端口号

  • Sharding-Proxy使用conf/server.yaml配置注册中心、认证信息以及公用属性

  • Sharding-Proxy支持多逻辑数据源，每个以"config-"做前缀命名yaml配置文件，即为一个逻辑数据源

2.Mycat实战

2.1.Mycat概念

  • 逻辑库
  • 逻辑表
  • 分片表
  • 非分片表
  • ER表
  • 全局表
  • 分片节点
  • 节点主机
  • 分片规则

2.2.server.xml配置

  • user标签

    <user name="user">
 <property name="password">user</property>
 <property name="schemas">lg_edu_order</property>
 <property name="readOnly">true</property>
 <property name="defaultSchema">lg_edu_order</property>
</user>

  • firewall标签

    <firewall>
 <!-- ip白名单 用户对应的可以访问的 ip 地址 -->
 <whitehost>
  	<host host="127.0.0.*" user="root"/>
	<host host="127.0.*" user="root"/>
	<host host="127.*" user="root"/>
	<host host="1*7.*" user="root"/>
 </whitehost>
 <!-- 黑名单允许的 权限 后面为默认 -->
 <blacklist check="true">
  <property name="selelctAllow">false</property>
  <property name="selelctIntoAllow">false</property>
  <property name="updateAllow">false</property>
  <property name="insertAllow">false</property>
  <property name="deletetAllow">false</property>
  <property name="dropAllow">false</property>
 </blacklist>
</firewall>

  • 全局序列号

    <system>
	<!-- 0表示使用本地文件方式；1表示使用数据库方式生成；2表示使用本地时间戳方式；3表示基于ZK与本地配置的分布式ID生成器；4表示使用zookeeper递增方式生成 -->   
	<property name="sequnceHandlerType">0</property>
</system>

    • 本地文件

      #default global sequence
GLOBAL.HISIDS=
GLOBAL.MINID=10001
GLOBAL.MAXID=20000
GLOBAL.CURID=10000
# self define sequence
COMPANY.HISIDS=
COMPANY.MINID=1001
COMPANY.MAXID=2000
COMPANY.CURID=1000
ORDER.HISIDS=
ORDER.MINID=1001
ORDER.MAXID=2000
ORDER.CURID=1000

    • 数据库方式

      在数据库中建立一张表，存放 sequence 名称（name），sequence 当前值（current_value），步长 （increment） 等信息

      CREATE TABLE MYCAT_SEQUENCE
(
 name     VARCHAR(64) NOT NULL,
 current_value BIGINT(20) NOT NULL,
 increment   INT     NOT NULL DEFAULT 1,
 PRIMARY KEY (name)
) ENGINE = InnoDB;

    • 本地时间戳方式

      ID为64 位二进制 ，42（毫秒）+5（机器 ID）+5（业务编码）+12（重复累加）
      换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。
      在 Mycat 下配置sequence_time_conf.properties文件

      WORKID=0-31 任意整数
DATAACENTERID=0-31 任意整数

    • 分布式 ZK ID 生成器

      Zk 的连接信息统一在 myid.properties 的 zkURL 属性中配置。基于 ZK 与本地配置的分布式 ID 生成器，InstanceID可以通过ZK自动获取，也可以通过配置文件配置。在sequence_distributed_conf.properties，只要配置INSTANCEID=ZK就表示从 ZK 上获取 InstanceID。
      ID 最大为63位二进制，可以承受单机房单机器单线程 1000*(2^6)=640000 的并发。结构如下:

      • current time millis（微秒时间戳 38 位，可以使用 17 年）
      • clusterId（机房或者 ZKid，通过配置文件配置，5 位）
      • instanceId（实例 ID，可以通过 ZK 或者配置文件获取，5 位）
      • threadId（线程 ID，9 位）
      • increment（自增，6 位）

    • ZK 递增方式

      Zk 的连接信息统一在 myid.properties 的 zkURL 属性中配置。需要配置sequence_conf.properties文件

      • TABLE.MINID 某线程当前区间内最小值
      • TABLE.MAXID 某线程当前区间内最大值
      • TABLE.CURID 某线程当前区间内当前值

2.3.schema.xml配置

  • schema标签

    <!-- 逻辑库 -->
<schema name="lg_edu_order" checkSQLschema="true" sqlMaxLimit="100"
dataNode="dn1"></schema>
    属性名 值 数量限制 说明
    dataNode 任意String (0..1) 分片节点
    sqlMaxLimit Integer (1) 查询返回的记录数限制limit
    checkSQLschema Boolean (1) 是否去表库名

  • table标签

    <table name="b_order" dataNode="dn1,dn2" rule="b_order_rule" primaryKey="ID"
autoIncrement="true"/>
    属性 值 数量限制 说明
    name String (1) 逻辑表名
    dataNode String (1..*) 分片节点
    rule String (0..1) 分片规则
    ruleRequired Boolean (0..1) 是否强制绑定分片规则
    primaryKey String (1) 主键
    type String (0..1) 逻辑表类型，全局表、普通表
    autoIncrement Boolean (0..1) 自增长主键
    subTables String (1) 分表
    needAddLimit Boolean (0..1) 是否为查询SQL自动加limit限制

  • dataNode标签

    <!-- 数据节点 -->
<dataNode name="dn1" dataHost="lg_edu_order_1" database="lg_edu_order_1" />

    name： 定义数据节点的名字，这个名字需要是唯一的，我们需要在 table 标签上应用这个名字，来建立表与分片对应的关系。
    dataHost : 用于定义该分片属于哪个分片主机，属性值是引用 dataHost 标签上定义的 name 属性。
    database： 用于定义该分片节点属于哪个具体的库。

  • dataHost标签

    <dataHost name="lg_edu_order_1" maxCon="100" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
</dataHost>
    属性 值 数量限制 说明
    name String (1) 节点主机名
    maxCon Integer (1) 最大连接数
    minCon Integer (1) 最小连接数
    balance Integer (1) 读操作负载均衡类型
    writeType Integer (1) 写操作负载均衡类型
    dbType String (1) 数据库类型
    dbDriver String (1) 数据库驱动
    switchType String (1) 主从切换类型

  • heartbeat标签

    <dataHost>
<heartbeat>select user()</heartbeat>
</dataHost>

  • writeHost和readHost标签

    <dataHost name="lg_edu_order_2" maxCon="100" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="M1" url="192.168.95.133:3306" user="root" password="1234">
	</writeHost>
</dataHost>
    属性 值 数量限制 说明
    host String (1) 主机名
    url String (1) 连接字符串
    password String (1) 密码
    user String (1) 用户名
    weight String (1) 权重
    usingDecrypt String (1) 是否对密码加密，默认0

2.4.rule.xml配置

  • tableRule标签

    <tableRule name="c_order_rule">
	<rule>
		<columns>user_id</columns>
		<algorithm>partitionByOrderFunc</algorithm>
	</rule>
</tableRule>

    name：指定唯一的名字，用于标识不同的表规则。
    columns：指定要拆分的列名字。
    algorithm：使用 function 标签中的 name 属性，连接表规则和具体路由算法。

  • function标签

    <function name="partitionByOrderFunc"
class="io.mycat.route.function.PartitionByMod">
	<property name="count">2</property>
</function>

    name：指定算法的名字。
    class：制定路由算法具体的类名字。
    property： 为具体算法需要用到的一些属性。

2.5.Mycat实战

2.5.1.Mycat安装

前提：需要先安装jdk

  • 下载Mycat-server工具包

  • 解压Mycat工具包

    tar -zxvf Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz

  • 进入mycat/bin，启动Mycat

    启动命令：./mycat start
停止命令：./mycat stop
重启命令：./mycat restart
查看状态：./mycat status

  • 访问Mycat

    mysql -uroot -proot -h127.0.0.1 -P8066 -uroot -p

2.5.2.分库分表

在rule.xml配置Mycat分库分表

<mycat:rule xmlns:mycat="http://io.mycat/">
 <tableRule name="b_order_rule">
   <rule>
     <columns>company_id</columns>
     <algorithm>partitionByOrderFunc</algorithm>
   </rule>
 </tableRule>
 <!-- 路由函数定义 -->
 <function name="partitionByOrderFunc"
class="io.mycat.route.function.PartitionByMod">
   <property name="count">2</property>
 </function>
</mycat:rule>

Mycat常用分片规则如下：

  • 时间类：按天分片、自然月分片、单月小时分片
  • 哈希类：Hash固定分片、日期范围Hash分片、截取数字Hash求模范围分片、截取数字Hash分片、一致性Hash分片
  • 取模类：取模分片、取模范围分片、范围求模分片
  • 其他类：枚举分片、范围约定分片、应用指定分片、冷热数据分片

Mycat常用分片配置示例：

  • 自动分片

    <tableRule name="auto-sharding-long">
 <rule>
  <columns>id</columns>
  <algorithm>rang-long</algorithm>
 </rule>
</tableRule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
 <property name="mapFile">autopartition-long.txt</property>
</function>

    autopartition-long.txt文件内容如下：

    # range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

  • 枚举分片

    把数据分类存储。

    <tableRule name="sharding-by-intfile">
<rule>
  <columns>sharding_id</columns>
  <algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
 <property name="mapFile">partition-hash-int.txt</property>
 <!-- 找不到分片时设置容错规则，把数据插入到默认分片0里面 -->
 <property name="defaultNode">0</property>
</function>

    partition-hash-int.txt文件内容如下：

    10000=0
10010=1

  • 取模分片

    根据分片字段值 % 分片数 。

    <tableRule name="mod-long">
 <rule>
   <columns>id</columns>
   <algorithm>mod-long</algorithm>
 </rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
 <!--分片数 -->
 <property name="count">3</property>
</function>

  • 冷热数据分片

    根据日期查询日志数据冷热数据分布 ，最近 n 个月的到实时交易库查询，超过 n 个月的按照 m 天分片。

    <tableRule name="sharding-by-date">
  <rule>
    <columns>create_time</columns>
    <algorithm>sharding-by-hotdate</algorithm>
  </rule>
</tableRule>
<function name="sharding-by-hotdate"
class="org.opencloudb.route.function.PartitionByHotDate">
  <!-- 定义日期格式 -->
  <property name="dateFormat">yyyy-MM-dd</property>
  <!-- 热库存储多少天数据 -->
  <property name="sLastDay">30</property>
  <!-- 超过热库期限的数据按照多少天来分片 -->
  <property name="sPartionDay">30</property>
</function>

  • 一致性哈希分片

    <tableRule name="sharding-by-murmur">
 <rule>
   <columns>id</columns>
   <algorithm>murmur</algorithm>
 </rule>
</tableRule>
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
 <property name="seed">0</property><!-- 默认是0 -->
 <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否
则没法分片 -->
 <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点
被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
 <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，
没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就
是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
 <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的
murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何
东西 -->
</function>

2.5.3.读写分离

在schema.xml文件中配置Mycat读写分离。使用前需要搭建MySQL主从架构，并实现主从复制， Mycat不负数据同步问题。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"
dbType="mysql" dbDriver="native">
  <heartbeat>select user()</heartbeat>
  <!-- can have multi write hosts -->
  <writeHost host="M1" url="localhost:3306" user="root" password="123456">
    <readHost host="S1" url="localhost:3307" user="root" password="123456" weight="1" />
  </writeHost>
</dataHost>

balance参数：

  • 0 ： 所有读操作都发送到当前可用的writeHost.
  • 1 ：所有读操作都随机发送到readHost和stand by writeHost
  • 2 ：所有读操作都随机发送到writeHost和readHost
  • 3 ：所有读操作都随机发送到writeHost对应的readHost上，但是writeHost不负担读压力

writeType参数：

  • 0 ： 所有写操作都发送到可用的writeHost
  • 1 ：所有写操作都随机发送到readHost
  • 2 ：所有写操作都随机发送到writeHost，readHost

或者

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0"
dbType="mysql" dbDriver="native">
  <heartbeat>select user()</heartbeat>
  <!-- can have multi write hosts -->
  <writeHost host="M1" url="localhost:3306" user="root" password="123456">
  </writeHost>
  <writeHost host="S1" url="localhost:3307" user="root" password="123456">
  </writeHost>
</dataHost>

2.5.4.强制路由

一个查询 SQL 语句以/* !mycat * /注解来确定其是走读节点还是写节点.

/*! /
/# /
/* */

强制走从:
/*!mycat:db_type=slave*/ select * from travelrecord //有效
/*#mycat:db_type=slave*/ select * from travelrecord
强制走写:
/*!mycat:db_type=master*/ select * from travelrecord //有效
/*#mycat:db_type=slave*/ select * from travelrecord

/*!mycat:sql=sql */    指定真正执行的SQL
/*!mycat:schema=schema1 */ 指定走那个schema
/*!mycat:datanode=dn1 */  指定sql要运行的节点
/*!mycat:catlet=io.mycat.catlets.ShareJoin */ 通过catlet支持跨分片复杂SQL实现以及存储过程支持等

2.5.5.主从延时切换

switchType参数：

  • -1： 表示不自动切换

  • 1 ：表示自动切换

  • 2 ：基于MySQL主从同步状态决定是否切换

  • 3 ：基于MySQL cluster集群切换机制

1.4 开始支持 MySQL 主从复制状态绑定的读写分离机制，让读更加安全可靠

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
  <heartbeat>show slave status </heartbeat>
  <!-- can have multi write hosts -->
  <writeHost host="M1" url="localhost:3306" user="root" password="123456">
  </writeHost>
  <writeHost host="S1" url="localhost:3316" user="root"
</dataHost>

1.4.1 版本开始支持 MySQL 集群模式，让读更加安全可靠，配置如下: MyCAT 心跳检查语句配置为 show status like ‘wsrep%’ ，dataHost 上定义两个新属性: switchType="3" 此时意味着开启 MySQL 集群复制状态状态绑定的读写分离与切换机制，Mycat 心跳机制通过检测集群 复制时延时，如果延时过大或者集群出现节点问题不会负载改节点。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
dbType="mysql" dbDriver="native" switchType="3" >
  <heartbeat> show status like ‘wsrep%’</heartbeat>
  <writeHost host="M1" url="localhost:3306" user="root"password="123456">
  </writeHost>
  <writeHost host="S1"url="localhost:3316"user="root"password="123456" >
  </writeHost>
</dataHost>

2.6.Mycat事务

2.6.1.Mycat数据库事务

Mycat 目前没有出来跨分片的事务强一致性支持

2.6.2.XA事务使用

Mycat 实现 XA 标准分布式事务

操作说明

  • XA 事务需要设置手动提交

    set autocommit=0;

  • 使用该命令开启 XA 事务

    set xa=on;

  • 执行相应的 SQL 语句部分

  • 提交或回滚事务

    commit；
rollback;

2.6.3.保证Repeatable Read

mycat 有一个特性，就是开事务之后，如果不运行 update/delete/select for update 等更新类语句 SQL 的话，不会将当前连接与当前 session 绑定。如下图所示:

Mycat开启事务

两次 select 中如果有其它的在提交的话，会出现两次同样的 select 不一 致的现象,即不能 Repeatable Read。

当 server.xml 的 system 配置了 strictTxIsolation=true 的时候，会关掉这个特性，以保证 repeatable read,加了开关 后如下图所示:

开启事务的情况

推荐阅读