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水平分表的几种方法

1.按时间分表

这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。

2.按区间范围分表

一般在有严格的自增id需求上，如按照user_id水平分表：

table_1  user_id从1~100w 

table_2  user_id从101~200w 

table_3  user_id从201~300w 

... 

3.hash分表

通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。

按如下分10张表：

function get_hash_table($table, $userid)

{

$str = crc32($userid);

if ($str  0) {

$hash = "0" . substr(abs($str), 0, 1);

} else {

$hash = substr($str, 0, 2);

}

return $table . "_" . $hash;

}

echo get_hash_table('message', 'user18991'); //结果为message_10

echo get_hash_table('message', 'user34523'); //结果为message_13

水平拆分

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"

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

一般面对几千万级的数据，刚开始可能都是把数据放在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表水平分割怎么实现

一条语句没法实现的... 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

浅谈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水平拆分怎么做 mysql水平拆分实现
文章URL：http://myzitong.com/article/dodgeji.html