目次
とある日
Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database
とあるバグ報告の記事見つけた。
Google翻訳にかけた内容。
クエリでEXPLAINを実行するとデータベースが変更される可能性があるかどうかを尋ねると、おそらくNOと表示されます。それは常識です。EXPLAINは、クエリを実行するのではなく、クエリがどのように実行されるかを示す必要があるため、データを変更することはできません。
残念ながら、これは常識がMySQLに適用されない場合です(この記事の執筆時点ではMySQL 8.0.21以前のバージョン)–このバグが示すように、EXPLAINが実際にデータベースを変更できるエッジケースがあります。
どうやら、DML
を実行するストアドファンクションをEXPLAIN
で実行計画を見ると、実行されてしまうというバグらしい。
オモシロイと思ったのでやってみる。
環境
Oracle VM VirtualBox
バージョン 6.0.14 r133895 (Qt5.6.2)
OS
mysql> \! cat /etc/os-release NAME="CentOS Linux" VERSION="8 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8 (Core)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://www.centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="8"
MySQL
mysql> status; -------------- mysql Ver 8.0.21 for Linux on x86_64 (Source distribution) Connection id: 27 Current database: hobby Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.21 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 11 hours 56 min 31 sec Threads: 5 Questions: 1817 Slow queries: 0 Opens: 409 Flush tables: 3 Open tables: 312 Queries per second avg: 0.042
実践
- テーブル作成
- データ挿入
- テーブル確認
- ストアドファンクション作成
EXPLAIN
でストアドファンクション実行- テーブル確認
の手順で実行していく。
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use hobby Database changed mysql>create table t1(st char(50)); mysql> INSERT into t1 values("1"); Query OK, 1 row affected (0.12 sec) mysql> show tables; +-----------------+ | Tables_in_hobby | +-----------------+ | max_chcek | | mt | | orde | | t1 | +-----------------+ 4 rows in set (0.00 sec) mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> DELIMITER $$ mysql> CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET latin1 -> DETERMINISTIC -> BEGIN -> delete from t1; -> RETURN 'OK'; -> END $$ Query OK, 0 rows affected (0.12 sec) mysql> DELIMITER ; mysql> explain select * from (select cleanup()) as t1clean; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.09 sec) mysql> select * from t1; Empty set (0.00 sec)
実際にデータがなくなっているのが確認できた。
そもそも、EXPLAIN
をSELECT
以外に使ったことないので、最後の方にINSERT
やDELETE
をEXPLAIN
で実行してみる。
一般ログ確認
一般ログで実際に実行されたSQL
のログを確認してる。
EXPLAIN DML LOG
mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> explain insert into t1 values("2"); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | t1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec)
2020-11-04T17:19:02.334894Z 15 Query show tables 2020-11-04T17:19:06.238490Z 15 Query select * from t1 2020-11-04T17:19:29.975423Z 15 Query explain insert into t1 values("2") 2020-11-04T17:19:32.840570Z 15 Query select * from t1
insert
は実行されていないことが確認できた。
次はストアドファンクションのログ確認。
EXPLAIN FUNCTION LOG
mysql> explain select * from (select cleanup()) as t1clean; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (1.07 sec) mysql> select * from t1; Empty set (0.00 sec)
2020-11-04T17:19:06.238490Z 15 Query select * from t1 2020-11-04T17:19:29.975423Z 15 Query explain insert into t1 values("2") 2020-11-04T17:19:32.840570Z 15 Query select * from t1 2020-11-04T17:21:17.335484Z 15 Query explain select * from (select cleanup()) as t1clean 2020-11-04T17:21:17.338968Z 15 Query delete from t1 2020-11-04T17:21:26.108609Z 15 Query select * from t1
delete
文は実行され、データも消えている。
一般ログにもきちんと記録されていたことが確認できた。
EXPLAIN DML TEST
EXPLAIN概要
WHERE
句内のテーブル、カラム、インデックス、および条件の詳細に応じて、MySQL オプティマイザは SQL クエリーに含まれるルックアップを効率的に実行するための多くの技法を考慮します。巨大なテーブルに対するクエリーは、すべての行を読み取らなくても実行でき、複数のテーブルを含む結合は、行のすべての組み合わせを比較しなくても実行できます。オプティマイザがもっとも効率的なクエリーを実行するために選択する操作のセットは、「クエリー実行プラン」と呼ばれ、EXPLAIN
プランとも呼ばれます。目的は、クエリーが適切に最適化されていることを示すEXPLAIN
プランの側面を認識し、非効率的な操作が見られた場合に、プランを改善するための SQL 構文とインデックス設定技法を学ぶことです。
バグを紹介して記事では、「EXPLAIN
はデータを変更することはできません」と記述したが、ドキュメントを読んでみたがEXPLAIN で実行したDMLが適用されないといった記述は見つからなかった。
EXPLAIN
は、実行計画を見ることができる便利なものだ。
SELECT
以外で使ったことなかった。
SELECT
以外にもDELETE
、INSERT
、UPDATE
、REPLACE
でも使用できる。
SELECT
が唯一の説明可能
INSERT
mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> explain insert into t1 values("1"); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | t1 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.01 sec)
DELETE
mysql> explain delete from t1 ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec)
UPDATE
mysql> explain update t1 set st = "2"; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | UPDATE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t1; +------+ | st | +------+ | 1 | +------+ 1 row in set (0.00 sec)
DELETE
、INSERT
、UPDATE
の3つをEXPLAIN
で実行してみたがテーブルは変化なし。
ストアドファンクションを通してではなく実行すると、テーブルには変化が起きないことが判明した。
〆
なかなか、面白いバグだった。
EXPLAINをもっと詳しく見ればもっと他のことがわかりそう。
もっと複雑なストアドファンクションやストアドプロシージャでも、テーブルが破壊されるか試してみたかったが、
ストアドファンクションに不慣れすぎてやめました。
もっと、MySQLと仲良くならなくては。
SELECT以外でEXPLAINを使わないから自分は影響ないけど、
INSERTとかの実行計画見ることとかあるのかな。
そこが一番の疑問となった。