使用sharding-jdbc实现mysql的分库分表

简介

之前提到的用sharding-jdbc对mysql进行读写分离可以很好的处理在读远远大于写的情况下的并发问题，但是无法突破写入大量数据造成性能下降的问题，在数据量达到千万甚至亿级别时，内存可能已经无法缓存全部索引，就要从硬盘中读取索引，这时即便再怎么优化也难免造成性能下降的问题，这时我们需要对数据进行分库分表，mysql中提供了分区功能，但是无法突破单机性能瓶颈,这里我们使用sharding-jdbc来实现分库分表功能。

核心概念简介

逻辑表

水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是t_order_0到t_order_9，他们的逻辑表名为t_order。

真实表

在分片的数据库中真实存在的物理表。即上个示例中的t_order_0到t_order_9。

绑定表

指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：

1	SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在配置绑定表关系后，路由的SQL应该为2条：

1
2
3

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

其中t_order在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。所有路由计算将会只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键要完全相同。

广播表

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

创建数据库

在mysql中创建两个数据库user_1和user_2,并且在这两个数据库中分别创建以下测试表

地址表（用于测试广播）

CREATE TABLE `t_address`  (
  `id_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

用户表(用于测试分库分区)

CREATE TABLE `t_user_0`  (
  `user_id_` bigint(255) NULL DEFAULT NULL,
  `name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


CREATE TABLE `t_user_1`  (
  `user_id_` bigint(255) NULL DEFAULT NULL,
  `name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

订单表和订单明细表(用于测试绑定表)

CREATE TABLE `t_order_0`  (
  `order_id_` bigint(255) NULL DEFAULT NULL,
  `order_name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `user_id_` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


CREATE TABLE `t_order_1`  (
  `order_id_` bigint(255) NULL DEFAULT NULL,
  `order_name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `user_id_` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;




CREATE TABLE `t_order_item_0`  (
  `order_item_id_` bigint(255) NULL DEFAULT NULL,
  `item_name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `user_id_` int(255) NULL DEFAULT NULL,
  `order_id_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;




CREATE TABLE `t_order_item_1`  (
  `order_item_id_` bigint(255) NULL DEFAULT NULL,
  `item_name_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `user_id_` int(255) NULL DEFAULT NULL,
  `order_id_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

创建maven工程

关于sharding-jdbc打包参考之前sharding-jdbc主从分离文章

<dependencies>
        <!-- for spring namespace -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.1.4.RELEASE</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

    </dependencies>

创建application.properties配置文件

spring.shardingsphere.datasource.names=ds0,ds1

#创建数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.171.76:3306/user_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=Liush123!@#
spring.shardingsphere.datasource.ds0.minimum-idle=10
spring.shardingsphere.datasource.ds0.maximum-pool-size=10
spring.shardingsphere.datasource.ds0.pool-name=HikariCP1


spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.171.76:3306/user_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=Liush123!@#
spring.shardingsphere.datasource.ds1.minimum-idle=10
spring.shardingsphere.datasource.ds1.maximum-pool-size=10
spring.shardingsphere.datasource.ds1.pool-name=HikariCP2

# 设置分库键和分库算法
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id_
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id_ % 2}

#设置绑定表规则

#如果不#如果不设置此参数，会产生笛卡尔积查询，使效率大大减慢（表间绑定，比如这里相同的orderID的t_order和t_order_item会划分到同一分片下，这样就不会产生笛卡尔积查询）
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item

#设置广播表
spring.shardingsphere.sharding.broadcast-tables=t_address


#设置t_user表分片规则，和主键生成规则（主键生成规则采用SNOWFLAKE）
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user_$->{0..1}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id_
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{user_id_ % 2}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id_
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.props.worker.id=123


#设置t_order分片规则，和主键生成规则（主键生成规则采用SNOWFLAKE）
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id_
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id_ % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id_
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=123


#设置t_order_item分片规则，和主键生成规则（主键生成规则采用SNOWFLAKE）
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item_$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id_
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id_ % 2}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id_
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=123

# 显示执行sql
spring.shardingsphere.props.sql.show=true

编写mybatis测试代码

@Insert("insert into t_user(name_) values(#{name})")
void insertUserShardingByName(@Param("name")String name);

@Insert("insert t_address(id_,name_) values(#{id},#{name})")
void insertAddress(@Param("id")String id,@Param("name")String name);

@Insert("insert into t_order(order_name_,user_id_) values(#{name},#{userId})")
void insertOrder(@Param("userId")int userId,@Param("name")String name);

@Insert("insert into t_order_item(item_name_,user_id_,order_id_) values(#{name},#{userId},#{orderId})")
void insertItem(@Param("userId")int userId,@Param("name")String name,@Param("orderId") Long orderId);

@Select("select item_name_ from t_order t1 inner join t_order_item t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( #{orderId1},#{orderId2} )")
List<String> findOrderItem(@Param("orderId1")long orderId1,@Param("orderId2")long orderId2);

执行结果

insertUserShardingByName
执行此代码数据库会自动根据之前配置的SNOWFLAKE的策略生成分布式唯一主键，在对应的库和对应的表中插入数据
insertAddress插入广播数据
在所有的库中的t_address表都插入数据，因为是广播表(类似与字典，所以不会分表分区)
insertOrder和insertItem
在对应设置的库和分表中插入语句
findOrderItem
我们重点看一下这个执行结果,这里我们做了一个t_order表和t_order_item的连表查询，这两张表使用oder_id_进行关联，并且在配置文件中配置了这两张表Z为绑定关系(spring.shardingsphere.sharding.binding-tables=t_order,t_order_item),我们执行查询语句在日志中发现一下信息

1
2

Actual SQL: ds0 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? )
Actual SQL: ds1 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? )

发现其只是在各个分库中对对应的分片进行了连表查询(因为我们传入的order_id_根据我们的分片方式都在分片0上)，并没有进行笛卡尔积查询，现在我们注释掉spring.shardingsphere.sharding.binding-tables=t_order,t_order_item，不设置表间绑定，我们查看日志,发现产生了笛卡尔积查询，所以必须配置spring.shardingsphere.sharding.binding-tables参数，不然查询效率很有可能比单表更慢

2019-10-12 10:37:19.557  INFO 2632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select item_name_ from t_order_0 t1 inner join t_order_item_1 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]
2019-10-12 10:37:19.557  INFO 2632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]
2019-10-12 10:37:19.557  INFO 2632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select item_name_ from t_order_0 t1 inner join t_order_item_1 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]
2019-10-12 10:37:19.557  INFO 2632 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select item_name_ from t_order_0 t1 inner join t_order_item_0 t2 on t1.order_id_=t2.order_id_ where t1.order_id_ in ( ?,? ) ::: [389458016099348480, 389458016128708608]

问题与思考

分库分表的瓶颈

传统数据库的分库分表很多都是依赖于中间件去实现的，不同于new sql(tidb)，或者nosql(mongo,hdfs)，这些数据库或者组件提供了auto sharding的特性，即系统可以自动对数据进行分区分片并完成数据平衡，当系统扩展后(增加计算节点)，这些系统会自动将数据再平衡，可以实现0维护扩展节点（只需当初的将节点加入集群其余的什么也不用做），传统数据库进行扩展需要重新手动对数据进行迁移，重新设计分片(如上文如果我们根据userId将2个库扩展为3个库那么原来的分库策略就不能使用了，因为运来采用分库策略是对userId和2进行取模运算{user_id_ % 2}，如果改成3个节点那么算法就要改成{user_id_ % 3}，那么需要手动将原来的数据做再平衡，将匹配的数据分配到第三台节点上)，所以使用关系型数据库做分库分片最好一次性就计划好未来的数据量，最好节点的规划.还有一个问题就是分片键的问题，为了查询能准确定位到数据所在的分片，每次查询时都要带上分片键，那么就会产生这么一个问题，如果现在有需求需要根据用户名查询用户数据，那么怎么办？因为数据都是根据userId进行分库分片的，这种情况下我们可以建立一张用户名和userId的映射表，这张表也可以进行分库分表，这张表根据用户名进行hash运算后进行分库分表，这样我们就可以先通过用户名，用户id关系表进行查询到用户id后，再根据用户id获取用户信息。

什么情况下对数据进行分库分表

只有在有需要的情况下才对数据进行分库分表，分库分表比单表会带来很多限制，比如数据的join查询等（因为分片的原因需要考虑查询效率）,或者限制要在根据用户id进行分片的数据中对用户名进行模糊查询怎么办？有些时候就要对业务进行一些妥协，或者采用其它方案去解决如NOSQL，同时分表分库也带来了复杂度的提升，维护难度的提升.