使用MySQL federated 引擎構建 MySQL 分布式數(shù)據(jù)庫訪問層

前言:隨著應用復雜度的增加,數(shù)據(jù)庫不斷細化切分,導致應用程序中數(shù)據(jù)庫應用就得復雜,凌亂。絕大部分程序人員可能都遇到這種情況,應用程序中需要連接多臺數(shù)據(jù)庫服務器,進行相應的操作。隨著時間積累,太多的數(shù)據(jù)庫服務器的連接邏輯出現(xiàn)在程序之中,這給程

前言:隨著應用復雜度的增加,數(shù)據(jù)庫不斷細化切分,導致應用程序中數(shù)據(jù)庫應用就得復雜,凌亂。絕大部分程序人員可能都遇到這種情況,應用程序中需要連接多臺數(shù)據(jù)庫服務器,進行相應的操作。隨著時間積累,太多的數(shù)據(jù)庫服務器的連接邏輯出現(xiàn)在程序之中,這給程序的維護擴展,數(shù)據(jù)庫維護工作帶來極大的工作量。

于是一些分布式數(shù)據(jù)庫代理層應運而生,如常見 mysql 代理層 :

mysql proxy : 主要實現(xiàn)讀寫分離和負載均衡

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

HiveDB : HiveDB是一個用來橫向切分 mysql 數(shù)據(jù)庫的開源框架,構建一個高性能和可擴展的基于 mysql 的系統(tǒng),但目前僅支持 Java 客戶端。

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

而本文所描述的 federated屬于 MySQL的一種特殊引擎,利用它可將本地數(shù)據(jù)表映射至遠程 MySQL 數(shù)據(jù)表,從而就可以解決應用程序中繁多的跨機器連接數(shù)據(jù)庫問題,拓撲圖如下:

如此就可以構造出一個統(tǒng)一的數(shù)據(jù)訪問入口,就大大提高了整個數(shù)據(jù)庫系統(tǒng)的可維護性。

Federated引擎是基于表級別的,只能將本地數(shù)據(jù)表定義為 Federated 引擎并映射至遠程實體表,無法實現(xiàn)基于庫級別的整體映射。

在本文中,我們將啟用Federated 引擎的數(shù)據(jù)庫訪問入口服務器稱為本地數(shù)據(jù)庫,而將本地數(shù)據(jù)表對應的遠程數(shù)據(jù)表,稱之為實體表。

本地數(shù)據(jù)庫需要啟用Federated 引擎支持,而遠程數(shù)據(jù)表無須 Federated 引擎支持。 Federated 引擎表使用標準的 MySQL 客戶端協(xié)議與遠程數(shù)據(jù)庫建立 TCP 連接。

創(chuàng)建Federated 表的過程:

1.  以root 登錄遠程 MySQL ,上創(chuàng)建合適的訪問賬號

? grant all on DB1.* to ‘federated’@’%’ identified by ‘federated’;

flush privileges;

2.?? 在遠程MySQL 找到對應實體表的創(chuàng)建命令(如果是新表,請先建立好數(shù)據(jù)表,再執(zhí)行此命令)

假設在遠程mysql 上有庫名 DB1,? 表名 tag,? 執(zhí)行以下命令找到遠程表的結構:

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 表上。那么我們應該保持本地虛擬表與遠程實體表結構一致(結構可以有所差異,但會造成使用,管理上的麻煩)。根據(jù)遠程實體表的創(chuàng)建命令,創(chuàng)建本地虛擬表 ( 結構部分完全一樣,創(chuàng)建表選項有所差異 ) :

登錄本地Mysql 服務器,創(chuàng)建相應的數(shù)據(jù)庫及表:

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 只創(chuàng)建了表定義文件 , 而沒有數(shù)據(jù)文件。我們對本地虛擬表的數(shù)據(jù)修改,均會發(fā)送到遠程機器上執(zhí)行。

本地虛擬表名與遠程表名,可不相同。

經(jīng)過測試,這個引擎的一些額外特點:

1. 本地虛擬表與遠程實體表之間是 TCP 長連接,并且是多個客戶端利用的。所以不用擔心因頻繁建立連接帶來的網(wǎng)絡開銷。

2.? 本虛擬表表與遠程實體表之間的網(wǎng)絡連接斷開后,當對虛擬表發(fā)起查詢時,它會嘗試重新連接遠程實體表,所以我們不用擔心網(wǎng)絡連接斷開造成的永久中斷問題。

3.?? 如果無時間未對本地虛擬表作任何操作,虛擬表與實體表之間的連接將在遠程主機的 wait_timeout 秒后自動斷開,當對虛擬表發(fā)起查詢時,連接又會重新建立。

一些注意事項:

1. 對本地虛擬表的結構修改,并不會修改遠程表的結構

2.  truncate 命令,會清除遠程表數(shù)據(jù)

3.? drop命令只會刪除虛擬表,并不會刪除遠程表

4.? 不支持 alter table 命令

目前使用federated 最大的缺點:

1. select count(*), select * from limit M, N 等語句執(zhí)行效率非常低,數(shù)據(jù)量較大時存在很嚴重的問題,但是按主鍵或索引列查詢,則很快,如以下查詢就非常慢(假設 id 為主索引)

select id from db.tablea where id >100 limit 10 ;

而以下查詢就很快:

select id from db.tablea where id >100 and id
2.? 如果虛擬虛擬表中字段未建立索引,而實體表中為此字段建立了索引,此種情況下,性能也相當差。但是當給虛擬表建立索引后,性能恢復正常。

3. 類似 where name like “str%” limit 1 的查詢,即使在 name 列上創(chuàng)建了索引,也會導致查詢過慢,是因為

federated引擎會將所有滿足條件的記錄讀取到本,再進行 limit 處理。

這幾個問題已經(jīng)嚴重影響了federated 在實際環(huán)境中的應用。

? 版權聲明
THE END
喜歡就支持一下吧
點贊12 分享