Mycat水平分库

一、什么是水平分库

将一张表水平切分到多个库中

1.1分片原则

1.需要分片的表是少数的
2.能不切分尽量不要切分
3.日志表可以采取归档方式
4.选择合适的切分规则和分片建，确保数据分片均匀，否则依然存在性能瓶颈
5.尽量避免跨分片join操作，保证关联操作表在同一分片

1.2分片后如何处理查询

1、根据简单分片规则，对分片键进行路由到正确的后端物理数据库
2、如果不是使用分片键的话，将会遍历后端数据库，极大消耗性能

二、水平切分步骤

2.1根据业务状态确定要进行水平切分的表

读写频繁，访问量非常大的表才需要切分，一般是订单表

如何选择分片键

1、尽可能的比较均匀分布数据到各个节点上，自增长主键并不是很好的选择，因为不会用于查询
2、业务字段是最频繁的或者最重要的查询条件

2.2分析业务模型选择分片键及分片算法

一般分片键选择的是频繁作为查询的字段，关键是能保证分片后的数据分布均匀，常用简单取模算法

对订单相关表进行水平切分
不仅仅是订单表，经常与订单表关联查询的表也需要一并分片，避免跨分片查询，大表不适合作为全局表
订单号，可以保证分片均匀，但是实际业务很少根据订单号来查询
下单人ID，业务查询更多，更适合，但是分片并不均匀，但不严重，值得考虑
采用简单取模分片算法，可以保证数据尽可能均匀

2.3适用mycat部署分片集群

1、使用schema.xml配置逻辑库及逻辑表

<!-- <table name="order_master" primaryKey="order_id" dataNode="dn_orderdb" /> -->
    <table name="order_master" primaryKey="order_id" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master"/>
<!-- 只需要增加表所在的物理节点schema名称和指定规则rule -->

<dataNode name="dn_orderdb01" dataHost="mysql10143" database="orderdb01" />
<dataNode name="dn_orderdb02" dataHost="mysql10143" database="orderdb02" />
<dataNode name="dn_orderdb03" dataHost="mysql10144" database="orderdb03" />
<dataNode name="dn_orderdb04" dataHost="mysql10144" database="orderdb04" />

2.使用rule.xml配置分片表的分片规则

<tableRule name="order_master">
    <rule>
        <columns>customer_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
<!-- 分片算法，唯一；JAVA类路径，1.6是"io.mycat.route.function.PartitionByMod"-->
    <property name="count">4</property>
</function>

3、验证

# 现在逻辑库上进行查看，此时是没有数据的
# 需要提前建库建表，否则报错表不存在
app_imooc@172.16.10.142 00:26:  [imooc_db]> select * from order_master;
Empty set (0.08 sec)

# 在逻辑库imooc_db上插入5条数据，正常是orderdb02 2条数据（节点索引顺序为1），其他各1条数据
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70005,90005,'yzw5',5,5,5,'address1',1,20);

# truncate table报错
app_imooc@172.16.10.142 10:51:  [imooc_db]> truncate table order_master;
ERROR 1105 (HY000): DROP command denied to user 'bm_mycat'@'172.16.10.142' for table 'order_master'

# 逻辑库查看插入结果
app_imooc@172.16.10.142 10:50:  [imooc_db]> select order_id,order_sn,customer_id from order_master;
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    80005 |    70002 |       90002 |
|    80005 |    70003 |       90003 |
|    80005 |    70004 |       90004 |
|    80006 |    70001 |       90001 |
|    80007 |    70005 |       90005 |
+----------+----------+-------------+
5 rows in set (0.00 sec)

# 在物理库查看是否有这4条数据，验证成功
root@localhost 10:52:  [orderdb01]> select order_id,order_sn,customer_id from order_master;
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    80005 |    70004 |       90004 |
+----------+----------+-------------+
1 row in set (0.00 sec)

root@localhost 10:52:  [orderdb02]> select order_id,order_sn,customer_id from order_master;
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    80006 |    70001 |       90001 |
|    80007 |    70005 |       90005 |
+----------+----------+-------------+
2 rows in set (0.00 sec)

root@localhost 10:05:  [orderdb03]> select order_id,order_sn,customer_id from order_master;

+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    80005 |    70002 |       90002 |
+----------+----------+-------------+
1 row in set (0.00 sec)

