Mycat
参考文章：
https://zhuanlan.zhihu.com/p/72476293
基本操作
bin/mycat start
bin/mycat stop
bin/mycat restart
bin/mycat status
记得开启8066(数据端口，建议使用)，9066（管理端口）的端口防火墙
登陆mycat
mysql -h10.106.11.151 -uroot -p123456 -P8066
- 读写分离配置(通过mysql的日志进行了验证，注意balance参数使用读写策略)
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="10.106.11.153:3306" user="root" password="123456">
<readHost host="hostS1" url="10.106.11.152:3306" user="root" password="123456"></readHost>
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
- 同一个数据库分表配置(注意要创建 hello1,hello2,hello3数据库表)
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!-- <table name="hello" primaryKey="id" dataNode="dn1,dn2" rule="crc32slot" splitTableNames ="true"/> -->
<table name="hello" primaryKey="id" dataNode="dn1" subTables="hello$1-3" rule="mod-long"/>
</schema>
- 不同库分表配置(要先创建BIG,SMALL数据库表)
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<table name="test" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" splitTableNames ="true"/>
<!-- <table name="hello" primaryKey="id" dataNode="dn1" subTables="hello$1-3" rule="mod-long"/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="BIG" />
<dataNode name="dn2" dataHost="localhost1" database="SMALL" />
Mysql自带分区表的创建
-- 创建分区表
CREATE TABLE `sales` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR (40) NOT NULL,
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`,`created_time`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 PARTITION BY RANGE (YEAR(created_time))(
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN MAXVALUE);
-- 查询指定某个分区的数据
SELECT * FROM sales PARTITION (p0);
-- 观察优化器是否执行了分区过滤
EXPLAIN PARTITIONS SELECT * FROM sales;
-- 观察优化器是否执行了分区过滤
EXPLAIN PARTITIONS SELECT * FROM sales WHERE created_time>'2020-04-24 07:30:17';
