MySQL存储引擎之：`MySQL`的`Federated`存储引擎：其在远程表访问中的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL Federated 存储引擎：远程表访问的利器

各位同学，大家好！今天我们来聊聊MySQL中一个相对冷门但功能强大的存储引擎——Federated。Federated引擎允许我们在本地MySQL服务器上创建一个表，该表的实际数据存储在远程的MySQL服务器上。这为跨数据库服务器的数据访问和集成提供了极大的便利。

1. Federated 引擎的基本概念

Federated存储引擎本身不存储任何数据。它只是一个指向远程MySQL表的指针。当我们对Federated表进行查询、插入、更新或删除操作时，这些操作会被转发到远程MySQL服务器执行，然后将结果返回给本地服务器。

可以理解为，Federated表就是一个本地的“视图”，这个视图的数据来源于远程的MySQL表。但 Federated 表和 View 的区别在于， Federated 表可以进行写操作，而 View 通常只能进行读操作。

2. Federated 引擎的优点

数据集成: 允许访问位于不同服务器上的数据，无需进行数据迁移。
简化分布式查询: 可以通过单个查询访问多个数据源，简化了分布式查询的复杂性。
减少本地存储空间: 本地服务器不需要存储实际数据，节省了存储空间。
实时数据访问: 始终访问远程表的最新数据。

3. Federated 引擎的缺点

性能瓶颈: 所有操作都需要通过网络传输到远程服务器，网络延迟会显著影响性能。
依赖远程服务器: 如果远程服务器不可用，则Federated表也无法访问。
安全问题: 需要配置远程服务器的访问权限，增加了安全风险。
事务支持: Federated存储引擎对事务的支持有限，跨多个服务器的事务需要特别注意。

4. Federated 引擎的安装和配置

默认情况下，Federated存储引擎可能未启用。我们需要手动启用它。

检查 Federated 引擎是否启用:

SHOW ENGINES;

如果 Federated 引擎的 Support 列显示为 NO，则需要启用它。

启用 Federated 引擎:

在 MySQL 的配置文件 (例如 my.cnf 或 my.ini) 中添加以下行：

[mysqld]
federated

或者，可以使用以下命令动态地启用它（但服务器重启后会失效）：

INSTALL PLUGIN federated SONAME 'ha_federated.so';

验证 Federated 引擎是否启用:

再次运行 SHOW ENGINES; 命令，确保 Federated 引擎的 Support 列显示为 YES 或 DEFAULT。

5. 创建 Federated 表

创建 Federated 表的语法如下：

CREATE TABLE federated_table (
    column1 datatype,
    column2 datatype,
    ...
) ENGINE=FEDERATED
CONNECTION='mysql://user:password@host:port/database/remote_table';

federated_table: 本地Federated表的名称。
column1, column2, …: 列的定义，需要与远程表的列定义匹配。
ENGINE=FEDERATED: 指定存储引擎为 Federated。
CONNECTION: 连接字符串，指定远程MySQL服务器的连接信息。
- user: 远程MySQL服务器的用户名。
- password: 远程MySQL服务器的密码。
- host: 远程MySQL服务器的主机名或IP地址。
- port: 远程MySQL服务器的端口号 (默认为 3306)。
- database: 远程数据库的名称。
- remote_table: 远程表的名称。

示例：

假设我们有两个 MySQL 服务器：

本地服务器: 192.168.1.100，数据库 local_db
远程服务器: 192.168.1.200，数据库 remote_db，表 users

远程服务器的 users 表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在，我们在本地服务器上创建一个 Federated 表，指向远程的 users 表：

USE local_db;

CREATE TABLE remote_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=FEDERATED
CONNECTION='mysql://remote_user:[email protected]:3306/remote_db/users';

注意：

需要在远程服务器上创建一个用户 remote_user，并授予其访问 remote_db 数据库中 users 表的权限。
本地Federated表的列定义需要与远程表的列定义保持一致。虽然列的顺序不一定要完全一样，但是数据类型必须兼容。最好是完全一样。
连接字符串中的密码需要进行适当的加密和保护，避免泄露。

6. Federated 表的使用

创建 Federated 表后，就可以像使用普通表一样使用它。

查询数据:

SELECT * FROM remote_users;

插入数据:

INSERT INTO remote_users (username, email) VALUES ('test_user', '[email protected]');

更新数据:

UPDATE remote_users SET email = '[email protected]' WHERE username = 'test_user';

删除数据:

DELETE FROM remote_users WHERE username = 'test_user';

所有这些操作都会被转发到远程MySQL服务器执行。

7. Federated 存储引擎的限制和注意事项

权限控制: 确保远程服务器上的用户具有足够的权限来执行所需的操作。
网络延迟: 考虑到网络延迟的影响，尽量避免在Federated表上执行复杂的查询或大量的写入操作。
数据类型兼容性: 本地Federated表的列定义需要与远程表的列定义保持一致。
事务: Federated存储引擎对事务的支持有限。如果需要跨多个服务器执行事务，需要使用分布式事务解决方案。
索引: Federated 表本身没有索引。所有索引都依赖于远程表。优化 Federated 表的查询，需要优化远程表的索引。
数据转换: Federated 引擎本身不做任何数据转换。远程表的数据类型必须与本地表的列定义兼容。

