mycat1.6.74后单库分表简单join

 

SELECT t.id FROM tr t LEFT JOIN t1 ON t.id =   t1.id WHERE t.id IN (1,2)
tr是逻辑表，mycat会替换此逻辑表，而sql其他部分不处理

 

tr是分表

t1 一般是全局表

mycat，t1需要在schema里面配置，mycat会检查sql中的表

 

因为没有结果集合拼

所以路由只能路由到一个分片，来保证正确性。所以必须写查询条件保证查询范围落到一个分片表上。

如果不写查询条件，则导致会路由到所有分片表，涉及多个分片。

 

测试

CREATE TABLE `address` (
  `id` int(11) NOT NULL,
  `addressname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `travelrecord` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int(11) DEFAULT NULL,
  `blob` longblob DEFAULT NULL,
  `d` double DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `travelrecord2` (
  `id` bigint(20) NOT NULL,
  `user_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int(11) DEFAULT NULL,
  `blob` longblob DEFAULT NULL,
  `d` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `travelrecord3` (
  `id` bigint(20) NOT NULL,
  `user_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `traveldate` date DEFAULT NULL,
  `fee` decimal(10,0) DEFAULT NULL,
  `days` int(11) DEFAULT NULL,
  `blob` longblob DEFAULT NULL,
  `d` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

 

测试SQL

EXPLAIN SELECT t0000.id FROM travelrecord t0000 LEFT JOIN address aaaaa ON t0000.id =   aaaaa.id WHERE t0000.id IN (1)

 

结果

DATA_NODE	SQL
dn1	SELECT t0000.id FROM travelrecord2 t0000  LEFT JOIN address aaaaa ON t0000.id = aaaaa.id WHERE t0000.id IN (1)

 

测试SQL

SELECT t0000.id FROM travelrecord t0000 LEFT JOIN address aaaaa ON t0000.id =   aaaaa.id WHERE t0000.id IN (1)

 

mycat配置

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
		<table name="travelrecord" dataNode="dn1" rule="auto-sharding-long"  subTables="travelrecord2,travelrecord3,travelrecord4"/>
		<table name="address" type="global" dataNode="dn1"/>
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="db1" />