关于MYSQL DML(UPDATE DELETE)中的子查询问题和ERROR 1093 (HY000)错误

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 从5.6开始MYSQL的子查询进行了大量的优化,5.5中只有EXISTS strategy,在5.7中包含如下: IN(=ANY) --Semi-join   --table pullout(最快的,子查询条件为唯一键)   --first match   ...
从5.6开始MYSQL的子查询进行了大量的优化,5.5中只有EXISTS strategy,在5.7中包含如下:
IN(=ANY)
--Semi-join
  --table pullout(最快的,子查询条件为唯一键)
  --first match
  --semi-join materialization
  --loosescan
  --duplicateweedout
--Materialization
--EXISTS strategy(最慢的)
NOT IN( <>ALL)
--Materialization
--EXISTS strategy(最慢的)


而(not)exist却没有任何优化还是关联子查询的方式,这和ORACLE不一样,ORACLE中in、exists
都可以使用半连接(semi)优化.所以MYSQL中尽量使用in不要用exists。not in不能使用semi-join
要小心使用,更不要用not exists,关于上面每一个含义可以参考官方手册和mariadb手册。

我们简单的看一个列子,


使用semi-join materialization优化的
mysql> explain select * from testde1 where testde1.id in(select id from testde2);
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE       | | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | testde1     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | testde2     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)


Note (Code 1003): /* select#1 */ select `test`.`testde1`.`id` AS `id` from `test`.`testde1` semi join (`test`.`testde2`) where (`test`.`testde1`.`id` = ``.`id`)
semi join (`test`.`testde2`)  说明了问题


禁用semi join使用Materialization优化
mysql> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)


mysql> explain select * from testde1 where testde1.id in(select id from testde2);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where |
|  2 | SUBQUERY    | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


Note (Code 1003): /* select#1 */ select `test`.`testde1`.`id` AS `id` from `test`.`testde1` where (`test`.`testde1`.`id`,`test`.`testde1`.`id` in ( (/* select#2 */ select `test`.`testde2`.`id` from `test`.`testde2` where 1 ), (`test`.`testde1`.`id` in on where ((`test`.`testde1`.`id` = `materialized-subquery`.`id`)))))


