锐客网

  1. 首页 > 生活百科 > it技术 > >

mysql怎么水平拆分 mysql水平拆分join

IT技术mysql

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扩展字段查询,只会根据idkey 或者 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的一个单库单表里mysql怎么水平拆分 ， 但往往这么大量级的数据到后期，数据库查询速度就很慢，因为数据量级太大 ， 导致表的索引很大，树的层级很高，进而导致搜索性能下降，而且能放内存缓存的数据页是比较少 。
所以推荐MySQL单表数据量不超过1000w，最好是在500w内，若能控制在100万以内，那是 最佳选择，基本单表100万以内的数据 ， 性能上不会有太大的问题，前提是，只要你建好索引就行，其实保证MySQL高性能通常没什么特别高深的技巧，就是控制数据量不要太大，另外就是保证你的查询用上mysql怎么水平拆分了索引就行 。
针对该问题 ， 就能分库分表，可选择将该用户大表拆分为比如100张表，那么此时几千万 数据瞬间分散到100个表里去，类似
这样的100个表，每个表也就几十万数据而已 。
其次，可将这100个表分散到多台数据库服务器上去 ， 此时要分散到几台服务器呢?你要考虑两个点 ， 一个是数据量有多少个GB/TB，一个是针对用户中心的并发压力有多高 。
互联网公司对用户中心的压力不会高到离谱，因为一般不会有很多人同时注册/登录或同时修改自己的个人信息，所以并发不是大问题 。
数据量层面，一般1亿行数据，大致在1~几GB之间的范围，和具体你一行数据有多少个字段也有关，所以说你几千万的用户数据，往多了说也就几个GB 。这点数据量，对服务器存储空间没压力 。
综上，此时你完全能给mysql怎么水平拆分他分配两台数据库服务器，放两个库 ， 然后100张表均匀分散在2台服务器 。分的时候，注意指定一个字段来分，一般就指定userid, 根据用户id进行hash后，对表进行取模 ， 路由到一个表里去 ， 这就能让数据均匀分散 。
至此搞定用户表的分库分表，只要给系统加上数据库中间件技术，设置好路由规则，即可轻松对2个分库上的100张表crud 。平时针对某个用户增删改查,直接对他的userid进行hash,然后对表取模，做个路由 ， 就知道到哪个表里去找这个用户的数据了 。
但是这里可能会出现一些问题mysql怎么水平拆分：用户在登录时，可能不是根据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回到分库分表环境里去找出具体的用户数据，在页面上展示出来即可 。
这就是一套比较常规和完整的分库分表的方案 。
水平拆分7.1 范围分片(range)
比如说t3表
(1)行数非常多 ， 2000w（1-1000w:sh11000w01-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-4idx
取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点
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
1bj1212
2sh22222
3bj3333
4sh44444
5bj5555
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
vimpartition-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.4Mycat全局表
abcd.....
join
t
a
idnameage
1zs18sh1
idaddraid
1001bj1
1002sh2
2ls19sh2
idaddraid
1001bj1
1002sh2
t
idaddraid
1001bj1
1002sh2
使用场景：
如果你的业务中有些数据类似于数据字典，比如配置文件的配置 ，
常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，
而且大部分的业务场景都会用到 ， 那么这种表适合于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
ona.xx =b.yy
a
idname
1ash1
3c
2bsh2
4d
b
idaddraid
1001bj1sh1
1002sh2
1003tj3sh2
1004wh4
为了防止跨分片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怎么水平拆分的... 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水平拆分join】mysql怎么水平拆分的介绍就聊到这里吧，感谢你花时间阅读本站内容 ， 更多关于mysql水平拆分join、mysql怎么水平拆分的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：视频小程序手机版下载安装，视频小程序怎么制作

    下一篇：山东蒸海鲜直播话术，直播海鲜干货主播话语