MySQL federated引擎试验

亦凉 2022-01-15 02:47 237阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

如果写的不对的地方，欢迎各位提意见。  
可以在数据非常大的时候起到分发表或者库到不同的服务器。减少每个服务器的IO。  
首先看看有没有federated 引擎。  
mysql> show engines;  
\+------------+----------+----------------------------------------------------------------+  
| Engine     | Support  | Comment                                                        |  
\+------------+----------+----------------------------------------------------------------+  
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |  
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |  
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys     |  
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |  
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |  
| EXAMPLE    | YES      | Example storage engine                                         |  
| ARCHIVE    | YES      | Archive storage engine                                         |  
| CSV        | YES      | CSV storage engine                                             |  
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |  
| FEDERATED  | YES      | Federated MySQL storage engine                                 |  
| MRG\_MYISAM | YES      | Collection of identical MyISAM tables                          |  
| ISAM       | NO       | Obsolete storage engine                                        |  
\+------------+----------+----------------------------------------------------------------+  
12 rows in set (0.00 sec)  
  
A(192.168.0.233:3306)  
B(192.168.0.233:3307)  
C(192.168.0.233:3308)  
D(192.168.0.234:3306)  
在A、B、C、D 分别创建数据库。  
mysql> create database t\_boy;  
Query OK, 1 row affected (0.00 sec)  
mysql> use t\_boy;  
Database changed  
在B、C上分别创建授权用户。  
mysql> grant all privileges on t\_boy.\* to root@'%' identified by '123456';  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)  
在B主机上：  
mysql> create table t\_tableB (id int not null auto\_increment primary key, c\_str char(20) not null) engine myisam;  
Query OK, 0 rows affected (0.00 sec)  
mysql> show tables;  
\+-----------------+  
| Tables\_in\_t\_boy |  
\+-----------------+  
| t\_table         |  
\+-----------------+  
1 row in set (0.00 sec)  
在C主机上：  
mysql> create table t\_tableC (id int not null auto\_increment primary key, c\_str char(20) not null) engine myisam;  
Query OK, 0 rows affected (0.00 sec)  
mysql> show tables;  
\+-----------------+  
| Tables\_in\_t\_boy |  
\+-----------------+  
| t\_table         |  
\+-----------------+  
1 row in set (0.00 sec)  
  
关于创建FEDERATED引擎的方法详细见手册。  
在A主机上：  
  
`mysql> create table t_1 (id int not null auto_increment primary key, c_str char(20) not null) engine federated connection = 'mysql://root:123456@192.168.0.233:3307/t_boy/t_tableB'; Query OK, 0 rows affected (0.04 sec) mysql> create table t_2 (id int not null auto_increment primary key, c_str char(20) not null) engine federated connection = 'mysql://root:123456@192.168.0.233:3308/t_boy/t_tableC'; Query OK, 0 rows affected (0.03 sec)`  
`mysql> insert into t_1(c_str) values (rand()); Query OK, 1 row affected (0.53 sec) mysql> insert into t_2(c_str) values (rand()); Query OK, 1 row affected (0.03 sec)`  
可以像在一个主机上进行操作。  
  
