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

作者:abce 时间:2024-01-19 14:53:49 

如果有人问你,对查询执行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。

来源:https://www.cnblogs.com/abclife/p/14101191.html

标签:MySQL,explain,命令,修改数据
0
投稿

猜你喜欢

  • Python中模块string.py详解

    2021-05-20 05:25:03
  • Mongodb基本操作与Python连接mongodb并进行基础操作的方法

    2023-10-14 23:55:51
  • 微信小程序实战之仿android fragment可滑动底部导航栏(4)

    2023-07-02 16:22:38
  • python使用pil生成缩略图的方法

    2022-06-07 13:07:44
  • perl面向对象实例

    2022-11-29 03:22:44
  • 解决Python下json.loads()中文字符出错的问题

    2022-06-17 21:16:52
  • Python pandas之求和运算和非空值个数统计

    2023-11-19 03:04:59
  • Go如何优雅的使用字节池示例详解

    2024-02-10 21:10:17
  • PHP获取特殊时间戳的方法整理

    2023-05-25 00:47:36
  • asp动态调用不同include文件方法

    2007-09-26 14:22:00
  • golang 格式化输入输出操作

    2024-04-25 15:10:24
  • Python的UTC时间转换讲解

    2023-08-20 03:07:42
  • pytorch Dataset,DataLoader产生自定义的训练数据案例

    2022-12-05 06:41:23
  • python高效过滤出文件夹下指定文件名结尾的文件实例

    2023-08-03 13:55:13
  • vscode配置go开发环境的实战过程

    2024-04-28 09:17:38
  • 微信公众平台开发入门教程(图文详解)

    2023-06-21 11:10:05
  • mysql 不等于 符号写法

    2024-01-22 07:03:06
  • Kettle下载安装pdi-ce-7.1.0.0-12教程

    2023-11-27 05:38:36
  • Python如何计算语句执行时间

    2023-03-20 23:04:03
  • 利用JavaScript阻止表单提交的两种方法

    2024-04-22 22:34:44
  • asp之家 网络编程 m.aspxhome.com