相关MYSQL DML UPDATE DELETE 中的子查询问题

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

禁用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)

禁用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)

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

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)

这里我们看到小表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> 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)

先使用hash join将TESTDE2 和TESTDE1  建立为一个视图VW_NSO_1，然后使用了HASH JOIN SEMI的优化方式，明显用了到半连接优化

这也是为什么ORACLE比现在的MYSQL还是更加强劲的一个小例子，虽然都是作为一个整体，但是MYSQL已经用不到SEMI优化方式了，ORACLE

依然可以，但是可以预见不久的将来MYSQL肯定支持的。