MySQL 不同实例间跨库联表查询

MySQL engine

FEDERATED 引擎简介

使用FEDERATED存储引擎，您可以从远程MySQL数据库访问数据，而无需使用复制或群集技术。查询本地FEDERATED表会自动从远程（联合）表中提取数据。没有数据存储在本地表上。

FEDERATED如果要从源代码构建MySQL，则要 包含存储引擎，请使用 -DWITH_FEDERATED_STORAGE_ENGINE 选项调用CMake。

该FEDERATED存储引擎默认情况下未在运行的服务器启用; 要启用 FEDERATED，您必须使用该--federated选项启动MySQL服务器二进制文件。

当您使用标准的存储引擎(如MyISAM、CSV或InnoDB)创建一个表时，该表由表定义和相关数据组成。创建FEDERATED表时，表定义是相同的，但数据的物理存储是在远程服务器上处理的；

在FEDERATED本地服务器上的表上执行查询和语句时 ，通常将在本地数据文件中插入，更新或删除信息的操作将发送到远程服务器以执行，在此操作中，它们将更新远程服务器或本地服务器上的数据文件。从远程服务器返回匹配的行。

FEDERATED 表结构

当客户端发出引用FEDERATED表的SQL语句时， 本地服务器（执行SQL语句的地方）和远程服务器（实际存储数据的地方）之间的信息流如下：

1. 存储引擎将遍历FEDERATED表具有的每一列， 并构造一个引用远程表的适当SQL语句。
2. 该语句使用MySQL客户端API发送到远程服务器。
3. 远程服务器处理该语句，而本地服务器检索该语句产生的任何结果（受影响的行数或结果集）。
4. 如果该语句产生结果集，则每一列都将转换为该FEDERATED引擎期望的内部存储引擎格式， 并可用于将结果显示给发出原始语句的客户端。

本地服务器使用MySQL客户端C API函数与远程服务器通信。它调用 mysql_real_query()发送语句。要读取结果集，它使用 mysql_store_result()并一次读取一行 mysql_fetch_row()。

看到这里有个 .frmFile，那么下面解释一下 innoDB，MyIsam 引擎的文件了。

InnoDB 引擎：

特点：

• 支持事务，事务回滚；
• 行级锁；
• 支持外键；

更多具体优点参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-benefits.html

对应文件：

• Table.frm 表结构；
• Table.ibd 表数据+表索引

MyISAM 引擎

特点：

• 表级锁；

具体信息查看：https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

对应文件：

• Table.frm 存储表结构；
• Table.MYI(MYIndex) 索引文件；
• Table.MYD(MYData) 数据文件

那么现在再看该图，相信就可以理解 FEDERATED表是怎么一回事了，个人理解，类似于软链接。

如何创建 FEDERATED 表

先决条件：

​ 开启 FEDERATED 引擎，查看 引擎是否开启可通过show engines;，查看；

​ 谁需要用，就谁开启，没必要都开启，本地端开启该引擎。

​ 如未开启，显示为

解决方法：

​ 1、停止 MySQL 服务；

​ 2、在配置文件中，[mysqld] 下写入 federated；

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated    # 示例使用的docker启动的 Mysql:5.7 路径为 /etc/mysql/mysql.conf.d/mysqld.cnf

​ 3、开启服务；

​ 4、再次查看。

​ 解决了先决条件以后，现在进入具体实施环节。

具体实施：

具体包含两个步骤：

1. 在远程服务器上创建表。或者，可以使用以下SHOW CREATE TABLE 语句记录现有表的表定义。
2. 使用相同的表定义在本地服务器上创建表，但是添加将本地表链接到远程表的连接信息。

第一步的目的其实就是拿到，你远程服务器上需要的某张表创建时候的字段信息，也就是表结构，结构图中的 .frm；

第二步的则是将得到的表结构在，本地服务器上再创建一遍，只不过需要添加链接远程表的连接信息。

命令给出来，就十分清晰明了了

-- 远程服务器创建该表时的sql
CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=innodb
DEFAULT CHARSET=utf8mb4;

-- 本地服务器创建FEDERATED时的sql语句，后指定 ENGINE=FEDERATED CONNECTION='mysql://{user}:{password}@{host}:{port}/{database}/{table}
CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://test:123456@192.168.92.130:3316/test_db/test_table';

CONNECTION字符串包含连接到远程服务器所需的信息，该远程服务器包含将用于物理存储数据的表。连接字符串指定服务器名称，登录凭据，端口号和数据库/表信息

连接字符串的格式如下：

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

• scheme：公认的连接协议。此时仅mysql支持作为 scheme值。
• user_name：连接的用户名。此用户必须已经在远程服务器上创建，并且必须具有适当的特权来执行所需的操作（SELECT， INSERT， UPDATE在远程表，等等）。
• password：（可选）的相应密码 user_name。
• host_name：远程服务器的主机名或IP地址。
• port_num：（可选）远程服务器的端口号。默认值为3306。
• db_name：保存远程表的数据库的名称。
• tbl_name：远程表的名称。本地和远程表的名称不必匹配

重要：

