1、概述
mysql分库分表一般有如下场景
1). 垂直分表(将表分为主表和扩展表) 2). 垂直分库(将表按业务归属到不同的库,如订单相关的放到订单库,用户相关的表放到用户库等,这也是我们常说的权限回收其中的一部分) 3). 水平拆表(当数据库整体瓶颈还未到时,少量表到达性能瓶颈) 4). 水平拆库 & 拆表(数据整体性能到达瓶颈,单一写入出现性能瓶颈)
其中1,2相对较容易实现,本文重点讲讲水平拆表和水平拆库,以及基于mybatis插件方式实现水平拆分方案落地。
2、水平拆表
在
这里我们以分KV表水平拆分为场景
CREATE TABLE `kv` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`key` varchar(30) NOT NULL COMMENT '存储字段名',
`value` varchar(3000) NOT NULL DEFAULT '' COMMENT '存储value',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '字段类型: 1: string , 2: json',
PRIMARY KEY (`id`,`name`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单扩展字段KV表';
1. 确定shardingKey
对于kv扩展字段查询,只会根据id + key 或者 id 为条件的方式查询,所以这里我们可以按照id 分片即可
2. 确定拆分表数量
分512张表(实际场景具体分多少表还得根据字段增加的频次而定)
分表后表名为kv_000 ~ kv_511
id % 512 = 1 .... 分到 kv_001,
id % 512 = 2 .... 分到 kv_002
依次类推!
3. 水平分表思路
先看看未拆分前sql语句insert
insert into kv(id, key, value,create_time,type) value(1, "domain", "www.bytearch.com", "2020-05-17 00:00:00", 1);select
select id, key, value,create_time,type from kv where id = 1 and key = "domain";
我们可以通过动态更改sql语句表名,拆分后sql语句insert
insert into kv_001 (id, key, value,create_time,type) value(1, "domain", "www.bytearch.com", "2020-05-17 00:00:00", 1);select
select id, key, value,create_time,type from kv_001 where id = 1 and key = "domain";
水平分表相对比较容易,后面会讲到基于mybatis插件实现方案
3、水平拆库
场景:以下我们基于博客文章表分库场景来分析
目标:分成1024张库, 000-511号库共用数据节点node1(一个数据节点保护一主多从数据源), 512~1023号库用数据节点node2
支持读写分离
表结构如下(节选部分字段):
CREATE TABLE IF NOT EXISTS `article` (
`id` bigint(20) NOT NULL COMMENT '文章id',
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '作者id',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '文章状态 -1: 删除 1:草稿 2:已发布' ,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_create_time` (`create_time`),
KEY `idx_user_id` (`user_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '订单信息表';
1)确定shardingKey
按照user_id sharding
2) 确定分库数量
假如分1024个库,按照user_id % 1024 hash
user_id % 1024 = 1 分到db_001库
user_id % 1024 = 2 分到db_002库
依次类推
3) 架构图如下
4) 性能线性增长
目前是2个节点,假如后期达到瓶颈,我们可以增加至4个节点
最多可以增加只1024个节点,性能线性增长
5) 非shardingKey查询问题
对于水平分表/分库后,非shardingKey查询首先得考虑到基因法: 见
映射表法: 可以建一张mapping表关联,但是这样引入了额外的单点问题
冗余法: 相同数据按照另外一个字段冗余一张表
nosql法: 将全量数据存到ES,查询ES
4、基于mybatis插件水平分库分表
基于mybatis分库分表,一般常用的一种是基于spring AOP方式, 另外一种基于mybatis插件。其实两种方式思路差不多。
基于mybatis分库得首先解决如下问题1). 如何根据shardingKey选择不同的数据源
2). 在哪个阶段切换数据源
3). 在哪个阶段 更改sql语句(也就是需要更改库名&表名, 解决了问题1和问题2,问题3就很容易解决了)
问题1: 使用Spring的AbstractRoutingDataSource进行数据源的动态切换,原理是使用ThreadLocal先存储数据源key,等需要的的时候获取。
问题2: 这个问题得先分析一下mybatis四大类和插件执行流程,也就是找出也就是分析Executor 和StatementHandler哪个在获取属于源之前执行
为了比较直观解决这个问题,我分别在Executor 和StatementHandler阶段2个拦截器
package com.bytearch.mybatis.sharding.plugin;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;
import java.sql.Connection;
import java.util.Properties;
/**
* @author bytearch
*/
@Intercepts({
@Signature(type = StatementHandler.class,
method = "prepare",
args = {Connection.class, Integer.class})})
@Slf4j
public class StatementHandlerTestInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
log.info("statementHander执行阶段>>>>>>>");
return invocation.proceed();
}
@Override