關于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)

而(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)

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 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';

+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 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 |

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);

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);

|  1 | DELETE             | testde1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.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);

可以看完全一樣

應該使用：

mysql> explain delete  testde1 from testde1,testde2 where testde1.id=testde2.id;

|  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 |

這裡我們看到小表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, 2 rows affected (0.00 sec)

mysql> flush status;

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'的方式。