プログラミング/IoT の関連商品 (Amazonのアソシエイトとして、当メディアは適格販売により収入を得ています。)
最近の投稿
概要
こちらのページの内容をもとに、具体的にクエリチューニングを行ってみます。
スキーマの用意
データベース作成
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 として機能しています。こちらのページで分かりやすく図解されています。
関連ページ
