mysql怎么水平拆分 MySQL水平分表

给小白演示 分库分表案例

受群里小伙伴之邀，搞一个分库分表案例，这样让很多没用过分库分表的心里也有个底，不然永远看到的都是网上的各种概念和解决方案性的文章。

成都创新互联公司专业提供成都主机托管四川主机托管成都服务器托管四川服务器托管，支持按月付款！我们的承诺：贵族品质、平民价格，机房位于中国电信/网通/移动机房，川西大数据中心服务有保障！

由于用户表过于庞大，采取相关SQL优化，还是不能满足，所以现对其进行做分库分表。

数据库： my-sharding

数据库表： t_user

建表语句如下：

关于数据库分库分表通常有两种方案：

下面我们来演示水平拆分，大致思路：

加入有2000万条数据，那么为了方便演示，我们就暂定分为五个库，每个数据库对应五个表。

五个数据库：

每个数据库有五张表：

建表语句如下：

使用技术栈： JDK8 + MySQL + Spring Boot + Mybatis + Shardingsphere + Druid

maven 相关依赖：

配置文件相关配置如下：

分库分表的两个分片类：

下面是业务部分代码，先看 UserMapper.xml 内容：

UserMapper 接口：

为了更好地演示，我这里加入了 controller 层和 service 层，这也是大家平常开发套路。

service 层代码如下：

controller层代码如下：

最后是项目的启动类：

启动项目，启动成功：

下面我们来演示一下新增数据和查询。

先来添加数据到数据库中，这里使用的是IDEA中restful工具：

后台日志：

再查看数据库表中：

到此，我们的数据依旧落库，下面我们来演示一下数据查询。

浏览器里输入：

返回数据：

后台日志：

从日志和返回结果可以看出，已经为我们正确的选择到对应的数据库和表了，这样，一个分库分表的查询就成功了。

本文没有太多的概念，直接使用案例演示。相关概念性的文章，还有分库分表解决方案的文章，网上一堆堆的，感兴趣可以自行查阅。

亿级用户大表如何作水平拆分？

一般面对几千万级的数据，刚开始可能都是把数据放在MySQL的一个单库单表里，但往往这么大量级的数据到后期，数据库查询速度就很慢，因为数据量级太大，导致表的索引很大，树的层级很高，进而导致搜索性能下降，而且能放内存缓存的数据页是比较少。

所以推荐MySQL单表数据量不超过1000w，最好是在500w内，若能控制在100万以内，那是 最佳选择，基本单表100万以内的数据，性能上不会有太大的问题，前提是，只要你建好索引就行，其实保证MySQL高性能通常没什么特别高深的技巧，就是控制数据量不要太大，另外就是保证你的查询用上了索引就行。

针对该问题，就能分库分表，可选择将该用户大表拆分为比如100张表，那么此时几千万 数据瞬间分散到100个表里去，类似

这样的100个表，每个表也就几十万数据而已。

其次，可将这100个表分散到多台数据库服务器上去，此时要分散到几台服务器呢?你要考虑两个点，一个是数据量有多少个GB/TB，一个是针对用户中心的并发压力有多高。

互联网公司对用户中心的压力不会高到离谱，因为一般不会有很多人同时注册/登录或同时修改自己的个人信息，所以并发不是大问题。

数据量层面，一般1亿行数据，大致在1~几GB之间的范围，和具体你一行数据有多少个字段也有关，所以说你几千万的用户数据，往多了说也就几个GB。这点数据量，对服务器存储空间没压力。

综上，此时你完全能给他分配两台数据库服务器，放两个库，然后100张表均匀分散在2台服务器。分的时候，注意指定一个字段来分，一般就指定userid, 根据用户id进行hash后，对表进行取模，路由到一个表里去，这就能让数据均匀分散。

至此搞定用户表的分库分表，只要给系统加上数据库中间件技术，设置好路由规则，即可轻松对2个分库上的100张表crud。平时针对某个用户增删改查,直接对他的userid进行hash,然后对表取模，做个路由，就知道到哪个表里去找这个用户的数据了。

