如何为单MySQL实例多用户库的API管理数据库连接？

在单MySQL实例中，每个用户对应独立数据库，Node.js API需要根据调用用户切换到对应数据库，以下是两种官方方案的优劣势分析及其他可选思路：

方案一：全局连接池 + changeUser 切换数据库 #

示例代码 #

const pool = createPool({
    connectionLimit: 10,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    port: 3306,
    host: process.env.SQL_INSTANCE_HOST,
})

const connection = await pool.getConnection();
try {
  await connection.changeUser({ database: userDatabase });
  // 执行数据库操作
} catch (err) {
  throw new Error(`Could not connect to database: ${err}`);
} finally {
  connection.release();
}

优势 #

• 连接复用率高：全局共享一个连接池，总连接数可控，不会因用户数量多导致连接数爆炸。
• 配置成本低：只需维护一套基础连接配置，无需为每个用户单独设置。

劣势 #

• 性能损耗：每次获取连接后都要执行changeUser切换数据库，额外增加网络开销和数据库操作耗时。
• 安全风险：如果连接释放前未切回默认数据库，后续被其他用户复用该连接时，可能访问到错误的数据库；若使用统一的数据库账号，该账号需拥有所有用户库的权限，权限过大易引发数据泄露风险。
• 事务兼容性差：changeUser无法在事务执行过程中调用，若业务涉及事务操作，会导致报错或事务中断。

方案二：PoolCluster 为每个数据库创建独立连接池 #

示例代码 #

const poolCluster = mysql.createPoolCluster();
poolCluster.add('USER1', config1);
poolCluster.add('USER2', config2);
poolCluster.add('USER3', config3);

// 根据用户获取对应连接池并执行查询
const pool = poolCluster.of('USER1');
pool.query('SELECT * FROM table', function (error, results, fields) {
  // 处理查询结果
});

优势 #

• 性能更优：每个数据库对应独立连接池，无需切换操作，直接复用对应库的连接。
• 权限精细化：可为每个用户配置独立的数据库账号，实现最小权限访问，降低安全风险。
• 事务友好：连接与数据库绑定，事务操作不会因切换库受到干扰，稳定性更高。
• 配置灵活性：可针对不同用户的业务量，单独调整连接池的connectionLimit等参数。

劣势 #

• 连接数失控风险：若用户数量较多，每个连接池都维持一定数量的连接，总连接数可能超出MySQL实例的max_connections限制。
• 内存占用高：多个连接池会占用更多内存资源，尤其在用户规模大的场景下更明显。
• 配置繁琐：需提前为每个用户添加连接配置，若用户动态新增，需额外处理配置的动态更新。

其他可选方案 #

1. 动态按需创建连接池 #

维护一个以用户标识为key的Map，存储已创建的连接池。当用户首次发起请求时，动态创建对应数据库的连接池并加入Map；设置闲置超时机制，长时间未使用的连接池自动销毁，避免资源浪费。示例伪代码：

const poolMap = new Map();

function getPoolForUser(userId, dbConfig) {
  if (!poolMap.has(userId)) {
    const pool = createPool({
      ...dbConfig,
      database: `db_${userId}`
    });
    poolMap.set(userId, pool);
    // 设置超时销毁逻辑
    setTimeout(() => {
      if (poolMap.get(userId) === pool) {
        pool.end();
        poolMap.delete(userId);
      }
    }, 3600000); // 1小时闲置后销毁
  }
  return poolMap.get(userId);
}

2. 基于ORM框架的动态数据源切换 #

使用Sequelize、TypeORM等ORM框架，它们支持多数据源配置与动态切换。以Sequelize为例，可为每个用户创建独立的Sequelize实例：

// 动态创建用户对应的Sequelize实例
function getSequelizeForUser(userId) {
  return new Sequelize({
    database: `db_${userId}`,
    username: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    host: process.env.SQL_INSTANCE_HOST,
    dialect: 'mysql'
  });
}

3. 连接时直接指定数据库 #

在创建连接时直接指定目标数据库，而非事后切换。这种方式本质上和每个库一个连接池类似，但可以结合动态创建逻辑，避免提前配置所有用户：

const pool = createPool({
    connectionLimit: 10,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    port: 3306,
    host: process.env.SQL_INSTANCE_HOST,
    // 不指定默认database，每次获取连接时指定
})

// 获取连接时直接指定数据库
const connection = await pool.getConnection({ database: userDatabase });

内容的提问来源于stack exchange，提问作者niklash