root@localhost 10:52:  [orderdb04]> select order_id,order_sn,customer_id from order_master;
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    80005 |    70003 |       90003 |
+----------+----------+-------------+
1 row in set (0.00 sec)

4、使用server.xml配置访问权限

#配置用户登录

2.4测试分片集群，采用应用端双写方式进行

2.5业务及数据迁移

三、全局自增ID

分片表中的自增ID在逻辑表中有重复
第三方给ID或者使用mycat自增ID

3.1全局自增ID方法

1、本地文件方式：适用服务器本地磁盘的方式
2、数据库方式：适用数据库存储的方式(自增主键方式)
3、本地时间戳方式：适用时间戳
4、分布式zookeeper生成ID

3.2本地文件全局ID

优点：本地加载，读取速度快，配置简单
缺点：集群部署无法使用，不同的mycat无法保证id唯一，使mycat变成了有状态的中间件

配置方法

1.server.xml增加属性

<property name="sequnceHandlerType>0</property>

2.sequence_conf.properties配置

#default global sequence
GLOBAL.HISIDS=
GLOBAL.MINID=10001
GLOBAL.MAXID=20000
GLOBAL.CURID=10000

# self define sequence
ORDER_MASTER.HISIDS=
ORDER_MASTER.MINID=1001
ORDER_MASTER.MAXID=2000
ORDER_MASTER.CURID=1000


# 以上配置文件中，自定义表名必须大写书写HISIDS：表示使用过的历史分段(一般无特殊需要可不配置)
# MINID ：最小ID 值
# MAXID ：表示最大ID 值
# CURID ：表示当前ID 值。
# 当 sequence_conf.properties的配置名字与 表名一致的时候sql可以不包含ID字段（此处表名为id_local_file），逻辑表需要增加属性autoIncrement="true"

3.插入数据

# mycat restart
# delete from order_master;
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70005,90005,'yzw5',5,5,5,'address1',1,20);
# 此时逻辑表中的order_id唯一
app_imooc@172.16.10.142 11:35:  [imooc_db]> select order_id,order_sn,customer_id from order_master;                                                                                            
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|    10004 |    70004 |       90004 |
|    10003 |    70003 |       90003 |
|    10001 |    70001 |       90001 |
|    10005 |    70005 |       90005 |
|    10002 |    70002 |       90002 |
+----------+----------+-------------+
5 rows in set (0.01 sec)

insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70005,90005,'yzw5',5,5,5,'address1',1,20);

# 此时逻辑表中的order_id依然是唯一，但是ID值使用的是自定义表自增属性的值
app_imooc@172.16.10.142 11:48:  [imooc_db]> select order_id,order_sn,customer_id from order_master;
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|     1009 |    70003 |       90003 |
|     1008 |    70002 |       90002 |
|     1007 |    70001 |       90001 |
|     1011 |    70005 |       90005 |
|     1010 |    70004 |       90004 |
+----------+----------+-------------+
5 rows in set (0.00 sec)
# 不指定自增字段插入
# 逻辑表必须增加自增属性，否则不指定自增字段插入则使用分片表本身自增id
#<table name="order_master" primaryKey="order_id" autoIncrement="true" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master"/>
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70005,90005,'yzw5',5,5,5,'address1',1,20);

app_imooc@172.16.10.142 11:58:  [imooc_db]> select order_id,order_sn,customer_id from order_master;                                                                                            
+----------+----------+-------------+
| order_id | order_sn | customer_id |
+----------+----------+-------------+
|     1001 |    70001 |       90001 |
|     1005 |    70005 |       90005 |
|     1003 |    70003 |       90003 |
|     1004 |    70004 |       90004 |
|     1002 |    70002 |       90002 |
+----------+----------+-------------+
5 rows in set (0.01 sec)

3.3数据库方式

优点：mycat重启后，sequence值不会被初始化，因为从数据库中取，每次取完按自增步长增加
缺点：sequence数据库主从切换后，存在mycat适用缓存序列号，可能存在ID重复风险，可以手动增加步长避免

配置方法

1.server.xml增加属性

<property name="sequnceHandlerType">1</property>

2.sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=dn_test
ORDER_MASTER=dn_test
datanode必须要mycat能访问的数据库，并不一定需要在分片表的数据库中

