MySQLのサブクエリ最適化は常に課題となっており、パフォーマンスのボトルネックになることが多いです。MySQLはサブクエリを処理する際、開発者が期待する「内側（サブクエリ）を先に実行し、その結果で外側を駆動する」とは逆の動作をすることがあります。本記事では、実際のケーススタディを通じて、MySQLのサブクエリの挙動と最適化手法を解説します。

ケース1：サブクエリによる長時間ロックと全表スキャン

あるシステムでデータベースの応答が遅延し、トランザクションの更新がブロックされるという障害が発生しました。長時間実行されているクエリを確認したところ、以下のようなサブクエリを含むSELECT文が特定されました。

SELECT o.* 
FROM orders o 
WHERE o.status = '1' 
  AND o.order_id IN (
    SELECT oi.order_id 
    FROM order_items oi 
    WHERE oi.product_name LIKE '%keyword%' 
       OR oi.product_code LIKE '%keyword%'
  ) 
  AND o.category = 'A' 
  AND o.payment_type = '1' 
  AND o.step = '0' 
  AND o.store_code LIKE 'S001%' 
ORDER BY o.priority ASC, o.created_at DESC 
LIMIT 15;

このクエリにより、他のテーブルに対するUPDATE文がブロックされていました。応答性を回復させるため、該当のクエリを強制終了しました。次に、EXPLAINを使用して実行計画を分析します。

EXPLAIN SELECT o.* 
FROM orders o 
WHERE o.status = '1' 
  AND o.order_id IN (
    SELECT oi.order_id 
    FROM order_items oi 
    WHERE oi.product_name LIKE '%keyword%' 
       OR oi.product_code LIKE '%keyword%'
  ) 
  AND o.category = 'A' 
  AND o.payment_type = '1' 
  AND o.step = '0' 
  AND o.store_code LIKE 'S001%' 
ORDER BY o.priority ASC, o.created_at DESC 
LIMIT 15;

実行計画の結果は以下の通りです。

id	select_type	table	type	possible_keys	key	rows	Extra
1	PRIMARY	o	ALL	NULL	NULL	30000	Using where; Using filesort
2	DEPENDENT SUBQUERY	oi	ALL	NULL	NULL	50000	Using where

サブクエリ部分（id=2）で DEPENDENT SUBQUERY が発生し、order_items テーブルに対して全表スキャンが実行されています。まず、適切なインデックスの追加を検討しました。

ALTER TABLE order_items ADD INDEX idx_product (product_name, product_code, order_id);

しかし、以下のエラーが発生しました。

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

テーブル定義を確認すると、product_name が VARCHAR(1000) で定義されており、組み合わせインデックスのサイズ制限を超えていました。

FIELD	TYPE	NULL	KEY	DEFAULT	Extra
ID	VARCHAR(50)	NO	PRI	NULL
ORDER_ID	VARCHAR(50)	YES		NULL
PRODUCT_CODE	VARCHAR(50)	YES		NULL
PRODUCT_NAME	VARCHAR(1000)	YES		NULL

実際のデータ長を確認したところ、最大長は100程度、平均は25程度でした。そのため、カラム定義を VARCHAR(100) に縮小しました。

ALTER TABLE order_items MODIFY COLUMN product_name VARCHAR(100);

再度インデックスを作成し、実行計画を確認しました。

id	select_type	table	type	possible_keys	key	rows	Extra
1	PRIMARY	o	ref	idx_status	idx_status	10000	Using where; Using filesort
2	DEPENDENT SUBQUERY	oi	index	NULL	idx_product	50000	Using where; Using index

インデックスは使用されるようになりましたが、スキャン行数（10000 * 50000）が依然として大きく、パフォーマンスは改善されませんでした。サブクエリ単体の結果を確認すると、該当するデータは0件でした。つまり、サブクエリを先に実行して駆動表とすべきでした。

MySQLのサブクエリにはこのような構造的な弱点があるため、IN 句を JOIN を使用した派生テーブルに書き換えます。

SELECT o.* 
FROM orders o
JOIN (
  SELECT oi.order_id 
  FROM order_items oi 
  WHERE oi.product_name LIKE '%keyword%' 
     OR oi.product_code LIKE '%keyword%'
) AS t2 ON o.order_id = t2.order_id
WHERE o.status = '1' 
  AND o.category = 'A' 
  AND o.payment_type = '1' 
  AND o.step = '0' 
  AND o.store_code LIKE 'S001%' 
ORDER BY o.priority ASC, o.created_at DESC 
LIMIT 15;

書き換え後の実行計画は以下のようになります。

id	select_type	table	type	possible_keys	key	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DERIVED	oi	index	NULL	idx_product	50000	Using where; Using index

DERIVED としてサブクエリが先に実行されるようになり、実行時間はミリ秒単位にまで短縮されました。

ケース2：他DBからの移行時に潜むサブクエリの罠

Oracleなどの他のRDBMSを使用していた開発者は、サブクエリが適切に最適化され、駆動表が自動選択されるという経験を持っています。しかし、その知識をそのままMySQLに適用すると、期待を裏切られることがあります。

例えば、以下のようなクエリを考えます。

SELECT item_id, SUM(sales_amount) AS total_sales
FROM sales_records
WHERE item_id IN (
  SELECT item_id 
  FROM sales_records 
  WHERE created_at >= '2023-01-01 00:00:00'
)
GROUP BY item_id;

このクエリの意図は、「特定日以降に売れた商品IDを抽出し、その商品IDの全期間における売上合計を集計する」というものです。sales_records テーブルには数十万件のデータがあり、サブクエリの結果も多数の重複を含んでいました。

MySQLのオプティマイザは、このサブクエリを「外側のテーブルの各行に対して、内側のサブクエリを毎回実行する」という形に書き換えてしまいます。外側のテーブルが数十万件ある場合、数十万回のサブクエリ実行が発生し、クエリが数時間経過しても完了しないという事態に陥りました。

この問題を解決するため、サブクエリを JOIN に書き換え、さらにサブクエリ内で DISTINCT を使用して結合回数を削減します。

SELECT t2.item_id, SUM(t2.sales_amount) AS total_sales
FROM (
  SELECT DISTINCT item_id 
  FROM sales_records 
  WHERE created_at >= '2023-01-01 00:00:00'
) AS t1
JOIN sales_records t2 ON t1.item_id = t2.item_id
GROUP BY t2.item_id;

この書き換えにより、クエリの実行時間は100ミリ秒以内に改善されました。MySQLでサブクエリを使用する際は、実行計画を必ず確認し、パフォーマンスが劣化する場合は JOIN への書き換えを検討する必要があります。また、テーブル設計段階で必要以上に長い VARCHAR を使用しないことも、インデックスの制約を回避し、パフォーマンスを維持する上で重要な要素となります。