关于MYSQL DML UPDATE DELETE中的子查询难题
发布时间：2022-03-30 11:17:26 所属栏目：搜索优化 来源：互联网
导读：从5.6开始MYSQL的子查询进行了大量的优化，5.5中只有EXISTS strategy，在5.7中包含如下: IN(=ANY) --Semi-join --table pullout(最快的,子查询条件为唯一键) --first match --semi-join materialization --loosescan --duplicateweedout --Materialization
|
从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(最慢的) 使用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) 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) 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) 使用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) 同时在官方文档也说明了在DML中的子查询用不到SEMI优化和Materialization优化，只能使用exists言外之意就是只能使用关联子查询，转换为exists的格式。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