3.schema.xml增加主机节点和数据节点，让mycat能访问上面配置的数据节点
4.在对应节点的数据库中增加函数和表，用户必须授予表、函数、存储过程的访问权限

# 需要全局ID的表明需要写入表中，大写
INSERT INTO mycat_sequence values('ORDER_MASTER', 1, 100);
root@master 11:48:  [test]> select * from mycat_sequence;
+--------------+---------------+-----------+
| NAME         | current_value | increment |
+--------------+---------------+-----------+
| GLOBAL       |        100000 |       100 |
| ORDER_MASTER |             1 |       100 |
+--------------+---------------+-----------+
2 rows in set (0.00 sec)

5.schema.xml增加配置，适用全局自增IDautoIncrement="true"

<table name="order_master" primaryKey="order_id" autoIncrement="true" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master"/>

6.在逻辑库中插入数据

# 适用ID表里面的全局ID插入
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70005,90005,'yzw5',5,5,5,'address1',1,20);
# 使用ID表里面的对应表ID插入
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70005,90005,'yzw5',5,5,5,'address1',1,20);
# 不指定id列，效果同上
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70005,90005,'yzw5',5,5,5,'address1',1,20);

3.4本地时间戳方式

ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加)

优点：与mycat无关，跟时间有关系
缺点：字段最大长度必须大于等于18，int无法满足

配置方法

1.server.xml增加属性

<property name="sequnceHandlerType">2</property>

2.sequence_time_conf.properties

#sequence depend on TIME
WORKID=01
DATAACENTERID=01

# 0-31任意整数

3.在对应节点的数据库中增加函数和表，用户必须授予表、函数、存储过程的访问权限

# 需要全局ID的表明需要写入表中，大写
INSERT INTO mycat_sequence values('ORDER_MASTER', 1, 100);
root@master 11:48:  [test]> select * from mycat_sequence;
+--------------+---------------+-----------+
| NAME         | current_value | increment |
+--------------+---------------+-----------+
| GLOBAL       |        100000 |       100 |
| ORDER_MASTER |             1 |       100 |
+--------------+---------------+-----------+
2 rows in set (0.00 sec)

4.schema.xml增加配置，适用全局自增IDautoIncrement="true"

<table name="order_master" primaryKey="order_id" autoIncrement="true" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master"/>

5.在逻辑库中插入数据

# int无法满足
ERROR 1264 (22003): Out of range value for column 'order_id' at row 

app_imooc@172.16.10.142 12:32:  [imooc_db]> select next value for MYCATSEQ_ORDER_MASTER;
+---------------------+
| 1025962766269288448 |
+---------------------+
| 1025962766269288448 |
+---------------------+
1 row in set (0.00 sec)

app_imooc@172.16.10.142 12:32:  [imooc_db]> select next value for MYCATSEQ_GLOBAL;
+---------------------+
| 1025962827724230656 |
+---------------------+
| 1025962827724230656 |
+---------------------+
1 row in set (0.00 sec)
1
# 适用ID表里面的全局ID插入
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_GLOBAL,70005,90005,'yzw5',5,5,5,'address1',1,20);
# 使用ID表里面的对应表ID插入
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(next value for MYCATSEQ_ORDER_MASTER,70005,90005,'yzw5',5,5,5,'address1',1,20);
# 不指定id列，效果同上
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70005,90005,'yzw5',5,5,5,'address1',1,20);

select order_id,order_sn,customer_id from order_master;

3.5分布式zookeeper生成ID 未测试，1.5没有默认配置文件

基于ZK 与本地配置的分布式ID 生成器(可以通过ZK 获取集群（机房）唯一InstanceID，也可以通过配置文件配置InstanceID)ID 结构：long 64 位，ID 最大可占63 位
current time millis(微秒时间戳38 位,可以使用17 年)
instanceId（实例ID，可以通过ZK 或者配置文件获取，5 位，也就是十进制0-31）
threadId（线程ID，9 位）
increment(自增,6 位)
一共63 位，可以承受单机房单机器单线程1000*(2^6)=640000 的并发。

优点：无悲观锁，无强竞争，吞吐量更高
缺点：对zookeeper集群的要求增加。

配置方法

1.server.xml增加属性

<property name="sequnceHandlerType">3</property>

2.sequence_distributed_conf.properties

INSTANCEID=01
CLUSTERID=01

