1. 启动 federated 引擎
show engines;
首先我们查看一下当前已经安装的存储引擎,默认情况为 Engine 列表里面有 FEDERATED,但是Support 为 NO。这时我们需要修改 MySQL 配置文件。
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在文件末尾添加如下两行。
[mysqld]
federated
然后重启 MySQL。
sudo systemctl restart mysql.service
再查看一下 FEDERATED 引擎的 Support 就变成 YES 了。
如果存储引擎的列表里面没有找到 FEDERATED,那么你可能就需要考虑重新安装 MySQL 了。
2. 创建 federated 表
启用 FEDERATED 引擎之后我们就可以创建 FEDERATED 表了。
show create table lltmdc.edm_readers;
show create table lltmmc.edm_tasks;
首先我们登陆远程数据库查看一下表结构,然后根据表结构创建对应的 FEDERATED 表。
CREATE DATABASE `lltedm`;
CREATE TABLE `edm_readers` (
`readerid` int(16) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`reader_email` varchar(64) NOT NULL COMMENT '读者Email',
`edm_subject` varchar(128) NOT NULL COMMENT '邮件主题',
`read_times` smallint(8) NOT NULL DEFAULT '1' COMMENT '打开次数',
`read_ip` varchar(16) DEFAULT NULL COMMENT '初次访问IP',
`datetime` int(10) NOT NULL COMMENT '首次打开时间',
PRIMARY KEY (`readerid`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://lltro:dvj7HIa73gw6k@10.8.14.62:3306/lltmdc/edm_readers';
CREATE TABLE `edm_tasks` (
`taskid` int(16) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`adminid` int(16) NOT NULL COMMENT '管理员ID',
`edmsubject` varchar(128) NOT NULL COMMENT '邮件标题',
`edmfilename` varchar(64) NOT NULL COMMENT '邮件文件名',
`memberids` longtext,
`membergroup` varchar(16) NOT NULL COMMENT '接收会员组',
`taskcomment` varchar(256) NOT NULL COMMENT '任务描述',
`taskcoupon` varchar(32) NOT NULL COMMENT '邮件中包含的优惠券码',
`tasklog` varchar(256) NOT NULL COMMENT '任务执行日志',
`testmemberid` varchar(128) DEFAULT NULL COMMENT '测试邮件用户ID',
`edmsent` int(16) NOT NULL DEFAULT '0' COMMENT '总发送数',
`edmrealsent` int(16) NOT NULL COMMENT '发送成功数',
`taskschedule` int(10) NOT NULL DEFAULT '0' COMMENT '计划执行时间',
`createdatetime` int(10) NOT NULL COMMENT '任务生成时间',
`startdatetime` int(10) NOT NULL COMMENT '开始执行时间',
`finishdatetime` int(10) NOT NULL COMMENT '任务完成时间',
`taskstatus` tinyint(1) NOT NULL DEFAULT '0' COMMENT '任务状态:0=初始化/1=开始执行/4=执行失败/8=执行完成',
PRIMARY KEY (`taskid`)
) ENGINE= FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://lltro:dvj7HIa73gw6k@10.8.75.167:3306/lltmmc/edm_tasks';
3. 联合查询
一切都准备好之后我们就可以开始开始联合查询了。
不过需要注意的是 FEDERATED 引擎不支持索引,每次查询都是全表查询,所以千万不要使用子查询。
create view lltedm.v_edm_reasders as select edm_subject, count(*) as read_count from lltedm.edm_readers group by edm_subject;
SELECT DATE(FROM_UNIXTIME(startdatetime)) as 'Date', edmsent as 'Edm Sent', edmrealsent as 'Edm Real Sent',
IF(edmrealsent > 0, concat((edmrealsent / edmsent) * 100, '%'), '') as 'Success Rate',
read_count as 'Edm Read',
IF(edmrealsent > 0, concat(read_count / edmrealsent * 100, '%'), '') as 'Open Rate',
edmsubject as 'Edm Subject', edmfilename as 'Edm File Name'
FROM lltedm.edm_tasks
join lltedm.v_edm_reasders on (v_edm_reasders.edm_subject = substring_index(substring_index(edmfilename, '/', -1), '.', 1))
WHERE startdatetime >= UNIX_TIMESTAMP('2016-01-01')
ORDER BY startdatetime;
参考:
https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html
https://stackoverflow.com/questions/810349/mysql-cross-server-select-query
680 total views, 1 views today