44. mycat范围分片

1. mycat核心特性——分片（水平拆分）

#1.适用范围
    1.行数非常多，800w
    2.访问非常频繁
#2.分片目的
    1.将大数据量进行分布存储
    2.提供均衡的访问路由
#3.分片策略
    1.范围
    2.取模
    3.枚举
    4.哈希
    5.时间

2. 范围分片

比如说t3表
1.行数非常多，2000w（1-1000w：dn1  1000w01-2000w：dn2）
2.访问非常频繁，用户访问比较离散

3. 配置文件

mv schema.xml schema.xml.bak1
vim 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="dn1">
    <table name="t3" dataNode="dn1,dn2" rule="auto-sharding-long" />
</schema>

    <dataNode name="dn1" dataHost="oldguo1" database="world" />
    <dataNode name="dn2" dataHost="oldguo2" database="world" />

    <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.178.161:3307" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3307" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3309" user="root" password="123" />
    </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.178.161:3308" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3308" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3310" user="root" password="123" />
    </writeHost>
    </dataHost>

</mycat:schema>

4. 查看分片策略

vim rule.xml

1.找到分片名
2.基于哪个列拆分
3.找到函数
4.找到调用的txt文件
5.修改txt文件
    1-10=0
    11-20=1

5. 重启mycat

mycat restart

6. 创建测试数据

#空表就可以，分片不仅提高读性能，而且提高写性能
mysql -S /data/3307/mysql.sock
use world
create table t3(id int not null primary key auto_increment,name varchar(20) not null);

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

7. 测试

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');

8. mycat客户端查看

select * from t3;

9. 分别到不同的分片中查看t3表

select * from t3;

select * from t3;

---

最后更新: 2022-02-20 08:44:07