mycat 分表分库

本文介绍使用mycat数据库中间件，将一个大表，按月份拆分为12个小表，提高数据库性能。

1、扩展12个表：

我用存储过程，按原表的表结构，批量扩展12个表，create_db 脚本如下：

-- dbPre 数据库 前缀
-- num   表个数
CREATE DEFINER=`root`@`%` PROCEDURE `create_db`(IN dbPre VARCHAR(50),IN num INT)
BEGIN
	-- 声明变量
	DECLARE i INT DEFAULT 0;
	DECLARE dbName VARCHAR(50);
	SET i=1;
	WHILE i<=12 DO
		SET dbName = CONCAT(dbPre,i);
		-- 创建数据库
		SET @createDbsql = CONCAT('create database ',dbName);
		PREPARE temp FROM @createDbsql;
		EXECUTE temp;
		-- 创建表
		SET @createDbsql = CONCAT('CREATE TABLE ', dbName ,'.contactdetail LIKE contactdetail_all');
		-- 动态执行sql语句
		PREPARE temp FROM @createDbsql;
		EXECUTE temp;
		SET i=i+1;
	END WHILE; 
    END$$

DELIMITER ;

执行存储过程sql语句，call create_db( ‘sharding_by_month’,12 );

2、配置schema.xml

配置顺序是dataHost -> dateNode --> schema

<!-- 配置规则 -->
<schema name="conform" checkSQLschema="true" sqlMaxLimit="100">
         <table name="contactdetail" dataNode="dn_sharding_by_month_$1-12"     
     rule="sharding-by-month-contactdetail"/>
</schema>

 <!-- 配置节点 -->
<dataNode name="dn_sharding_by_month_1" dataHost="dhOrder"         
    database="sharding_by_month_1" />
<dataNode name="dn_sharding_by_month_2" dataHost="dhOrder" 
    database="sharding_by_month_2" />
<dataNode name="dn_sharding_by_month_3" dataHost="dhOrder" 
    database="sharding_by_month_3" />
<dataNode name="dn_sharding_by_month_4" dataHost="dhOrder" 
    database="sharding_by_month_4" />
<dataNode name="dn_sharding_by_month_5" dataHost="dhOrder" 
    database="sharding_by_month_5" />
<dataNode name="dn_sharding_by_month_6" dataHost="dhOrder" 
    database="sharding_by_month_6" />
<dataNode name="dn_sharding_by_month_7" dataHost="dhOrder" 
    database="sharding_by_month_7" />
<dataNode name="dn_sharding_by_month_8" dataHost="dhOrder" 
    database="sharding_by_month_8" />
<dataNode name="dn_sharding_by_month_9" dataHost="dhOrder" 
    database="sharding_by_month_9" />
<dataNode name="dn_sharding_by_month_10" dataHost="dhOrder" 
    database="sharding_by_month_10" />
<dataNode name="dn_sharding_by_month_11" dataHost="dhOrder" 
    database="sharding_by_month_11" />
<dataNode name="dn_sharding_by_month_12" dataHost="dhOrder" 
    database="sharding_by_month_12" />

<!-- 配置数据库连接 -->
<dataHost name="dhOrder" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <writeHost host="hostM1" url="192.168.0.1:3306" user="root" password="123456"></writeHost>
</dataHost>

在这里遇到一个小坑，

checkSQLschema="false"后，插入数据提示 没找到数据库，查了mycat操作指引，

当配置为false时，实际分发的sql语句是select * from conform.contactdetail；

当配置为true时，实际分发的sql语句是select * from contactdetail；

所以当配置为false时，mysql数据库和mycat定义的数据库，名称必须一致，不然提示如下错误。

 3、数据测试

插入一条记录后，就可以在数据库sharding_by_month_1 查看到分发的记录

INSERT INTO `conform`.`CONTACTDETAIL` (
  `lsh`,
  `qdate`
) 
VALUES
  (
    '001025107006005',
    '2022-01-17 19:37:41.000000'
  ) ;

SELECT *
FROM
  `sharding_by_month_1`.`contactdetail`