Node.jsからMySQLに接続する — mysql2のコネクションプール入門

接続を確認するでは、SHOW PROCESSLIST に Sleep の接続が何本も並んでいる様子を見ました。 あれを作り出していたのが、アプリケーション側のコネクションプールです。

この章では、Node.jsからMySQLに接続し、そのプールを自分の手で組み立てます。

学習者

接続して、クエリを投げて、閉じる。それだけの話じゃないんですか？

その「それだけ」を素直に書くと、リクエストが増えた瞬間に破綻します。なぜ破綻するのかを先に見ましょう。

まず素直に書いてみる

Node.jsからMySQLを使うライブラリは mysql2 が定番です。

npm install mysql2

もっとも単純な形が createConnection です。

import mysql from 'mysql2/promise';
 
const conn = await mysql.createConnection({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'myapp',
});
 
const [rows] = await conn.query('SELECT id, name FROM users WHERE id = ?', [1]);
console.log(rows);
 
await conn.end();

動きます。しかし、これをWebアプリのリクエストハンドラの中に書くとどうなるでしょうか。

// これをやってはいけない
app.get('/users/:id', async (req, res) => {
  const conn = await mysql.createConnection(config); // 毎回作る
  const [rows] = await conn.query('SELECT * FROM users WHERE id = ?', [req.params.id]);
  await conn.end(); // 毎回閉じる
  res.json(rows);
});

MySQLとは何かで見た通り、接続の確立にはTCPのハンドシェイクと認証の往復があります。

このコードは、たった1行のSELECTのために、毎回その往復を丸ごと払っています。

さらに悪いことに、同時アクセスが増えれば増えるだけ接続が同時に作られます。100件同時に来れば100本。max_connections の151はあっという間です。

コネクションプールという解き方

あらかじめDBサーバーへのTCP接続を複数確立して保持しておき、SQLを実行するときに未使用の接続を取得して使用する。

これで、最初に挙げた2つの問題が同時に解けます。

createPool

書き方は createConnection とほとんど変わりません。

構文： mysql.createPool(オプション)

import mysql from 'mysql2/promise';
 
export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  waitForConnections: true,
  queueLimit: 0,
});

戻り値： Pool オブジェクト（await は不要。この時点ではまだ接続していない）

主なオプション

接続情報（host など）に加えて、プールの挙動を決めるオプションがあります。

いちばん重要なのは connectionLimit です。ここを 1アプリケーションプロセスが同時に持てる接続の本数 として捉えてください。

先生

waitForConnections は true のままでいい。false にすると、混雑した瞬間にリクエストがエラーになる。待たせたほうがマシな場面がほとんどだよ。

クエリを投げる

プールから直接クエリを投げられます。

構文： pool.query(sql[, values])

戻り値： [rows, fields] の配列。rows が結果行、fields が列のメタ情報

const [rows] = await pool.query('SELECT id, name FROM users WHERE id = ?', [userId]);

第1要素だけ使うことがほとんどなので、const [rows] = ... と分割代入で受けるのが定番です。

query と execute の違い

mysql2 にはよく似た2つのメソッドがあります。

同じSQLを繰り返し実行するなら execute に利があります。まずは execute を既定にして、動的にSQL構造そのものが変わる箇所だけ query を使う、という整理で困りません。

トランザクションでは接続を固定する

ここがこの章でもっとも重要な部分です。

学習者

pool.query が接続を自動でやってくれるなら、トランザクションもこう書けますよね？

// 動かない
await pool.query('BEGIN');
await pool.query('UPDATE accounts SET balance = balance - 1000 WHERE id = 1');
await pool.query('UPDATE accounts SET balance = balance + 1000 WHERE id = 2');
await pool.query('COMMIT');

このコードは、SQLとしては完全に正しいのに壊れます。

理由は、いま確認した pool.query の性質にあります。1回ごとに接続を借りて返しているので、4本のSQLがそれぞれ別の接続で実行される可能性があるのです。

