使用mysql federated引擎构建MySQL分布式数据库访问层

使用mysql federated 引擎构建 MySQL 分布式数据库访问层

 

 

前言：随着应用复杂度的增加，数据库不断细化切分，导致应用程序中数据库应用就得复杂，凌乱。绝大部分程序人员可能都遇到这种情况，应用程序中需要连接多台数据库服务器，进行相应的操作。随着时间积累，太多的数据库服务器的连接逻辑出现在程序之中，这给程序的维护扩展，数据库维护工作带来极大的工作量。

 

于是一些分布式数据库代理层应运而生，如常见 MySQL 代理层 :

mysql proxy : 主要实现读写分离和负载均衡

MySQL Amoeba : 由陈思儒主导开发　功能比较完善，用深入应用的价值，参考网站 http://docs.hexnova.com/amoeba/ 

HiveDB : HiveDB是一个用来横向切分 mysql 数据库的开源框架，构建一个高性能和可扩展的基于 mysql 的系统，但目前仅支持 Java 客户端。 http://www.hivedb.org/

 

我认为mysql proxy, MySQL Amoeba 都是极好的实用价值，应该多深入了解之。

 

而本文所描述的 federated属于 MySQL的一种特殊引擎，利用它可将本地数据表映射至远程 MySQL 数据表，从而就可以解决应用程序中繁多的跨机器连接数据库问题，拓扑图如下：

 

如此就可以构造出一个统一的数据访问入口，就大大提高了整个数据库系统的可维护性。

Federated引擎是基于表级别的，只能将本地数据表定义为 Federated 引擎并映射至远程实体表，无法实现基于库级别的整体映射。

 

在本文中，我们将启用Federated 引擎的数据库访问入口服务器称为本地数据库，而将本地数据表对应的远程数据表，称之为实体表。

 

本地数据库需要启用Federated 引擎支持，而远程数据表无须 Federated 引擎支持。 Federated 引擎表使用标准的 MySQL 客户端协议与远程数据库建立 TCP 连接。

 

创建Federated 表的过程：

 

1. 　以root 登录远程 MySQL ，上创建合适的访问账号
  grant all on DB1.* to 'federated'@'%' identified by 'federated';

flush privileges;

2.   在远程MySQL 找到对应实体表的创建命令（如果是新表，请先建立好数据表，再执行此命令）
假设在远程mysql 上有库名 DB1,  表名 tag,  执行以下命令找到远程表的结构：
show create table DB1.tag

输出：
CREATE TABLE `tag` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(128) NOT NULL,

  `frequency` int(10) unsigned NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

 

3.  假设我们要将远程的DB1.tag 映射至本地 DB.TableA 表上。那么我们应该保持本地虚拟表与远程实体表结构一致（结构可以有所差异，但会造成使用，管理上的麻烦）。根据远程实体表的创建命令，创建本地虚拟表 ( 结构部分完全一样，创建表选项有所差异 ) ：

 

登录本地Mysql 服务器，创建相应的数据库及表：
create database DB;

use DB;
CREATE TABLE `TableA` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(128) NOT NULL,

  `frequency` int(10) unsigned NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`)

) ENGINE=federated connection="mysql://federated:federated@127.0.0.1:3306/DB1/tag";

 

这时，即建立好了federated 虚拟表，实际上本地 MySQL 只创建了表定义文件 , 而没有数据文件。我们对本地虚拟表的数据修改，均会发送到远程机器上执行。

 

本地虚拟表名与远程表名，可不相同。

 

经过测试，这个引擎的一些额外特点：
1.　本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。
2.  本虚拟表表与远程实体表之间的网络连接断开后，当对虚拟表发起查询时，它会尝试重新连接远程实体表，所以我们不用担心网络连接断开造成的永久中断问题。
3.   如果无时间未对本地虚拟表作任何操作，虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开，当对虚拟表发起查询时，连接又会重新建立。

 

一些注意事项：
1.　对本地虚拟表的结构修改，并不会修改远程表的结构
2.　 truncate 命令，会清除远程表数据
3.  drop命令只会删除虚拟表，并不会删除远程表

4.  不支持 alter table 命令

目前使用federated 最大的缺点：
1. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）
select id from db.tablea where id >100 limit 10 ;

而以下查询就很快：
select id from db.tablea where id >100 and id<150

 

2.  如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。

 

3. 类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为

federated引擎会将所有满足条件的记录读取到本，再进行 limit 处理。

 

这几个问题已经严重影响了federated 在实际环境中的应用。

评论

2 楼 lizuodu 2018-01-15  

非常不错

1 楼 wl6179 2015-01-18  

写的好！谢谢！！~