漫谈MySQL分库分表_vlambda技术博客

vlambda
2021-06-06

漫谈MySQL分库分表

...写在前面...

分库分表一时爽，出现问题火葬场！！！

随着业务发展，数据库数据会越来越多，单表数据量会超过千万甚至更多，简单的查询都可能需要数秒，修改表结构这种操作将会直接卡死.......此时，你有如下选择：

升级数据库配置，如4核16G、8核32G、16核64G.....;
使用分布式数据库，如 PolarDB；
从时间维度上进行数据备份后删除，如仅保存最近6个月数据；
接入数据库中间件进行分库分表，如 shardingsphere-jdbc。

升级配置能解燃眉之急，但无法消除数据库瓶颈；数据备份后删除需要在业务上做取舍，能够忍受这种取舍的业务很少存在；所以我们只能使用分库分表的方式解决问题。

什么？你问我为啥不讲分布式数据库，实话跟你讲吧，因为前天购买资源时看错了价格，买不起就没办法帮大家测试了.........

漫谈MySQL分库分表

这一天100的价格无法承受哇，直到刚才，我又去瞟了一眼，发现了新天地：

漫谈MySQL分库分表

啊，这......这跟白嫖有什么区别漫谈MySQL分库分表。说明一下这个包年包月有优惠，所以便宜，但是：

漫谈MySQL分库分表

这该死的阿里云，居然误导我......

正文开始，上车吧，程序员！

分库分表实现方式

一般来讲，数据库分库分表可以从以下几个层面进行：

代码层判断并切换到不同的数据源执行 SQL；
基于 ORM 框架进行拦截，比如 MyBatis 的 Interceptor 接口；
基于数据库代理服务器，比如 Mycat、MySQL Router；
基于语言-数据库驱动，比如 shardingsphere-jdbc、TDDL。

在代码层面如果要做类似于分库分表这样的工作，难免需要对业务代码大量侵入、而且还需要对查询结果进行归并、排序、限制条数（分页时）等，一般不做考虑。

基于 ORM 框架首先需要所有模块使用的框架是一致的，对于新老项目混合、共享数据库的公司显然不太适合，而且需要每个模块编写对应的拦截器，一旦发生改动，需要所有模块的代码同步修改，因此这种方式也不建议尝试。

基于代理的方式其实就是相当于把所有处理逻辑单独部署到服务器或集群上，每个应用/模块的数据源与代理服务器建立连接，实际 SQL 的解析、改写、执行、结果归并、排序等所有操作由代理完成，对于开发人员无感知。由于代理服务器实现了对应数据库的通信协议，那么只要是 MySQL 提供了驱动的语言都可以支持。由于需要保证代理服务器的高可用，势必会增加服务器成本，对于普通的分库分表场景也不建议使用。

基于数据库驱动实现方便、对业务代码零侵入（相对来讲），并且是去中心化设计，只要数据库没挂，应用的访问就不会受到影响。缺点是升级困难和无法进行全局 SQL 限流。

如何选择？

单语言使用基于驱动的方式，多语言项目使用代理的方式；
预算不足、业务相对简单的使用驱动实现，反之使用代理；
DBA 需要经常维护分表内容必须使用代理；
请看下图，问：你会使用这样 Proxy 框架吗

漫谈MySQL分库分表

你敢相信这是技术文档？

shardingsphere-jdbc 概览

由于 5.x 版本存在诸多问题（具体原因未知），本文所有内容基于 4.1.1 版本介绍（仅数据分片部分）。同时，也奉劝刚入坑的小伙伴，不要使用 5.x 版本试水，太坑了。（现在是2021年6月3号，很久之后可能会修复）当然，已经把 4.x 版本摸熟的读者可以尝试通过阅读源码去深究原因，此处不再细讲。

逻辑表&真实表

水平拆分后的数据库（表）的相同逻辑和数据结构的总称，如：user 表拆分成 user_0，user_1，user_2。那么逻辑表就是 user，

真实表就是 user_{0...2}。

数据节点

数据分片的最小单元，由数据源（库）名称和数据表组成，比如 db_0.user_0。

分片键

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将 user 表中的主键尾数取模分片，则主键 id 为分片字段。SQL中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

绑定表

绑定表一般用于关联主表和子表没比如 user 和 user_detail 这样关系的表。绑定表的数据切分逻辑必须一致。使用绑定表可以避免笛卡尔积关联。

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);

在不配置绑定表关系时假设分片键 user_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 应该是：

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);

广播表

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

shardingsphere-jdbc 示例项目（保姆级教程）

创建数据节点

本项目以三库分两表为例。即创建三个数据库，每个数据库中需要切分的表按主键以2取模切分。

首先创建三个数据库：db0，db1，db2

在三个库中分别执行如下 SQL

