前言

最近又個(gè)朋友問我，如何進(jìn)行sql的跨庫(kù)關(guān)聯(lián)查詢？ 首先呢，我們知道m(xù)ysql是不支持跨庫(kù)連接的，但是老話說得好，只要思想不滑坡，思想總比困難多！

PS: 問題擺在這里了，還能不解決是怎么的？

經(jīng)過一番思考我給他提出了三個(gè)方案，雖然都不盡善盡美，但各領(lǐng)風(fēng)騷！

連接方案，以postgreSql庫(kù)為例。

方案一：連接多個(gè)庫(kù)，同步執(zhí)行查詢

具體思路為在代碼中分別連接多個(gè)庫(kù)，查到一個(gè)庫(kù)中所需要的數(shù)據(jù)之后，通過關(guān)鍵字段，同步執(zhí)行去其他的庫(kù)中進(jìn)行查詢相關(guān)數(shù)據(jù)，然后進(jìn)行需要的數(shù)據(jù)分析或更新！

優(yōu)點(diǎn)

• 可以進(jìn)行實(shí)時(shí)查詢；
• 可對(duì)數(shù)據(jù)進(jìn)行按需修改及邏輯范圍內(nèi)的修改返回值；
• 一般采用此方案，查詢數(shù)據(jù)會(huì)分頁查詢，或查詢條件精確，從而量會(huì)比較小，對(duì)服務(wù)器壓力??；
• 服務(wù)器靜態(tài)分析數(shù)據(jù)，效率高；

缺點(diǎn)

• 不適合進(jìn)行大批數(shù)據(jù)寫入/查詢，會(huì)造成數(shù)據(jù)庫(kù)連接超時(shí)或獲取的數(shù)據(jù)流過大導(dǎo)致服務(wù)器內(nèi)存被大量占用；
• 同步執(zhí)行策略，查詢數(shù)據(jù)庫(kù)用時(shí)和運(yùn)行時(shí)間成正比；

代碼執(zhí)行

一些簡(jiǎn)單的代碼邏輯，不會(huì)有人看不懂吧～～～

postgreSql.js

//鏈接多個(gè)數(shù)據(jù)庫(kù)，并暴露
const pg = require('pg');
const sqlConfig = {
  testOnePgSql: {
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.111",//數(shù)據(jù)庫(kù)ip地址(胡亂寫的，寫自己的庫(kù)ip哈)
    port: 5432, // 擴(kuò)展屬性
    max: 20, // 連接池最大連接數(shù)
    idleTimeoutMillis: 3000
  },
  //超島商戶
  testTwoPgSql: {
    //測(cè)試數(shù)據(jù)庫(kù)
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.112",//數(shù)據(jù)庫(kù)ip地址(胡亂寫的，寫自己的庫(kù)ip哈)
    port: 5432, // 擴(kuò)展屬性
    max: 20, // 連接池最大連接數(shù)
    idleTimeoutMillis: 3000
  },
  //桃娘商戶
  testThreePgSql: {
    //測(cè)試數(shù)據(jù)庫(kù)
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.113",//數(shù)據(jù)庫(kù)ip地址(胡亂寫的，寫自己的庫(kù)ip哈)
    port: 5432, // 擴(kuò)展屬性
    max: 20, // 連接池最大連接數(shù)
    idleTimeoutMillis: 3000
  },
};
const testOnePgSql = new pg.Pool(sqlConfig.banuPgSql);
const testTwoPgSql = new pg.Pool(sqlConfig.testTwoPgSql);
const testThreePgSql = new pg.Pool(sqlConfig.testThreePgSql);
module.exports = {
  testOnePgSql,
  testTwoPgSql,
  testThreePgSql
};

封裝查詢pgsql方法

postgreSqlClass.js

let sqlMap = require('./postgreSql');
module.exports = {
  /**
   *查詢pgsql數(shù)據(jù)
   * @param sqlSelect 查詢語句 string
   * @param tenancy 商戶id string
   */
  select(sqlSelect, tenancy) {
    //按需連接
    let pool = sqlMap[tenancy];
    return new Promise((resolve, reject) => {
      pool.connect(async function (err, connection) {
        if (err) {
          // 結(jié)束會(huì)話
          connection.release();
          return reject(err);
        }
        let result = await pgQuery(sqlSelect, connection);
        // 結(jié)束會(huì)話
        connection.release();
        return resolve(result);
      });
    });
  }

};
/**
 * pgsql查詢數(shù)據(jù)
 * @param sqlQuery 查詢語句
 * @param connection pgSql連接后的connection
 * @returns {Promise<unknown>}
 */
async function pgQuery(sqlQuery, connection) {
  return new Promise((resolve, reject) => {
    connection.query(sqlQuery, (err, rows) => {
      if (err) return reject(err);
      return resolve(rows.rows || []);
    });
  });
}

現(xiàn)在進(jìn)行業(yè)務(wù)模塊

test.js

  "use strict";
//引入pg函數(shù)
let PGSQL = require("./postgreSqlClass");
exports.getUserList = async () => {

  let sqlOneSelect = `${第一個(gè)表查詢語句}`;
  let userList = await PGSQL.select(sqlSelect, "testOnePgSql");
  //獲取對(duì)應(yīng)two表的數(shù)據(jù)
  //...邏輯

  let sqlTwoSelect = `${第一個(gè)表查詢語句}`;
  let userListTwo = await PGSQL.select(sqlTwoSelect, "testTwoPgSql");
  let result = [];
  //組合你想要的數(shù)據(jù)
  //...邏輯

  return result;
};