3.在对应节点的数据库中增加函数和表，用户必须授予表、函数、存储过程的访问权限

# 需要全局ID的表明需要写入表中，大写
INSERT INTO mycat_sequence values('ORDER_MASTER', 1, 100);
root@master 11:48:  [test]> select * from mycat_sequence;
+--------------+---------------+-----------+
| NAME         | current_value | increment |
+--------------+---------------+-----------+
| GLOBAL       |        100000 |       100 |
| ORDER_MASTER |             1 |       100 |
+--------------+---------------+-----------+
2 rows in set (0.00 sec)

5.schema.xml增加配置，适用全局自增IDautoIncrement="true"

<table name="order_master" primaryKey="order_id" autoIncrement="true" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master"/>

6.在逻辑库中插入数据

四.ER关系

跨分片查询存在跨节点问题

app_imooc@172.16.10.142 13:55:  [imooc_db]> select * from order_master m join order_detail o on m.order_id=o.order_id;
ERROR 1064 (HY000): invalid route in sql, multi tables found but datanode has no intersection  sql:select * from order_master m join order_detail o on m.order_id=o.order_id

根据ER关系，也需要对order_detail这张表进行分片

4.1配置步骤

1.在原先order_master所在4个分配数据库(orderdb01/02/03/04)中建立order_detail表结构

CREATE TABLE `order_detail` (
  `order_detail_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID,订单详情表ID',
  `order_id` int(10) unsigned NOT NULL COMMENT '订单表ID',
  `product_id` int(10) unsigned NOT NULL COMMENT '订单商品ID',
  `product_name` varchar(50) NOT NULL COMMENT '商品名称',
  `product_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '购买商品数量',
  `product_price` decimal(8,2) NOT NULL COMMENT '购买商品单价',
  `average_cost` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '平均成本价格',
  `weight` float DEFAULT NULL COMMENT '商品重量',
  `fee_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠分摊金额',
  `w_id` int(10) unsigned NOT NULL COMMENT '仓库ID',
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`order_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

2.schema.xml将全局分片表进行ER分片，在父表中增加子表信息

<table name="order_master" primaryKey="order_id" autoIncrement="true" dataNode="dn_orderdb01,dn_orderdb02,dn_orderdb03,dn_orderdb04" rule="order_master">
    <childTable name="order_detail" primaryKey="order_detail_id" joinKey="order_id" parentKey="order_id" autoIncrement="true" />
    <!-- 指定关联子表的表名、主键，及父表关联key、父表的主键，同时也需要指定全局ID -->
</table>

3.配置表order_detail全局自增ID(使用数据库方式)

# server.xml

# sequence_db_conf.properties增加order_detail自增ID
#sequence stored in datanode
GLOBAL=dn_test
ORDER_MASTER=dn_test
ORDER_DETAIL=dn_test
# 数据库中增加分片表信息
insert into mycat_sequence values('ORDER_DETAIL',1,1);

4.插入数据

# 先插入order_master
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70001,90001,'yzw1',1,1,1,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70002,90002,'yzw2',2,2,2,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70003,90003,'yzw3',3,3,3,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70004,90004,'yzw4',4,4,4,'address1',1,20);
insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money) values(70005,90005,'yzw5',5,5,5,'address1',1,20);
app_imooc@172.16.10.142 14:46:  [imooc_db]> select order_sn,customer_id,order_id from order_master;
+----------+-------------+----------+
| order_sn | customer_id | order_id |
+----------+-------------+----------+
|    70005 |       90005 |        5 |
|    70001 |       90001 |        6 |
|    70005 |       90005 |       10 |
|    70004 |       90004 |        4 |
|    70004 |       90004 |        9 |
|    70003 |       90003 |        3 |
|    70003 |       90003 |        8 |
|    70002 |       90002 |        2 |
|    70002 |       90002 |        7 |
+----------+-------------+----------+
9 rows in set (0.00 sec)

# 根据order_id插入order_detail
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,2,12,'商品2',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,3,13,'商品3',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,4,14,'商品4',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,5,15,'商品5',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,6,16,'商品6',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,7,17,'商品7',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,8,18,'商品8',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,9,19,'商品9',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,10,20,'商品10',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,5,21,'商品11',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,6,12,'商品2',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,8,12,'商品2',1,22.2,12,1,10,2);
insert into order_detail(order_detail_id,order_id,product_id,product_name,product_cnt,product_price,average_cost,weight,fee_money,w_id) values(next value for MYCATSEQ_ORDER_DETAIL,8,12,'商品2',1,22.2,12,1,10,2);

