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

柔情只为你懂 2021-05-12 11:50 403阅读 0赞

使用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 数据表，从而就可以解决应用程序中繁多的跨机器连接数据库问题，拓扑图如下：

![05441d2a-603b-3bcb-8cc4-deead834fba3.png][]

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

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 在实际环境中的应用。

[05441d2a-603b-3bcb-8cc4-deead834fba3.png]: /images/20210511/e4084313e1ba44f4a0c05770381ac84b.png