Mycat

參考文章：
https://zhuanlan.zhihu.com/p/72476293
基本操作

bin/mycat start
bin/mycat stop
bin/mycat restart
bin/mycat status

記得開啟8066(數(shù)據(jù)端口，建議使用)，9066（管理端口）的端口防火墻
登陸mycat

mysql -h10.106.11.151 -uroot -p123456 -P8066

1. 讀寫分離配置(通過mysql的日志進(jìn)行了驗(yàn)證，注意balance參數(shù)使用讀寫策略)

<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>

2. 同一個數(shù)據(jù)庫分表配置(注意要創(chuàng)建 hello1,hello2,hello3數(shù)據(jù)庫表)

<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>

3. 不同庫分表配置(要先創(chuàng)建BIG,SMALL數(shù)據(jù)庫表)

<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自帶分區(qū)表的創(chuàng)建

-- 創(chuàng)建分區(qū)表
CREATE TABLE `sales` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (40) NOT NULL,
    `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
    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);

-- 查詢指定某個分區(qū)的數(shù)據(jù)
SELECT * FROM sales PARTITION (p0);

-- 觀察優(yōu)化器是否執(zhí)行了分區(qū)過濾
EXPLAIN PARTITIONS SELECT * FROM sales;

-- 觀察優(yōu)化器是否執(zhí)行了分區(qū)過濾
EXPLAIN PARTITIONS SELECT * FROM sales WHERE created_time>'2020-04-24 07:30:17';