但是这里可能会出现一些问题：用户在登录时，可能不是根据userid登录，而是根据username、手机号，此时你就没有userid了, 怎么知道去哪个表里找这个用户的数据判断是否能登录? 对此。常规方案是建立一个索引映射表，搞个表结构为 (username, userid)的索引映射 表，把username和userid一一映射， 然后针对username再做一次分库分表，把这个索引映射表可拆分为比如100个表分散在两台服务器。然后用户登录时，就能根据username先去索引映射表里查找对应userid，比如对username进行hash，然后取模路由到一个表里去，找到username对应的userid，接着根据userid进行hash再取模，路由到按照userid分库分表的一个表里，找到用户的完整数据。但这种方式会把一次查询转化为两个表的两次查询，先查索引映射表，再根据userid去查具体的数据，性能有损耗，不过有时为解决分库分表问题，也只能用这种方案。

另外就是若公司运营团队里，有用户管理模块，需要对公司的用户按照手机号、住址、年龄、性别、职业等各种条件搜索，这咋办?

没啥好办法，基本上就是要对你的用户数据表进行binlog监听，把你要搜索的所有字段同步到ES，建立好搜索的索引。然后你的运营系统就可以通过ES进行复杂的多条件搜索，ES是适合干这个事儿的，然后定位到一批userid，通过userid回到分库分表环境里去找出具体的用户数据，在页面上展示出来即可。

这就是一套比较常规和完整的分库分表的方案。

mysql里的大表用mycat做水平拆分，是不是要先手动分好，再配置mycat

将所有数据都迁移到mycat中，一共有4个数据库，blog01，blog02，blog_article01，blog_article02。

article，article_tags分别在blog_article01，blog_article02，按照uid进行水平拆分。

user_info表在blog01，link，category，tag在blog02数据库中。

浅谈mysql数据库分库分表那些事-亿级数据存储方案

mysql分库分表一般有如下场景

其中1，2相对较容易实现,本文重点讲讲水平拆表和水平拆库,以及基于mybatis插件方式实现水平拆分方案落地。

在 《聊一聊扩展字段设计》 一文中有讲解到基于KV水平存储扩展字段方案,这就是非常典型的可以水平分表的场景。主表和kv表是一对N关系,随着主表数据量增长,KV表最大N倍线性增长。

这里我们以分KV表水平拆分为场景

对于kv扩展字段查询,只会根据id + key 或者 id 为条件的方式查询,所以这里我们可以按照id 分片即可

分512张表(实际场景具体分多少表还得根据字段增加的频次而定)

分表后表名为kv_000 ~ kv_511

id % 512 = 1 .... 分到 kv_001,

id % 512 = 2 .... 分到 kv_002

依次类推!

水平分表相对比较容易,后面会讲到基于mybatis插件实现方案

场景:以下我们基于博客文章表分库场景来分析

目标:

表结构如下(节选部分字段):

按照user_id sharding

假如分1024个库,按照user_id % 1024 hash

user_id % 1024 = 1 分到db_001库

user_id % 1024 = 2 分到db_002库

依次类推

目前是2个节点,假如后期达到瓶颈,我们可以增加至4个节点

最多可以增加只1024个节点,性能线性增长

对于水平分表/分库后,非shardingKey查询首先得考虑到

基于mybatis分库分表,一般常用的一种是基于spring AOP方式, 另外一种基于mybatis插件。其实两种方式思路差不多。

为了比较直观解决这个问题,我分别在Executor 和StatementHandler阶段2个拦截器

实现动态数据源获取接口

测试结果如下

由此可知,我们需要在Executor阶段 切换数据源

对于分库:

原始sql:

目标sql:

其中定义了三个注解

@useMaster 是否强制读主

@shardingBy 分片标识

@DB 定义逻辑表名 库名以及分片策略

1)编写entity

Insert

select

以上顺利实现mysql分库,同样的道理实现同时分库分表也很容易实现。

此插件具体实现方案已开源:

目录如下:

mysql分库分表,首先得找到瓶颈在哪里(IO or CPU),是分库还是分表,分多少？不能为了分库分表而拆分。

原则上是尽量先垂直拆分 后 水平拆分。

以上基于mybatis插件分库分表是一种实现思路,还有很多不完善的地方,

例如:

mysql表水平分割怎么实现

