MyCat跨库查询方案

Mycat跨库查询的解决方案

1.使用全局表

1.1 使用全局表的业务特点：

• 数据变动不频繁
• 数据总体变化不大
• 数据规模不大，很少有超过数十万条记录

1.2 全局表特性：

• 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片节点数据的一致性
• 全局表的查询操作，只从一个节点获取
• 全局表可以跟任何一个表进行JOIN操作

注意：
对数据的更新应该通过连接mycat来进行，不然会导致各节点之间的数据不一致。

1.3 schema.xml配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="Test" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
 <table name="tenant" primaryKey="tenant_id" autoIncrement="true" dataNode="dn1,dn2" type="global"  />

</schema>

<dataNode name="dn1" dataHost="host1" database="interface"/>
<dataNode name="dn2" dataHost="host2" database="platform" />
<dataNode name="hzero_mdm" dataHost="host2" database="hzero_mdm" />
<dataHost balance="3" maxCon="1000" minCon="10" name="host1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <writeHost host="192.168.56.127" url="192.168.56.127:3306" password="123456" user="root"/>
</dataHost>

<dataHost balance="3" maxCon="1000" minCon="10" name="host2" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <writeHost host="192.168.56.127" url="192.168.56.127:3306" password="123456" user="root"/>
</dataHost>
</mycat:schema>

1.4 在dn1 和 dn2都执行 tenant表的DDL语句

CREATE TABLE `tenant` (
  `tenant_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表ID，主键，供其他表做外键',
  `tenant_num` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '租户编码'
) ENGINE=InnoDB AUTO_INCREMENT=180146 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='租户信息';

1.5 跨库查询sql测试

 SELECT
    sms.monitor_system_id,
    sms.tenant_id,
    hpht.tenant_name,
    sms.external_system_code,
    sms.enabled_flag,
    sms.object_version_number
FROM
    monitor_system sms
LEFT JOIN
    tenant hpht
ON
    sms.tenant_id = hpht.tenant_id 

2.使用ER Join

2.1 使用 ER Join的业务场景

MyCAT借鉴了NewSQL领域的新秀Foundation DB的设计思路，Foundation DB创新性的提出了Table Group的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了JION的效率和性能问题，根据这一思路，提出了基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

2.2 ER JOin 的特点

两个表之间有父子表关系，主表数据和字表数据都在都一个节点上

2.3 schema.xml配置

<table name="customer" dataNode="dn1,dn2" rule="sharding-by-intfile">
    <childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table

2.4 跨库查询sql测试

select c.*,o.* from customer join orders on c.id = o.customer_id

3.使用Share Join

3.1使用 Shar Join的业务场景

需要Join的表数据量很大

使用全局表不能保证各个节点数据一致

3.2 schema.xml配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="Test" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<table name="tenant" primaryKey="tenant_id" autoIncrement="true" dataNode="dn2"  />
</schema>

<dataNode name="dn1" dataHost="host1" database="interface"/>
<dataNode name="dn2" dataHost="host2" database="platform" />
<dataNode name="hzero_mdm" dataHost="host2" database="hzero_mdm" />
<dataHost balance="3" maxCon="1000" minCon="10" name="host1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <writeHost host="192.168.56.127" url="192.168.56.127:3306" password="123456" user="root"/>
</dataHost>

<dataHost balance="3" maxCon="1000" minCon="10" name="host2" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
     <heartbeat>select user()</heartbeat>
     <writeHost host="192.168.56.127" url="192.168.56.127:3306" password="123456" user="root"/>
</dataHost>
</mycat:schema>

3.3 跨库查询sql测试

/*!mycat:catlet=io.mycat.catlets.ShareJoin */ 
 SELECT
          sms.monitor_system_id,
          sms.tenant_id,
          hpht.tenant_name,
          sms.external_system_code,
          sms.enabled_flag,
          sms.object_version_number
        FROM
          monitor_system sms       
        LEFT JOIN
          tenant hpht
        ON
          sms.tenant_id = hpht.tenant_id 

注意
该方案的特点是需要在sql里加上 mycat的注解，但是能够避免使用全局表导致数据量过大和各节点数据不一致的情况。