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;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

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;

+---------+---------+----------+----------+

+---------+---------+----------+----------+

+---------+---------+----------+----------+

1 row in set (0.37 sec)

mysql> explain select id c_str from t where id between 1 and 1048576;

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+---------+--------------------------+

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.　truncate 指令，會清除遠端表資料，同理修改虛拟表的資料會同步到源表
3. drop指令隻會删除虛拟表，并不會删除遠端表
4. 不支援 alter table 指令
目前使用federated 最大的缺點：
1. select count(*), select * from limit M, N 等語句執行效率非常低，資料量較大時存在很嚴重的問題，但是按主鍵或索引列查詢，則很快，如以下查詢就非常慢（假設 id 為主索引）
select id from db.tablea where id >100 limit 10 ;
而以下查詢就很快：
select id from db.tablea where id >100 and id<150
2. 如果虛拟虛拟表中字段未建立索引，而實體表中為此字段建立了索引，此種情況下，性能也相當差。但是當給虛拟表建立索引後，性能恢複正常。
3. 類似 where name like "str%" limit 1 的查詢，即使在 name 列上建立了索引，也會導緻查詢過慢，是因為
federated引擎會将所有滿足條件的記錄讀取到本，再進行 limit 處理。
這幾個問題已經嚴重影響了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/

MySQL federated引擎試驗（DBLINK的作用）

注意：

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述