概述

先进行范围分片计算出分片组，组内再求模：

• 优点可避免扩容时的数据迁移，又可以一定程度上避免范围分片的热点问题；
• 综合了范围分片和求模分片的 优点，分片组内使用求模可保证组内数据比较均匀，分片组之间是范围分片，可以兼顾范围查询；
• 最好事先规划好分片的数量，数据扩容时按分片组扩容，则原有分片组的数据不需要迁移。由二分片组内数据比较均匀，所以分片组内可避免范围分片的热点问题。

创建表

DROP TABLE IF EXISTS `t_range`;
CREATE TABLE `t_range` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置

• schema.xml

<table name="t_range" primaryKey="id" dataNode="node1,node2" rule="auto-sharding-rang-mod" />

• rule.xml

<tableRule name="auto-sharding-rang-mod">
    <rule>
        <columns>id</columns>
        <algorithm>rang-mod</algorithm>
    </rule>
</tableRule>

<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
    <property name="mapFile">partition-range-mod.txt</property>
    <property name="defaultNode">0</property>
</function>

• partition-range-mod.txt

# range start-end ,data node group size
0-1M=1
1M1-2M=1

测试

mysql -umycat -p123456 -P 8066 -h 192.168.201.34 -DTESTDB

MySQL [TESTDB]> INSERT INTO t_range (id, name)VALUES ('9999', 'db111');
Query OK, 1 row affected (0.09 sec)

MySQL [TESTDB]> explain select * from t_range;
+-----------+---------------------------------+
| DATA_NODE | SQL                             |
+-----------+---------------------------------+
| node1     | SELECT * FROM t_range LIMIT 100 |
| node2     | SELECT * FROM t_range LIMIT 100 |
+-----------+---------------------------------+
2 rows in set (0.06 sec)

MySQL [TESTDB]> explain select * from t_range where id=9999; 
+-----------+-------------------------------------+
| DATA_NODE | SQL                                 |
+-----------+-------------------------------------+
| node1     | select * from t_range where id=9999 |
+-----------+-------------------------------------+
1 row in set (0.03 sec)

MySQL [TESTDB]> INSERT INTO t_range (id, name)VALUES ('10000', 'db111');
Query OK, 1 row affected (0.01 sec)

MySQL [TESTDB]> explain select * from t_range where id=10000;     
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| node1     | select * from t_range where id=10000 |
+-----------+--------------------------------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> INSERT INTO t_range (id, name)VALUES ('10001', 'db222');
Query OK, 1 row affected (0.02 sec)

MySQL [TESTDB]> explain select * from t_range where id=10001;                   
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| node2     | select * from t_range where id=10001 |
+-----------+--------------------------------------+
1 row in set (0.01 sec)

MySQL [TESTDB]> INSERT INTO t_range (id, name)VALUES ('20000', 'db222');
Query OK, 1 row affected (0.01 sec)

MySQL [TESTDB]> explain select * from t_range where id=10002;                   
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| node2     | select * from t_range where id=10002 |
+-----------+--------------------------------------+
1 row in set (0.00 sec)

mysql -u root -p123456  -h 192.168.201.33
MariaDB [db1]> select * from t_range;
+-------+--------------+
| id    | name         |
+-------+--------------+
|  9999 | db111 |
| 10000 | db111 |
+-------+--------------+
2 rows in set (0.00 sec)

mysql -u root -p123456  -h 192.168.201.35
MariaDB [db2]> select * from t_range;
+-------+--------------+
| id    | name         |
+-------+--------------+
| 10001 | db222 |
| 20000 | db222 |
+-------+--------------+
2 rows in set (0.00 sec)