explain命令为什么可能会修改MySQL数据

如果有人问你，对查询执行explain是否可以改变你的数据库，你可能会说不会; 通常都是这么认为的。explain应该向我们展示查询是如何执行的，而不是执行查询，因此它不能更改任何数据。

不幸的是，在这种情况下，常识并不适用于mysql(在写这篇文章的时候，mysql 8.0.21和以前的版本)-有一些情况下，explain可以改变你的数据库，就像这个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执行了存储函数cleanup()，该函数是可以修改数据的。

这与更理智的postgresql行为不同，后者在运行explain时不会执行存储函数(如果你运行explain analyze，则会执行)。

在mysql中，这个决定来自于尝试做正确的事情并提供最可靠的解释(查询执行计划很可能取决于存储函数返回什么)，但似乎没有考虑这种安全权衡。

尽管当前mysql explain设计的这种后果是最严重的后果之一，但你还遇到一个问题，即explain（理性的用户希望这是检查查询性能的一种快速方法）可能需要花费大量时间才能完成， 例如：

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

这会运行一个多小时。

虽然很不幸有这样的行为，但只有在拥有不受限制的权限时才会发生。如果有一个更复杂的设置，行为可能会有所不同。

如果用户缺少execute权限，explain语句将失败。

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

如果用户有execute权限，但是执行存储函数的用户没有delete权限，也会失败:

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

那么，如果想提高explain的安全性，例如，正在开发percona monitoring and management之类的工具，该工具除其他功能之外，还允许用户对其查询运行explain，该怎么办？

·建议用户设置权限以进行正确的监控。这应该是这个（以及许多其他）问题的第一道防线，但是，这很难依靠。许多用户将选择简单的方式，并将使用具有完全特权的“ root”用户进行监控。

·将explain语句包装在begin…rollback中，这将撤消explain可能造成的任何损害。缺点当然是删除数据的“工作”，并且在撤消工作时将完成工作。（注意：当然，这仅适用于事务表。如果你仍然运行myisam，在这种情况下，有更严重的问题需要担心）

·使用“set transaction read-only”，表示不希望进行任何写操作。在这种情况下，尝试写数据的explain将失败，并且不做任何工作。

虽然这些变通办法可以使工具更安全地运行explain，但它不能帮助用户直接运行explain，并且我真的希望通过重新设计explain来解决此问题，就像postgresql那样不会尝试运行存储函数。对于那些想知道如何精确执行查询的人，现在有了explain analyze。

以上就是explain命令为什么可能会修改mysql数据的详细内容，更多关于explain命令修改mysql数据的资料请关注其它相关文章！