一条语句没法实现的... create table table2 select * from table1 order by id limit 1,100 create table table3 select * from table1 order by id limit 100,100 create table table4 select * from table1 order by id limit 200,100

水平拆分

7.1 范围分片(range)

比如说t3表

(1)行数非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2）

(2)访问非常频繁，用户顺序访问较多

cp schema.xml schema.xml.1

vim schema.xml

添加：

table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" /

vim rule.xml

tableRule name="auto-sharding-long"

rule

columnsid/columns

algorithmrang-long/algorithm

/rule

function name="rang-long"

class="io.mycat.route.function.AutoPartitionByLong"

property name="mapFile"autopartition-long.txt/property

/function

vim autopartition-long.txt

0-10=0

10-20=1

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t3"

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t3"

7.2 取模分片

1%3 1

2%3 2

3%3 0

4%3 1

5%3 2

任何正整数数字和N（正整数）取模，得的值永远都是 0~N-1

id % 分片数量取模

N % 5 = 0-4 idx

取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点

vim schema.xml

table name="t4" dataNode="sh1,sh2" rule="mod-long" /

vim rule.xml

property name="count"2/property

创建测试表：

mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.52 -P8066 --default-auth=mysql_native_password

use TESTDB

insert into t4(id,name) values(1,'a');

insert into t4(id,name) values(2,'b');

insert into t4(id,name) values(3,'c');

insert into t4(id,name) values(4,'d');

insert into t4(id,name) values(6,'x'),(8,'y'),(10,'z');

分别登录后端节点查询数据

mysql -S /data/3308/mysql.sock -e "select * from taobao.t4"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t4"

7.3 枚举分片 (区域、zone)

t5 表

id name telnum

1 bj 1212

2 sh 22222

3 bj 3333

4 sh 44444

5 bj 5555

sharding-by-intfile

vim schema.xml

table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /

vim rule.xml

function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"

property name="mapFile"partition-hash-int.txt/property

property name="type"1/property

/function

vim partition-hash-int.txt 配置：

bj=0

sh=1

DEFAULT_NODE=1

mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password

use TESTDB

insert into t5(id,name) values(1,'bj');

insert into t5(id,name) values(2,'sh');

insert into t5(id,name) values(3,'bj');

insert into t5(id,name) values(4,'sh');

insert into t5(id,name) values(5,'tj');

mysql -S /data/3308/mysql.sock -e "select * from taobao.t5"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t5"

7.4 Mycat全局表

a b c d .....

join

t

a

id name age

1 zs 18 sh1

id addr aid

1001 bj 1

1002 sh 2

2 ls 19 sh2

id addr aid

1001 bj 1

1002 sh 2

t

id addr aid

1001 bj 1

1002 sh 2

使用场景：

如果你的业务中有些数据类似于数据字典，比如配置文件的配置，

常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，

而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，

要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，

避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。

vim schema.xml

table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" /

mysql -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password

mysql -S /data/3308/mysql.sock -e "select * from taobao.t_area"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t_area"

7.5 E-R分片

a

join

b

on a.xx =b.yy

a

id name

1 a sh1

3 c

2 b sh2

4 d

b

id addr aid

1001 bj 1 sh1

1002 sh 2

1003 tj 3 sh2

1004 wh 4

为了防止跨分片join，可以使用E-R模式

table name="a" dataNode="sh1,sh2" rule="mod-long"

childTable name="b" joinKey="aid" parentKey="id" /

/table

select * from a join b on a.id = b.aid

例子：

mysql -S /data/3308/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null ,aid int );"

insert into b(id,addr,aid) values(1001,'bj',1);

insert into b(id,addr,aid) values(1002,'sj',3);

insert into b(id,addr,aid) values(1003,'sd',4);

insert into b(id,addr,aid) values(1004,'we',2);

insert into b(id,addr,aid) values(1005,'er',5);

========

后端数据节点数据分布：

mysql -S /data/3307/mysql.sock -e "select * from taobao.a"

mysql -S /data/3307/mysql.sock -e "select * from taobao.b"

mysql -S /data/3308/mysql.sock -e "select * from taobao.a"

mysql -S /data/3308/mysql.sock -e "select * from taobao.b"