#yyds干货盘点#sql跨表查询的三种方案

时间：2022-10-02 20:00:13

最近又个朋友问我，如何进行sql的跨库关联查询？ 首先呢，我们知道mysql是不支持跨库连接的，但是老话说得好，只要思想不滑坡，办法总比困难多！

​

方案一：连接多个库，同步执行查询

具体思路为在代码中分别连接多个库，查到一个库中所需要的数据之后，通过关键字段，同步执行去其他的库中进行查询相关数据，然后进行需要的数据分析或更新！

优点

  • 可以进行实时查询；
  • 可对数据进行按需修改及逻辑范围内的修改返回值；
  • 一般采用此方案，查询数据会分页查询，或查询条件精确，从而量会比较小，对服务器压力小；
  • 服务器静态分析数据，效率高；

缺点

  • 不适合进行大批数据写入/查询，会造成数据库连接超时或获取的数据流过大导致服务器内存被大量占用；
  • 同步执行策略，查询数据库用时和运行时间成正比；

代码执行

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时间的报表展示；
  • 局域网本地数据库信息上报至线上数据库；
标签：

相关文章