MySQL 部署分布式架构 MyCAT (三)

配置垂直分表

修改 schema.xml (db1)

cd /data/mycat/conf

cp schema.xml schema.xml.rwh

# 修改配置
vi 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="user" dataNode="sh1"/>
        <table name="order_t" dataNode="sh2"/>
</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.31.205:3307" user="root" password="123">
            <readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
            <readHost host="db4" url="192.168.31.206: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.31.205:3308" user="root" password="123">
            <readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
            <readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>

创建测试数据(db1)

mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"

启动 mycat, 插入数据(db1)

mycat start

mysql -uroot -p123456 -h 127.0.0.1 -P8066

use TESTDB;
insert user values(1, 'klvchen');
insert order_t values(1, 'lucy');

测试(db1)

mysql -S /data/3307/mysql.sock -e "use taobao;select * from user;"
+------+---------+
| id   | name    |
+------+---------+
|    1 | klvchen |
+------+---------+

mysql -S /data/3308/mysql.sock -e "use taobao;select * from order_t;"
+------+------+
| id   | name |
+------+------+
|    1 | lucy |
+------+------+

mysql -S /data/3307/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| user             |
+------------------+

mysql -S /data/3308/mysql.sock -e "use taobao;show tables;"
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
+------------------+

分片（水平拆分）

范围分片

分片：对一个"bigtable"，比如说t3表

(1)行数非常多，800w
(2)访问非常频繁

分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由

分片策略：
范围 range  800w  1-400w 400w01-800w
取模 mod    取余数
枚举 
哈希 hash 
时间 流水

优化关联查询
全局表
ER分片

配置

cd /data/mycat/conf/
 
cp schema.xml schema.xml.vertical
vi 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.31.205:3307" user="root" password="123">
            <readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
            <readHost host="db4" url="192.168.31.206: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.31.205:3308" user="root" password="123">
            <readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
            <readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>

vi rule.xml 
        <tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>

# 根据 id 来分片
vi autopartition-long.txt
0-10=0
11-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);"
mycat restart

# 插入数据
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 -uroot -p123456 -h 127.0.0.1 -P 8066
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t3;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

mysql -S /data/3308/mysql.sock -e "use taobao;select * from t3;"
+----+------+
| id | name |
+----+------+
| 11 | aa   |
| 12 | bb   |
| 13 | cc   |
| 14 | dd   |
+----+------+