Mysql之 Mycat分布式-范围分片
使用范围:
(1)行数非常多,假如2000w(1-1000w:sh1 1000w01-2000w:sh2)
(2)当这个表访问非常频繁,用户访问较离散
测试环境:t3表(20行数据)
vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.78.4:3307" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3309" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3307" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3309" user="root" password="123456" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.78.4:3308" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3310" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3308" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3310" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
把一张表拆分两张表,用rule,auto-sharding-long (范围分片)策略把t3表今进行拆分,分片到两个节点sh1,sh2。
看一下范围分片策略定义:
cat /usr/local/mycat/conf/rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
解释:
<columns>id</columns> 考虑范围分片最好拿什么做分片--主键id--没有重复--叫分片键也叫分片列
<algorithm>rang-long</algorithm> rang-long函数名字,通过mycat写好的分片策略
cat /usr/local/mycat/conf/rule.xml
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
解释:
函数要有参数,参数:你的告诉,怎么分配,1-1000w进行分片,1000-2000w进行分片
<property name="mapFile">autopartition-long.txt</property> mapFile说明了,你就按照autopartition-long.txt分片
vim /usr/local/mycat/conf/autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
解释:# range start-end ,data node index 两部分组成,用,分割
range start-end 起始点,终点
data node index 数据节点索引
这里面默认使用0,2...节点。代表sh1,sh2
# K=1000,M=10000. 可以使用k或者M作为单位
0-500M=0 代表0到500万(500×1M(10000))为0(sh1)分片
500M-1000M=1 代表500万到1000万为1(sh2)分片
问题:
0-500M=0
500M-1000M=1
500m归谁
0-500M=0 大于0小于等于500M
500M-1000M=1 大于500M小于等于1000M
由于测试t3表为1-10 10 -20
# range start-end ,data node index
# K=1000,M=10000.
1-10=0
10-20=1
问题:多出来,不插入,所以需要评估
mysql> insert into t3(id,name) values(21,'a');
ERROR 1064 (HY000): can't find any valid datanode :T3 -> ID -> 21
创建测试表:
mysql -uroot -p123456 -S /data/3307/mysql.sock -P 3307 -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -uroot -p123456 -S /data/3308/mysql.sock -P 3308 -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
测试:
重启mycat
mycat restart
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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(10,'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');
insert into t3(id,name) values(20,'dd');
mysql -uroot -p123456 -S /data/3307/mysql.sock -P 3307
mysql> use taobao;
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 10 | d |
+----+------+
mysql -uroot -p123456 -S /data/3308/mysql.sock -P 3308
mysql> use taobao;
mysql> select * from taobao.t3;
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
| 20 | dd |
+----+------+
相关文章
- 【MySQL高级】应用优化及Mysql中查询缓存优化以及Mysql内存管理及优化
- MySQL 事物及隔离级别
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- 一分钟读懂MySQL分布式消息的处理
- 《MySQL DBA修炼之道》——第2章 MySQL安装部署和入门 2.1如何选择MySQL版本
- MySQL第四讲 MySql Undo日志 - 对聚簇索引进行CUD操作
- 【MySQL进阶-02】mysql的explain执行计划以及索引优化
- Mysql 数据库设计规范
- t-ora issue can't login mysql
- mysql的中文乱码问题
- Mysql 中获取刚插入的自增长id的值
- mysql—MySQL数据库中10位或13位时间戳和标准时间相互转换
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- mysql 索引优化,索引建立原则和不走索引的原因
- Mysql error.log报错:Error: Table “mysql”.“innodb_table_stats” not found
- window安装MYSQL出错:a windows service with the name MYSQL already...service.
- Mysql之 Mycat 分布式-取模分片(mod-long)