關於MYSQL DML(UPDATE DELETE)中的子查詢問題和ERROR 1093 (HY000)錯誤
從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'的方式。
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'的方式。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2131596/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於 mysql 中的 rand () 查詢問題MySql
- UPDATE子查詢的ORA-1779錯誤
- 關於mysql 子查詢中 使用 limitMySqlMIT
- Mysql中的巢狀子查詢問題QSBSMySql巢狀
- mysql報錯ERROR 1093MySqlError
- oracle和mysql關於關聯更新的一些差別以及ERROR 1093OracleMySqlError
- MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）MySql優化
- 關於delete還是update會產生更多日誌的問題delete
- Java中關於二分查詢的問題Java
- Mysql連線錯誤ERROR 2003 (HY000)MySqlError
- MySQL 相關子查詢MySql
- 關於Hibernate的查詢問題
- 關於UPDATE中關聯查詢的執行時間考慮
- mysql 的delete from where 子查詢的一些限制MySqldelete
- mysql慢查詢和錯誤日誌分析MySql
- MySQL聯結查詢和子查詢MySql
- 關於delete,drop,truncate的問題delete
- MySQL鎖表相關問題查詢思路MySql
- 區分關聯子查詢和非關聯子查詢
- exist-in和關聯子查詢-非關聯子查詢
- 關於PHP 時區錯誤的問題PHP
- 【MySQL】ERROR 1175 安全模式UPDATE/DELETE操作失敗MySqlError模式delete
- MySQL之連線查詢和子查詢MySql
- 關於action的error問題Error
- mysql 5.5 中自連線update and deleteMySqldelete
- 恢復update,delete表資料錯誤的語句delete
- mysql update join優化update in查詢效率MySql優化
- 【MySQL】ERROR 1093 You canMySqlError
- 關於mysql查詢字符集不匹配問題的解決方法MySql
- mysql的子查詢MySql
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- 關於restful 查詢API設計問題RESTAPI
- MySQL子查詢MySql
- MYsql 子查詢MySql
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql
- MySQL error 錯 誤 碼MySqlError
- MySQL全面瓦解11：子查詢和組合查詢MySql
- 相關子查詢&非相關子查詢概念