5.此时再进行分片关联查询验证数据

select m.order_id,o.order_detail_id,o.product_id,o.product_name from order_master m join order_detail o on m.order_id=o.order_id;
+----------+-----------------+------------+--------------+
| order_id | order_detail_id | product_id | product_name |
+----------+-----------------+------------+--------------+
|        3 |               4 |         13 | 商品3        |
|        8 |               9 |         18 | 商品8        |
|        8 |              14 |         12 | 商品2        |
|        8 |              15 |         12 | 商品2        |
|        2 |               3 |         12 | 商品2        |
|        7 |               8 |         17 | 商品7        |
|        4 |               5 |         14 | 商品4        |
|        9 |              10 |         19 | 商品9        |
|        5 |               6 |         15 | 商品5        |
|        6 |               7 |         16 | 商品6        |
|       10 |              11 |         20 | 商品10       |
|        5 |              12 |         21 | 商品11       |
|        6 |              13 |         12 | 商品2        |
+----------+-----------------+------------+--------------+
13 rows in set (0.00 sec)

6.在其中一个分片上可以验证，order_id一致的表落在同一个分片

root@localhost 15:05:  [orderdb02]> select order_sn,customer_id,order_id,mod(customer_id,4) from order_master;
+----------+-------------+----------+
| order_sn | customer_id | order_id |
+----------+-------------+----------+
|    70005 |       90005 |        5 |
|    70001 |       90001 |        6 |
|    70005 |       90005 |       10 |
+----------+-------------+----------+
3 rows in set (0.00 sec)

root@localhost 15:06:  [orderdb02]> select order_detail_id,product_id,order_id from order_detail;
+-----------------+------------+----------+
| order_detail_id | product_id | order_id |
+-----------------+------------+----------+
|               6 |         15 |        5 |
|               7 |         16 |        6 |
|              11 |         20 |       10 |
|              12 |         21 |        5 |
|              13 |         12 |        6 |
+-----------------+------------+----------+
5 rows in set (0.00 sec)

五、MyCat的其他常用功能

1、MyCat的SQL拦截器

SQL拦截是一个比较有用的高级技巧，用户可以写一个java类，将传入MyCAT的SQL进行改写然后交给Mycat去执行，此技巧可以完成如下一些特殊功能：

捕获和记录某些特殊的SQL
记录sql查找异常
出于性能优化的考虑，改写SQL，比如改变查询条件的顺序或增加分页限制
将某些Select SQL强制设置为Read 模式，走读写分离（很多事务框架很难剥离事务中的Select SQL
后期Mycat智能优化，拦截所有sql 做智能分析，自动监控节点负载，自动优化路由，提供数据库优化建议

SQL拦截的原理是在路由之前拦截SQL，然后做其他处理，完了之后再做路由，执行,如下图所示：

默认的拦截器实现了Mysql转义字符的过滤转换，非默认拦截器只有一个拦截记录sql的拦截器。
默认SQL拦截器：

配置：
<system> 
    <property name="sqlInterceptor">org.opencloudb.interceptor.impl.DefaultSqlInterceptor</property>
</system>
源码：
 /**
  * escape mysql escape letter 
  */ 
@Override 
public String interceptSQL(String sql, int sqlType) { 
    if (sqlType == ServerParse.UPDATE || sqlType == ServerParse.INSERT||
        sqlType == ServerParse.SELECT||sqlType == ServerParse.DELETE) { 
        return sql.replace("\\'", "''"); } 
    else { 
        return sql; 
        }
    }
}

配置server.xml

# 1.5可用
<system>
    <property name="sqlInterceptor">org.opencloudb.interceptor.impl.StatisticsSqlInterceptor</property>
    <property name="sqlInterceptorType">UPDATE,DELETE,INSERT,SELECT</property>
    <property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>

# 1.6可用
<system>
    <property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
    <!-- 1.6 io.mycat.server.interceptor.impl.StatisticsSqlInterceptor -->
    <property name="sqlInterceptorType">UPDATE,DELETE,INSERT,SELECT</property>
    <property name="sqlInterceptorFile">/tmp/sql.txt</property>
