MySQL 远程表 — 基于FEDERATED存储引擎

0
263

业务需求

在分布式环境中访问数据库,经常会有数据库分库的需求。将一些非业务核心的数据分摊在不同的数据库,根据业务需求将请求定向到各自需求的数据,以减少对数据库的冲击。但是这样做对于一些有业务逻辑判断的请求会不好操作。如下一下简单的需求:

核心数据库中存储了用户信息,几个业务数据库按地区存储了各地区用户的消费情况,消费情况表中为节约存储只保存了一个用户唯一ID,在生成统计信息时需要将用户的一些基信息一并统计出来。

要实现这样的统计需求可以有以下几种方法:

  1. 在程序中建立多数据源组合数据;
  2. 通过数据库代理(如Mycat)来访问数据库获取数据;
  3. 在存储过程中跨数据库访问组合数据;
  4. 其他,请各位大佬补充。

本方主要针对第3种方法展开详细说明,其他几种后续再补充。

要实现跨数据库访问这里我们不得不引出MySQL的FEDERATED存储引擎。那么什么是FEDERATED存储引擎呢?FEDERATED存储引擎又有什么高级特性呢?请听我一一道来。

MySQL存储引擎

存储引擎通俗的说就是数据库存储数据、组织数据、建立索引、查询数据的不同方法。MySQL中常用的有以下几种存储引擎:

  1. MyISAM:提供高速存储和检索,以及全文搜索能力。不支持事务。
  2. InnoDB:支持事务操作,支持行级锁,存储空间会占用比较大,是默认的MySQL引擎。
  3. Memory:所有数据均保存在内存中,数据不安全。
  4. FEDERATED:可以将多个物理服务器创建一个逻辑数据库,适用于分布式环境。

其他如CSV、Archive、Example之类的存储引擎这里就不详细说明了,会放在存储引擎专用篇章说明。

FEDERATED引擎

FEDERATED可以将远程MySQL的数据表映射到本地数据来解决应用程序中跨库访问需求。在本地创建了FEDERATED引擎表之后,文件目录中只会有一个表定义的文件,没有其他的数据文件,因为数据都是在远程数据库中,本地只是一个映射关系而已。这和Oracle的dblink、SQL Server的链接服务器是相似的。尽管FEDERATED被定义适用于分布式环境,但在实际使用过程中还是一定的局限性:
1. 不支持事务,这就注定了FEDERATED只能用于查询类的访问;
2. 如果远程表结构发生了变化,本地的FEDERATED表是不会实时同步的,需要手动修改本地表,同样对本地表修改,远程表也不会同步,这是因为FEDERATED只是维护一个映射关系,并不作数据和结构的维护;
3. 关于索引,远程表中创建了索引和本地表创建了索引是两回事,本地表中没有索引查询性能直线下降;
4. 关于效率,远程表中数据太大也会影响查询效率,不要将远程表作为子查询;

配置FEDERATED远程表

开启FEDERATED引擎

首先查看本地的MySQL是否启用了FEDERATED(远程数据库可以不启用),默认是不启用的。

可以看到FEDERATED的Support为NO。

修改MySQL配置文件:

在最后加上federated,保存退出,重启MySQL服务:

再次查看ENGINES可以看到此时FEDERATED的Support为YES,表明已经成功启用FEDERATED了。

创建用于远程表的用户

这里我给remoteuser这个用户只配置了SELECT权限,因为我不希望本地数据库能修改远程表的数据。大家可以根据实际需求自行配置权限。

在本地数据库创建远程表

首先UserInfo这张表必须要在你的远程数据库存在,而且本地需要的数据结构也需要和远程表中对应,这样一张远程表就创建好了。

进阶

我们可以看到CONNECTION配置中是直接作用的用户和地址访问,如果有很多远程表,当某天远程数据库的连接地址或者配置信息发生变化,我们总不至于要每张表去修改吧。能不能像dblink或者链接服务器对象那样使用一种对象来管理CONNECTION呢,答案是肯定的。这就是MySQL的Create Server方法。

这样就创建好了一个server对象,CONNECTION中直接配置:

最后

虽然FEDERATED目前还存在一些限制,但对于跨数据库关联查询使用得当还是有很方便的。唯一不足的就是FEDERATED还是基于表级别的,而不能将dblink那样将整个数据库映射出来。

留下一个答复

请输入你的评论!
请在这里输入你的名字

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据