具体的にクエリチューニングを行ってみます。

スキーマの用意

データベース作成

CREATE DATABASE mydb;
USE mydb

テーブルおよびインデックス作成

CREATE TABLE t1 (id INT PRIMARY KEY);

CREATE TABLE t2 (id INT);
ALTER TABLE t2 ADD INDEX myindex(id);

CREATE TABLE t3 (id_md5 VARCHAR(32) PRIMARY KEY, myval INT);

データの流し込み

RAND 関数は引数の seed が同じであれば同じ結果を返します。

t1

i=1; while [ $i -lt 10000 ]; do mysql -uroot mydb -e "
  INSERT INTO t1 VALUES(CEIL(RAND($i)*1000000000));
"; i=`expr $i + 1`; done

t2

i=1; while [ $i -lt 10000 ]; do mysql -uroot mydb -e "
  INSERT INTO t2 VALUES(CEIL(RAND($i)*1000000000));
  INSERT INTO t2 VALUES(CEIL(RAND(10000 - $i)*1000000000));
"; i=`expr $i + 1`; done

t3

i=1; while [ $i -lt 10000 ]; do mysql -uroot mydb -e "
  INSERT INTO t3 VALUES(MD5(CEIL(RAND(10000 - $i)*1000000000)), FLOOR(RAND()*10));
"; i=`expr $i + 1`; done

検証対象となるクエリ

SELECT t3.myval, _t2.cnt, COUNT(*)

FROM t1

LEFT JOIN
  (SELECT id, COUNT(*) AS cnt FROM t2 GROUP BY id) AS _t2
  ON t1.id = _t2.id

LEFT JOIN
  t3
  ON MD5(t1.id) = t3.id_md5

GROUP BY
  t3.myval, _t2.cnt;

EXPLAIN

+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY     | t1         | index  | NULL          | PRIMARY | 4       | NULL | 10141 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL |  9999 |                                              |
|  1 | PRIMARY     | t3         | eq_ref | PRIMARY       | PRIMARY | 98      | func |     1 |                                              |
|  2 | DERIVED     | t2         | index  | NULL          | myindex | 5       | NULL | 19351 | Using index                                  |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
4 rows in set (0.00 sec)

SHOW FULL PROCESSLIST 抜粋

| State                |
| Copying to tmp table |

クエリ結果

+-------+------+----------+
| myval | cnt  | count(*) |
+-------+------+----------+
|     0 |    2 |     1020 |
|     1 |    2 |      997 |
|     2 |    2 |      954 |
|     3 |    2 |     1022 |
|     4 |    2 |      998 |
|     5 |    2 |     1058 |
|     6 |    2 |      974 |
|     7 |    2 |     1033 |
|     8 |    2 |      914 |
|     9 |    2 |     1029 |
+-------+------+----------+
10 rows in set (4.21 sec)

原因の切り分け 1

SHOW FULL PROCESSLIST の結果が Copying to tmp table であるから tmp_table_size の不足ではないことが分かります。

原因の切り分け 2

GROUP BY を取り除いても要する時間は変化しないため LEFT JOIN がボトルネックであることが分かります。

SELECT t3.myval, _t2.cnt

FROM t1

LEFT JOIN
  (SELECT id, COUNT(*) AS cnt FROM t2 GROUP BY id) AS _t2
  ON t1.id = _t2.id

LEFT JOIN
  t3
  ON MD5(t1.id) = t3.id_md5;

9999 rows in set (4.11 sec)

EXPLAIN

+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY     | t1         | index  | NULL          | PRIMARY | 4       | NULL | 10141 | Using index |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL |  9999 |             |
|  1 | PRIMARY     | t3         | eq_ref | PRIMARY       | PRIMARY | 98      | func |     1 |             |
|  2 | DERIVED     | t2         | index  | NULL          | myindex | 5       | NULL | 19351 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
4 rows in set (0.01 sec)

SHOW FULL PROCESSLIST 抜粋

| State        |
| Sending data |

EXPLAIN にあるように Copying to tmp table は表示されなくなりました。GROUP BY を使用しないためです。

チューニング

インデックスを使用しない LEFT JOIN が原因であることが分かったため改善します。以下の EXPLAIN の上から二番目です。一番目と四番目は type が index であり Extra にも Using index と表示されています。三番目は possible_keys が PRIMARY となっているため問題ありません。

+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY     | t1         | index  | NULL          | PRIMARY | 4       | NULL | 10141 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL |  9999 |                                              |
|  1 | PRIMARY     | t3         | eq_ref | PRIMARY       | PRIMARY | 98      | func |     1 |                                              |
|  2 | DERIVED     | t2         | index  | NULL          | myindex | 5       | NULL | 19351 | Using index                                  |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+----------------------------------------------+

問題は FROM 句で使用しているサブクエリで作成される一時テーブルにインデックスが作成されず、LEFT JOIN で t1 のレコードに合致する _t2.id の検索が遅くなっていることです。そこでサブクエリではなく別のクエリで手動で一時テーブルを作成して、それにインデックスを張ります。

CREATE TEMPORARY TABLE _t2
  (id INT PRIMARY KEY, cnt INT)
  SELECT id, COUNT(*) AS cnt FROM t2 GROUP BY id;

SELECT t3.myval, _t2.cnt, COUNT(*)

FROM t1

LEFT JOIN
  _t2
  ON t1.id = _t2.id

LEFT JOIN
  t3
  ON MD5(t1.id) = t3.id_md5

GROUP BY
  t3.myval, _t2.cnt;

結果として「0.06 + 0.04 = 0.10 秒」に短縮されました。

Query OK, 9999 rows affected (0.06 sec)
Records: 9999  Duplicates: 0  Warnings: 0

+-------+------+----------+
| myval | cnt  | COUNT(*) |
+-------+------+----------+
|     0 |    2 |     1020 |
|     1 |    2 |      997 |
|     2 |    2 |      954 |
|     3 |    2 |     1022 |
|     4 |    2 |      998 |
|     5 |    2 |     1058 |
|     6 |    2 |      974 |
|     7 |    2 |     1033 |
|     8 |    2 |      914 |
|     9 |    2 |     1029 |
+-------+------+----------+
10 rows in set (0.04 sec)

その他の話題

MySQL InnoDB におけるインデックスには二種類あります。

• Clustered Index には行データがすべて含まれています。インデックスを辿るだけでデータ取得も完了するため高速です。
• Secondary Index には primary key が含まれています。primary key をもとに Clustered Index を検索します。取得したいデータが primary key のみである場合は Clustered Index を検索しないため高速です。

Secondary Index を検索した後に Clustered Index の検索が発生しないような Secondary Index を特に covering index とよぶことがあります。Secondary Index で primary key のみを検索する場合や、複合インデックスのキーのみを検索する場合が該当します。EXPLAIN でクエリの実行計画を調べたときに Using index と表示されるクエリでは Secondary Index が covering index として機能しています。こちらのページで分かりやすく図解されています。