MySQL IN ステートメントにおける低速クエリの効率を最適化する手法の例

表の構造は以下のとおりです。記事数は690件のみです。

記事テーブル article(id,title,content)
タグテーブル tag(tid,tag_name)
タグ記事中間テーブル article_tag (id, tag_id, article_id)

tidが135のタグがあります。タグtidが135の記事リストを検索してください。

690 件の記事、次のクエリを使用、非常に遅い:

id が次の条件に該当する記事から id、title を選択します (
tag_id=135 の article_tag から article_id を選択
）

これは非常に高速です:

tag_id=135 の article_tag から article_id を選択

クエリの結果は、ID 428、429、430、431、432 の 5 つの記事です。

次の SQL を使用して記事を検索するのもおすすめです。

id が次の条件に該当する記事から id、title を選択します (
428,429,430,431,432
）

解決：

id が次の条件に該当する記事から id、title を選択します (
(tag_id=135 の article_tag から article_id を選択) から article_id を tbt として選択します。
）

その他の解決策: (例)

mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

スペースを節約するため、出力内容は省略します。以下も同様です。

セット内67行（12.00秒）

返されたデータは 67 行だけでしたが、12 秒かかりました。システムには同時にこのようなクエリが多数存在する可能性があり、システムは間違いなくそれを処理できないでしょう。 desc を使用して表示します (注: explain も OK です)

mysql> desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');
+----+--------------------+------------------+--------+-----------------+---------+-----------+----------+--------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------------+------------------+--------+-----------------+---------+-----------+----------+--------------------------+
| 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | where の使用 |
| 2 | 従属サブクエリ | abc_number_phone | eq_ref | phone,number_id | phone | 70 | const,func | 1 | where の使用; index の使用 |
+----+--------------------+------------------+--------+-----------------+---------+-----------+----------+--------------------------+
セット内の 2 行 (0.00 秒)

このクエリを実行すると、200万行以上がスキャンされることがわかります。これはインデックスが作成されていないためでしょうか？見てみましょう

mysql>abc_number_phone からインデックスを表示します。
+------------------+------------+-------------+--------------+-----------------+-------------+----------+--------+-------+-----------+--------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+------------------+------------+-------------+--------------+-----------------+-------------+----------+--------+-------+-----------+--------------+
| abc_number_phone | 0 | PRIMARY | 1 | number_phone_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 0 | 電話 | 1 | 電話 | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 0 | 電話 | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | NULL | NULL | YES | BTREE | | |
+------------------+------------+-------------+--------------+-----------------+-------------+----------+--------+-------+-----------+--------------+
セット内の6行（0.06秒）
mysql>abc_number_prop からインデックスを表示します。
+-----------------+-------------+--------------+--------------+----------------+-------------+-----------+---------+----------+----------+----------+---------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+-----------------+-------------+--------------+--------------+----------------+-------------+-----------+---------+----------+----------+----------+---------------+
| abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | NULL | NULL | YES | BTREE | | |
+-----------------+-------------+--------------+--------------+----------------+-------------+-----------+---------+----------+----------+----------+---------------+
4 行セット (0.15 秒)

上記の出力からわかるように、これら 2 つのテーブルには number_id フィールドにインデックスが作成されています。
サブクエリ自体に問題がないか確認してください。

mysql> desc select number_id from abc_number_phone where phone = '82306839';
+----+-------------+------------------+-------+---------------+--------+-------+------+--------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+------------------+-------+---------------+--------+-------+------+--------------------------+
| 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | where の使用; index の使用 |
+----+-------------+------------------+-------+---------------+--------+-------+------+--------------------------+
セット内の 1 行 (0.00 秒)

問題ありません。数行のデータをスキャンするだけで、インデックスが機能します。

それをチェックしてください：

mysql> abc_number_phone から number_id を選択します。ここで、phone = '82306839' です。
+-----------+
| 番号ID |
+-----------+
| 8585 |
| 10720 |
|148644|
|151307|
|170691|
|221897|
+-----------+
セット内の 6 行 (0.00 秒)

サブクエリから取得したデータを上記のクエリに直接入力します

mysql> abc_number_prop から * を選択します。ここで、 number_id は (8585, 10720, 148644, 151307, 170691, 221897) です。
セット内の行数は 67 行 (0.03 秒)

速度も速いです。サブクエリを扱う場合、MySQL では不十分なようです。 MySQL 5.1.42 と MySQL 5.5.19 の両方でこれを試しましたが、どちらでもこの問題が発生しました。

インターネットで検索したところ、多くの人がこの問題に遭遇していることがわかりました。

参考 1: MySQL の最適化: サブクエリの代わりに結合を使用する

参考2: MYSQLサブクエリとネストされたクエリの最適化例の分析

これらのオンライン資料の提案に従って、代わりに join を使用してみてください。
変更前:

abc_number_prop から * を選択します。ここで number_id は (abc_number_phone から number_id を選択します。ここで phone = '82306839')。

変更後:

abc_number_prop から a.* を選択します。a.number_id = b.number_id で、phone = '82306839' である abc_number_phone b を内部結合します。
mysql> abc_number_prop から a.* を選択します。 abc_number_phone b は a.number_id = b.number_id で、phone = '82306839' になります。
セット内の行数は 67 です (0.00 秒)

効果は良好で、クエリ時間はほぼ0です。 MySQL がこのクエリをどのように実行するかを見てみましょう。

mysql>desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';
+----+-------------+-------+-------+-----------------+-----------+---------+----------------+------+---------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-----------------+-----------+---------+----------------+------+---------------------------+
| 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | where の使用; index の使用 |
| 1 | シンプル | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | |
+----+-------------+-------+-------+-----------------+-----------+---------+----------------+------+---------------------------+
セット内の 2 行 (0.00 秒)

概要: サブクエリが遅い場合は、JOIN を使用してクエリを書き換えて最適化することができます。

JOIN ステートメントを使用するクエリは、必ずしもサブクエリを使用するクエリよりも高速であるとは限らないというオンライン記事もあります。

MySQL マニュアルにもこれについて記載されており、具体的な元のテキストは MySQL ドキュメントのこの章にあります。
I.3. サブクエリの制限
13.2.8. サブクエリ構文

抜粋：

1) IN を使用したサブクエリについて:

IN のサブクエリの最適化は、= 演算子や IN(value_list) 構造の場合ほど効果的ではありません。

IN サブクエリのパフォーマンスが低下する典型的なケースは、サブクエリが少数の行を返すのに対し、外部クエリがサブクエリの結果と比較する多数の行を返す場合です。

問題は、IN サブクエリを使用するステートメントの場合、オプティマイザがそれを相関サブクエリとして書き換えることです。相関のないサブクエリを使用する次のステートメントを考えてみましょう。

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

オプティマイザーは、ステートメントを相関サブクエリに書き換えます。

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

内部クエリと外部クエリがそれぞれ M 行と N 行を返す場合、実行時間は、相関のないサブクエリの場合の O(M+N) ではなく、O(M×N) のオーダーになります。

つまり、IN サブクエリは、サブクエリが返すのと同じ値をリストする IN(value_list) 構造を使用して記述されたクエリよりもはるかに遅くなる可能性があります。

2) サブクエリを結合に変換する方法について:

オプティマイザーはサブクエリよりも結合に対して成熟しているため、多くの場合、サブクエリを使用するステートメントは結合として書き換えるとより効率的に実行できます。

IN サブクエリを SELECT DISTINCT 結合として書き換えることができる場合は例外が発生します。例:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

この文は次のように書き直すことができます。

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

しかし、この場合、結合には追加のDISTINCT操作が必要となり、サブクエリよりも効率的ではありません。

要約する

以上が、MySQL のステートメントサブクエリの効率低下に対する最適化手法に関するこの記事の内容のすべてです。ご興味がある方は、「MySQL のサブクエリユニオンの効率に関する簡単な説明」、「エンタープライズプロダクション MySQL の最適化の紹介」などを参照してください。ご質問がある場合は、メッセージを残してください。どなたでもコミュニケーションして参照できます。

この記事がお役に立てば幸いです。

以下もご興味があるかもしれません: