從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肯定支持的。 |
免責聲明：本站部分文章和圖片均來自用戶投稿和網絡收集，旨在傳播知識，文章和圖片版權歸原作者及原出處所有，僅供學習與參考，請勿用于商業用途，如果損害了您的權利，請聯系我們及時修正或刪除。謝謝！
始終以前瞻性的眼光聚焦站長、創業、互聯網等領域，為您提供最新最全的互聯網資訊，幫助站長轉型升級，為互聯網創業者提供更加優質的創業信息和品牌營銷服務，與站長一起進步！讓互聯網創業者不再孤獨！
掃一掃，關注站長網微信
大家都在看
