MySQL federated存储引擎测试

IT小Chen 2023-01-16

508

环境：

172.16.1.137  远程库
172.16.1.138  本地库
数据库版本：MySQL 5.7.36
复制

需求：

本地库172.16.1.138，通过federated存储引擎访问远程库172.16.1.137部分表。
复制

测试结论：

使用之前以为和Oracle dblink类似，测试发现限制一大堆。

1.远程库表执行insert,update,delete,truncate可以同步到本地表。
2.远程库执行alter添加字段、修改字段、删除字段等操作，不能同步到本地表，需要删除重建本地表。
3.远程库表执行drop table不会同步到本地表，但本地表也无法执行查询等所有操作。
4.本地表执行drop table只会删除本地虚拟表，不会删除远程表。
5.本地表执行insert,update,delete,truncate也会反向同步到远程表，需要特别注意。
6.本地表执行count,limit等操作效率差，很多场景下不走索引。
7.本地表不支持事务。
复制

测试过程如下：

一：本地库启用federated
二：新增测试数据
三：数据同步测试
四：本地库性能如何？
五：远程表是否支持事务？
复制

一：本地库启用federated

远程库不需要启用

检查参数

[mysql@cjc-db-01 ~]$ cat etc/my.cnf|grep -i federated
skip-federated
MySQL [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | NO      | dev/null storage engine (anything you write to it disappears) | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
复制

修改参数

172.16.1.138

[mysql@cjc-db-02 ~]$ vi etc/my.cnf
#skip-federated
federated
复制

[mysql@cjc-db-02 ~]$ cat etc/my.cnf|grep feder
#skip-federated
federated
复制

重启数据库生效

[mysql@cjc-db-02 ~]$ mysqladmin -uroot -p shutdown
[mysql@cjc-db-02 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &
复制

检查参数

MySQL [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | NO      | /dev/null storage engine (anything you write to it disappears) | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
复制

二：新增测试数据

远程库：172.16.1.137

create database test1;
create user test1@'172.%' identified by "1";
grant all privileges on test1.* to test1@'172.%';
flush privileges;
use test1;
create table t1(id int,time datetime);
insert into t1 values(1,now()),(2,now()),(3,now()),(4,now()),(5,now());
复制

本地库：创建FEDERATED表，指向远程库

create database test2;
create user test2@'172.%' identified by "1";
grant all privileges on test2.* to test2@'172.%';
flush privileges;
use test2;
create table t1(id int,time datetime) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
复制

其中：创建FEDERATED表语法如下

The format of the connection string is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
复制

参数说明：

1.scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
2.user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
3.password: (Optional) The corresponding password for user_name.
4.host_name: The host name or IP address of the remote server.
5.port_num: (Optional) The port number for the remote server. The default is 3306.
6.db_name: The name of the database holding the remote table.
7.tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.
复制

查询本地库数据

MySQL [test2]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2023-01-11 17:23:48 |
|    2 | 2023-01-11 17:23:48 |
|    3 | 2023-01-11 17:23:48 |
|    4 | 2023-01-11 17:23:48 |
|    5 | 2023-01-11 17:23:48 |
+------+---------------------+
5 rows in set (0.05 sec)
复制

三：数据同步测试

---insert

172.16.1.137 远程库

MySQL [test1]> insert into t1 values(6,now());
MySQL [test1]> insert into t1 values(7,now());
MySQL [test1]> insert into t1 values(8,now());
复制

172.16.1.138 本地库，正常同步

MySQL [test2]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2023-01-11 17:23:48 |
|    2 | 2023-01-11 17:23:48 |
|    3 | 2023-01-11 17:23:48 |
|    4 | 2023-01-11 17:23:48 |
|    5 | 2023-01-11 17:23:48 |
|    6 | 2023-01-11 17:40:13 |
|    7 | 2023-01-11 17:40:17 |
|    8 | 2023-01-11 17:40:22 |
+------+---------------------+
8 rows in set (0.02 sec)
复制

---update

172.16.1.137 远程库

MySQL [test1]> update t1 set id=500 where id>=5;
复制

172.16.1.138 本地库，正常同步

MySQL [test2]> select * from t1;
MySQL [test2]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2023-01-11 17:23:48 |
|    2 | 2023-01-11 17:23:48 |
|    3 | 2023-01-11 17:23:48 |
|    4 | 2023-01-11 17:23:48 |
|  500 | 2023-01-11 17:23:48 |
|  500 | 2023-01-11 17:40:13 |
|  500 | 2023-01-11 17:40:17 |
|  500 | 2023-01-11 17:40:22 |
+------+---------------------+
8 rows in set (0.01 sec)
复制

---delete

172.16.1.137 远程库

MySQL [test1]> delete from t1 where id=4;
复制

172.16.1.138 本地库，正常同步

MySQL [test2]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2023-01-11 17:23:48 |
|    2 | 2023-01-11 17:23:48 |
|    3 | 2023-01-11 17:23:48 |
|  500 | 2023-01-11 17:23:48 |
|  500 | 2023-01-11 17:40:13 |
|  500 | 2023-01-11 17:40:17 |
|  500 | 2023-01-11 17:40:22 |
+------+---------------------+
7 rows in set (0.02 sec)
复制

---alter

添加字段

远程库：

alter table t1 add(name char(10));
MySQL [test1]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)


insert into t1 values(6,now(),'a');
复制

本地库：不同步

MySQL [test2]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
复制

本地库，也不支持手动添加字段

MySQL [test2]> alter table t1 add(name char(10));
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
复制

只能重建表

MySQL [test2]> drop table t1;
MySQL [test2]> 
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
复制

MySQL [test2]> select * from t1;
+------+---------------------+------+
| id   | time                | name |
+------+---------------------+------+
|    1 | 2023-01-11 17:23:48 | NULL |
|    2 | 2023-01-11 17:23:48 | NULL |
|    3 | 2023-01-11 17:23:48 | NULL |
|  500 | 2023-01-11 17:23:48 | NULL |
|  500 | 2023-01-11 17:40:13 | NULL |
|  500 | 2023-01-11 17:40:17 | NULL |
|  500 | 2023-01-11 17:40:22 | NULL |
|    6 | 2023-01-11 17:59:01 | a    |
+------+---------------------+------+
8 rows in set (0.05 sec)
复制

修改字段类型

远程库：

alter table t1 modify name char(15);
复制

本地库：不同步

MySQL [test2]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制

删除字段

远程库：

MySQL [test1]> alter table t1 drop column name;
复制

本地库:不同步

MySQL [test2]> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| time  | datetime | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
复制

---truncate

远程库：

MySQL [test1]> truncate table t1;
Query OK, 0 rows affected (0.03 sec)
复制

本地库：表结构不一致时，无法执行查询操作

MySQL [test2]> select * from t1;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'name' in 'field list'' from FEDERATED
复制

重建表

drop table t1;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL
) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
复制

远程库：

重新插入几条新数据库后，再次执行truncate操作。

本地库：正常同步

MySQL [test2]> select * from t1;
Empty set (0.01 sec)
复制

---drop

远程库：

MySQL [test1]> drop table t1;
复制

本地库：不同步，查询报错

MySQL [test2]> select * from t1;
ERROR 1430 (HY000): : 0 :
复制

本地库可以执行哪些操作

---select 可以查询

---insert

本地库：可以insert

MySQL [test2]> insert into t1 values(100,now());
复制

远程库：同步

MySQL [test1]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
......
|  100 | 2023-01-12 15:02:15 |
+------+---------------------+
7 rows in set (0.01 sec)
复制

---update

本地库：可以update

MySQL [test2]> update t1 set id=1000 where id=100;
复制

远程库：同步

MySQL [test1]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
......
| 1000 | 2023-01-12 15:02:15 |
+------+---------------------+
7 rows in set (0.00 sec)
复制

---delete

本地库：可以delete

MySQL [test2]> delete from t1 where id=1;
复制

远程库：同步

MySQL [test1]> select * from t1 where id=1;
复制

---truncate

本地库：可以truncate

MySQL [test2]> truncate table t1;
复制

远程库：同步

MySQL [test1]> select * from t1;
Empty set (0.01 sec)
复制

---alter

本地库：不能alter

MySQL [test2]> alter table t1 add(name char(10));
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
MySQL [test2]> alter table t1 modify id int(12);
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
MySQL [test2]> alter table t1 drop column id;
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
复制

---drop

本地库：可以drop

MySQL [test2]> drop table t1;
Query OK, 0 rows affected (0.00 sec)
复制

远程库：不同步

MySQL [test1]> select * from t1;
+------+---------------------+
| id   | time                |
+------+---------------------+
|  100 | 2023-01-12 15:11:48 |
|  100 | 2023-01-12 15:11:50 |
|  100 | 2023-01-12 15:11:50 |
+------+---------------------+
3 rows in set (0.00 sec)
复制

四：本地库性能如何？

远程库，新建表，添加索引

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制

插入100万数据

MySQL [test1]> insert into t1 values(1,now()),(1,now()),(1,now()),(1,now()),(1,now());
MySQL [test1]> insert into t1 select * from t1;
MySQL [test1]> insert into t1 select * from t1;
...
复制

场景一：count汇总

远程库查询耗时0.6秒

MySQL [test1]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.60 sec)
复制