MySQLのトランザクションは接続ごとに独立しています。接続1で BEGIN しても、接続2から見ればトランザクションは始まっていません。 結果として、2本のUPDATEはトランザクションの外で即座に確定し、ROLLBACK しても戻りません。送金元だけ減って送金先が増えない、という最悪の壊れ方をします。

正しくは getConnection で接続を取り出します。

構文： pool.getConnection()

戻り値： PoolConnection（release() で必ず返却する必要がある）

const conn = await pool.getConnection();
try {
  await conn.beginTransaction();
 
  await conn.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [1000, 1]);
  await conn.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [1000, 2]);
 
  await conn.commit();
} catch (err) {
  await conn.rollback();
  throw err;
} finally {
  conn.release();
}

ポイントは3つです。

1. conn を使い回す — pool ではなく取り出した接続に対してクエリを投げる
2. catch で rollback — 例外時に確実に巻き戻す
3. finally で release — 成功でも失敗でも必ず返す

connectionLimit をいくつにするか

「大きいほど速い」ではありません。ここは max_connections と直結します。

計算はシンプルです。

connectionLimit × アプリのプロセス数 ≦ max_connections（既定151）

たとえば connectionLimit: 10 のアプリを4インスタンス動かせば40本。ここに管理ツールやバッチ、監視、手元の mysql コマンドが乗ります。

アイドル接続が切られる問題

接続を確認するで見た wait_timeout（既定8時間）を思い出してください。サーバーは、長時間使われない接続を一方的に切ります。

プールはそれを知らないので、切れた接続を「まだ使える」と思って保持し続けます。次にそれを借りたリクエストがエラーになります。

Error: Connection lost: The server closed the connection.

mysql2 の idleTimeout（既定60000ミリ秒＝60秒）は、まさにこの対策です。サーバーが切るより先に、プール側からアイドル接続を閉じてしまうという考え方です。

既定の60秒は8時間よりはるかに短いので、通常はそのままで問題ありません。設定を触るときだけ、この関係を崩していないか確認してください。

よくあるハマりどころ

リクエストごとに createPool してしまう

// これでは意味がない
app.get('/users', async (req, res) => {
  const pool = mysql.createPool(config); // リクエストごとに新しいプール
  const [rows] = await pool.query('SELECT * FROM users');
  res.json(rows);
});

プールはアプリケーション全体で1つ作り、モジュールとして共有します。リクエストごとに作ると、createConnection を毎回呼んでいたのと同じか、それ以上に悪い状態になります。

プロセスが終了しない

スクリプトやバッチで、処理が終わったのにプロセスが終了しないことがあります。プールが接続を保持したままだと、Node.jsはイベントループが空にならないと判断します。

await pool.end();

接続を返す前に return してしまう

// release されないパスがある
const conn = await pool.getConnection();
const [rows] = await conn.query('SELECT * FROM users');
if (rows.length === 0) return null; // ここで漏れる
conn.release();

早期リターン、例外、throw——release() の手前で抜けるパスがひとつでもあると接続は漏れます。try/finally はこれを構造的に防ぐための書き方です。

ちゃんと使うためのポイント

• リクエストごとの createConnection は、確立コストと接続数の両方で破綻する
• プールはアプリケーション全体で1つだけ作り、共有する
• createPool は接続を張らない。接続は必要になってから作られる
• connectionLimit の既定は10。connectionLimit × プロセス数 ≦ max_connections で見積もる
• 単発のクエリは pool.query / pool.execute でよい
• トランザクションは getConnection で接続を固定し、try/catch/finally で commit / rollback / release を書く
• idleTimeout はサーバーの wait_timeout より十分短く保つ

参考リンク

• mysql2 — createPool の例
• mysql2 ドキュメント
• MySQL 8.4 Reference Manual — Too many connections

接続処理を本番へ出したあとも、テーブル定義は変化します。スキーマ変更とマイグレーションでは、ALTER TABLE の方式と、旧版・新版を共存させるリリース手順を扱います。