快捷導(dǎo)航

explain命令為什么可能會修改MySQL數(shù)據(jù)

更新時間：2020年12月17日 10:02:30 作者：abce

這篇文章主要介紹了explain命令為什么可能會修改MySQL數(shù)據(jù)，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下

如果有人問你，對查詢執(zhí)行EXPLAIN是否可以改變你的數(shù)據(jù)庫，你可能會說不會; 通常都是這么認(rèn)為的。EXPLAIN應(yīng)該向我們展示查詢是如何執(zhí)行的，而不是執(zhí)行查詢，因此它不能更改任何數(shù)據(jù)。

不幸的是，在這種情況下，常識并不適用于MySQL(在寫這篇文章的時候，MySQL 8.0.21和以前的版本)-有一些情況下，explain可以改變你的數(shù)據(jù)庫，就像這個Bug所示:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+
1 row in set (0.01 sec)
 
mysql> DELIMITER $$
mysql> CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET utf8mb4
    ->     DETERMINISTIC
    -> BEGIN
    -> delete from test.t1;
    -> RETURN 'OK';
    -> END $$
Query OK, 0 rows affected (0.00 sec)
 
mysql>

mysql> select * from t1$$
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
+------+------+
2 rows in set (0.00 sec)
 
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.01 sec)
 
mysql> select * from t1$$
Empty set (0.00 sec)
 
mysql>

這里的問題是explain執(zhí)行了存儲函數(shù)cleanup()，該函數(shù)是可以修改數(shù)據(jù)的。

這與更理智的PostgreSQL行為不同，后者在運(yùn)行EXPLAIN時不會執(zhí)行存儲函數(shù)(如果你運(yùn)行EXPLAIN ANALYZE，則會執(zhí)行)。

在MySQL中，這個決定來自于嘗試做正確的事情并提供最可靠的解釋(查詢執(zhí)行計劃很可能取決于存儲函數(shù)返回什么)，但似乎沒有考慮這種安全權(quán)衡。

盡管當(dāng)前MySQL EXPLAIN設(shè)計的這種后果是最嚴(yán)重的后果之一，但你還遇到一個問題，即EXPLAIN（理性的用戶希望這是檢查查詢性能的一種快速方法）可能需要花費(fèi)大量時間才能完成，例如：

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

這會運(yùn)行一個多小時。

雖然很不幸有這樣的行為，但只有在擁有不受限制的權(quán)限時才會發(fā)生。如果有一個更復(fù)雜的設(shè)置，行為可能會有所不同。

如果用戶缺少EXECUTE權(quán)限，EXPLAIN語句將失敗。

mysql> explain select * from (select cleanup()) as t1clean;
ERROR 1370 (42000): execute command denied to user 'abce'@'localhost' for routine 'test.cleanup'

如果用戶有EXECUTE權(quán)限，但是執(zhí)行存儲函數(shù)的用戶沒有DELETE權(quán)限，也會失敗:

mysql> explain select * from (select cleanup()) as t1clean;
ERROR 1142 (42000): DELETE command denied to user 'abce'@'localhost' for table 't1'

那么，如果想提高EXPLAIN的安全性，例如，正在開發(fā)Percona Monitoring and Management之類的工具，該工具除其他功能之外，還允許用戶對其查詢運(yùn)行EXPLAIN，該怎么辦？

·建議用戶設(shè)置權(quán)限以進(jìn)行正確的監(jiān)控。這應(yīng)該是這個（以及許多其他）問題的第一道防線，但是，這很難依靠。許多用戶將選擇簡單的方式，并將使用具有完全特權(quán)的“ root”用戶進(jìn)行監(jiān)控。

·將EXPLAIN語句包裝在BEGIN…ROLLBACK中，這將撤消EXPLAIN可能造成的任何損害。缺點(diǎn)當(dāng)然是刪除數(shù)據(jù)的“工作”，并且在撤消工作時將完成工作。（注意：當(dāng)然，這僅適用于事務(wù)表。如果你仍然運(yùn)行MyISAM，在這種情況下，有更嚴(yán)重的問題需要擔(dān)心）

·使用“set transaction read-only”，表示不希望進(jìn)行任何寫操作。在這種情況下，嘗試寫數(shù)據(jù)的EXPLAIN將失敗，并且不做任何工作。

雖然這些變通辦法可以使工具更安全地運(yùn)行EXPLAIN，但它不能幫助用戶直接運(yùn)行EXPLAIN，并且我真的希望通過重新設(shè)計EXPLAIN來解決此問題，就像PostgreSQL那樣不會嘗試運(yùn)行存儲函數(shù)。對于那些想知道如何精確執(zhí)行查詢的人，現(xiàn)在有了EXPLAIN ANALYZE。

以上就是explain命令為什么可能會修改MySQL數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于explain命令修改MySQL數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: