前言:对 Federated引擎的解释
通过FEDERATED引擎创建的表只是在本地有表定义文件,数据文件则存在于远程数据库中。通过这个引擎可以实现类似Oracle下DBLINK的远程数据访问功能。就是说,这种建表方式只会在数据库A中创建一个表B的表结构文件,表的索引、数据等文件还是在机器B上的数据库B中,相当于只是在数据库A中创建了表B的一个快捷方式。1.准备阶段:开启Federated引擎
1.找到mysql的配置文件my.ini
2.在my.ini文件追加字段:federated
3.重启mysql服务,或者重启电脑
4.执行SHOW ENGINES; 确认打开FEDERATED引擎
2.实战阶段:实现两个数据库的表级数据同步
1.创建数据库db1,创建表tb1,插入数据
//建数据库db1CREATE DATABASE db1;//创建表tb1USE db1;CREATE TABLE tb1(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT)ENGINE=INNODB;//插入数据insert into tb1 select NULL;insert into tb1 select NULL;insert into tb1 select NULL;
在sqlyog里操作后,实现效果:
2.创建数据库db2
//建数据库db2CREATE DATABASE db2;//重点:创建映射表remote_tb1use db2;create table remote_tb1(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT)ENGINE=FEDERATEDCONNECTION='mysql://root:123456@localhost:3306/db1/tb1';//创建表tb2create table tb2(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT)ENGINE=INNODB;//插入数据insert into tb2 select NULL;insert into tb2 select NULL;insert into tb2 select NULL;
在sqlyog里操作后,实现效果:
3.测试对db2数据库里的表remote_tb1数据进行增删改查,db1里表tb1的数据是否会同步修改
delete from db2.remote_tb1 where id =3;
实验得知两边的表都会删除id=3的记录
4.tb2表上右键,创建触发器
创建触发器的语句:
DELIMITER $$CREATE/*[DEFINER = { user | CURRENT_USER }]*/TRIGGER `db2`.`tb2_insert_trigger` AFTER INSERTON `db2`.`tb2`FOR EACH ROW BEGINIF NOT EXISTS(SELECT * FROM remote_tb1 WHERE id=new.id) THENINSERT INTO remote_tb1(id) VALUES( new.id);END IF;END$$DELIMITER ;
F9运行之后,生成触发器,名称是之前自己设的,如下图
3.最终效果
在tb2表中增加一条记录,会通过触发器在remote_tb1表增加这条记录,再通过关联映射更改db1中tb1中的表记录。
4.FEDRATE引擎的特点
本地虚拟表与远程实体表之间是 TCP 长连接,并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。本虚拟表表与远程实体表之间的网络连接断开后,当对虚拟表发起查询时,它会尝试重新连接远程实体表,所以我们不用担心网络连接断开造成的永久中断问题。如果无时间未对本地虚拟表作任何操作,虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开,当对虚拟表发起查询时,连接又会重新建立。5.注意事项
1、FEDERATED 表可能会被复制到其他的slave数据库,需要确保slave服务器也能够连接上远程服务器。2、远程服务器必须是MySQL数据库3、在访问FEDERATED表中定义的远程数据库的表前,远程数据库中必须存在这张表。4、FEDERATED 表不支持通常意义的索引,服务器从远程库获取所有的行然后在本地进行过滤,不管是否加了where条件或limit限制。—查询可能造成性能下降和网络负载,因为查询返回的数据必须存放在内存中,所以容易造成使用系统的swap分区或挂起。5、FEDERATED表不支持字段的前缀索引6、FEDERATED表不支持ALTER TABLE语句或者任何DDL语句7、FEDERATED表不支持事务8、本地FEDERATED表无法知道远程库中表结构的改变9、任何drop语句都只是对本地库的操作,不对远程库有影响10、对本地虚拟表的结构修改,并不会修改远程表的结构 11、truncate命令,会清除远程表数据
6.目前使用FEDRATE引擎最大的缺点
1.limit语句查询过慢
select count(*), select * from limit M, N 等语句执行效率非常低,数据量较大时存在很严重的问题但按主键或索引列查询,则很快。
以下查询就非常慢(假设 id 为主索引) select * from test_db.xxl_job_log limit 1;而以下查询就很快: select * from test_db.xxl_job_log where id=1;
2.虚拟表和实体表字段不一致
如果虚拟表中字段未建立索引,而实体表中为此字段建立了索引,此种情况下,性能也相当差。但是当给虚拟表建立索引后,性能恢复正常。
3.like语句查询过慢
类似 where name like "str%" limit 1 的查询,即使在 name 列上创建了索引,也会导致查询过慢。原因:federated引擎会将所有满足条件的记录读取到本地,再进行 limit 处理。