走索引了

MySQL [test1]> explain  select count(*) from t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | i_t1_id | 5       | NULL | 1046760 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
复制

本地库

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  KEY `i_t1_id` (`id`)
) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
复制

查询耗时2.37秒

MySQL [test2]> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (2.37 sec)
复制

全表扫描，不走索引

MySQL [test2]> explain  select count(*) from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1046760 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.01 sec)
复制

场景2：limilt

远程库：0.0秒

MySQL [test1]> select id from t1 limit 1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
复制

走索引了

MySQL [test1]> explain select id from t1 limit 1;


+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | i_t1_id | 5       | NULL | 1046760 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
复制

本地库：2.08秒

MySQL [test2]> select id from t1 limit 1;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (2.08 sec)
复制

没走索引

MySQL [test2]> explain select id from t1 limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1046760 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.02 sec)
复制

指定索引列才能走索引

MySQL [test2]> select id from t1 where id=0 limit 1;
Empty set (0.01 sec)
MySQL [test2]> explain select id from t1 where id=0 limit 1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | i_t1_id       | i_t1_id | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
复制

五：本地表是否支持事务？

本地表：

MySQL [test2]> select count(*) from t1;
MySQL [test2]> start transaction;
MySQL [test2]> insert into t1 values(1,now());
MySQL [test2]> rollback;
MySQL [test2]> select count(*) from t1;
复制

数据并没有回滚，不支持事务。

参考：

https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html
复制

###chenjuchao 20230113 12:00###

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL federated存储引擎测试

评论

相关阅读