【MySQL8】サブクエリとグループ列の落とし穴：インデックスが使われない問題を回避する方法とは？

2024-06-08

MySQL 8 では、サブクエリにグループ列が含まれている場合、インデックスが使用されないことがあります。これは、パフォーマンスの問題につながる可能性があります。

原因

この問題は、MySQL 8 のクエリオプティマイザの変更によるものです。MySQL 8 では、クエリオプティマイザは、サブクエリにグループ列が含まれている場合、インデックスを使用しない可能性があります。これは、サブクエリが結果セット全体をスキャンする必要があるとオプティマイザが判断するためです。

解決策

この問題を解決するには、以下のいずれかの方法を実行できます。

サブクエリを結合に置き換える

サブクエリを結合に置き換えることで、オプティマイザがインデックスを使用する可能性が高くなります。

SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id
WHERE table2.group_column IN (
  SELECT group_column
  FROM table3
  GROUP BY group_column
);

サブクエリに STRAIGHT_JOIN ヒントを使用する

サブクエリに STRAIGHT_JOIN ヒントを使用することで、オプティマイザがインデックスを使用するように強制できます。

SELECT *
FROM table1
JOIN table2
STRAIGHT_JOIN
ON table1.id = table2.id
WHERE table2.group_column IN (
  SELECT group_column
  FROM table3
  GROUP BY group_column
);

サブクエリに USE_INDEX ヒントを使用する

SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id
WHERE table2.group_column IN (
  SELECT USE_INDEX(group_column_index) group_column
  FROM table3
  GROUP BY group_column
);

MariaDB では、この問題は発生しません。MariaDB のクエリオプティマイザは、サブクエリにグループ列が含まれている場合でも、インデックスを使用することができます。

MySQL 8 でサブクエリにグループ列がある場合、インデックスが使用されないことがあります。この問題は、サブクエリを結合に置き換える、サブクエリに STRAIGHT_JOIN ヒントを使用する、またはサブクエリに USE_INDEX ヒントを使用することで解決できます。MariaDB では、この問題は発生しません。

-- テーブル定義
CREATE TABLE table1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  group_column VARCHAR(255) NOT NULL
);

CREATE TABLE table2 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  foreign_key INT NOT NULL,
  value INT NOT NULL,
  INDEX (foreign_key)
);

CREATE TABLE table3 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  group_column VARCHAR(255) NOT NULL
);

-- データ挿入
INSERT INTO table1 (group_column) VALUES ('A'), ('B'), ('C');

INSERT INTO table2 (foreign_key, value) VALUES (1, 10), (1, 20), (2, 30), (2, 40), (3, 50);

INSERT INTO table3 (group_column) VALUES ('A'), ('B'), ('C');

-- 問題のあるクエリ
SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
JOIN table2
ON table1.id = table2.foreign_key
WHERE table2.foreign_key IN (
  SELECT id
  FROM table3
  GROUP BY group_column
);

このクエリは、table1.group_column ごとに table2.value の合計値を返します。しかし、MySQL 8 は table2 テーブルの foreign_key 列のインデックスを使用しません。

SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
JOIN table2
ON table1.id = table2.foreign_key
JOIN table3
ON table2.foreign_key = table3.id
GROUP BY table1.group_column;

このクエリは、サブクエリを結合に置き換えたものです。この変更により、MySQL 8 は table2 テーブルの foreign_key 列のインデックスを使用する可能性が高くなります。

SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
STRAIGHT_JOIN table2
ON table1.id = table2.foreign_key
WHERE table2.foreign_key IN (
  SELECT id
  FROM table3
  GROUP BY group_column
);

SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
JOIN table2
ON table1.id = table2.foreign_key
WHERE table2.foreign_key IN (
  SELECT USE_INDEX(group_column_index) id
  FROM table3
  GROUP BY group_column
);

-- MariaDB で実行

SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
JOIN table2
ON table1.id = table2.foreign_key
WHERE table2.foreign_key IN (
  SELECT id
  FROM table3
  GROUP BY group_column
);

このクエリは、MariaDB で問題なく実行されます。MariaDB は table2 テーブルの foreign_key 列のインデックスを自動的に使用します。

列の統計情報を更新する

MySQL 8 は、クエリを最適化するために列の統計情報を使用します。統計情報が古かったり、不正確だったりすると、MySQL 8 は誤ったクエリプランを選択する可能性があり、インデックスが使用されない可能性があります。

列の統計情報を更新するには、次のコマンドを使用します。

ANALYZE TABLE table_name;

クエリプランを分析する

MySQL 8 には、EXPLAIN キーワードを使用してクエリプランを分析する機能があります。EXPLAIN を使用すると、MySQL 8 がクエリをどのように実行しようとしているのかを確認できます。

EXPLAIN
SELECT table1.group_column, SUM(table2.value) AS total_value
FROM table1
JOIN table2
ON table1.id = table2.foreign_key
WHERE table2.foreign_key IN (
  SELECT id
  FROM table3
  GROUP BY group_column
);