使用MySQL federated 引擎構建 MySQL 分散式資料庫訪問層

前言：隨著應用複雜度的增加，資料庫不斷細化切分，導致應用程式中資料庫應用就得複雜，淩亂。絕大部分程式人員可能都遇到這種情況，應用程式中需要串連多台資料庫伺服器，進行相應的操作。隨著時間積累，太多的資料庫伺服器的串連邏輯出現在程式之中，這給程式的維護擴充，資料庫維護工作帶來極大的工作量。

於是一些分散式資料庫代理層應運而生，如常見 MySQL 代理層 :

mysql proxy : 主要實現讀寫分離和負載平衡

MySQL Amoeba : 由陳思儒主導開發　功能比較完善，用深入應用的價值。

HiveDB : HiveDB是一個用來橫向切分 mysql 資料庫的開源架構，構建一個高效能和可擴充的基於 mysql 的系統，但目前僅支援 Java 用戶端。

我認為mysql proxy, MySQL Amoeba 都是極好的實用價值，應該多深入瞭解之。

而本文所描述的 federated屬於 MySQL的一種特殊引擎，利用它可將本機資料表映射至遠程 MySQL 資料表，從而就可以解決應用程式中繁多的跨機器串連資料庫問題，拓撲圖如下：

如此就可以構造出一個統一的資料訪問入口，就大大提高了整個資料庫系統的可維護性。

Federated引擎是基於表層級的，只能將本機資料表定義為 Federated 引擎並映射至遠程實體表，無法實現基於庫層級的整體映射。

在本文中，我們將啟用Federated 引擎的資料庫訪問入口伺服器稱為本機資料庫，而將本機資料表對應的遠端資料表，稱之為實體表。

本機資料庫需要啟用Federated 引擎支援，而遠端資料表無須 Federated 引擎支援。 Federated 引擎表使用標準的 MySQL 用戶端協議與遠端資料庫建立 TCP 串連。

建立Federated 表的過程：

1. 　以root 登入遠程 MySQL ，上建立合適的訪問帳號

  grant all on DB1.* to 'federated'@'%' identified by 'federated';

flush privileges;

2.   在遠程MySQL 找到對應實體表的建立命令（如果是新表，請先建立好資料表，再執行此命令）

假設在遠程mysql 上有庫名 DB1,  表名 tag,  執行以下命令找到遠端資料表的結構：

show create table DB1.tag

輸出：

CREATE TABLE `tag` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) NOT NULL,

`frequency` int(10) unsigned NOT NULL DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

3.  假設我們要將遠端DB1.tag 映射至本地 DB.TableA 表上。那麼我們應該保持本地虛擬表與遠程實體表結構一致（結構可以有所差異，但會造成使用，管理上的麻煩）。根據遠程實體表的建立命令，建立本地虛擬表 ( 結構部分完全一樣，建立表選項有所差異 ) ：

登入本地Mysql 伺服器，建立相應的資料庫及表：

create database DB;

use DB;

CREATE TABLE `TableA` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) NOT NULL,

`frequency` int(10) unsigned NOT NULL DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=federated connection="mysql://federated:federated@127.0.0.1:3306/DB1/tag";

這時，即建立好了federated 虛擬表，實際上本地 MySQL 只建立了表定義檔案 , 而沒有資料檔案。我們對本地虛擬表的資料修改，均會發送到遠程機器上執行。

本地虛擬表名與遠端資料表名，可不相同。

經過測試，這個引擎的一些額外特點：

1.　本地虛擬表與遠程實體表之間是 TCP 長串連，並且是多個用戶端利用的。所以不用擔心因頻繁建立串連帶來的網路開銷。

2.  本虛擬表表與遠程實體表之間的網路連接斷開後，當對虛擬表發起查詢時，它會嘗試重新串連遠程實體表，所以我們不用擔心網路連接斷開造成的永久中斷問題。

3.   如果無時間未對本地虛擬表作任何操作，虛擬表與實體表之間的串連將在遠程主機的 wait_timeout 秒後自動斷開，當對虛擬表發起查詢時，串連又會重建立立。

一些注意事項：

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 在實際環境中的應用。