</system>

sqlInterceptorType：拦截sql类型
sqlInterceptorFile： sql保存文件路径
注意：捕获记录sql拦截器的配置只有1.4及其以后可用，1.3无本拦截。
如果需要实现自己的sql拦截，只需要将配置类改为自己配置即可：

1、定义自定义类 implements SQLInterceptor ，然后改写sql后返回。
2、将自己实现的类放入catlet 目录，可以为class或jar。
3、配置配置文件：

<system> 
    <property name="sqlInterceptor">org.opencloudb.interceptor.impl.自定义class</property> 
    <!--其他配置--> 
</system>

测试

# mycat restart
app_imooc@172.16.10.142 15:39:  [imooc_db]>  select order_sn,order_id,customer_id from order_master limit 1;
+----------+----------+-------------+
| order_sn | order_id | customer_id |
+----------+----------+-------------+
|    70005 |        5 |       90005 |
+----------+----------+-------------+
1 row in set (0.01 sec)

app_imooc@172.16.10.142 15:40:  [imooc_db]> delete from order_master where order_id=5;
Query OK, 1 row affected (0.03 sec)

文件名会带日期

sql2020-01-19.txt

2、mycat sql防火墙

统一控制哪些用户可以通过哪些主机访问后端数据库
统一屏蔽一些SQL语句，加强安全控制(如没有条件的delete语句等)

server.xml文件

firewall标签用来定义防火墙
firewall下whitehost标签用来定义 IP白名单
blacklist用来定义 SQL黑名单

<firewall>
    <whitehost>
        <host user="mycat" host="127.0.0.1"></host> #ip 白名单 用户对应的可以访问的 ip 地址
    </whitehost>
    <blacklist check="true">
        <property name="selelctAllow">false</property> #黑名单允许的 权限 后面为默认
    </blacklist>
</firewall>

黑名单拦截明细配置

