MySql分片规则·求模分片

一、求模分片规则(mod-long)

• 表中必须有名叫id的表头
• 根据id表头与设定数字取余的结果存储数据
  余数是 0 数据存储到 dn1
  余数是 1 数据存储到 dn2
  余数是 2 数据存储到 dn3

二、相关配置

• schema.xml文件中设置mod-long规则

# 查看使用mod-long分片规则的表名：
[root@maxscale56 ~]# vim /usr/local/mycat/conf/schema.xml                   
<table name="hotnews" primaryKey="ID" autoIncrement="true" 
       dataNode="dn1,dn2,dn3"  rule="mod-long" />

• rule.xml存储分片规则对应的 分片字段名 以及 求模的数值

# 设置取余计算的数字
# <colums>存储分片字段名 
# <algorithm>存储算法规则
# <property>定义分片字段做求模计算的数字
[root@maxscale56 ~]# vim /usr/local/mycat/conf/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">3</property> 
</function>

三、示例：

• 建表并存储数据

# 客户端进入分片服务器
[root@host50 ~]# mysql -h192.168.88.60 -P8066 -uroot -p123456
# 进入虚拟库
mysql> use TESTDB;
mysql> create  table  hotnews(id  int  , 
  title char(30),
  comment varchar(150) , 
  worker char(3) 
);
# 插入数据
mysql> insert into hotnews(id,title,comment,worker)
  values(9,"a","a","a");    
mysql> insert into hotnews(id,title,comment,worker)
  values(10,"b","a","a");       
mysql> insert into hotnews(id,title,comment,worker)
  values(11,"b","a","a");
mysql> insert into hotnews(id,title,comment,worker)
  values(7,"b","a","a");
mysql> insert into hotnews(id,title,comment,worker)
  values(1000,"d","a","a");

• 在数据库服务器查看数据(确认不同的数据根据不同的id值被分配到不同的数据库即为成功)

[root@host63 ~]# mysql -uroot -p123qqq...A -e 'select  * from db1.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id   | title | comment | worker |
+------+-------+---------+--------+
|    9 | a     | a       | a      |
|    0 | d     | a       | a      |
+------+-------+---------+--------+

[root@host64 ~]# mysql -uroot -p123qqq...A -e 'select  * from db2.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id   | title | comment | worker |
+------+-------+---------+--------+
|   10 | b     | a       | a      |
| 1000 | d     | a       | a      |
+------+-------+---------+--------+

[root@host65 ~]# mysql -uroot -p123qqq...A -e 'select  * from db3.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+--------+
| id   | title | comment | worker |
+------+-------+---------+--------+
|   11 | c     | a       | a      |
+------+-------+---------+--------+