這篇文章主要介紹“MySQL子查詢原理是什么”，在日常操作中，相信很多人在MySQL子查詢原理是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL子查詢原理是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
子查詢，通俗解釋就是查詢語句中嵌套著另一個查詢語句。相信日常工作中接觸到 MySQL 的同學都了解或使用過子查詢，但是具體它是怎樣實現的呢? 查詢效率如何? 這些恐怕好多人就不太清楚了，下面咱們就圍繞這兩個問題共同探索一下。
這里我們需要用到3個表，這3個表都有一個主鍵索引 id 和一個索引 a，字段 b 上無索引。存儲過程 idata() 往表 t1 里插入的是 100 行數據，表 t2、t3 里插入了 1000 行數據。建表語句如下：
CREATE TABLE `t1` ( `id` INT ( 11 ) NOT NULL, `t1_a` INT ( 11 ) DEFAULT NULL, `t1_b` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ), KEY `idx_a` ( `t1_a` )) ENGINE = INNODB; CREATE TABLE `t2` ( `id` INT ( 11 ) NOT NULL, `t2_a` INT ( 11 ) DEFAULT NULL, `t2_b` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ), KEY `idx_a` ( `t2_a` )) ENGINE = INNODB; CREATE TABLE `t3` ( `id` INT ( 11 ) NOT NULL, `t3_a` INT ( 11 ) DEFAULT NULL, `t3_b` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ), KEY `idx_a` ( `t3_a` )) ENGINE = INNODB; -- 向t1添加100條數據 -- drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=100)do insert into t1 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); -- 向t2添加1000條數據 drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=101; while(i<=1100)do insert into t2 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); -- 向t2添加1000條數據，且t3_a列的值為倒敘 drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=101; while(i<=1100)do insert into t3 values(i, 1101-i, i); set i=i+1; end while; end;; delimiter ; call idata();
子查詢的語法規定，子查詢可以在一個外層查詢的各種位置出現，這里我們只介紹常用的幾個：
如 SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
這個例子中的子查詢是：(SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2)，這個放在FROM子句中的子查詢相當于一個表，但又和我們平常使用的表有點兒不一樣，這種由子查詢結果集組成的表稱之為派生表。
如：SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
其他的還有 SELECT 子句中，ORDER BY 子句中，GROUP BY 子句中，雖然語法支持，但沒啥意義，就不嘮叨這些情況了。
標量子查詢，只返回一個單一值的子查詢稱之為標量子查詢，比如：
SELECT * FROM t1 WHERE m1 = (SELECT m1 FROM t1 LIMIT 1);
行子查詢，就是只返回一條記錄的子查詢，不過這條記錄需要包含多個列（只包含一個列就成了標量子查詢了）。比如：SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
列子查詢，就是只返回一個列的數據，不過這個列的數據需要包含多條記錄（只包含一條記錄就成了標量子查詢了）。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
表子查詢，就是子查詢的結果既包含很多條記錄，又包含很多個列，比如：
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
其中的 (SELECT m2, n2 FROM t2) 就是一個表子查詢，這里需要和行子查詢對比一下，行子查詢中我們用了 LIMIT 1 來保證子查詢的結果只有一條記錄。
不相關子查詢，就是子查詢可以單獨運行出結果，而不依賴于外層查詢的值，我們就可以把這個子查詢稱之為不相關子查詢。
相關子查詢，就是需要依賴于外層查詢的值的子查詢稱之為相關子查詢。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
如下邊這個查詢語句：
mysql root@localhost:test> explain select * from t1 where t1_a = (select t2_a from t2 limit 1); +----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+ | 1 | PRIMARY | t1 | ref | idx_a | idx_a | 5 | const | 1 | Using where | | 2 | SUBQUERY | t2 | index | <null> | idx_a | 5 | <null> | 1000 | Using index | +----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+
它的執行方式：
先單獨執行 (select t2_a from t2 limit 1) 這個子查詢。
然后在將上一步子查詢得到的結果當作外層查詢的參數再執行外層查詢 select * from t1 where t1_a = ...。
也就是說，對于包含不相關的標量子查詢或者行子查詢的查詢語句來說，MySQL 會分別獨立的執行外層查詢和子查詢，就當作兩個單表查詢就好了。
比如下邊這個查詢：
mysql root@localhost:test> explain select * from t1 where t1_a = (select t2_a from t2 where t1.t1_b=t2.t2_b limit 1); +----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+ | 1 | PRIMARY | t1 | ALL | <null> | <null> | <null> | <null> | 100 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where | +----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+
它的執行方式就是這樣的：
先從外層查詢中獲取一條記錄，本例中也就是先從 t1 表中獲取一條記錄。
然后從上一步驟中獲取的那條記錄中找出子查詢中涉及到的值，就是 t1 表中找出 t1.t1_b 列的值，然后執行子查詢。
最后根據子查詢的查詢結果來檢測外層查詢 WHERE 子句的條件是否成立，如果成立，就把外層查詢的那條記錄加入到結果集，否則就丟棄。
然后重復以上步驟，直到 t1 中的記錄全部匹配完。
如果子查詢的結果集中的記錄條數很少，那么把子查詢和外層查詢分別看成兩個單獨的單表查詢效率還是蠻高的，但是如果單獨執行子查詢后的結果集太多的話，就會導致這些問題：
結果集太多，可能內存中都放不下～
對于外層查詢來說，如果子查詢的結果集太多，那就意味著 IN 子句中的參數特別多，這就導致：
1）無法有效的使用索引，只能對外層查詢進行全表掃描。
2）在對外層查詢執行全表掃描時，由于 IN 子句中的參數太多，這會導致檢測一條記錄是否符合和 IN 子句中的參數匹配花費的時間太長。
于是就有：不直接將不相關子查詢的結果集當作外層查詢的參數，而是將該結果集寫入一個臨時表里。寫入臨時表的過程是這樣的：
該臨時表的列就是子查詢結果集中的列。
寫入臨時表的記錄會被去重，讓臨時表變得更小，更省地方。
一般情況下子查詢結果集不大時，就會為它建立基于內存的使用 Memory 存儲引擎的臨時表，而且會為該表建立哈希索引。
如果子查詢的結果集非常大，超過了系統變量 tmp_table_size或者 max_heap_table_size，臨時表會轉而使用基于磁盤的存儲引擎來保存結果集中的記錄，索引類型也對應轉變為 B+ 樹索引。
這個將子查詢結果集中的記錄保存到臨時表的過程稱之為物化（Materialize）。為了方便起見，我們就把那個存儲子查詢結果集的臨時表稱之為物化表。正因為物化表中的記錄都建立了索引（基于內存的物化表有哈希索引，基于磁盤的有 B+ 樹索引），通過索引執行IN語句判斷某個操作數在不在子查詢結果集中變得非?？?，從而提升了子查詢語句的性能。
mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2); +----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+ | 1 | SIMPLE | t3 | ALL | idx_a | <null> | <null> | <null> | 1000 | Using where | | 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 5 | test.t3.t3_a | 1 | <null> | | 2 | MATERIALIZED | t2 | index | idx_a | idx_a | 5 | <null> | 1000 | Using index | +----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+
其實上邊的查詢就相當于表 t3 和子查詢物化表進行內連接：
mysql root@localhost:test> explain select * from t3 left join t2 on t3.t3_a=t2.t2_a; +----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+ | 1 | SIMPLE | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | <null> | | 1 | SIMPLE | t2 | ref | idx_a | idx_a | 5 | test.t3.t3_a | 1 | <null> | +----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+
此時 MySQL 查詢優化器會通過運算來選擇成本更低的方案來執行查詢。
雖然，上面通過物化表的方式，將IN子查詢轉換成了聯接查詢，但還是會有建立臨時表的成本，能不能不進行物化操作直接把子查詢轉換為連接呢？直接轉換肯定不行。
-- 這里我們先構造了3條記錄，其實也是構造不唯一的普通索引
+------+------+------+ | id | t2_a | t2_b | +------+------+------+ | 1100 | 1000 | 1000 | | 1101 | 1000 | 1000 | | 1102 | 1000 | 1000 | +------+------+------+ -- 加限制條件where t2.id>=1100是為了減少要顯示的數據 mysql root@localhost:test> select * from t3 where t3_a in (select t2_a from t2 where t2.id>=1100); +-----+------+------+ | id | t3_a | t3_b | +-----+------+------+ | 101 | 1000 | 101 | +-----+------+------+ 1 row in set Time: 0.016s mysql root@localhost:test> select * from t3 left join t2 on t3.t3_a=t2.t2_a where t2.id>=1100; +-----+------+------+------+------+------+ | id | t3_a | t3_b | id | t2_a | t2_b | +-----+------+------+------+------+------+ | 101 | 1000 | 101 | 1100 | 1000 | 1000 | | 101 | 1000 | 101 | 1101 | 1000 | 1000 | | 101 | 1000 | 101 | 1102 | 1000 | 1000 | +-----+------+------+------+------+------+ 3 rows in set Time: 0.018s
所以說 IN 子查詢和表聯接之間并不完全等價。而我們需要的是另一種叫做半聯接 (semi-join) 的聯接方式 ：對于 t3 表的某條記錄來說，我們只關心在 t2 表中是否存在與之匹配的記錄，而不關心具體有多少條記錄與之匹配，最終的結果集中也只保留 t3 表的記錄。
注意：semi-join 只是在 MySQL 內部采用的一種執行子查詢的方式，MySQL 并沒有提供面向用戶的 semi-join 語法。
Table pullout （子查詢中的表上拉）
當子查詢的查詢列表處只有主鍵或者唯一索引列時，可以直接把子查詢中的表上拉到外層查詢的 FROM 子句中，并把子查詢中的搜索條件合并到外層查詢的搜索條件中，比如這個：
mysql root@localhost:test> select * from t3 where t3_a in (select t2_a from t2 where t2.id=999) +-----+------+------+ | id | t3_a | t3_b | +-----+------+------+ | 102 | 999 | 102 | +-----+------+------+ 1 row in set Time: 0.024s mysql root@localhost:test> select * from t3 join t2 on t3.t3_a=t2.t2_a where t2.id=999; +-----+------+------+-----+------+------+ | id | t3_a | t3_b | id | t2_a | t2_b | +-----+------+------+-----+------+------+ | 102 | 999 | 102 | 999 | 999 | 999 | +-----+------+------+-----+------+------+ 1 row in set Time: 0.028s mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.id=999) +----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+ | 1 | SIMPLE | t2 | const | PRIMARY,idx_a | PRIMARY | 4 | const | 1 | <null> | | 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | const | 1 | <null> | +----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+
FirstMatch execution strategy （首次匹配）
FirstMatch 是一種最原始的半連接執行方式，跟相關子查詢的執行方式是一樣的，就是說先取一條外層查詢的中的記錄，然后到子查詢的表中尋找符合匹配條件的記錄，如果能找到一條，則將該外層查詢的記錄放入最終的結果集并且停止查找更多匹配的記錄，如果找不到則把該外層查詢的記錄丟棄掉。然后再開始取下一條外層查詢中的記錄，重復上邊這個過程。
mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.t2_a=1000) +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | const | 1 | <null> | | 1 | SIMPLE | t2 | ref | idx_a | idx_a | 5 | const | 4 | Using index; FirstMatch(t3) | +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
DuplicateWeedout execution strategy （重復值消除）
轉換為半連接查詢后，t3 表中的某條記錄可能在 t2 表中有多條匹配的記錄，所以該條記錄可能多次被添加到最后的結果集中，為了消除重復，我們可以建立一個臨時表，并設置主鍵id，每當某條 t3 表中的記錄要加入結果集時，就首先把這條記錄的id值加入到這個臨時表里，如果添加成功，說明之前這條 t2 表中的記錄并沒有加入最終的結果集，是一條需要的結果；如果添加失敗，說明之前這條 s1 表中的記錄已經加入過最終的結果集，直接把它丟棄。
LooseScan execution strategy （松散掃描）
這種雖然是掃描索引，但只取值相同的記錄的第一條去做匹配操作的方式稱之為松散掃描。
當然，并不是所有包含IN子查詢的查詢語句都可以轉換為 semi-join，只有形如這樣的查詢才可以被轉換為 semi-join：
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ... -- 或者這樣的形式也可以： SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...
用文字總結一下，只有符合下邊這些條件的子查詢才可以被轉換為 semi-join：
該子查詢必須是和IN語句組成的布爾表達式，并且在外層查詢的 WHERE 或者 ON 子句中出現
外層查詢也可以有其他的搜索條件，只不過和 IN 子查詢的搜索條件必須使用AND 連接起來
該子查詢必須是一個單一的查詢，不能是由若干查詢由 UNION 連接起來的形式
該子查詢不能包含 GROUP BY 或者 HAVING 語句或者聚集函數
不管子查詢是相關的還是不相關的，都可以把 IN 子查詢嘗試轉為 EXISTS子查詢。其實對于任意一個 IN 子查詢來說，都可以被轉為 EXISTS 子查詢，通用的例子如下：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where) -- 可以被轉換為： EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)
當然這個過程中有一些特殊情況，比如在 outer_expr 或者 inner_expr 值為 NULL 的情況下就比較特殊。因為有 NULL 值作為操作數的表達式結果往往是 NULL，比方說：
mysql root@localhost:test> SELECT NULL IN (1, 2, 3); +-------------------+ | NULL IN (1, 2, 3) | +-------------------+ | <null> | +-------------------+ 1 row in set
而 EXISTS 子查詢的結果肯定是 TRUE 或者 FASLE 。但是現實中我們大部分使用 IN 子查詢的場景是把它放在 WHERE 或者 ON 子句中，而 WHERE 或者 ON 子句是不區分 NULL 和 FALSE 的，比方說：
mysql root@localhost:test> SELECT 1 FROM s1 WHERE NULL; +---+ | 1 | +---+ 0 rows in set Time: 0.016s mysql root@localhost:test> SELECT 1 FROM s1 WHERE FALSE; +---+ | 1 | +---+ 0 rows in set Time: 0.033s
所以只要我們的IN子查詢是放在 WHERE 或者 ON 子句中的，那么 IN -> EXISTS 的轉換就是沒問題的。說了這么多，為啥要轉換呢？這是因為不轉換的話可能用不到索引，比方說下邊這個查詢：
mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.t2_a>=999) or t3_b > 1000; +----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+ | 1 | PRIMARY | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where | | 2 | SUBQUERY | t2 | range | idx_a | idx_a | 5 | <null> | 107 | Using where; Using index | +----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+
但是將它轉為 EXISTS 子查詢后卻可以使用到索引：
mysql root@localhost:test> explain select * from t3 where exists (select 1 from t2 where t2.t2_a>=999 and t2.t2_a=t3.t3_a) or t3_b > 1000; +----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+ | 1 | PRIMARY | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ref | idx_a | idx_a | 5 | test.t3.t3_a | 1 | Using where; Using index | +----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+
需要注意的是，如果 IN 子查詢不滿足轉換為 semi-join 的條件，又不能轉換為物化表或者轉換為物化表的成本太大，那么它就會被轉換為 EXISTS 查詢?；蛘咿D換為物化表的成本太大，那么它就會被轉換為 EXISTS 查詢。
到此，關于“MySQL子查詢原理是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