方案二：在主數(shù)據(jù)庫(kù)增加冗余表，通過定時(shí)更新，造成同庫(kù)聯(lián)表查詢

比如A庫(kù)為主數(shù)據(jù)庫(kù)，B、C為其他的增項(xiàng)庫(kù)，我們需要將三個(gè)庫(kù)中的user表進(jìn)行數(shù)據(jù)聯(lián)表查詢； 具體思路為：

• 在A庫(kù)存在user表，此時(shí)創(chuàng)建冗余表user_two、user_three表，并字段對(duì)應(yīng)B、C庫(kù)的user表字段；
• 通過代碼邏輯，進(jìn)行定時(shí)任務(wù)，將B、C表，數(shù)據(jù)更新至A庫(kù)user_two、user_three表；
• 在需要數(shù)據(jù)分析/查詢時(shí)，僅查詢A庫(kù)即可，但需要將A庫(kù)的user、user_two、user_three表進(jìn)行按需取用；

優(yōu)點(diǎn)

• 化跨表查詢?yōu)橥聿樵?，?zhí)行邏輯更為簡(jiǎn)單；
• 可進(jìn)行大數(shù)據(jù)分析和大數(shù)據(jù)查詢；
• 可以預(yù)處理數(shù)據(jù)，提高分析速率；

缺點(diǎn)

• 定時(shí)更新，不具備及時(shí)性；
• 需要對(duì)應(yīng)表有最后更新時(shí)間字段，否則同步數(shù)據(jù)會(huì)比較多；
• 增加冗余表，會(huì)造成主表空間占用率增加；
• 定時(shí)更新，會(huì)導(dǎo)致某一時(shí)間點(diǎn)有大量數(shù)據(jù)寫入/修改數(shù)據(jù)，可能會(huì)影響數(shù)據(jù)讀取，因此，建議多節(jié)點(diǎn)部署（讀寫、只讀）；

相似實(shí)現(xiàn)場(chǎng)景

• T+1時(shí)間的報(bào)表展示；
• 局域網(wǎng)本地?cái)?shù)據(jù)庫(kù)信息上報(bào)至線上數(shù)據(jù)庫(kù)；

方案三：dbLink本地連接多個(gè)庫(kù)，在本地進(jìn)行數(shù)據(jù)分析

（極度不建議）

具體思路：

• dblink就是我們?cè)趧?chuàng)建表的時(shí)候連接到我們的遠(yuǎn)程庫(kù)，然后我們本地新建的表數(shù)據(jù)就是映射遠(yuǎn)程的表的數(shù)據(jù)。
• 當(dāng)我們創(chuàng)建一個(gè)以FEDERATED為存儲(chǔ)引擎的表時(shí)，服務(wù)器在數(shù)據(jù)庫(kù)目錄只創(chuàng)建一個(gè)表定義文件。文件由表的名字開始，并有一個(gè)frm擴(kuò)展名。無其它文件被創(chuàng)建，因?yàn)閷?shí)際的數(shù)據(jù)在一個(gè)遠(yuǎn)程數(shù)據(jù)庫(kù)上。這不同于為本地表工作的存儲(chǔ)引擎的方式。

執(zhí)行步驟：

• 1.如我現(xiàn)在本地要連接我的阿里云的sys_user表，所以我需要在本地建一個(gè)相同字段的表，我取名叫sys_user_copy,并連接到遠(yuǎn)程庫(kù)，建好后，我本地sys_user_copy的表里面的數(shù)據(jù)是映射遠(yuǎn)程的表的數(shù)據(jù)

• 2.所以我關(guān)聯(lián)查詢，可以直接關(guān)聯(lián)我本地sys_user_copy表從而查出來。改了本地的數(shù)據(jù)，遠(yuǎn)程的表數(shù)據(jù)也會(huì)跟著變

**開啟FEDERATED引擎，**show engines

 如果這里是NO，需要在配置文件[mysqld]中加入一行：federated 

改完重啟服務(wù)，就變成yes了。

• 4 建表時(shí)加上連接

CREATE TABLE (......)
ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'

優(yōu)點(diǎn)

• 不需要程序員介入，不需要開發(fā)
• 快速形成結(jié)果，如果只想查詢一些數(shù)據(jù)的話

缺點(diǎn)

• 本地表結(jié)構(gòu)必須與遠(yuǎn)程表完全一樣
• 不支持事務(wù)
• 不支持表結(jié)構(gòu)修改
• 刪除本地表，遠(yuǎn)程表不會(huì)刪除
• 遠(yuǎn)程服務(wù)器必須是一個(gè)MySQL服務(wù)器
• 并不會(huì)在本地寫入數(shù)據(jù)庫(kù)數(shù)據(jù)，實(shí)質(zhì)上是一個(gè)軟連接，查詢大量數(shù)據(jù)會(huì)導(dǎo)致本地內(nèi)存爆滿，因?yàn)槭遣樵兌鄠€(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)到本地內(nèi)存，然后在內(nèi)存中進(jìn)行計(jì)算，此時(shí)時(shí)間復(fù)雜度為O(N^2)，空間復(fù)雜度也為O(N^2)；500條數(shù)據(jù)，對(duì)應(yīng)本地時(shí)間復(fù)雜度為25W，時(shí)間復(fù)雜度為25W;

可用于：兩庫(kù)之間數(shù)據(jù)導(dǎo)入，不涉及計(jì)算，即A導(dǎo)入B,不進(jìn)行查詢A\B進(jìn)行計(jì)算寫入C；

到此這篇關(guān)于sql跨表查詢的三種方案總結(jié)的文章就介紹到這了,更多相關(guān)sql跨表查詢 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論