本文介绍了 Mycat 数据库中间件的功能特性，包括读写分离、数据分片、垂直拆分、水平拆分及多数据源整合等。通过配置示例详细展示了如何进行分片规则设置、负载均衡策略选择以及复杂查询处理等高级应用。
Mycat介绍：
是什么
数据库中间件
前身是阿里的cobar
干什么的
读写分离
数据分片
垂直拆分
水平拆分
垂直+水平拆分
多数据源整合
原理
Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL语句，首先对 SQL 语句做了
一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL 发往后端的真实数据库，
并将返回的结果做适当的处理，最终再返回给用户
这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用mycat还是mysql。
安装启动
解压缩文件拷贝到linux下 /usr/local/
三个文件
定义逻辑库，表、分片节点等内容
rule.xml
定义分片规则
server.xml
定义用户以及系统相关变量，如端口等.
启动前先修改schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--逻辑库 name名称, checkSQLschema sqlMaxLimit 末尾是否要加 limit xxx-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
<!--逻辑库 name名称, dataHost 引用的哪个dataHost database:对应mysql的database-->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
<schema>
<dataNode>
<dataHost>
再修改server.xml
<user name="root">
<propertyname="password">654321</property>
<propertyname="schemas">TESTDB</property>
</user>
启动程序
控制台启动 ：去mycat/bin目录下 mycat console
后台启动 ：去mycat/bin目录下 mycat start
启动时可能出现报错
域名解析失败
用vim 修改/etc/hosts 文件
在 127.0.0.1 后面增加你的机器名
修改后重新启动网络服务
登录
后台管理窗口
mysql -uroot -p654321 -P9066 -h192.168.67.131命令
show database
show @@help
数据窗口
mysql -uroot -p654321 -P8066-h192.168.67.131
区分读写
向数据表插入系统变量
update mytbl0815 set name=@@hostname
读写分离
schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<!--读库（从库）的配置 -->
<readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>
</writeHost>
</dataHost>
</mycat:schema>
balance
负载均衡类型，目前的取值有4 种：
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
4. balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力
读写分离
创建表
create table t_replica
( id int auto_increment ,
name varchar(200)
);
分别在两个库下插入：insert into t_replica(name) values (@@hostname)
然后再mycat下执行select * from t_replica能够
分库
如何选择分库表
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
select o.*,od.detail,d.order_type
from orders o
inner join orders_detail od on o.id =od.order_id
inner join dict_order_type d ono.order_type=d.id
where o.customer_id=xxxx
schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/"><schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
<dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.67.1:3306" user="root"
password="123123">
</writeHost>
</dataHost>
</mycat:schema>561686dd2a60System.NullReferenceExceptionlume
水平分表
schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
<dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>-->
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.67.1:3306" user="root"
password="123123">
</writeHost>
</dataHost>
rule.xml
<tableRule name="mod_rule"><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">2</property>
</function>
跨库join
全局表
设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。
所以全局表一般不能是大数据表或者更新频繁的表
一般是字典表或者系统表为宜。schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
<dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>-->
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.67.1:3306" user="root"
password="123123">
</writeHost>
</dataHost>
ER表
为了相关联的表的行尽量分在一个库下
schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<table name="dict_status" dataNode="dn1,dn2" type="global" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
<dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>-->
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.67.1:3306" user="root"
password="123123">
</writeHost>
</dataHost>
建相关表
federated引擎
oracle DBLINK
开启引擎
修改my.cnf
在[mysqld]下增加一行
federated
然后重启mysql
建表
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
)ENGINE=FEDERATED
CONNECTION="mysql://root:123123@192.168.67.141:3306/myorder/customer"
使用时的注意事项
1. 本地的表结构必须与远程的完全一样。
2.远程数据库目前仅限MySQL
3.不支持事务
4.不支持表结构修改 ddl
最大的问题
性能
全局序列
三种方式
本地文件
不推荐
数据库方式
数据库序列方式原理
利用数据库一个表来进行计数累加。
但是并不是每次生成序列都读写数据库，这样效率太低
mycat会预加载一部分号段到mycat的内存中，这样大部分读写序列都是在内存中完成的。
如果内存中的号段用完了 mycat会再向数据库要一次。问：那如果mycat崩溃了，那内存中的序列岂不是都没了？
是的。如果是这样，那么mycat启动后会向数据库申请新的号段，原有号段会弃用。
也就是说如果mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。
建库序列脚本
win10CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER;
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;
SELECT * FROM MYCAT_SEQUENCE
TRUNCATE TABLE MYCAT_SEQUENCE
##增加要用的序列
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);
修改mycat配置
sequence_db_conf.properties
vim sequence_db_conf.properties
意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
server.xml
然后重启
插入语句
insert into`orders`(id,amount,customer_id,order_type) values(nextvalue for MYCATSEQ_ORDERS,1000,101,102);
时间戳方式
比较长
自主生成
根据业务逻辑组合
可以利用 redis的单线程原子性incr来生成序列
扫一扫
1739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
