Mycat分片（分库分表）实践：一张逻辑表拆分为4台服务器的16张表

需求

一张业务表有10亿数据，需要按身份证号字段certno拆分为4台服务器中的16个子表，怎么操作？

设计

使用mycat的分片功能实现分库分表

4台服务器，每台服务器起一个mysql实例，每个实例创建4个database，每个database建一张同名(tu_trade_ng)的表

制定mycat的分片规则，按certno字段的hash值进行分片

mysql 实例	database	tablename
127.0.0.1:7006	nifa_db_t0	tu_trade_ng
	nifa_db_t1	tu_trade_ng
	nifa_db_t2	tu_trade_ng
	nifa_db_t3	tu_trade_ng
127.0.0.1:7007	nifa_db_t0	tu_trade_ng
	nifa_db_t1	tu_trade_ng
	nifa_db_t2	tu_trade_ng
	nifa_db_t3	tu_trade_ng
127.0.0.1:7008	nifa_db_t0	tu_trade_ng
	nifa_db_t1	tu_trade_ng
	nifa_db_t2	tu_trade_ng
	nifa_db_t3	tu_trade_ng
127.0.0.1:7009	nifa_db_t0	tu_trade_ng
	nifa_db_t1	tu_trade_ng
	nifa_db_t2	tu_trade_ng
	nifa_db_t3	tu_trade_ng

实现

mysql创建database

每个mysql实例中创建4个database，共计16个database：

CREATE DATABASE `nifa_db_t0` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `nifa_db_t1` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `nifa_db_t2` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `nifa_db_t3` /*!40100 DEFAULT CHARACTER SET utf8 */;

mysql创建table

每个database中创建一个table，共计16个table：

# 复用了tu_trade_total_area_0的表结构
create table nifa_db_t0.tu_trade_ng like tu_trade_total_area_0;
create table nifa_db_t1.tu_trade_ng like tu_trade_total_area_0;
create table nifa_db_t2.tu_trade_ng like tu_trade_total_area_0;
create table nifa_db_t3.tu_trade_ng like tu_trade_total_area_0;

mycat制定

mycat需要配置的东西：
scheme.xml

<!-- 表名登记 以及分片规则、datanode关联 -->
<table name="tu_trade_ng" primaryKey="ID"  rule="certno-hashcode"  dataNode="dnng$1-16" />
<!-- 16个datanode -->
<dataNode name="dn1" dataHost="NIFADB_1" database="nifa_db_t0" />
<dataNode name="dn2" dataHost="NIFADB_1" database="nifa_db_t1" />
<dataNode name="dn3" dataHost="NIFADB_1" database="nifa_db_t2" />
<dataNode name="dn4" dataHost="NIFADB_1" database="nifa_db_t3" />
<dataNode name="dn5" dataHost="NIFADB_2" database="nifa_db_t0" />
<dataNode name="dn6" dataHost="NIFADB_2" database="nifa_db_t1" />
<dataNode name="dn7" dataHost="NIFADB_2" database="nifa_db_t2" />
<dataNode name="dn8" dataHost="NIFADB_2" database="nifa_db_t3" />
<dataNode name="dn9" dataHost="NIFADB_3" database="nifa_db_t0" />
<dataNode name="dn10" dataHost="NIFADB_3" database="nifa_db_t1" />
<dataNode name="dn11" dataHost="NIFADB_3" database="nifa_db_t2" />
<dataNode name="dn12" dataHost="NIFADB_3" database="nifa_db_t3" />
<dataNode name="dn13" dataHost="NIFADB_4" database="nifa_db_t0" />
<dataNode name="dn14" dataHost="NIFADB_4" database="nifa_db_t1" />
<dataNode name="dn15" dataHost="NIFADB_4" database="nifa_db_t2" />
<dataNode name="dn16" dataHost="NIFADB_4" database="nifa_db_t3" />
<!-- 4个mysql实例 -->
<dataHost name="NIFADB_1" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="NIFADB_HOST_M1" url="127.0.0.1:7006" user="dusername" password="password">
        </writeHost>
</dataHost>
<dataHost name="NIFADB_2" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="NIFADB_HOST_M2" url="127.0.0.1:7007" user="dusername" password="password">
        </writeHost>
</dataHost>
<dataHost name="NIFADB_3" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="NIFADB_HOST_M3" url="127.0.0.1:7008" user="dusername" password="password">
        </writeHost>
</dataHost>
<dataHost name="NIFADB_4" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="NIFADB_HOST_M4" url="127.0.0.1:7009" user="dusername" password="password">
        < /writeHost>
</dataHost>

rule.xml

<!-- 分片规则 columns是table中的列名 algorithm是分片算法  -->
<tableRule name="certno-hashcode">
    <rule>
        <columns>certno</columns>
        <algorithm>func_ng</algorithm>
    </rule>
</tableRule>
<!-- 分片算法 partitionCount是我们要分的16个表 partitionCount*partitionLength=1024  -->
<!-- 注意1024是固定值，不能变！！！  -->
<function name="func_ng" class="org.opencloudb.route.function.PartitionByLong">
    <property name="partitionCount">16</property>
    <property name="partitionLength">64</property>
</function>

说明：
使用mycat的固定分片hash 算法，本条规则类似于十进制的求模运算，区别在于是二进制的操作,是取id 的二进制低10 位，即id 二进制&1111111111。
此算法的优点在于如果按照10 进制取模运算，在连续插入1-10 时候1-10 会被分到1-10 个分片，增大了插入的事务控制难度，而此算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度。
1024是固定的，即最大支持1024 分区。

测试

# 插入121条数据 分了五个不等分的批次插入，插入10条到30条不等，每一条的certno字段都不一样（每批次的certno在数字上连续）
insert into tu_trade_ng (NAME , CERTTYPE , CERTNO , ...... ) values ('余崇天','0','110120199901012233',......);

# count总的数据量
select count(*) from tu_trade_ng;
+--------+
| COUNT0 |
+--------+
|    121 |
+--------+

# count每个table的数据量 
+-----------+
| count(* ) |
+-----------+
|        60 | // dn1
|         1 | // dn2
|         0 | // dn3
|         0 | // dn4
|         1 | // dn5
|         0 | // dn6
|         0 | // dn7
|         0 | // dn8
|         1 | // dn9
|         1 | // dn10
|         9 | // dn11
|         0 | // dn12
|         1 | // dn13
|         2 | // dn14
|        25 | // dn15
|        20 | // dn16
+-----------+

测试说明了：

1. 我们的分库分表生效了，从mycat中查询的数据量等于从mysql中分表查询的数据量
2. 固定分片hash 算法的优点：算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度

注意

mycat不支持insert into ... select ...这种sql：

insert into tu_trade_ng (NAME , CERTTYPE , CERTNO , ......) 
select NAME , CERTTYPE , CERTNO , ......  from an_old_table;

注意schema中的mysql实例账号(下面的user字段)，要保证该账号能访问每一个tu_trade_ng表，不然使用mycat操作的tu_trade_ng会失败：

<writeHost host="NIFADB_HOST_M3" url="127.0.0.1:7008" user="dusername" password="password">

以上实践参考官方文档。