在《Mycat之——实现MySQL垂直分库》一文中,我们将整个商城数据库分成用户库、商品库和订单库。由于订单数据库中的订单表数据会随着业务的不断增长而增长,最终单表难以承载巨大的数据量,此时,我们就需要在垂直分库的基础上对订单表中的数据进行水平切分。
而Mycat对数据表的水平切分提供了多种切分规则和算法,后续,我们会深入介绍这些切分规则和算法。这里,我们直接根据order_master表中的customer_id字段进行切分,切分规则就是简单的使用这个字段的值对数值4进行取模,将数据分散到4个不同的数据库中。
服务器规划
主机名 | IP地址 | 安装的服务 | 业务数据库 |
---|---|---|---|
binghe151 | 192.168.175.151 | Mycat | shop(逻辑库) |
binghe152 | 192.168.175.152 | MySQL | order_db(实体库) |
binghe153 | 192.168.175.153 | MySQL | product_db(实体商品库) |
binghe154 | 192.168.175.154 | MySQL | customer_db(实体用户库) |
binghe155 | 192.168.175.155 | MySQL | orderdb01(实体库,切分order_master表) |
binghe156 | 192.168.175.156 | MySQL | orderdb02(实体库,切分order_master表) |
binghe157 | 192.168.175.157 | MySQL | orderdb03(实体库,切分order_master表) |
binghe158 | 192.168.175.158 | MySQL | orderdb04(实体库,切分order_master表) |
创建数据库
分别在binghe155binghe158服务器上创建orderdb01orderdb04数据库,并在数据库中创建order_master数据表,创建order_master数据表的SQL语句如下所示。
CREATE TABLE `order_master` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
`customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
`shipping_user` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收货人姓名',
`province` smallint(6) NOT NULL COMMENT '收货人所在省',
`city` smallint(6) NOT NULL COMMENT '收货人所在市',
`district` smallint(6) NOT NULL COMMENT '收货人所在区',
`address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收货人详细地址',
`payment_method` tinyint(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
`order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
`district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
`shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
`payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
`shipping_comp_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '快递公司名称',
`shipping_sn` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '快递单号',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
`order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
`order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
`invoice_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '发票抬头',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`order_id`),
UNIQUE KEY `ux_ordersn` (`order_sn`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表';
配置schema.xml文件
修改后的schema.xml文件的内容如下所示。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="shop" checkSQLschema="false" sqlMaxLimit="1000">
<!--<table name="order_master" primaryKey="order_id" dataNode = "ordb"/>-->
<table name="order_master" primaryKey="order_id" dataNode = "orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
<table name="order_detail" primaryKey="order_detail_id" dataNode = "ordb"/>
<table name="order_cart" primaryKey="cart_id" dataNode = "ordb"/>
<table name="order_customer_addr" primaryKey="customer_addr_id" dataNode = "ordb"/>
<table name="region_info" primaryKey="region_id" dataNode = "ordb,prodb,custdb" type="global"/>
<table name="serial" primaryKey="id" dataNode = "ordb"/>
<table name="shipping_info" primaryKey="ship_id" dataNode = "ordb"/>
<table name="warehouse_info" primaryKey="w_id" dataNode = "ordb"/>
<table name="warehouse_proudct" primaryKey="wp_id" dataNode = "ordb"/>
<table name="product_brand_info" primaryKey="brand_id" dataNode = "prodb"/>
<table name="product_category" primaryKey="category_id" dataNode = "prodb"/>
<table name="product_comment" primaryKey="comment_id" dataNode = "prodb"/>
<table name="product_info" primaryKey="product_id" dataNode = "prodb"/>
<table name="product_pic_info" primaryKey="product_pic_id" dataNode = "prodb"/>
<table name="product_supplier_info" primaryKey="supplier_id" dataNode = "prodb"/>
<table name="customer_balance_log" primaryKey="balance_id" dataNode = "custdb"/>
<table name="customer_inf" primaryKey="customer_inf_id" dataNode = "custdb"/>
<table name="customer_level_inf" primaryKey="customer_level" dataNode = "custdb"/>
<table name="customer_login" primaryKey="customer_id" dataNode = "custdb"/>
<table name="customer_login_log" primaryKey="login_id" dataNode = "custdb"/>
<table name="customer_point_log" primaryKey="point_id" dataNode = "custdb"/>
</schema>
<dataNode name="ordb" dataHost="binghe152" database="order_db" />
<dataNode name="prodb" dataHost="binghe153" database="product_db" />
<dataNode name="custdb" dataHost="binghe154" database="customer_db" />
<dataNode name="orderdb01" dataHost="binghe155" database="orderdb01" />
<dataNode name="orderdb02" dataHost="binghe156" database="orderdb02" />
<dataNode name="orderdb03" dataHost="binghe157" database="orderdb03" />
<dataNode name="orderdb04" dataHost="binghe158" database="orderdb04" />
<dataHost name="binghe152" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe52" url="192.168.175.152:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe153" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe53" url="192.168.175.153:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe154" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe54" url="192.168.175.154:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe155" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe55" url="192.168.175.155:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe156" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe56" url="192.168.175.156:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe157" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe57" url="192.168.175.157:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe158" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe58" url="192.168.175.158:3306" user="mycat" password="mycat"/>
</dataHost>
</mycat:schema>
相比于垂直切分数据库来说,在schema.xml文件中增加了4个数据库服务器的配置,如下所示。
<dataHost name="binghe155" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe55" url="192.168.175.155:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe156" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe56" url="192.168.175.156:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe157" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe57" url="192.168.175.157:3306" user="mycat" password="mycat"/>
</dataHost>
<dataHost name="binghe158" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="binghe58" url="192.168.175.158:3306" user="mycat" password="mycat"/>
</dataHost>
接下来,在schema.xml中添加4个数据节点,如下所示。
<dataNode name="orderdb01" dataHost="binghe155" database="orderdb01" />
<dataNode name="orderdb02" dataHost="binghe156" database="orderdb02" />
<dataNode name="orderdb03" dataHost="binghe157" database="orderdb03" />
<dataNode name="orderdb04" dataHost="binghe158" database="orderdb04" />
最后,我们还需要对shop逻辑库中的order_master数据表的配置进行修改,将其修改成如下所示。
<table name="order_master" primaryKey="order_id" dataNode = "orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
此时,我们看到这里将order_master数据表的数据节点配置成了orderdb01,orderdb02,orderdb03,orderdb04。并且为order_master数据表添加了切分规则,这里将切分规则定义为order_master。
这里,我们对order_master进行了水平切分,需要对Mycat的rule.xml文件进行配置。
配置rule.xml文件
修改后的rule.xml文件的内容如下所示。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="order_master">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">4</property>
</function>
</mycat:rule>
通过上面的配置,我们可以发现,这里,对order_master表的切分规则使用了Mycat中的io.mycat.route.function.PartitionByMod类,将数据表切分为4个分片。
这里需要注意的是:在rule.xml文件中配置的切分规则名称,必须与schema.xml文件中配置的逻辑表的切分规则一致。例如,在schema.xml文件中的配置如下所示
<table name="order_master" primaryKey="order_id" dataNode = "orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master"/>
在rule.xml文件中配置如下所示。
<tableRule name="order_master">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
配置server.xml文件
修改后的server.xml文件的内容如下所示。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useHandshakeV10">1</property>
<property name="defaultSqlParser">druidparser</property>
<property name="serverPort">3307</property>
<property name="managerPort">3308</property>
<property name="nonePasswordLogin">0</property>
<property name="bindIp">0.0.0.0</property>
<property name="charset">utf8mb4</property>
<property name="frontWriteQueueSize">2048</property>
<property name="txIsolation">2</property>
<property name="processors">2</property>
<property name="idleTimeout">1800000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequenceHandlerType">2</property>
<property name="defaultMaxLimit">1000</property>
<property name="maxPacketSize">104857600</property>
</system>
<user name="mycat" defaultAccount="true">
<property name="usingDecrypt">1</property>
<property name="password">cTwf23RrpBCEmalp/nx0BAKenNhvNs2NSr9nYiMzHADeEDEfwVWlI6hBDccJjNBJqJxnunHFp5ae63PPnMfGYA==</property>
<property name="schemas">shop</property>
</user>
</mycat:server>
关于对server.xml文件的说明,可以参考《Mycat之——实现MySQL垂直分库》一文。
此时,我们就完成了对订单数据库中的order_master数据表按照简单取模规则的水平切分。