おもしろかったのでメモ。
CREATE TABLE `tbl` ( `id` BIGINT UNSIGNED NOT NULL, `done` BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (`id`), KEY `done_and_id` (`done`, `id`) );
tblテーブルにdone = FALSEで100万行INSERTしておくSELECT id FROM tbl WHERE done = FALSE ORDER BY id ASC LIMIT 50でidを順に集める- 処理する
UPDATE tbl SET done = TRUE WHERE id IN (...)で処理済にする- これを
done = FALSEな行がなくなるまで繰り返す
KEY `done_and_id (`done`, `id`) ありでEXPLAINする
mysql> EXPLAIN SELECT * FROM tbl_with_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | tbl_with_index | ref | done_and_id | done_and_id | 1 | const | 499284 | Using where; Using index | +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ 1 row in set (0.01 sec)
明らかに効くインデックスのはずなのに rows がめっちゃ大きくてやばそう。
KEY `done_and_id (`done`, `id`) を消してからEXPLAINする
mysql> EXPLAIN SELECT * FROM tbl_without_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +------+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_without_index | index | NULL | PRIMARY | 8 | NULL | 50 | Using where | +------+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.01 sec)
rows はLIMITぶんしかないので安心？
実験
ConoHaのDBサーバー*1 (Server version: 5.5.5-10.0.19-MariaDB-log MariaDB Server) に向けて以下のようなクエリにかかる時間を、 done = TRUE な行を1000行ずつ増やしながら測定した。
SELECT * FROM tbl WHERE done = FALSE ORDER BY id ASC LIMIT 50
実験に使ったコード: GitHub - utgwkk/20201023-sketch-mysql-query-exam
実験結果: mysql explain test - Google スプレッドシート
考察
- インデックスなしだと、
done = TRUEな行が増えるにつれてSELECTの速度が低下している - インデックスありの場合は、
done = TRUEな行の数に関係なく、ほぼ一定時間でSELECTできている done = TRUEな行が少ないうちは、インデックスなしのほうがSELECTが速い- インデックスのオーバーヘッドがありそう
まとめ
- MySQLのEXPLAINが
そのまま信用できるとは限らない直感とは異なる結果になることがある- 効くでしょうと思って貼ったインデックスが裏目に出るようなEXPLAIN結果になることもある
- 逆に、EXPLAINのrowsが小さいのでいけるかと思ってもうまくいかないこともある
- 自信がなかったら実験する
- データ量が増えたときにどうなるか
done = TRUEな行が増えたときにどうなるか
- MySQLのバージョンが上がるとEXPLAINが正確になるのかな
追記
EXPLAIN EXTENDED も見た。
mysql> EXPLAIN EXTENDED SELECT * FROM tbl_with_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ | 1 | SIMPLE | tbl_with_index | ref | done_and_id | done_and_id | 1 | const | 499284 | 100.00 | Using where; Using index | +------+-------------+----------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN EXTENDED SELECT * FROM tbl_without_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +------+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_without_index | index | NULL | PRIMARY | 8 | NULL | 50 | 100.00 | Using where | +------+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
filtered カラムは、テーブル条件によってフィルタ処理されるテーブル行の推定の割合を示します。つまり、rows は調査される推定の行数を示し、rows × filtered / 100 が前のテーブルと結合される行数を示します。EXPLAIN EXTENDED を使用すると、このカラムが表示されます。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット
追記2
バージョンごとのEXPLAIN結果を見比べてみたけど、どれも変わらないようだった。実行時間もいちおう貼っておくけど大して変わらなかった。
mysql explain test for different versions - Google スプレッドシート
MySQL 5.6 (5.6.47)
mysql> EXPLAIN SELECT * FROM tbl_without_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_without_index | index | NULL | PRIMARY | 8 | NULL | 50 | Using where | +----+-------------+-------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT * FROM tbl_with_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | tbl_with_index | ref | done_and_id | done_and_id | 1 | const | 499284 | Using where; Using index | +----+-------------+----------------+------+---------------+-------------+---------+-------+--------+--------------------------+ 1 row in set (0.00 sec)
MySQL 5.7 (5.7.25)
mysql> EXPLAIN SELECT * FROM tbl_without_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_without_index | NULL | index | NULL | PRIMARY | 8 | NULL | 50 | 10.00 | Using where | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM tbl_with_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ | 1 | SIMPLE | tbl_with_index | NULL | ref | done_and_id | done_and_id | 1 | const | 499284 | 100.00 | Using where; Using index | +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.02 sec)
MySQL 8.0 (8.0.18)
mysql> EXPLAIN SELECT * FROM tbl_without_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_without_index | NULL | index | NULL | PRIMARY | 8 | NULL | 50 | 10.00 | Using where | +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM tbl_with_index WHERE done = FALSE ORDER BY id ASC LIMIT 50; +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+ | 1 | SIMPLE | tbl_with_index | NULL | ref | done_and_id | done_and_id | 1 | const | 499572 | 100.00 | Using index | +----+-------------+----------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+ 1 row in set, 1 warning (0.02 sec)
追試はGoogle Could SQLで行った。
Google Cloud SQL、高速にオープンインターネット大公開のMySQLをこしらえることができて便利すぎる https://t.co/D6GILHLFcH pic.twitter.com/YqwJBQESVs
— うたがわきき (@utgwkk) 2020年10月23日
追記3
MySQL 8.0でしか確認してないが、tbl_with_index テーブルについて、 done = TRUE な行が増えるにつれてEXPLAINの rows が減少した。この現象については MySQLのEXPLAIN結果がどんどん変わっていく例 - 私が歌川です で挙動を確かめた。
追記4
もともと「MySQLのEXPLAINがそのまま信用できるとは限らない事例」という記事タイトルだったが、信用できる・できない という言い方は正確ではなかったのでタイトルを変更した。
- EXPLAINは嘘をつかない
- 統計情報がちゃんと更新されているかを確認するべき
- なぜこの実行計画が選ばれたかを確認したいならオプティマイザトレースを確認するとよさそう
という指摘をいただいた。オプティマイザトレースについてはあとで確認してみる。
*1:RDSを使おうとしたけど、外から接続する方法が分からず断念した
