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 プロパティ

推薦する

2008 年の Web デザインにおける 10 の経験

<br />インターネットは絶えず変化しており、BusinessWeek.com は専門...

Ubuntu 12.04 でカーネルツリーを構築する実装プロセスの詳細な説明

まず使用しているカーネルのバージョンを確認してくださいlin@lin-仮想マシン:~$ uname ...

MySQL 8.0.22 解凍版インストールチュートリアル(初心者向け)

目次1. リソースのダウンロード2. ソフトウェアを解凍する2.1 場所を選択する2.2 名前を変更...

Apacheドメイン名設定の落とし穴の詳細な説明

私はApacheを使ったことがありません。仕事を始めてからはずっとnginxを使っていました(運用保...

テーブルリストを破棄するには、標準のdl、dt、ddタグを使用します。

現在、ますます多くのフロントエンド開発者が、元のテーブル レイアウトを xHTML + CSS に置...

HTML のブロックレベル要素と行レベル要素、特殊文字、ネスト規則

基本的な HTML Web ページ タグのネスト ルールを紹介する場合、最初に説明する必要があるのは...

jQueryは広告を上下にスクロールする効果を実現します

この記事では、広告を上下にスクロールする効果を実現するためのjQueryの具体的なコードを参考までに...

MySQLは現在の日付と時刻を取得する関数の例の詳細な説明

現在の日付 + 時刻 (日付 + 時刻) を取得する関数: now() mysql> now(...

MySQL データ型の選択原則

目次小さいけれど美しいシンプルにNULL値を避けるデータタイプを選択する手順データ型の紹介1. 文字...

nginx と openssl で https を実装する方法

サーバーデータがSSL証明書を使用して暗号化および認証されていない場合、ユーザーのデータはプレーンテ...

HTML独習の旅(I)基本要素と属性の練習(自分でコードを書く)

私は W3school のチュートリアルに従いました。チュートリアルはとても良いと思います。各セクシ...

webpack と rollup を使用してコンポーネント ライブラリをパッケージ化する方法

序文以前、ローディングスタイルのコンポーネントを作成しました。コードの再利用性を実現するために、この...

ディレクトリスクロール効果を実現するネイティブJS

これはネイティブ JS で実装されたテキスト スクロール効果です。この効果は通常、ニュース、ダイナミ...

CSS3はマスク連打機能を実現する

最近Bステーションでスマートアンチブロッキング弾幕と呼ばれる弾幕エフェクトを見ました。これは伝説のマ...

複数のサーバーにNginxリバースプロキシを実装する方法

Nginx は複数のサーバーをリバース プロキシします。つまり、nginx に異なるリクエストを送信...