iT邦幫忙

0

多個資料庫如何有效的進行查詢 ?

問題

因目前系統架構資料都存在本地端
(大概有 50 幾家, 資料結構都一樣)
資料管理是使用 MYSQL

目前想要製作各家數據的統計
但因為架構的關係
不知道如何下手

想到的解決方案

有先自行架設一台資料庫,也是使用 MYSQL
這台主機有安裝 Navicat , 裡面有個功能是 數據同步
定期會同步相關資料到這台主機
(所以建立了大概 50 幾家 資料庫)

  1. 簡單粗暴的方法使用 SQL
    (不過這個效能似乎沒有非常好)

  2. 使用 ETL 把多個資料庫合併成一個資料庫
    (目前也在研究這塊)

不知道這兩條思路是否正確?
或者各位前輩有更好的解決方法嗎?

dragonH iT邦超人 5 級 ‧ 2021-02-16 17:14:50 檢舉
datalake

2 個回答

4
ckp6250
iT邦研究生 1 級 ‧ 2021-02-16 16:50:41
最佳解答

簡單粗暴的方法使用 SQL(不過這個效能似乎沒有非常好)

您是如何地簡單粗暴使用SQL?不妨先描述一下。

資料結構都一樣,資料管理是使用 MYSQL

既然都已經同步到一台主機上了,為何還要合併呢?一段精巧的 Stored Procedure 不能勝任嗎?

看更多先前的回應...收起先前的回應...

您是如何地簡單粗暴使用SQL?不妨先描述一下。

select *
from a1.user
union all
select *
from a2.user
union all
select *
from a3.user

具體的實現大致是這樣子的

既然都已經同步到一台主機上了,為何還要合併呢?一段精巧的 Stored Procedure 不能勝任嗎?

沒想到要使用 Stored Procedure

一段精巧的 Stored Procedure , 前輩能否大致描述一下呢? 感謝

ckp6250 iT邦研究生 1 級 ‧ 2021-02-16 19:35:52 檢舉
select *
from a1.user
union all
select *
from a2.user
union all
select *
from a3.user

這個寫法不行哦,萬一有1000個資料庫,不就寫到天昏地暗?
又,每次有增減資料庫時,語法又得修改,難怪您會說效能似乎沒有非常好

我先提示一下,

SELECT
	TABLE_SCHEMA 
FROM
	`information_schema`.`TABLES` 
WHERE
	`TABLES`.TABLE_NAME = 'user'

這樣您就可以得知,總共有幾個資料庫中有user這個資料表了,這是動態的,不必考慮日後增減問題。

您先研究一下information_schema這個特殊資料庫,它能提供很多您需要的東西。

謝謝說明
看起來要多點時間來研究一下了
感謝給方向

剛剛想到的大致上的思路
使用 Stored Procedure
根據 information_schema WHERE 某個表
寫 SQL 跑 FOR
產生 VIEW

ckp6250 iT邦研究生 1 級 ‧ 2021-02-17 10:16:17 檢舉

所見略同。

ckp6250 iT邦研究生 1 級 ‧ 2021-02-17 11:29:27 檢舉

剛好我也用的上,順便寫一支備用,您可以參考改寫。

CREATE DEFINER=`marco`@`%` PROCEDURE `getCombineData`(IN `vDbFilter` varchar(100),IN `vTableName` varchar(100),IN `vFields` varchar(255),IN vTableWhere varchar(100) , IN `vSortFields` varchar(100))
    READS SQL DATA
    COMMENT '取得多表資料'
BEGIN
	declare Sqlcmd text;
	/* 先過濾待作業的資料庫群組,將取得之資料庫群放入 @DbList 變數中 */
	set Sqlcmd = concat("SELECT group_concat(`TABLE_SCHEMA`) INTO @DbList FROM information_schema.`TABLES` WHERE ",vDbFilter , " AND `TABLE_NAME` = '",vTableName,"'");
	PREPARE QUERY FROM Sqlcmd;
	EXECUTE QUERY;

	/* 組合 SQL 語句 */
	SELECT
		group_concat(concat( 'SELECT ' , vFields , ' FROM ', '`', TABLE_SCHEMA, '`.`',
		TABLE_NAME, '` WHERE ' , vTableWhere , ' union all ' ) SEPARATOR ' ') into Sqlcmd
	FROM
		information_schema.`TABLES`
	WHERE
		FIND_IN_SET(TABLE_SCHEMA,@DbList)
		AND TABLE_NAME = vTableName;
	/* 去除最後多餘的 union all , 並加上 order by */
	set Sqlcmd = concat(substr(Sqlcmd,1,length(Sqlcmd)-11) , ' order by ' , vSortFields);
	PREPARE QUERY FROM Sqlcmd;
	EXECUTE QUERY;
END

使用時,

call getCombineData("length(TABLE_SCHEMA)=8","comcustomer","custid,cname","cname<>''","custid")

感謝前輩提供參考~我再研究看看

2

一般這得看你的需求性而定。

如果資料統計不需要即時性,最小時間單位為一日的情況下。
大多數來說,我會比較推額外統計表來處理。

也就是用排程還是函式庫的方式。
每日各程式資料庫,自動跟總統計庫送統計資料。
但這招一般安全點的結算期最小安全時間最好是一日為單位。
如果要做成即時性的就不準。

第二招,資料對應處理。
做好處理的話,也可以做為總統計性處理。
也就是個別主表在更新與統計有相關的資料發送時。
也同步對總統計表做發送處理。

這招的優點是可以做到即時性查尋。
但缺點則是程式規劃要很好。一個沒搞好可能會操死你的機器。

ckp6250 iT邦研究生 1 級 ‧ 2021-02-16 21:23:30 檢舉

如果做多源同步的話,再加上完善的 stored procedure ,
就能兼顧這二點了。

目前需求是不需要即時的
但未來不一定

另外多源同步是設定排程(每一天)

ckp6250 iT邦研究生 1 級 ‧ 2021-02-17 10:14:18 檢舉

多源同步是即時的。

我要發表回答

立即登入回答