分享人:Digoal 阿里云资深数据库专家
正文:
本篇内容将通过三个部分来介绍PG+MySQL第6课。
一、直接读写MySQL数据
二、冷热分离
三、外部归档表
一、直接读写MySQL数据
pg的外部表应用,可以访问外部数据源。阿里云的对象存储特点是廉价,由于内部多副本的机制,还可以保证数据的可靠性。我们在内网里使用OSS存储的归档数据,也叫冷数据的话。适合历史数据的归档存储。fdw接口不仅能支持关系数据库,mysql 等对外接口,还支持对象存储的外部存储,用来做外部归档表。
连接MySQL提供一个主机端口,拿到地址之后,使用user paassword,将pg数据库user连接到mysql server。通过foreign table,把mysql表结构映射到pg外部表。有了这三个元素之后,我们就能够在pg里通过table访问mysql表。
接下来,保证RDS MySQL和RDS PG 12两个实例,在同样的vpc里,配置MySQ server的白名单。允许pg实例访问vpn内网的MySQ server。插入test_mm和test_innodb的测试数据。
在pg实例里,创建extension mysql_fdw插件,然后创建mysql server,设置mysql网络地址'和port 'mysql端口。给digoal用户创建mysql server的user mapping,以及'mysqluser和mysqluserpwd'。完成这三步之后,就能创建mysql1的server外部表。
创建外部表有两种方法。第一,我们在pg里直接用CREATE FOREIGN TABLE 的语法创建,指定server mysql ,在option里指定远端的table text mm表。
外部表创建之后,就能查询到mysql的数据了。
第二,如果你想把mysql的所有表,在pg里一次性创建好。可以使用import schema的命令。比如把test_innodb和test_mmsk创建到本地的schema下。首先创建本地的schema,然后导入外部表,选择导入的内容,输入命令。就可以一次性导入。
如何查询导入数据,如上图所示。SELECT `id`, `user_id`, `group_id`, `create_time` FROM `db1`.`test_mm` WHERE ((`id` = 2))就可以完成查询。
我们要查询一个外部表有没有把条件推送到远端?通过Explain指定web详情,就可以打印remote query。如果查询远程表较慢时,可以检查一下,是不是把所有的数据都起到本地导致的。
当数据在pg里分析完之后,需要把数据插回MySQL到里,我们可以直接调用insert into的外部表。
当我们完成远程插入之后, 可以远程更新MySQL.这个时候只需要使用Update on digoal,输入需要更新的表格就可以完成远程更新。
当你需要删除MySQL记录时,先输入explain verbose delete from where。选择需要删除的表格和相应的ID,然后选择需要删除的内容,就可以完成删除。
注意,在远程执行MySQL时,需要检查explain verbose,如果没有push down,需要返回全表数据到pg。
当我们需要查询外部表时,完成上述操作后,需要输入如下代码:
db1=> \des
List of foreign servers
Name | Owner | Foreign-data wrapper
------------+--------+----------------------
mysql_1 | digoal | mysql_fdw
ossserver1 | digoal | oss_fdw
(2 rows)
db1=> \deu
List of user mappings
Server | User name
---------+-----------
mysql_1 | digoal
(1 row)
db1=> \det
List of foreign tables
Schema | Table | Server
--------+---------+------------
public | oss_tb1 | ossserver1
public | oss_tb2 | ossserver1
public | test_mm | mysql_1
(3 rows)
然后,就可以完成外部表、server、用户映射的相关查询。
二、冷热分离
OSS fdw通常用在归档数据或者冷热分离的业务场景。比如说我们在pg里存了大量的数据,其中有一部分的数据,访问频次非常低。通过OSS fdw,我们可以使用更少的本地存储,节省成本,提升整体的性价比。
如何开通OSS fdw呢?首先我们要开通OSS服务,创建一个子账号,然后指定读写OSS对象的权限,它会把id和密码告诉你,如何我们要到OSS控制台,创建一个bucket,即数据存储的归类。与之对应的是pg数据库的外部表的存放位置。然后指定低频访问或标准的存储,确认之后,bucket就创建好了。
然后,我们在pg里创建create extension oss_fdw插件,然后创建OSS server,指定一个名字,输入OSS的位置,id,key和bucket。是bucket。然后就完成了oss_fdw和server的创建。
三、外部归档表
创建OSS server之后,就可以创建外部表。首先,指定外部表的结构,然后指定server,指定option里的目录,最后指定格式。如上图所示,创建oss外部表时,分为压缩和不压缩,大家可以根据不同的需求选择相应的命令。
紧接着,我们创建一张pg的本地表。结构跟OSS表的结构一模一样,然后在这张本地表里写入100万条测试记录。可以看到,写入100万条数据花了九秒的时间。
当我们查询外部表时,它的查询速度还是比较快的。查询limit1时,只要几十毫秒就返回了。在查询时,注意OSS的表没有索引,所以需要把表格下载到本地,然后通过本地过滤,再进行查询。
在查询过程中,我们可以发现不带压缩的表,它的查询速度更慢。因为OSS和pg实例间的传输带宽有限,如果不做压缩,查询效率会更低。同样的200万条记录,压缩后只花了14秒查询,不压缩花了将近27秒查询。所以建议大家在查询过程中使用压缩,提升工作效率。
除此之外,归档数据和外部数据是不一样的。外部表可以做update和delete。但归档数据只能insert和查询。它不能做update和delete。如果我们直接对OSS的外部表做update和delete操作,系统就会报错。
每一个OSS的外部只跟OSS建立一个连接,OSS带宽限制跟连接直接挂钩。如果我们同时跟OSS建立多个会话,访问一张外部表,整体的带宽就会增加,从而提升处理的吞吐能力。
上图链接,是OSS外部表快速入门的帮助文档,有需要的同学可以登陆查看。其中,大家需要注意文件的位置,有三个参数可以指定,且这三个参数互斥。如果有写入要求,必须选择虚拟目录或者prefix格式。如果你对OSS表有写入要求,就不能选择parse_errors参数。