MySQL federated storage engine

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

940 total views, 1 views today

Leave a comment Cancel reply