前言

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

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

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

連接方案，以postgreSql庫為例。

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

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

優(yōu)點

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

缺點

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

代碼執(zhí)行

一些簡單的代碼邏輯，不會有人看不懂吧～～～

postgreSql.js

//鏈接多個數(shù)據(jù)庫，并暴露
const pg = require('pg');
const sqlConfig = {
  testOnePgSql: {
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.111",//數(shù)據(jù)庫ip地址(胡亂寫的，寫自己的庫ip哈)
    port: 5432, // 擴(kuò)展屬性
    max: 20, // 連接池最大連接數(shù)
    idleTimeoutMillis: 3000
  },
  //超島商戶
  testTwoPgSql: {
    //測試數(shù)據(jù)庫
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.112",//數(shù)據(jù)庫ip地址(胡亂寫的，寫自己的庫ip哈)
    port: 5432, // 擴(kuò)展屬性
    max: 20, // 連接池最大連接數(shù)
    idleTimeoutMillis: 3000
  },
  //桃娘商戶
  testThreePgSql: {
    //測試數(shù)據(jù)庫
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.113",//數(shù)據(jù)庫ip地址(胡亂寫的，寫自己的庫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é)束會話
          connection.release();
          return reject(err);
        }
        let result = await pgQuery(sqlSelect, connection);
        // 結(jié)束會話
        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 = `${第一個表查詢語句}`;
  let userList = await PGSQL.select(sqlSelect, "testOnePgSql");
  //獲取對應(yīng)two表的數(shù)據(jù)
  //...邏輯

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

  return result;
};

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

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

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

優(yōu)點

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

缺點

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

相似實現(xiàn)場景

• T+1時間的報表展示；
• 局域網(wǎng)本地數(shù)據(jù)庫信息上報至線上數(shù)據(jù)庫；

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

（極度不建議）

具體思路：

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

執(zhí)行步驟：

• 1.如我現(xiàn)在本地要連接我的阿里云的sys_user表，所以我需要在本地建一個相同字段的表，我取名叫sys_user_copy,并連接到遠(yuǎn)程庫，建好后，我本地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ù)也會跟著變

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

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

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

• 4 建表時加上連接

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

優(yōu)點

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

缺點

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

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

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

最新評論