咨询Node.js MySQL库的正确高效使用方式

Hey there! Let's break down how to use the Node.js MySQL library efficiently for your event-driven multi-query scenario, and fix the issues in your code snippets.

First off, never call connect() for every query—this is the biggest mistake in your second code snippet. Repeatedly creating and tearing down database connections kills performance and wastes database server resources completely unnecessarily.

Let's walk through the problems in your code:

Problems in Your Second Snippet #

// Imagine this could be called at any time after execution
function event() {
  if(database != null) {
    database.connect(function(err) { // ❌ Big mistake here
      if (err) throw err;
      database.query("SELECT * FROM customers", function (err, result, fields) {
        if (err) throw err;
        console.log(result);
      });
    });
  }
}

• Calling database.connect() every time the event fires will attempt to re-establish a connection repeatedly. If the connection is already active, this can throw errors or create redundant connections.
• Frequent connection creation/destruction adds massive overhead, especially if your event is triggered often.

Issues in Your Third Snippet (Closer, But Not Perfect) #

Your third snippet moves connect() to initialization—which is the right direction—but it's missing a critical safety net:

function setupDatabase() {
  database = mysql.createConnection({/* config */});
  if(database != null) {
    database.connect(function(err) {
      if (err) throw err;
    });
  }
}

• If the connection drops unexpectedly (e.g., database restart, network blip), your subsequent query() calls will fail because there's no mechanism to reconnect automatically.

Here's how to do it right, with two options depending on your concurrency needs:

1. Single Connection with Auto-Reconnect (For Low-Moderate Concurrency) #

Initialize a single connection once, and add logic to handle unexpected disconnections:

var mysql = require('mysql');
var database;

function setupDatabase() {
  // Create connection object
  database = mysql.createConnection({
    host: token.host,
    user: token.user,
    password: token.password,
    database: token.database,
    port: token.port
  });

  // Establish initial connection
  database.connect(function(err) {
    if (err) {
      console.error('Failed to connect, retrying in 1s:', err);
      setTimeout(setupDatabase, 1000);
      return;
    }
    console.log('Database connected successfully');
  });

  // Handle connection errors to auto-reconnect
  database.on('error', function(err) {
    console.error('Database connection error:', err);
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      // Reconnect if connection is lost
      setupDatabase();
    } else {
      throw err;
    }
  });
}

// Initialize on app start
setupDatabase();

// Event handler - reuse existing connection
function event() {
  if (!database) {
    console.error('Database connection not initialized yet');
    return;
  }

  // No need to call connect() here!
  database.query("SELECT * FROM customers", function (err, result, fields) {
    if (err) {
      console.error('Query failed:', err);
      return;
    }
    console.log(result);
  });
}

2. Connection Pool (For High Concurrency) #

If your event is triggered very frequently, a connection pool is better. It manages a set of reusable connections, avoiding the bottleneck of a single serial connection:

var mysql = require('mysql');
var pool;

function setupDatabase() {
  // Create connection pool
  pool = mysql.createPool({
    connectionLimit: 10, // Adjust based on your database's capacity
    host: token.host,
    user: token.user,
    password: token.password,
    database: token.database,
    port: token.port
  });

  // Verify pool initialization
  pool.getConnection(function(err, connection) {
    if (err) {
      console.error('Pool initialization failed:', err);
      return;
    }
    console.log('Connection pool ready');
    connection.release(); // Return connection to pool
  });
}

setupDatabase();

// Event handler - get connection from pool
function event() {
  if (!pool) {
    console.error('Connection pool not initialized yet');
    return;
  }

  pool.getConnection(function(err, connection) {
    if (err) {
      console.error('Failed to get connection from pool:', err);
      return;
    }

    connection.query("SELECT * FROM customers", function (err, result, fields) {
      // Always release the connection back to the pool, even if the query fails
      connection.release();
      
      if (err) {
        console.error('Query failed:', err);
        return;
      }
      console.log(result);
    });
  });
}

• Never call connect() per query: Reuse a single connection or use a pool.
• Add auto-reconnect logic: For single connections, handle PROTOCOL_CONNECTION_LOST errors to recover from unexpected disconnections.
• Use a pool for high concurrency: Connection pools eliminate the serial execution bottleneck of single connections and manage resources efficiently.

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