要创建将联合到远程表的本地表，有两个可用选项。您可以使用来创建本地表并指定用于连接到远程表的连接字符串（包含服务器名称，登录名，密码）CONNECTION，也可以使用以前使用该CREATE SERVER语句创建的现有连接 。

如果要FEDERATED 在同一服务器上创建多个表，或者要简化创建FEDERATED表的过程，则可以使用该CREATE SERVER语句来定义服务器连接参数，就像使用CONNECTION字符串一样。

CREATE SERVER

CREATE SERVER server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)

option:
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }

-- 创建连接
CREATE SERVER test_server
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'test',PASSWORD '123456', HOST '192.168.92.130',PORT '3316', DATABASE 'TestingCloud');

-- 创建federated表
CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='test_server/test_table'   # 简化了很长的连接信息，只需刚创建的server/table_name

注意：

您可以FEDERATED通过向主机上的表添加索引来提高表的性能 。之所以会进行优化，是因为发送到远程服务器的查询将包含WHERE子句的内容， 并将发送到远程服务器，然后在本地执行。这样可以减少网络流量，否则网络流量会从服务器请求整个表进行本地处理。

验证：

刚刚一直特地说明的 .frm .ibd，那么我们现在来验证一下；

远程服务器

# 远程MySQL 该 test_table 表有 InnoDB 引擎所需的两个文件，.frm .ibd
[root@jiangjw-test ~]# ls /data/mysql02/test_db/
db.opt  test_table.frm  test_table.ibd

本地服务器

# 本地MySQL image 库下，test_table 表只有一个 .frm 表结构文件
[root@jiangjw-test ~]# ls /data/mysql01/image/
db.opt  test_table.frm

注意事项

使用FEDERATED存储引擎时，您应该注意以下几点 ：

• FEDERATED表可以复制到其他从属服务器，但是您必须确保从属服务器能够使用CONNECTION字符串（或mysql.servers表中的行）中 定义的用户/密码组合 连接到远程服务器。

以下各项指示 FEDERATED存储引擎支持和不支持的功能：

• 远程服务器必须是MySQL服务器。
• 在尝试通过FEDERATED表访问远程表之前，必须存在联邦表所指向的远程表。
• 一个FEDERATED表可能指向另一个表，但是您必须小心不要创建重复。
• 通常，FEDERATED表不支持索引。因为对表数据的访问是远程处理的，所以实际上是使用索引的远程表。这意味着，对于不能使用任何索引因而需要全表扫描的查询，服务器将从远程表中获取所有行，并在本地对其进行筛选。这与任何语句无关 WHERE或LIMIT与该SELECT语句一起使用；这些子句在本地应用于返回的行。

  因此，无法使用索引的查询可能会导致性能下降和网络过载。另外，由于返回的行必须存储在内存中，因此此类查询还可能导致本地服务器交换，甚至挂起。

• 创建FEDERATED表时应小心， 因为MyISAM可能不支持来自等效表或其他表的索引定义。例如，在创建一个 FEDERATED与索引表的前缀上 VARCHAR， TEXT或 BLOB列将失败。中的以下定义MyISAM有效：

  CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;

  此示例中的键前缀与FEDERATED引擎不兼容 ，并且等效语句将失败：

  CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
    CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';

  如果可能，在远程服务器和本地服务器上创建表时，应尝试将列定义和索引定义分开，以避免出现这些索引问题。

• 在内部，实现使用 SELECT， INSERT， UPDATE，和 DELETE，但不会 HANDLER。
• 该FEDERATED存储引擎支持 SELECT， INSERT， UPDATE， DELETE， TRUNCATE TABLE，和索引。除之外，它不支持ALTER TABLE或任何直接影响表结构的数据定义语言语句 DROP TABLE。当前实现不使用准备好的语句。
• FEDERATED接受 INSERT ... ON DUPLICATE KEY UPDATE语句，但是如果发生重复键冲突，该语句将失败并显示错误。
• 不支持事务。

• FEDERATED执行批量插入处理，以便将多行批量发送到远程表，从而提高了性能。同样，如果远程表是事务性的，则它可以使远程存储引擎在发生错误时正确执行语句回滚。此功能具有以下限制：

  • 插入的大小不能超过服务器之间的最大数据包大小。如果插入超过此大小，则它将分成多个数据包，并且可能发生回滚问题。
  • 不会进行批量插入处理 INSERT ... ON DUPLICATE KEY UPDATE。
• 有没有办法让FEDERATED引擎知道，如果远程表已经改变。这样做的原因是，该表必须像数据文件一样工作，除了数据库系统之外，任何其他文件都不能写入。如果远程数据库发生任何更改，则可能会破坏本地表中数据的完整性。
• 使用CONNECTION字符串时，不能在密码中使用“ @”字符。您可以通过使用CREATE SERVER语句创建服务器连接来解决此限制。
• 在insert_id和 timestamp选项都不会传播到数据提供者。
• DROP TABLE针对FEDERATED表发出的 任何语句仅删除本地表，而不删除远程表。
• FEDERATED 表不适用于查询缓存。
• FEDERATED表 不支持用户定义的分区 。

具体参考：https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html