MySQL 不同实例间跨库联表查询-（1）

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表 不支持用户定义的分区 。

stem-variables.html#sysvar_insert_id)和 timestamp选项都不会传播到数据提供者。

• DROP TABLE针对FEDERATED表发出的 任何语句仅删除本地表，而不删除远程表。

• FEDERATED 表不适用于查询缓存。

• FEDERATED表 不支持用户定义的分区 。

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