8. Federated 引擎的常见问题及解决方案

无法连接到远程服务器:
- 检查连接字符串是否正确。
- 检查远程服务器是否正在运行。
- 检查防火墙是否阻止了连接。
- 检查远程MySQL服务器是否允许来自本地服务器的连接 (通过 GRANT 语句)。
权限不足:
- 检查远程服务器上的用户是否具有足够的权限来访问远程表。
数据类型不兼容:
- 检查本地Federated表的列定义是否与远程表的列定义匹配。
性能问题:
- 优化远程表的索引。
- 尽量避免在Federated表上执行复杂的查询或大量的写入操作。
- 考虑使用缓存来减少对远程服务器的访问。

9. Federated 引擎的实际应用场景

数据仓库: 将来自不同数据源的数据集成到数据仓库中，用于分析和报表。
分布式应用: 在分布式应用中，不同的服务可能需要访问相同的数据。可以使用Federated引擎来共享数据。
数据迁移: 在数据迁移过程中，可以使用Federated引擎来访问旧数据库中的数据，并将其迁移到新数据库中。
审计: 集中式的审计系统可以利用Federated引擎访问各个数据库服务器上的审计日志。

10. 代码示例：更复杂的查询

假设远程 users 表还有一个 role_id 列，指向另一个远程表 roles， roles 表位于同一个远程数据库 remote_db。

CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(255) NOT NULL
);

现在，我们要在本地服务器上创建一个 Federated 表，指向远程的 roles 表：

USE local_db;

CREATE TABLE remote_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(255) NOT NULL
) ENGINE=FEDERATED
CONNECTION='mysql://remote_user:[email protected]:3306/remote_db/roles';

然后，我们可以使用 JOIN 操作来查询两个 Federated 表：

SELECT
    u.username,
    u.email,
    r.role_name
FROM
    remote_users u
JOIN
    remote_roles r ON u.role_id = r.id;

11. 代码示例：存储过程中使用 Federated 表

可以在存储过程中使用 Federated 表，但需要注意事务的隔离级别和错误处理。

DELIMITER //

CREATE PROCEDURE GetUserAndRole(IN user_id INT)
BEGIN
    SELECT
        u.username,
        u.email,
        r.role_name
    FROM
        remote_users u
    JOIN
        remote_roles r ON u.role_id = r.id
    WHERE
        u.id = user_id;
END //

DELIMITER ;

CALL GetUserAndRole(1);

12. 代码示例：使用 Federated 表进行数据校验

可以使用 Federated 表来进行跨数据库的数据校验。例如，检查本地数据库中的数据是否存在于远程数据库中。

DELIMITER //

CREATE PROCEDURE CheckUserExistsRemotely(IN local_user_id INT)
BEGIN
    DECLARE remote_count INT;

    SELECT COUNT(*) INTO remote_count
    FROM remote_users
    WHERE id = local_user_id;

    IF remote_count > 0 THEN
        SELECT 'User exists remotely';
    ELSE
        SELECT 'User does not exist remotely';
    END IF;
END //

DELIMITER ;

CALL CheckUserExistsRemotely(1);

13. 表格：Federated 存储引擎配置参数

参数	描述
CONNECTION	连接字符串，指定远程MySQL服务器的连接信息。
CHARSET	指定连接的字符集。如果未指定，则使用服务器的默认字符集。
TABLE_TYPE	总是FEDERATED
INSERT_METHOD	指定插入数据的方式。默认为DIRECT。DIRECT表示直接插入到远程表。不支持其他值。
UPDATE_METHOD	指定更新数据的方式。默认为DIRECT。DIRECT表示直接更新到远程表。不支持其他值。
DELETE_METHOD	指定删除数据的方式。默认为DIRECT。DIRECT表示直接删除到远程表。不支持其他值。
READ_ONLY	指定是否以只读模式访问远程表。如果设置为YES，则无法对Federated表进行写入操作。默认为NO。
ROW_FORMAT	Federated 引擎不支持 ROW_FORMAT 参数。它总是使用远程表的行格式。
DATA_LENGTH	总是返回 0，因为 Federated 引擎本身不存储任何数据。数据长度信息来自远程表。
INDEX_LENGTH	总是返回 0，因为 Federated 引擎本身不存储任何索引。索引信息来自远程表。
AUTO_INCREMENT	Federated 引擎不支持 AUTO_INCREMENT 参数。远程表的自增列由远程服务器管理。

14. 总结：Federated 引擎是数据集成的有效方案

Federated存储引擎为MySQL提供了一种简单而强大的方式来访问远程表。虽然它存在一些限制和性能问题，但在合适的应用场景下，可以极大地简化数据集成和分布式查询的复杂性。通过合理的设计和优化，可以充分发挥Federated引擎的优势，实现高效的跨数据库服务器数据访问。掌握Federated 引擎的使用，能让你在数据处理和架构设计上多一种选择，解决实际问题。

MySQL Federated 存储引擎：远程表访问的利器

发表回复 取消回复

发表回复取消回复