materialized-subquery`.`id`)说明了问题


禁用join使用Materialization
ysql> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)


mysql> explain select * from testde1 where testde1.id in(select id from testde2);
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


Note (Code 1003): /* select#1 */ select `test`.`testde1`.`id` AS `id` from `test`.`testde1` where (`test`.`testde1`.`id`,(/* select#2 */ select 1 from `test`.`testde2` where ((`test`.`testde1`.`id`) = `test`.`testde2`.`id`)))


使用DEPENDENT SUBQUERY 关联子查询优化,这也是最慢的。这和
select * from testde1 where exists (select * from testde2 where testde1.id=testde2.id);的执行计划完全一致,
testde1大表必须作为驱动表
mysql> explain select * from testde1 where exists (select * from testde2 where testde1.id=testde2.id);
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)


Note (Code 1276): Field or reference 'test.testde1.id' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select `test`.`testde1`.`id` AS `id` from `test`.`testde1` where exists(/* select#2 */ select 1 from `test`.`testde2` where (`test`.`testde1`.`id` = `test`.`testde2`.`id`))


同时在官方文档也说明了在DML中的子查询用不到SEMI优化和Materialization优化,只能使用exists言外之意就是只能使用关联子查询,转换为exists的格式。
那么速度可想而知,这种方式明显是外层表取出一行,驱动内层表一次,顺序固定,而jion的时候一般会选取小表作为驱动表性能更好。所以建议我们使用join
的方式来删除
原文如下:
A limitation on UPDATE and DELETE statements that use a subquery to modify a
single table is that the optimizer does not use semi-join or materialization subquery
optimizations. As a workaround, try rewriting them as multiple-table UPDATEand
DELETEstatements that use a join rather than a subquery.


实际就是下面的执行计划:


mysql> explain delete from testde1 where id in (select id from testde2);
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE             | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.00 sec)


转换为了:
mysql> explain delete from testde1 where exists  (select * from testde2 where testde1.id=testde2.id);
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE             | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
可以看完全一样


应该使用:
mysql> explain delete  testde1 from testde1,testde2 where testde1.id=testde2.id;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testde2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        |
|  1 | DELETE      | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.00 sec)


这里我们看到小表testde2做了驱动表。
最后来说明一下这个报错:
mysql> delete from testde1 where id in(select testde1.id from testde1,testde2 where testde1.id=testde2.id );
ERROR 1093 (HY000): You can't specify target table 'testde1' for update in FROM clause
我们先不管他有没有意义,这个报错再手册上叫做ER_UPDATE_TABLE_USED,我们首先来分析一下这个报错
这样的delete会进行exists展开那么testde1既是修改条件的来源也是修改的对象,这样是不允许的。那么如何修改呢?
实际上就需要select testde1.id from testde1,testde2 where testde1.id=testde2.id 的结果保存在一个临时表中,
不要exists展开,手册中给出的方法是
方法一、建立一个algorithm=temptable 的视图
方法二、建立一个普通视图同时修改SET optimizer_switch = 'derived_merge=off';


其目的都在于不展开选取第二种方式测试:
mysql> create view myt1
    -> as
    -> select testde1.id from testde1,testde2 where testde1.id=testde2.id;
Query OK, 0 rows affected (0.02 sec)


mysql> delete from testde1 where id in (select * from myt1);
ERROR 1443 (HY000): The definition of table 'myt1' prevents operation DELETE on table 'testde1'.
mysql> SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)


mysql> delete from testde1 where id in (select * from myt1);
Query OK, 2 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql>  delete from testde1 where id in (select * from myt1);
Query OK, 2 rows affected (0.03 sec)

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 2    |
+-------------------------+-------+
3 rows in set (0.01 sec)

看看执行计划:
mysql> explain delete from testde1 where id in (select * from myt1);
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type        | table      | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+
|  1 | DELETE             | testde1    | NULL       | ALL            | NULL          | NULL        | NULL    | NULL |   13 |   100.00 | Using where                                        |
|  2 | DEPENDENT SUBQUERY | | NULL       | index_subquery |   | | 5       | func |    2 |   100.00 | Using index                                        |
|  3 | DERIVED            | testde2    | NULL       | ALL            | NULL          | NULL        | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  3 | DERIVED            | testde1    | NULL       | ALL            | NULL          | NULL        | NULL    | NULL |   13 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+----------------------------------------------------+
4 rows in set (0.00 sec)


可以看到子查询作为了一个整体,从status和执行计划dervied都可以看到使用了临时表,这样可行,但是性能上肯定不好。
在ORACLE中不存在这样的问题,执行计划如下:
SQL>  delete from testde1 where id in(select testde1.id from testde1,testde2 where testde1.id=testde2.id );
0 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 2653154564
--------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |          |     1 |    26 |     7  (15)| 00:00:01|
|   1 |  DELETE               | TESTDE1  |       |       |            |         |
|*  2 |   HASH JOIN SEMI      |          |     1 |    26 |     7  (15)| 00:00:01|
|   3 |    TABLE ACCESS FULL  | TESTDE1  |     5 |    65 |     2   (0)| 00:00:01|
|   4 |    VIEW               | VW_NSO_1 |     1 |    13 |     5  (20)| 00:00:01|
|*  5 |     HASH JOIN         |          |     1 |    26 |     5  (20)| 00:00:01|
|   6 |      TABLE ACCESS FULL| TESTDE2  |     1 |    13 |     2   (0)| 00:00:01|
|   7 |      TABLE ACCESS FULL| TESTDE1  |     5 |    65 |     2   (0)| 00:00:01|
---------------------------------------------------------------------------------

先使用hash join将 TESTDE2 和 TESTDE1  建立为一个视图 VW_NSO_1,然后使用了 HASH JOIN SEMI的优化方式,明显用了到半连接优化
这也是为什么ORACLE比现在的MYSQL还是更加强劲的一个小例子,虽然都是作为一个整体,但是MYSQL已经用不到SEMI优化方式了,ORACLE
依然可以,但是可以预见不久的将来MYSQL肯定支持的。


最后总结一下:
1、.所以MYSQL中尽量使用in不要用exists。not in不能使用semi-join要小心使用,更不要用not exists
2、子查询DML应该修改关联DML(update delete)
3、ERROR 1093 (HY000)错误原因是 某张表既是修改的对象也是信息来源的对象。需要使用algorithm=temptable或者
   optimizer_switch = 'derived_merge=off'的方式。
   

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——EXISTS(存在)
MySQL数据库子查询练习——EXISTS(存在)
14 1
|
28天前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询——in多个数据查询的示例
MySQL数据库子查询——in多个数据查询的示例
17 1
|
9天前
|
网络协议 关系型数据库 MySQL
启动mysql时的异常为:[ERROR] Can‘t start server: Bind on TCP/IP port. Got error: 98: Address already in used
启动mysql时的异常为:[ERROR] Can‘t start server: Bind on TCP/IP port. Got error: 98: Address already in used
|
11天前
|
关系型数据库 MySQL 数据库
MySQL—子查询
MySQL—子查询
|
16天前
|
SQL 关系型数据库 MySQL
MySQL基础(二)----DML学习
MySQL基础(二)----DML学习
14 3
|
16天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之 MySQL数据库中,执行delete命令删除数据后,存储空间通常不会立即释放,该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
24天前
|
关系型数据库 MySQL 数据库
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
【MySQL】MySQL数据库的delete from table和truncate table之间的区别
220 1
|
28天前
|
SQL 关系型数据库 MySQL
MySQL周内训参照2、DDL与DML语句
MySQL周内训参照2、DDL与DML语句
17 1
|
8天前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
|
9天前
|
关系型数据库 MySQL 数据库
mysql 中登录报错:ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)ERROR
mysql 中登录报错:ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)ERROR