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

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 の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • EXPLAIN コマンドの詳細な説明と MySQL での使用方法
  • MySQL での実行計画の explain コマンド例の詳細な説明
  • MySQLのEXPLAINコマンドの詳細な説明
  • MySQL Explainコマンドの簡単な説明
  • Mysql Explainコマンドの使用と分析
  • SQLでEXPLAINコマンドを使用する方法

<<:  VPSサーバーでよく使われるパフォーマンステストスクリプトの概要

>>:  CSS 標準: vertical-align プロパティ

推薦する

js の一般的でない演算子と演算子の概要

一般的な演算子と JavaScript の演算子の概要カテゴリオペレーター算術演算子+、–、*、/、...

MySQL Limitクエリのパフォーマンスを向上させる方法

MySQL データベース操作では、一部のクエリを実行するときにデータベース エンジンが完全なテーブル...

Dockerを使用してNextCloudネットワークディスクを展開する方法

NextCloud コンピュータ上の任意のファイルやフォルダを共有し、NextCloud サーバーと...

シンプルなナビゲーションバー機能を実現するHTML+CSS

さっそく、コードを見てみましょう(初心者:特に言うことはありません) <!DOCTYPE ht...

CentOS 7 での mysql 5.7 のインストール チュートリアル

1. 公式MySQL Yumリポジトリをダウンロードしてインストールする 実行ファイル: mysql...

Vueベースのビデオプレーヤーの実装例

既存のビデオ プレーヤーがニーズを満たせない場合は、ビデオを自分でカプセル化する必要があります。ビデ...

Vue プロジェクトはファイルダウンロードの進行状況バー機能を実装します

日常業務でファイルをダウンロードする一般的な方法は 2 つあります。 1 つ目は、サーバーのファイル...

MySQLがOracleのnvlと同様の機能を持つことができるかどうかについての簡単な議論

isnullの代わりにifnullを使用するisnull は、null かどうかを判断するために使用...

LeetCode の SQL 実装 (177. 給与が N 番目に高い)

[LeetCode] 177. 最も高い給与従業員テーブルからn番目に高い給与を取得する SQL ...

JavaScript で同時実行制御を実装する方法

目次1. 同時実行制御の概要1.1 フェーズ1 1.2 フェーズ2 1.3 フェーズ3 2. 同時実...

Nginx で HTTPS 証明書を構成する詳細なプロセス

1. HttpとHttpsの違いHTTP: インターネットで最も広く使用されているネットワーク プロ...

Mysql5.7でのスケジュールバックアップの実装

1. MySQL インストール パス D:\xxx\MYSQL\MySQL Workbench CE...

Linux での MySQL 5.6 バイナリのインストール プロセス

1.1 バイナリインストールパッケージをダウンロードするhttps://dev.mysql.com/...

MySql テーブル内の行を削除する実用的な方法

まず、どのフィールドまたはフィールドの組み合わせがデータ行を一意に識別できるかを決定する必要がありま...

React 純粋関数コンポーネント setState がページ更新を更新しない問題の解決方法

目次問題の説明:原因分析:解決:補足: Reactでは、フックが使用されている場合、useState...