explainコマンドがMySQLデータを変更する理由

クエリで EXPLAIN を実行するとデータベースが変更されるかどうかを尋ねられた場合、おそらく「いいえ」と答えるでしょう。通常、それはビューです。 EXPLAIN はクエリを実行するのではなく、クエリがどのように実行されるかを示すためのものであるため、データを変更することはできません。

残念ながら、この場合、MySQL では常識は当てはまりません (この記事の執筆時点では、MySQL 8.0.21 以前)。このバグが示すように、explain によってデータベースが変更される場合があります。

mysql> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 5.7.31 |
+-----------+
セット内の1行（0.01秒）

 mysql> 区切り文字 $$
mysql> CREATE FUNCTION `cleanup`() は char(50) CHARSET utf8mb4 を返します
    -> 決定論的
    -> 開始
    -> test.t1 から削除します。
    -> 'OK' を返します。
    -> 終了 $$
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

 マイSQL>

mysql> t1$$ から * を選択
+------+------+
| ID | 名前 |
+------+------+
| 1 | 単数 |
| 2 | bb |
+------+------+
セット内の 2 行 (0.00 秒)

 mysql> select * from (select cleanup()) を t1clean$$ として説明してください
+----+--------------+-------------+-----------+--------+-------+-------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+-------+-------+----------------+
| 1 | PRIMARY | <derived2> | NULL | システム | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | 派生 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | テーブルは使用されていません |
+----+--------------+-------------+-----------+--------+-------+-------+----------------+
セットに 2 行、警告 1 件 (0.01 秒)

 mysql> t1$$ から * を選択
空のセット (0.00 秒)

 マイSQL>

ここでの問題は、explain がデータを変更する可能性のあるストアド関数 cleanup() を実行することです。

これは、 EXPLAIN の実行時にストアド関数を実行しない ( EXPLAIN ANALYZE を実行する場合は実行します) という、より健全な PostgreSQL の動作とは異なります。

MySQL では、この決定は、正しいことを実行し、最も信頼性の高い説明を提供しようという試みから生じますが (クエリ実行プランは、ストアド関数が返す内容によって大きく左右される可能性があります)、この安全性のトレードオフは考慮されていないようです。

現在の MySQL EXPLAIN 設計によるこの結果は最も深刻なものの 1 つですが、EXPLAIN (合理的なユーザーであればクエリ パフォーマンスを迅速にチェックできると期待する) が完了するまでにかなりの時間がかかるという問題もあります。次に例を示します。

mysql> explain select * from (select sleep(5000) as a) b;

これは1時間以上続きます。

この動作は残念ですが、制限のない権限がある場合にのみ発生します。より複雑な設定の場合は、動作が異なる場合があります。

ユーザーに EXECUTE 権限がない場合、EXPLAIN ステートメントは失敗します。

mysql> select * from (select cleanup()) を t1clean として説明します。
エラー 1370 (42000): ルーチン 'test.cleanup' のユーザー 'abce'@'localhost' への実行コマンドが拒否されました

ユーザーに EXECUTE 権限があるが、ストアド関数を実行するユーザーに DELETE 権限がない場合にも、これは失敗します。

mysql> select * from (select cleanup()) を t1clean として説明します。
エラー 1142 (42000): テーブル 't1' に対するユーザー 'abce'@'localhost' への DELETE コマンドが拒否されました

では、EXPLAIN をより安全にしたい場合はどうすればよいでしょうか。たとえば、ユーザーがクエリに対して EXPLAIN を実行できるようにする Percona Monitoring and Management のようなツールを開発している場合はどうでしょうか。

適切な監視を行うために、ユーザーは権限を設定することをお勧めします。これは、この問題（および他の多くの問題）に対する最初の防御線となるはずですが、これに頼るのは困難です。多くのユーザーは簡単な方法を選択し、監視に完全な権限を持つ「root」ユーザーを使用します。

EXPLAIN ステートメントを BEGIN ... ROLLBACK で囲みます。これにより、EXPLAIN によって発生した可能性のある損害が元に戻ります。欠点は、もちろん、データを削除する「作業」であり、その作業を元に戻すと、データを削除したことになってしまいます。 (注: もちろん、これはトランザクション テーブルにのみ適用されます。まだ MyISAM を実行している場合は、より深刻な問題が発生する場合があります)

書き込み操作を行わないことを示すには、「set transaction read-only」を使用します。この場合、データを書き込もうとする EXPLAIN は失敗し、何も実行されません。

これらの回避策により、ツールが EXPLAIN を実行する際の安全性は高まりますが、EXPLAIN を直接実行するユーザーには役立ちません。PostgreSQL のようにストアド関数を実行しないように EXPLAIN を再設計することで、この問題が解決されることを心から願っています。クエリがどのように実行されるかを正確に知りたい人のために、EXPLAIN ANALYZE が利用可能になりました。

上記は、explain コマンドが MySQL データを変更する可能性がある理由の詳細です。MySQL データを変更する explain コマンドの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。