MySQL federated引擎试验（DBLINK的作用）

原文地址：http://yueliangdao0608.blog.51cto.com/397025/81606/

如果写的不对的地方，欢迎各位提意见。

可以在数据非常大的时候起到分发表或者库到不同的服务器。减少每个服务器的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 [email protected]'%' 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:[email protected]: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:[email protected]: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主机上的文件

[[email protected] t_boy]# cd /usr/local/mysql/data/t_boy/

[[email protected] 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

[[email protected] t_boy]# 

这里只有表的定义而没有数据。

注意：

1. 一些注意事项：
2. 1.　对本地虚拟表的结构修改，并不会修改远程表的结构
3. 2.　truncate 命令，会清除远程表数据 ，同理修改虚拟表的数据会同步到源表
4. 3. drop命令只会删除虚拟表，并不会删除远程表
5. 4. 不支持 alter table 命令
6. 目前使用federated 最大的缺点：
7. 1. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）
8. select id from db.tablea where id >100 limit 10 ;
9. 而以下查询就很快：
10. select id from db.tablea where id >100 and id<150
11. 2. 如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。
12. 3. 类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为
13. federated引擎会将所有满足条件的记录读取到本，再进行 limit 处理。
14. 这几个问题已经严重影响了federated 在实际环境中的应用。

手册中关于FEDERATED引擎的介绍：

[url]http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine[/url]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1872139/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1872139/