业务需求
在分布式环境中访问数据库,经常会有数据库分库的需求。将一些非业务核心的数据分摊在不同的数据库,根据业务需求将请求定向到各自需求的数据,以减少对数据库的冲击。但是这样做对于一些有业务逻辑判断的请求会不好操作。如下一下简单的需求:
核心数据库中存储了用户信息,几个业务数据库按地区存储了各地区用户的消费情况,消费情况表中为节约存储只保存了一个用户唯一ID,在生成统计信息时需要将用户的一些基信息一并统计出来。
要实现这样的统计需求可以有以下几种方法:
- 在程序中建立多数据源组合数据;
- 通过数据库代理(如Mycat)来访问数据库获取数据;
- 在存储过程中跨数据库访问组合数据;
- 其他,请各位大佬补充。
本方主要针对第3种方法展开详细说明,其他几种后续再补充。
要实现跨数据库访问这里我们不得不引出MySQL的FEDERATED存储引擎。那么什么是FEDERATED存储引擎呢?FEDERATED存储引擎又有什么高级特性呢?请听我一一道来。
MySQL存储引擎
存储引擎通俗的说就是数据库存储数据、组织数据、建立索引、查询数据的不同方法。MySQL中常用的有以下几种存储引擎:
- MyISAM:提供高速存储和检索,以及全文搜索能力。不支持事务。
- InnoDB:支持事务操作,支持行级锁,存储空间会占用比较大,是默认的MySQL引擎。
- Memory:所有数据均保存在内存中,数据不安全。
- FEDERATED:可以将多个物理服务器创建一个逻辑数据库,适用于分布式环境。
其他如CSV、Archive、Example之类的存储引擎这里就不详细说明了,会放在存储引擎专用篇章说明。
FEDERATED引擎
FEDERATED可以将远程MySQL的数据表映射到本地数据来解决应用程序中跨库访问需求。在本地创建了FEDERATED引擎表之后,文件目录中只会有一个表定义的文件,没有其他的数据文件,因为数据都是在远程数据库中,本地只是一个映射关系而已。这和Oracle的dblink、SQL Server的链接服务器是相似的。尽管FEDERATED被定义适用于分布式环境,但在实际使用过程中还是一定的局限性:
1. 不支持事务,这就注定了FEDERATED只能用于查询类的访问;
2. 如果远程表结构发生了变化,本地的FEDERATED表是不会实时同步的,需要手动修改本地表,同样对本地表修改,远程表也不会同步,这是因为FEDERATED只是维护一个映射关系,并不作数据和结构的维护;
3. 关于索引,远程表中创建了索引和本地表创建了索引是两回事,本地表中没有索引查询性能直线下降;
4. 关于效率,远程表中数据太大也会影响查询效率,不要将远程表作为子查询;
配置FEDERATED远程表
开启FEDERATED引擎
首先查看本地的MySQL是否启用了FEDERATED(远程数据库可以不启用),默认是不启用的。
1 2 |
SHOW ENGINES; |
可以看到FEDERATED的Support为NO。
修改MySQL配置文件:
1 2 |
vim /etc/my.cnf |
在最后加上federated,保存退出,重启MySQL服务:
1 2 |
systemctl restart mysqld |
再次查看ENGINES可以看到此时FEDERATED的Support为YES,表明已经成功启用FEDERATED了。
创建用于远程表的用户
1 2 3 4 |
ALTER USER 'remoteuser'@'%' IDENTIFIED BY '123456'; GRANT SELECT ON *.* TO 'remoteuser'@'%'; |
这里我给remoteuser这个用户只配置了SELECT权限,因为我不希望本地数据库能修改远程表的数据。大家可以根据实际需求自行配置权限。
在本地数据库创建远程表
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE `UserInfo` ( `UserID` int(11) NOT NULL, `UserName` varchar(32) NOT NULL DEFAULT '', `NickName` varchar(32) NOT NULL DEFAULT '', `Password` varchar(128) DEFAULT '' , `Telephone` varchar(20) NOT NULL DEFAULT '', `Mail` varchar(128) DEFAULT '', `Sex` varchar(8) NOT NULL DEFAULT '', `BirthDay` varchar(12) NOT NULL DEFAULT '', `Address` varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (`UserID`) ) ENGINE=FEDERATED CONNECTION='mysql://remoteuser:123456@127.0.0.1:3306/Basic/UserInfo'; |
首先UserInfo这张表必须要在你的远程数据库存在,而且本地需要的数据结构也需要和远程表中对应,这样一张远程表就创建好了。
进阶
我们可以看到CONNECTION配置中是直接作用的用户和地址访问,如果有很多远程表,当某天远程数据库的连接地址或者配置信息发生变化,我们总不至于要每张表去修改吧。能不能像dblink或者链接服务器对象那样使用一种对象来管理CONNECTION呢,答案是肯定的。这就是MySQL的Create Server方法。
1 2 3 4 |
CREATE SERVER BasicServer FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1,USER 'remoteuser',PASSWORD '123456' ,PORT 3306,DATABASE 'basic'); |
这样就创建好了一个server对象,CONNECTION中直接配置:
1 2 |
CONNECTION='BasicServer/UserInfo' |
最后
虽然FEDERATED目前还存在一些限制,但对于跨数据库关联查询使用得当还是有很方便的。唯一不足的就是FEDERATED还是基于表级别的,而不能将dblink那样将整个数据库映射出来。