-- 创建广播表：pr_countryCREATE TABLE `pr_country` ( `id` bigint(20) NOT NULL COMMENT '主键 ID。广播表', `country_name` varchar(32) NOT NULL COMMENT '国家名称', `position` varchar(5) NOT NULL COMMENT '位置：亚洲、欧洲、澳洲、非洲、北美洲、南极洲、南美洲', `area` bigint(20) NOT NULL COMMENT '面积。单位：平方米', `population` int(11) NOT NULL COMMENT '人口', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建用户真实表：pr_user0 pr_user1CREATE TABLE `pr_user0` ( `user_id` bigint(20) NOT NULL COMMENT '主键 ID。用于分片', `nickname` varchar(50) NOT NULL COMMENT '用户昵称', `sex` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别。0 女 1 男。默认 0', `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态。1 正常 2 封禁.默认 1', `country_id` bigint(20) NOT NULL COMMENT '国家 ID。', `deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否删除。0 否 1 是.默认 0', `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;CREATE TABLE `pr_user1` ( `user_id` bigint(20) NOT NULL COMMENT '主键 ID。用于分片', `nickname` varchar(50) NOT NULL COMMENT '用户昵称', `sex` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别。0 女 1 男。默认 0', `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态。1 正常 2 封禁.默认 1', `country_id` bigint(20) NOT NULL COMMENT '国家 ID。', `deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否删除。0 否 1 是.默认 0', `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 创建用户详细真实表：pr_user_detail0 pr_user_detail1CREATE TABLE `pr_user_detail0` ( `user_id` bigint(20) NOT NULL COMMENT '用户 ID', `mobile` varchar(32) NOT NULL DEFAULT '-' COMMENT '手机号。默认 ''-''，表示未填写', `country_id` bigint(20) NOT NULL DEFAULT '-1' COMMENT '国家 ID。默认 -1，表示未填写', PRIMARY KEY (`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;CREATE TABLE `pr_user_detail1` ( `user_id` bigint(20) NOT NULL COMMENT '用户 ID', `mobile` varchar(32) NOT NULL DEFAULT '-' COMMENT '手机号。默认 ''-''，表示未填写', `country_id` bigint(20) NOT NULL DEFAULT '-1' COMMENT '国家 ID。默认 -1，表示未填写', PRIMARY KEY (`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

数据节点准备已经完成，接着我们将以 country_id 作为分库键，以 user_id 作为分表键，pr_user 与 pr_user_detail 作为绑定表，pr_country 作为广播表进行项目搭建。

项目搭建

创建 maven 项目......步骤略

引入项目依赖（别多想，复制粘贴就完了）

<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j</artifactId> <version>1.3.8.RELEASE</version> <exclusions> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> </dependency> </dependencies>

application.yml 文件配置（别冲动，不要无脑复制）

server: port: 12306
spring: application: name: sharding-sphere-practice main: allow-bean-definition-overriding: true jackson: default-property-inclusion: NON_NULL
mybatis-plus: global-config: banner: true db-config: logic-delete-field: deleted logic-delete-value: 1 logic-not-delete-value: 0 type-aliases-package: com.javanpe.practice.sharding.core.dal.model mapper-locations: classpath:mappers/*.xml configuration: # 开启驼峰映射 map-underscore-to-camel-case: true # 允许将表中null值字段映射到对象字段中    call-setters-on-nulls: true

shardingsphere4.yml

# 数据源配置dataSources: db0: !!com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3306/db0 username: user password: pass db1: !!com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3306/db1 username: user password: pass db2: !!com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3306/db2 username: user password: pass# 规则shardingRule: # 分库分表规则 tables: pr_user: # 该表的所有数据节点 actualDataNodes: db${0..2}.pr_user${0..1} # 分库规则 databaseStrategy: inline: # 分库键 shardingColumn: country_id # 分库规则行表达式 algorithmExpression: db${country_id % 3} # 分表规则 tableStrategy: inline: # 分表键 shardingColumn: user_id # 分表规则行表达式 algorithmExpression: pr_user${user_id % 2} pr_user_detail: actualDataNodes: db${0..2}.pr_user_detail${0..1} databaseStrategy: inline: shardingColumn: country_id algorithmExpression: db${country_id % 3} tableStrategy: inline: shardingColumn: user_id algorithmExpression: pr_user_detail${user_id % 2} pr_country: actualDataNodes: db${0..2}.pr_country # 广播表 broadcastTables: - pr_country # 绑定表 bindingTables: - pr_user, pr_user_detail# 其他属性，如真实 SQL 打印props: sql.show: true

加载数据源和分片配置

@Configurationpublic class DataSourceConfigByYaml5 {  @Bean("datasource") public DataSource getDataSource() {    File yamlFile = new File("/path/shardingsphere4.yml");    try {      DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(yamlFile);      return dataSource;    } catch (Exception e) {      e.printStackTrace();    } return null; }}