`mysql> select t_1.*,t_2.* from t_1 inner join t_2 using(id); +----+----------------+----+------------------+ | id | c_str | id | c_str | +----+----------------+----+------------------+ | 1 | 0.304819039353 | 1 | 0.24238659184648 | +----+----------------+----+------------------+ 1 row in set (0.00 sec) 插入百万级别的数据后 mysql> select t_1.*,t_2.* from t_1 inner join t_2 using(id) limit 20; +----+----------------------+----+----------------------+ | id | c_str | id | c_str | +----+----------------------+----+----------------------+ | 1 | 0.304819039353 | 1 | 0.24238659184648 | | 2 | 1.304819039353 | 2 | 1.2423865918465 | | 3 | 1.304819039352999920 | 3 | 1.242386591846480037 | | 4 | 2.304819039353000143 | 4 | 2.242386591846500021 | | 5 | 1.304819039352999920 | 5 | 1.242386591846480037 | | 6 | 2.304819039353000143 | 6 | 2.242386591846500021 | | 7 | 2.304819039353000143 | 7 | 2.242386591846480037 | | 8 | 3.304819039353000143 | 8 | 3.242386591846500021 | | 9 | 1.304819039352999920 | 9 | 1.242386591846480037 | | 10 | 2.304819039353000143 | 10 | 2.242386591846500021 | | 11 | 2.304819039353000143 | 11 | 2.242386591846480037 | | 12 | 3.304819039353000143 | 12 | 3.242386591846500021 | | 13 | 2.304819039353000143 | 13 | 2.242386591846480037 | | 14 | 3.304819039353000143 | 14 | 3.242386591846500021 | | 15 | 3.304819039353000143 | 15 | 3.242386591846480037 | | 16 | 4.304819039353000143 | 16 | 4.242386591846500465 | | 17 | 1.304819039352999920 | 17 | 1.242386591846480037 | | 18 | 2.304819039353000143 | 18 | 2.242386591846500021 | | 19 | 2.304819039353000143 | 19 | 2.242386591846480037 | | 20 | 3.304819039353000143 | 20 | 3.242386591846500021 | +----+----------------------+----+----------------------+ 20 rows in set (0.73 sec) mysql> select max(id),min(id) from t_1; +---------+---------+ | max(id) | min(id) | +---------+---------+ | 1048576 | 1 | +---------+---------+ 1 row in set (1.40 sec) mysql> select max(id),min(id) from t_2; +---------+---------+ | max(id) | min(id) | +---------+---------+ | 1048576 | 1 | +---------+---------+ 1 row in set (1.40 sec) mysql> update t_2 set id = id +1048576; Query OK, 1048576 rows affected (2 min 10.38 sec) Rows matched: 1048576 Changed: 1048576 Warnings: 0 mysql> select max(id),min(id) from t_2; +---------+---------+ | max(id) | min(id) | +---------+---------+ | 2097152 | 1048577 | +---------+---------+ 1 row in set (1.63 sec) mysql> explain select id c_str from t_1 where id between 1 and 1048576; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |  1 | SIMPLE      | t_1   | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)`  
  
在D主机上  
`mysql> create table t(id int not null auto_increment primary key, c_str char(20)); Query OK, 0 rows affected (0.00 sec) 插入两百万级别数据后。 mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 2097152 | +----------+ 1 row in set (0.00 sec) mysql> analyze table t; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | t_boy.t | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.37 sec) mysql> explain select id c_str from t where id between 1 and 1048576;`  
\+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+  
| id | select\_type | table | type  | possible\_keys | key     | key\_len | ref  | rows    | Extra                    |  
\+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+  
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL | 1132065 | Using where; Using index |  
\+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+  
1 row in set (0.00 sec)  
  
和上面A主机上的查询相比，扫描的行数大减。  
  
插入的速度主要靠网络。  
看看A主机上的文件  
\[root [@localhost][localhost] t\_boy\]\# cd /usr/local/mysql/data/t\_boy/  
\[root [@localhost][localhost] t\_boy\]\# ls -sihl  
total 40K  
15892559 8.0K -rw-rw---- 1 mysql mysql   61 Feb 28 11:03 db.opt  
15892560  16K -rw-rw---- 1 mysql mysql 8.4K Feb 28 11:11 t\_1.frm  
15892561  16K -rw-rw---- 1 mysql mysql 8.4K Feb 28 11:14 t\_2.frm  
\[root [@localhost][localhost] t\_boy\]\#  
  
这里只有表的定义而没有数据。  
  
手册中关于FEDERATED引擎的介绍：  
[\[url\]http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html\#federated-storage-engine\[/url\]][url_http_dev.mysql.com_doc_refman_5.1_zh_storage-engines.html_federated-storage-engine_url]

本文出自 “[上帝，咱们不见不散！][Link 1]” 博客，转载请与作者联系！

转载于:https://my.oschina.net/u/585111/blog/219515

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/fb170da83cb8475b85a47ce122fbc8b4.png
[localhost]: http://my.oschina.net/u/570656
[url_http_dev.mysql.com_doc_refman_5.1_zh_storage-engines.html_federated-storage-engine_url]: http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine
[Link 1]: http://yueliangdao0608.blog.51cto.com