sql跨表查詢的三種方案總結

2022-08-11 14:00:14

前言

最近又個朋友問我，如何進行sql的跨庫關聯查詢？首先呢，我們知道mysql是不支援跨庫連線的，但是老話說得好，只要思想不滑坡，思想總比困難多！

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

經過一番思考我給他提出了三個方案，雖然都不盡善盡美，但各領風騷！

連線方案，以postgreSql庫為例。

方案一：連線多個庫，同步執行查詢

具體思路為在程式碼中分別連線多個庫，查到一個庫中所需要的資料之後，通過關鍵欄位，同步執行去其他的庫中進行查詢相關資料，然後進行需要的資料分析或更新！

優點

可以進行實時查詢；
可對資料進行按需修改及邏輯範圍內的修改返回值；
一般採用此方案，查詢資料會分頁查詢，或查詢條件精確，從而量會比較小，對伺服器壓力小；
伺服器靜態分析資料，效率高；

缺點

不適合進行大批資料寫入/查詢，會造成資料庫連線超時或獲取的資料流過大導致伺服器記憶體被大量佔用；
同步執行策略，查詢資料庫用時和執行時間成正比；

程式碼執行

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

postgreSql.js

//連結多個資料庫，並暴露
const pg = require('pg');
const sqlConfig = {
  testOnePgSql: {
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.111",//資料庫ip地址(胡亂寫的，寫自己的庫ip哈)
    port: 5432, // 擴充套件屬性
    max: 20, // 連線池最大連線數
    idleTimeoutMillis: 3000
  },
  //超島商戶
  testTwoPgSql: {
    //測試資料庫
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.112",//資料庫ip地址(胡亂寫的，寫自己的庫ip哈)
    port: 5432, // 擴充套件屬性
    max: 20, // 連線池最大連線數
    idleTimeoutMillis: 3000
  },
  //桃娘商戶
  testThreePgSql: {
    //測試資料庫
    user: "postgres",
    database: "admindb",
    password: "123",
    host: "192.168.1.113",//資料庫ip地址(胡亂寫的，寫自己的庫ip哈)
    port: 5432, // 擴充套件屬性
    max: 20, // 連線池最大連線數
    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資料
   * @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) {
          // 結束對談
          connection.release();
          return reject(err);
        }
        let result = await pgQuery(sqlSelect, connection);
        // 結束對談
        connection.release();
        return resolve(result);
      });
    });
  }

};
/**
 * pgsql查詢資料
 * @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 || []);
    });
  });
}

現在進行業務模組

test.js

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

  let sqlOneSelect = `${第一個表查詢語句}`;
  let userList = await PGSQL.select(sqlSelect, "testOnePgSql");
  //獲取對應two表的資料
  //...邏輯

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

  return result;
};

方案二：在主資料庫增加冗餘表，通過定時更新，造成同庫聯表查詢

比如A庫為主資料庫，B、C為其他的增項庫，我們需要將三個庫中的user表進行資料聯表查詢；具體思路為：

在A庫存在user表，此時建立冗餘表user_two、user_three表，並欄位對應B、C庫的user表欄位；
通過程式碼邏輯，進行定時任務，將B、C表，資料更新至A庫user_two、user_three表；
在需要資料分析/查詢時，僅查詢A庫即可，但需要將A庫的user、user_two、user_three表進行按需取用；

優點

化跨表查詢為同表查詢，執行邏輯更為簡單；
可進行巨量資料分析和巨量資料查詢；
可以預處理資料，提高分析速率；

缺點

定時更新，不具備及時性；
需要對應表有最後更新時間欄位，否則同步資料會比較多；
增加冗餘表，會造成主表空間佔用率增加；
定時更新，會導致某一時間點有大量資料寫入/修改資料，可能會影響資料讀取，因此，建議多節點部署（讀寫、唯讀）；

相似實現場景

T+1時間的報表展示；
區域網本地資料庫資訊上報至線上資料庫；

方案三：dbLink本地連線多個庫，在本地進行資料分析

（極度不建議）

具體思路：

dblink就是我們在建立表的時候連線到我們的遠端庫，然後我們本地新建的表資料就是對映遠端的表的資料。
當我們建立一個以FEDERATED為儲存引擎的表時，伺服器在資料庫目錄只建立一個表定義檔案。檔案由表的名字開始，並有一個frm擴充套件名。無其它檔案被建立，因為實際的資料在一個遠端資料庫上。這不同於為本地表工作的儲存引擎的方式。

執行步驟：

1.如我現在本地要連線我的阿里雲的sys_user表，所以我需要在本地建一個相同欄位的表，我取名叫sys_user_copy,並連線到遠端庫，建好後，我本地sys_user_copy的表裡面的資料是對映遠端的表的資料

2.所以我關聯查詢，可以直接關聯我本地sys_user_copy表從而查出來。改了原生的資料，遠端的表資料也會跟著變

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

如果這裡是NO，需要在組態檔[mysqld]中加入一行：federated

改完重啟服務，就變成yes了。

4 建表時加上連線

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

優點

不需要程式設計師介入，不需要開發
快速形成結果，如果只想查詢一些資料的話

缺點

本地表結構必須與遠端表完全一樣
不支援事務
不支援表結構修改
刪除本地表，遠端表不會刪除
遠端伺服器必須是一個MySQL伺服器
並不會在本地寫入資料庫資料，實質上是一個軟連線，查詢大量資料會導致本地記憶體爆滿，因為是查詢多個資料庫的資料到本地記憶體，然後在記憶體中進行計算，此時時間複雜度為O(N^2)，空間複雜度也為O(N^2)；500條資料，對應本地時間複雜度為25W，時間複雜度為25W;

可用於：兩庫之間資料匯入，不涉及計算，即A匯入B,不進行查詢AB進行計算寫入C；

到此這篇關於sql跨表查詢的三種方案總結的文章就介紹到這了,更多相關sql跨表查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！