配置项	缺省值	描述
selelctAllow	true	是否允许执行 SELECT 语句
selectAllColumnAllow	true	是否允许执行 SELECT * FROM T 这样的语句。如果设置为 false，不允许执行 select * from t，但可以select * from (select id, name from t) a。这个选项是防御程序通过调用 select * 获得数据表的结构信息。
selectIntoAllow	true	SELECT 查询中是否允许 INTO 字句
deleteAllow	true	是否允许执行 DELETE 语句
updateAllow	true	是否允许执行 UPDATE 语句
insertAllow	true	是否允许执行 INSERT 语句
replaceAllow	true	是否允许执行 REPLACE 语句
mergeAllow	true	是否允许执行 MERGE 语句，这个只在 Oracle 中有用
callAllow	true	是否允许通过 jdbc 的 call 语法调用存储过程
setAllow	true	是否允许使用 SET 语法
truncateAllow	true	truncate 语句是危险，缺省打开，若需要自行关闭
createTableAllow	true	是否允许创建表
alterTableAllow	true	是否允许执行 Alter Table 语句
dropTableAllow	true	是否允许修改表
commentAllow	false	是否允许语句中存在注释，Oracle 的用户不用担心，Wall 能够识别 hints和注释的区别
noneBaseStatementAllow	false	是否允许非以上基本语句的其他语句，缺省关闭，通过这个选项就能够屏蔽 DDL
multiStatementAllow	false	是否允许一次执行多条语句，缺省关闭
useAllow	true	是否允许执行 mysql 的 use 语句，缺省打开
describeAllow	true	是否允许执行 mysql 的 describe 语句，缺省打开
showAllow	true	是否允许执行 mysql 的 show 语句，缺省打开
commitAllow	true	是否允许执行 commit 操作
rollbackAllow	true	是否允许执行 roll back 操作
##如果把 selectIntoAllow、deleteAllow、updateAllow、insertAllow、mergeAllow 都设置为 false，这就是一个只读数据源了。##
拦截配置－永真条件
selectWhereAlwayTrueCheck	true	检查 SELECT 语句的 WHERE 子句是否是一个永真条件
selectHavingAlwayTrueCheck	true	检查 SELECT 语句的 HAVING 子句是否是一个永真条件
deleteWhereAlwayTrueCheck	true	检查 DELETE 语句的 WHERE 子句是否是一个永真条件
deleteWhereNoneCheck	false	检查 DELETE 语句是否无 where 条件，这是有风险的，但不是 SQL 注入类型的风险
updateWhereAlayTrueCheck	true	检查 UPDATE 语句的 WHERE 子句是否是一个永真条件
updateWhereNoneCheck	false	检查 UPDATE 语句是否无 where 条件，这是有风险的，但不是SQL 注入类型的风险
conditionAndAlwayTrueAllow	false	检查查询条件(WHERE/HAVING 子句)中是否包含 AND 永真条件
conditionAndAlwayFalseAllow	false	检查查询条件(WHERE/HAVING 子句)中是否包含 AND 永假条件
conditionLikeTrueAllow	true	检查查询条件(WHERE/HAVING 子句)中是否包含 LIKE 永真条件
其他拦截配置
selectIntoOutfileAllow	false	SELECT ... INTO OUTFILE 是否允许，这个是 mysql 注入攻击的常见手段，缺省是禁止的
selectUnionCheck	true	检测 SELECT UNION
selectMinusCheck	true	检测 SELECT MINUS
selectExceptCheck	true	检测 SELECT EXCEPT
selectIntersectCheck	true	检测 SELECT INTERSECT
mustParameterized	false	是否必须参数化，如果为 True，则不允许类似 WHERE ID = 1 这种不参数化的 SQL
strictSyntaxCheck	true	是否进行严格的语法检测，Druid SQL Parser 在某些场景不能覆盖所有的SQL 语法，出现解析 SQL 出错，可以临时把这个选项设置为 false，同时把 SQL 反馈给 Druid 的开发者。
conditionOpXorAllow	false	查询条件中是否允许有 XOR 条件。XOR 不常用，很难判断永真或者永假，缺省不允许。
conditionOpBitwseAllow	true	查询条件中是否允许有"&"、"~"、"	"、"^"运算符。
conditionDoubleConstAllow	false	查询条件中是否允许连续两个常量运算表达式
minusAllow	true	是否允许 SELECT * FROM A MINUS SELECT * FROM B 这样的语句
intersectAllow	true	是否允许 SELECT * FROM A INTERSECT SELECT * FROM B 这样的语句
constArithmeticAllow	true	拦截常量运算的条件，比如说 WHERE FID = 3 - 1，其中"3 - 1"是常量运算表达式。
limitZeroAllow	false	是否允许 limit 0 这样的语句
禁用对象检测配置
tableCheck	true	检测是否使用了禁用的表
schemaCheck	true	检测是否使用了禁用的 Schema
functionCheck	true	检测是否使用了禁用的函数
objectCheck	true	检测是否使用了“禁用对对象”
variantCheck	true	检测是否使用了“禁用的变量”
readOnlyTables	空	指定的表只读，不能够在 SELECT INTO、DELETE、UPDATE、INSERT、MERGE 中作为"被修改表"出现

参考：
https://www.cnblogs.com/jenvid/p/10180461.html

https://blog.51cto.com/5660061/2391986?source=dra

https://www.cnblogs.com/jenvid/p/10180479.html

https://my.oschina.net/u/3420885/blog/1942388

最后编辑于：2020.01.19 18:48:35

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 211,376评论 6赞 491
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,126评论 2赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 156,966评论 0赞 347
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,432评论 1赞 283
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,519评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,792评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,933评论 3赞 406
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,701评论 0赞 266
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,143评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,488评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,626评论 1赞 340
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,292评论 4赞 329
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,896评论 3赞 313
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,742评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,977评论 1赞 265
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,324评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,494评论 2赞 348

Mycat水平分库

一、什么是水平分库

1.1分片原则

1.2分片后如何处理查询

二、水平切分步骤

2.1根据业务状态确定要进行水平切分的表

2.2分析业务模型选择分片键及分片算法

2.3适用mycat部署分片集群

2.4测试分片集群，采用应用端双写方式进行

2.5业务及数据迁移

三、全局自增ID

3.1全局自增ID方法

3.2本地文件全局ID

配置方法

3.3数据库方式

配置方法

3.4本地时间戳方式

配置方法

3.5分布式zookeeper生成ID 未测试，1.5没有默认配置文件

配置方法

四.ER关系

4.1配置步骤

五、MyCat的其他常用功能

1、MyCat的SQL拦截器

2、mycat sql防火墙

推荐阅读更多精彩内容