作成日
最終更新
記事区分
具体的にクエリチューニングを行ってみます。
スキーマの用意
データベース作成
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 として機能しています。こちらのページで分かりやすく図解されています。
0
関連記事
- MySQL レプリケーション設定 (2段, 3段)MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をス...
- MySQL 5.7 インストール手順2016/11/07 時点、多くの環境では yum レポジトリ等に mysql 5.7 が含まれていません。公式ページからダウンロードしてインストールする手順を二つまとめます。 yum レポジトリを追加する手順 [Installing MySQL on Linux Using the MySQL Yum Repository (mysql 5.7)](http://dev.mysql.com/do...
- MyBatis 3 サンプルコード (Java/MySQL/Gradle)MyBatis は、JDBC を直接利用せずに、XML ファイル等で用意した SQL 文を利用して DB にアクセスするための Java ライブラリです。内部的には JDBC が利用されているため、JDBC のインストールは必要です。同様のライブラリに Hibernate ORM があります。DB は特に MySQL を対象として、MyBatis バージョン 3 の
- MySQL HandlerSocket Plugin の簡単な使用方法HandlerSocket は MySQL プロセス内のスレッドとして動作する、MySQL の NoSQL フロントエンドです。独自のプロトコルを用いて TCP 通信を行います。SQL 構文の解析が不要、プロトコルがシンプルなどの理由によって、単純な DB アクセスが高速に実行可能になります。MariaDB であれば[標準プラグインとして付属](https://mariadb.co
- Spring Boot から MyBatis を利用するための設定 (Gradle/MySQL)MyBatis を Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル
