はじめに
SQLを処理する際、 MySQL はまずクエリの構文解析や最適化を行い、その結果として「実行計画」を生成します。この実行計画には、どのインデックスを使うか、テーブルをどの順で結合するかといった情報が含まれます。通常、同じクエリが繰り返し実行される場合、都度この計画を作り直すのは非効率です。そこで登場するのが「実行計画のキャッシュ」です。特にPREPARE文を用いたプレースホルダ形式のSQLでは、 MySQL が生成した実行計画を再利用できるため、パフォーマンスが安定しやすくなります。
本記事では、実行計画のキャッシュが MySQL のパフォーマンスにどのような影響を与えるかを、単純な SELECT * FROM orders WHERE user_id = ? を用いて検証しました。
MySQL は8.4.5を利用しています。
検証の目的
- リテラルSQLとプレースホルダSQL（PREPARE + EXECUTE）のパフォーマンス差を測定
- performance_schema.events_statements_summary_by_digest による実行統計を比較
テストデータの準備
検証用に orders テーブルを作成し、10万件のダミーデータを挿入しました。user_id は 1〜10000 をローテーションして割り当てています。
-- 既存テーブルがあれば削除
DROP TABLE IF EXISTS orders;
-- orders テーブルの作成
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_name VARCHAR(100),
order_date DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- インデックスの追加（必須）
CREATE INDEX idx_user_id ON orders(user_id);
-- データ挿入（10万件）
SET @uid = 0;
INSERT INTO orders (user_id, product_name)
SELECT
(@uid := @uid + 1) % 10000 + 1 AS user_id,
CONCAT('Product-', FLOOR(1 + RAND() * 100))
FROM
information_schema.columns AS a,
information_schema.columns AS b
LIMIT 100000;
-- 統計情報の更新
ANALYZE TABLE orders;
検証環境と方法
- orders テーブルを TRUNCATE し、10万件のデータを挿入
- ANALYZE TABLE orders を実行し統計情報を更新
- リテラル形式のSELECTを1000回実行
- digest統計を確認・記録
- events_statements_summary_by_digest を TRUNCATE
- PREPARE + SET + EXECUTE形式で同様に1000回実行
- digest統計を再度記録
検証に使用したSQL
以下はそれぞれ1000回ずつ実行したSQLのサンプルです。
リテラルSQL（1000回）
-- 例：user_id を毎回変えて1000回実行
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
...
SELECT * FROM orders WHERE user_id = 1000;
プレースホルダSQL（1000回）
-- 実行前にPREPARE
PREPARE stmt FROM 'SELECT * FROM orders WHERE user_id = ?';
-- 変数を代入してEXECUTE
SET @uid = 1;
EXECUTE stmt USING @uid;
SET @uid = 2;
EXECUTE stmt USING @uid;
SET @uid = 3;
EXECUTE stmt USING @uid;
...
SET @uid = 1000;
EXECUTE stmt USING @uid;
※PREPAREは1回だけでOKですが、SETとEXECUTEは毎回繰り返します。
プレースホルダSQL（PREPARE文＋EXECUTE文）は、実行計画のキャッシュ対象となります。
一度PREPAREされたSQLは、異なるバインド変数（@uid）に対しても同じ実行計画が再利用され、
SQL解析・最適化のコストを軽減できます。詳細は
MySQL公式ドキュメント: SQL Syntax for Prepared Statements を参照してください。
実行結果
| クエリ形式 | 構成要素 | COUNT | total_time_sec | avg_time_sec |
|---|---|---|---|---|
| リテラルSQL | SELECT * FROM orders WHERE user_id = … | 1000 | 0.3740 | 0.0004 |
| プレースホルダSQL | SET @uid = ? | 1000 | 0.1259 | 0.0001 |
| EXECUTE stmt USING @uid | 1000 | 0.3448 | 0.0003 | |
| 合計（プレースホルダ） | SET + EXECUTE | 2000 | 0.4707 | – |
考察
- EXECUTE単体ではプレースホルダの方が速い（avg 0.0003秒 vs 0.0004秒）
- ただし、SETのコストを含めると、プレースホルダ全体の処理時間は約26%遅くなった
- 実行計画キャッシュ自体は効いており、EXECUTEが高速だったのはその証拠
- 今回のような単純なクエリでは、プレースホルダの恩恵が薄い可能性もある
MySQL まとめ
MySQLにおける実行計画キャッシュの効果は、SQLの再利用性に依存します。今回はプレースホルダ形式によって実行計画が再利用されていることが確認できましたが、SETのオーバーヘッドにより、単純なクエリではリテラルSQLの方が速くなる場合もあります。
今後はより複数テーブルの結合クエリでのキャッシュ効果を検証することで、実運用に近いケースでの有用性を確認していきます。
次回のテーマ
多段階JOINを含むSQLでのキャッシュ再利用
コメントを残す