MySQL学习笔记在垂直分表情况下，其中有一张表访问异常繁忙，数据量级很大，需要对这张热点数据表，进行水平拆分。 对一个"bigtable"分片：比如说t3表 (1)行数非常多，800w (2)访问非常频繁 分片的目的： (1)将大数据量进行分布存储 (2)提供均衡的访问路由
分片策略
范围分片： range 800w 1-400w一张表；400w-800w一张表 取模分片：mod 取余分配 枚举 哈希hash 时间：按月份分片，比如流水信息
优化关联查询
全局表 ER分片
range范围分片
比如说t3表 (1)行数非常多，2000w（1-1000w:sh1 1000w01-2000w:sh2） (2)访问非常频繁，用户访问较离散 cp schema.xml schema.xml.chuizhi [root@db01 conf]# vim schema.xml <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> <table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" /> </schema>
auto-sharding-long参数
vim rule.xml <tableRule name="auto-sharding-long"> <rule> <columns>id</columns> <algorithm>rang-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
创建测试表
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);" mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
range分片测试
[root@db01 conf]# mycat restart mysql> use TESTDB insert into t3(id,name) values(1,'a'); insert into t3(id,name) values(2,'b'); insert into t3(id,name) values(3,'c'); insert into t3(id,name) values(4,'d'); insert into t3(id,name) values(11,'aa'); insert into t3(id,name) values(12,'bb'); insert into t3(id,name) values(13,'cc'); insert into t3(id,name) values(14,'dd'); mysql> select * from t3; [root@db01 conf]# mysql -S /data/3307/mysql.sock mysql> use taobao mysql> select* from t3; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ [root@db01 conf]# mysql -S /data/3308/mysql.sock mysql> use taobao mysql> select* from t3; +----+------+ | id | name | +----+------+ | 11 | aa | | 12 | bb | | 13 | cc | | 14 | dd | +----+------+
取模分片（mod-long）
取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点 [root@db01 conf]# vim schema.xml <table name="t4" dataNode="sh1,sh2" rule="mod-long" /> vim rule.xml <tableRule name="mod-long"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> .... function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property>///更改节点数 </function>
创建测试表
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);"
取模分片测试
[root@db01 conf]# mycat restart [root@db01 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066 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'); 分别登录后端节点查询数据 mysql -S /data/3307/mysql.sock use taobao select * from t4; mysql> select * from t4; +----+------+ | id | name | +----+------+ | 2 | b | | 4 | d | mysql -S /data/3308/mysql.sock use taobao select * from t4; mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | a | | 3 | c |
枚举分片
id name telnum 1 bj 1212 2 sh 22222 3 bj 3333 4 sh 44444 5 bj 5555 vim schema.xml <table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /> vim rule.xml <tableRule name="sharding-by-intfile"> <rule> <columns>name</columns> //name列做分片 <algorithm>hash-int</algorithm> </rule> </tableRule> ... <function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txt</property> <property name="type">1</property>//这行为新增 <property name="defaultNode">0</property>//可以在这里添加也可以在文件里添加partition-hash-int.txt，表示默认表 </function> [root@db01 conf]# 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.51 -P8066 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'); [root@db01 conf]# mysql -S /data/3307/mysql.sock mysql> use taobao mysql> select * from t5; +----+------+ | id | name | +----+------+ | 1 | bj | | 3 | bj | +----+------+ [root@db01 conf]# mysql -S /data/3308/mysql.sock mysql> use taobao mysql> select * from t5; +----+------+ | id | name | +----+------+ | 2 | sh | | 4 | sh | | 5 | tj | +----+------+