FederatedX 下推语句调研

FederatedX 是mariadb 对原Federated引擎进行改进后的版本，主要区别如下：

federated vs federatedx

• Transactions (beta feature)
• Supports partitions (alpha feature)
• New class structure which allows developers to write connection classes for other RDBMSs without having to modify base classes for FederatedX
• Actively developed!

本文主要调研下FederatedX对语句下推上是否有改进，如下是公司同事的测试结果：

1.
remote server: mysql-5.6.12
IP: 172.16.70.175
CREATE TABLE `warehouse` (
`w_id` int(11) NOT NULL DEFAULT '0',
`w_name` varchar(10) DEFAULT NULL,
`w_street_1` varchar(20) DEFAULT NULL,
`w_street_2` varchar(20) DEFAULT NULL,
`w_city` varchar(20) DEFAULT NULL,
`w_state` char(2) DEFAULT NULL,
`w_zip` varchar(9) DEFAULT NULL,
`w_tax` double DEFAULT NULL,
`w_ytd` decimal(24,12) DEFAULT NULL,
PRIMARY KEY (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. local server: mariadb-10.0.12
IP: 172.16.90.20
CREATE TABLE `warehouse` (
`w_id` int(11) NOT NULL DEFAULT '0',
`w_name` varchar(10) DEFAULT NULL,
`w_street_1` varchar(20) DEFAULT NULL,
`w_street_2` varchar(20) DEFAULT NULL,
`w_city` varchar(20) DEFAULT NULL,
`w_state` char(2) DEFAULT NULL,
`w_zip` varchar(9) DEFAULT NULL,
`w_tax` double DEFAULT NULL,
`w_ytd` decimal(24,12) DEFAULT NULL,
PRIMARY KEY (`w_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1
CONNECTION='mysql://dbscale:dbscale@172.16.90.20:3306/test/warehouse';
CREATE TABLE `part_table3` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

3. Test
3.1 select * from warehouse limit 1,10; // test limit
select * from warehouse group by w_id; // test groupby the same
select * from warehouse having( w_id=10);// having the same
Generated SQL on remote server.
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`;

3.2 select * from warehouse; // test none
Output : SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`.

3.3 select count(*) from warehouse; // test count()
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`

3.4 select count(*) from warehouse where w_id=1; // test where condition
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE (`w_id` = 1);

3.5 select count(*) from warehouse where w_id in (1,2,3); // test in
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE ( (`w_id` = 1) ) AND ( (1=1) );
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE ( (`w_id` = 2) ) AND ( (1=1) );
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE ( (`w_id` = 3) ) AND ( (1=1) );

3.6 select count(*) from warehouse where w_id=1 OR w_id =2; // test OR
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE ( (`w_id` = 1) ) AND ( (1=1) );
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE ( (`w_id` = 2) ) AND ( (1=1) );

3.7 select count(*) from warehouse, part_table3; //test join
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`;

3.8 select count(*) from warehouse, part_table3 where warehouse.w_id=1; // test join with where
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE (`w_id` = 1)

3.9 select count(*) from warehouse, part_table3 where warehouse.w_id=part_table3.c1;// test join
condition
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE (`w_id` = 1);
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE (`w_id` = 2)
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE (`w_id` = 3)
**part_table3.c1 contains (1,2,3)**

3.10 select count(*) from warehouse, part_table3 where warehouse.w_id=part_table3.c1 OR
warehouse.w_id = 10; // test join OR
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`;

3.11 select count(*) from warehouse, part_table3 where warehouse.w_id=part_table3.c1 AND
warehouse.w_id = 10; // test join AND
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE (`w_id` = 10);

3.12 select * from warehouse, part_table3 where warehouse.w_id=c1 and warehouse.w_id=c2;//
test multi equals
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse` WHERE (`w_id` = 1)
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE (`w_id` = 2)
SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`,
`w_ytd` FROM `warehouse` WHERE (`w_id` = 3)
** c1 ∩ c2 = {1,2,3}**

3.13 select * from warehouse, part_table3 where warehouse.w_state like 't%'; // test like
select * from warehouse where warehouse.w_state like 't%';//the same
Output: SELECT `w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`,
`w_tax`, `w_ytd` FROM `warehouse`;

总结：从测试的结果来看，FederatedX在语句的下推上真的很弱！ limit/group by/order by /聚集函数都不下推， 对于or条件居然是拆分成多条语句

而且FederatedX返回的结果都会缓存在内存中，如果表大的话，基本就是无法用了。。。

不过mariadb 正在开发新的引擎 connect 去替代FederatedX， 希望到时能有改善。
目前的对比可以参考：
http://serge.frezefond.com/2013/04/mariadb-connect-storage-engine-vs-federatedx/

转载请注明转自高孝鑫的博客