MySQL子查詢的優化薦

zhilight發表於2015-04-19

一、MySQL子查詢的位置

當一個查詢是另一個查詢的子部分是，稱之為子查詢（查詢語句中巢狀含有查詢語句）。子查詢也是使用頻率比較高的一種查詢型別。因此，優化子查詢，對於整個系統的效能也有直接的影響。

從查詢出現在SQL語句的位置來看，它可以出現在目標列中，也可以出現在from子句中，還可以出現在JOIN/ON子句、GROUPBY子句、HAVING子句、ORDERBY子句等位置。下面依次來看這幾種形式的子查詢，以及對他們進行優化的一些想法。

1、子查詢出現在目標列位置

當子查詢出現在目標列位置的時候，這種查詢只能是標量子查詢。也就是說子查詢返回的結果只能是一個元組的一個屬性。否則，資料庫會返回錯誤資訊。

下面為了實驗上面這段話，我們來新建一些表，並插入一些資料。

create table t1 (k1 int primary key, c1 int);
create table t2 (k2 int primary key, c2 int);
insert into t2 values (1, 10), (2, 2), (3,30);

a、此時若我們執行如下SQL語句的結果為：

mysql> select t1.c1, (select t2.c2 from t2) from t1, t2;
Empty set (0.00sec)

b、然後，我們往t1表中插入一些資料：

mysql> insert into t1 values (1, 1), (2, 2), (3, 3);
Query OK, 3 rows affected (0.00 sec)

c、此時，我們再次執行a中的查詢，我們可以看到執行的結果

mysql>select t1.c1, (select t2.c2 from t2) from t1, t2;
ERROR 1242(21000): Subquery returns more than 1 row

d、此時我們清空t2表，然後再執行a中所做的查詢。

mysql>delete from t2;
QueryOK, 3 rows affected (0.00 sec)
 
mysql> select t1.c1, (select t2.c2 from t2) from t1, t2;
Empty set (0.00 sec)

此時返回的結果就又正常了。

e、我們進一步實驗。現在我們把剛剛從t2表中刪除的資料在插入到t2表：

mysql>insert into t2 values (1, 10), (2, 2), (3, 30);
Query OK,3 rows affected (0.00 sec)

然後執行如下查詢：

mysql> select t1.c1, (select t2.c2 from t2 where k2=1) from t1, t2;
+------+-----------------------------------+
| c1   | (select t2.c2 from t2 where k2=1) |
+------+-----------------------------------+
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
+------+-----------------------------------+

我們可以清楚的看到MySQL為我們返回的結果。

f、我們對e中的查詢再換一種寫法，可以看到返回的結果為

mysql> select t1.c1, (selectt2.c2 from t2 where c2 > 1) from t1, t2;
ERROR 1242 (21000): Subqueryreturns more than 1 row

通過以上實驗，我們可以得出這樣一個結論：了子查詢必須只能返回一個元組中的一個屬性。或者，更嚴謹的說，出現在目標列上的子查詢只能返回標量，即空值或單個元組的單個屬性。

2、子查詢出現在FROM字句的位置

簡單來說，FROM子句部分的子查詢只能是非相關子查詢，非相關子查詢出現在FROM子句中可以上拉到父層，在多表連線時統一考慮連線代價然後進行優化。

如果是相關子查詢出現在FROM字句中，資料庫可能返回錯誤提示。

接下來我們還是來看一些例子：

我們故意在FROM字句位置處使用相關子查詢

mysql> select * from t1, (select *from t2 where t1.k1 = t2.k2);
ERROR 1248 (42000): Every derived table musthave its own alias

我們把相關條件去掉後可以得出：

mysql> select * from t1, (select * from t2) as a_t2;
+----+------+----+------+
| k1 | c1   | k2 | c2   |
+----+------+----+------+
|  1 |    1 |  1 |   10 |
|  2 |    2 |  1 |   10 |
|  3 |    3 |  1 |   10 |
|  1 |    1 |  2 |    2 |
|  2 |    2 |  2 |    2 |
|  3 |    3 |  2 |    2 |
|  1 |    1 |  3 |   30 |
|  2 |    2 |  3 |   30 |
|  3 |    3 |  3 |   30 |
+----+------+----+------+
9 rows in set (0.00 sec)

3、子查詢出現在WHERE子句當中

出現在WHERE子句中的子查詢，是一個條件表示式的一部分，而表示式可以分為操作符和運算元；根據參與運算的操作符的不同型別，操作符也不盡相同。如INT型有>,<,=,<>等操作。這時對子查詢有一定的要求（如INT型的等值操作，要求子查詢必須是標量子查詢）。另外子查詢出現在WHERE字句中的格式，也有用謂詞指定的一些操作，如IN，BETWEEN，EXISTS等。

4、JOIN/ON字句位置

JOIN/ON子句可以分為兩部分，一是JOIN塊，類似於FROM子句。二是ON子句塊，類似於WHERE子句。這兩部分都可以出現子查詢。子查詢的處理方式同FROM子句和和WHERE子句。

二、子查詢的型別

1、從查詢物件間的關係上來區分

從查詢物件間的關係上來區分，子查詢可以分為相關子查詢和非相關子查詢。

相關子查詢：子查詢的執行依賴於外層父查詢的一些屬性的值。子查詢依賴於父查詢的一些引數，當父查詢的引數改變時，子查詢需要根據新引數值重新執行。下面給出一個例子：

mysql> select * from t1 where c1 = ANY (select c2 from t2 where t2.c2 = t1.c1);
+----+------+
| k1 | c1   |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.12 sec)

非相關子查詢：子查詢的執行不依賴與外層父查詢的任何屬性。這樣的子查詢具有獨立性，可以獨自求解，形成的一個子查詢計劃先與外層的查詢求解。下面給出一個例子：

mysql> select * from t1 where c1 = ANY(select c2 from t2 where t2.c2=10);
Empty set (0.02 sec)

2、從特定的謂詞來區分

[NOT] IN/ALL/ANY/SOME子查詢：語義相近，表示“[取反] 存在、所有、任何、任何”，左邊的運算元，右邊是子查詢，是最常見的子查詢型別之一。

[NOT] EXISTS子查詢：半連線語義，表示“[取反]存在”，沒有左運算元，右邊是子查詢，也是最常見的子查詢型別之一。

（PS：子查詢的分類還可以從語句的構成的複雜程度和查詢的結果等方面來進行分類，這裡不再贅述，我們把重點放在如何對子查詢進行優化上）

三、如何對子查詢進行優化

1、子查詢合併

在某些情況下，多個子查詢可以合併為一個子查詢。合併的條件是語義等價，即合併前後的查詢產生相同的結果集。合併後還是子查詢，可以通過其他技術消除子查詢。這樣可以把多次表掃描，多次表連線轉化為單次表掃描和單次表連線，例如：

mysql> select * from t1 where k1 < 10 and (
    -> exists(select k2 from t2 where t2.k2 < 5 and t2.c2 = 1) or
    -> exists(select k2 from t2 where t2.k2 < 5 and t2.c2 = 2)
    -> );
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.12 sec)

我們可以檢視這條語句的查詢執行計劃：

mysql> explain extended select * from t1 where k1 < 10 and (
    -> exists(select k2 from t2 where t2.k2 < 5 and t2.c2 = 1) or
    -> exists(select k2 from t2 where t2.k2 < 5 and t2.c2 = 2)
    -> );
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
|  3 | SUBQUERY    | t2    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
|  2 | SUBQUERY    | t2    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

可以看到，這條查詢語句有兩個子查詢。

我們把這條語句化簡：

mysql> select * from t1 where k1 < 10 and (
    -> exists(select k2 from t2 where t2.k2 < 5 and (t2.c2 = 1 or t2.c2 = 2))
    -> );
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

我們再來檢視這一條語句的查詢執行計劃：

mysql> explain extended select * from t1 where k1 < 10 and (
    -> exists(select k2 from t2 where t2.k2 < 5 and (t2.c2 = 1 or t2.c2 = 2))
    -> );
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
|  2 | SUBQUERY    | t2    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

很明顯，我們已經消除了一套子查詢，但是最後結果是一樣的。

兩個EXISTS子句可以合併為一個，條件也進行了合併。

2、子查詢展開

又稱為子查詢的反巢狀或者是子查詢的上拉。把一些子查詢置於外層的父查詢中，其實質是把某些子查詢轉化為等價的多表連線操作。帶來的一個明顯的好處就是，有關訪問路徑，連線方法和連線順序可能被有效的利用，使得查詢語句的層次儘可能的減少。

常見的IN、SOME、ALL、EXISTS依據情況轉換為半連線（SEMI JOIN）、普通型別的子查詢等情況屬於此類。我們直接比較兩條語句的查詢執行計劃：

mysql> explain select * from t1, (select * from t2 where t2.k2 > 10) v_t2 where t1.k1 < 10 and v_t2.k2 < 20;
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | PRIMARY     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where                                        |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t2         | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where                                        |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

優化後可以表示為：

mysql> explain extended select * from t1 where t1.a1 < 100 and t1.a1 in(select a2 from t2 where t2.a2 > 10); 
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | range  | PRIMARY       | PRIMARY | 4       | NULL         |   88 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.t1.a1 |    1 |   100.00 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.32 sec)

我們完全把它變成了簡單查詢。

四、MySQL可以支援什麼格式的子查詢

1、MySQL支援什麼型別的子查詢（1）簡單的select查詢中的子查詢。

（2）帶有DISTINCT,ORDERBY,LIMIT操作簡單select查詢中的子查詢。（非SPJ查詢不能被優化）

為了對這些查詢做出一些測試，我們來新建一些表，並且批量插入一些資料。

下面這段Python程式碼實現了建立三張表，並且每張表裡插入15000條資料：

import MySQLdb as mdb 
import random

host = `10.12.128.12`
name = `root`
password = `123456`
db = `testdb`
try:
	conn = mdb.connect(host, name, password, db)
	cur = conn.cursor()
	for i in range(1, 4):
		sql = `create table t%d(a%d int primary key auto_increment, b%d int)` % (i, i, i)
		cur.execute(sql)
		for j in range(1, 15000):
			value = random.randint(1, 15000)
			s = `insert into t%d(b%d) values(%d)` % (i, i, value)
			cur.execute(s);
	cur.close()
finally:
	if conn:
		conn.close()

2、MySQL不支援對什麼樣的子查詢進行優化

帶有UNOIN操作的查詢

帶有GROUPBY、HAVING、聚集函式的查詢

使用ORDERBY中帶有LIMIT的查詢

內表外表的連線數超過MySQL最大表的連線數

下面我們就來簡單驗證一下第一個：

帶有GROUPBY、HAVING、聚集函式的查詢

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1>(SELECT MIN(t2.a2) FROM t2);
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | t1    | range | PRIMARY       | PRIMARY | 4       | NULL | 7534 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+
2 rows in set, 1 warning (0.11 sec)

查詢執行的結果依然含有子查詢，所以MySQL不支援對這種查詢進行優化

2、MySQL查詢優化例項

MySQL對NOT IN型別的子查詢進行優化

mysql> explain  extended select * from t1 where t1.a1 NOT IN (select a2 from t2 where t2.a2 > 10);
+----+-------------+-------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
|  1 | PRIMARY     | t1    | ALL   | NULL          | NULL    | NULL    | NULL | 15068 |   100.00 | Using where              |
|  2 | SUBQUERY    | t2    | range | PRIMARY       | PRIMARY | 4       | NULL |  7534 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

通過反編譯查詢語句我們可以發現，雖然子查詢沒有被消除，但是NOT IN子查詢被物化，達到了部分優化的結果。

mysql> show warningsG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where (not(<in_optimizer>(`testdb`.`t1`.`a1`,`testdb`.`t1`.`a1` in ( <materialize> (/* select#2 */ select `testdb`.`t2`.`a2` from `testdb`.`t2` where (`testdb`.`t2`.`a2` > 10) ), <primary_index_lookup>(`testdb`.`t1`.`a1` in <temporary table> on <auto_key> where ((`testdb`.`t1`.`a1` = `materialized-subquery`.`a2`)))))))
1 row in set (0.00 sec)

MySQL對ALL型別的子查詢進行優化：

mysql> explain extended select * from t1 where t1.a1 > ALL(select a2 from t2 where t2.a2 > 10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 15068 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.03 sec)

反編譯可以看到ALL被優化為>MAX的操作。

mysql> show warningsG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where <not>((`testdb`.`t1`.`a1` <= (/* select#2 */ select max(`testdb`.`t2`.`a2`) from `testdb`.`t2` where (`testdb`.`t2`.`a2` > 10))))
1 row in set (0.00 sec)

MySQL對SOME型別的子查詢進行優化

mysql> explain extended select * from t1 where t1.a1 > SOME (select a2 from t2 where t2.a2 > 10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 15068 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到對SOME型別的操作轉化為對MIN型別的操作

mysql> show warningsG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where <nop>((`testdb`.`t1`.`a1` > (/* select#2 */ select min(`testdb`.`t2`.`a2`) from `testdb`.`t2` where (`testdb`.`t2`.`a2` > 10))))
1 row in set (0.00 sec)

對ANY型別的優化和對SOME型別的優化等同

附：explain的用法

explain語句用於檢視一條SQL語句的查詢執行計劃，用法很簡單，直接把explain放到要執行的SQL語句的前面即可。explain extended和explain的輸出結果一樣，只是用explain extended語句後可以通過show warnings檢視一條SQL語句的反編譯的結果，讓我們知道我們輸入的一條SQL語句真正是怎麼執行的。

對輸入結果簡單解釋一下：

select_type：表示select型別，常見的取值有SIMPLE（不使用表連線或子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的或者後面的查詢語句）、SUBQUERY（子查詢中的第一個select）等。
table：輸出結果集的表。
type：表示表的連線型別，效能由好到差的連線型別為system（表中僅有一行，即常量表）、const（單表中最多有一個匹配行，例如PRIMARY KEY或者UNIQUE INDEX）、eq_ref（對於前面的每一行，在此表中只查詢一條記錄，簡單來說，就是多表連線中使用PRIMARYKEY或者UNIQUE INDEX）、ref（與eq_ref類似，區別在於不使用PRIMARYKEY或者UNIQUE INDEX，而是使用普通的索引）、ref_of_null（與ref類似，區別在於條件中包含對NULL的查詢）、index_merge（索引合併化）、unique_subquery（in的後面是一個查詢主鍵欄位的子查詢）、index_subquery（與unique_subquery類似，區別在於in的後面是查詢非唯一索引欄位的子查詢）、range（單表中的範圍查詢）、index（對於前面的每一行都通過查詢索引來得到資料）、all（對於前面的每一行的都通過全表掃描來獲得資料）。
possible_keys：表示查詢時，可能使用到的索引。
key：表示實際使用的索引
key_len：索引欄位的長度
rows：掃描行的數量
extra：執行情況的說明和描述。

mysql子查詢的缺陷以及5.6的優化
2015-02-05
MySql優化
Mysql優化系列之——優化器對子查詢的處理
2020-01-21
MySql優化
MySQL 的查詢優化
2020-01-07
MySql優化
MySQL查詢優化
2021-03-04
MySql優化
exists與in子查詢優化
2019-04-09
優化
優化-mysql子查詢索引失效問題解決
2020-11-05
優化MySql索引
MySQL 慢查詢優化
2019-06-23
MySql優化
MySQL優化COUNT()查詢
2019-01-14
MySql優化
MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）
2020-04-11
MySql優化
mysql查詢優化檢查 explain
2018-05-04
MySql優化AI
MySQL調優之查詢優化
2021-11-09
MySql優化
mysql的子查詢
2010-12-05
MySql
Oracle not exist子查詢全掃的優化
2016-08-12
Oracle優化
優化擁有謂詞or的子查詢
2011-07-13
優化
MySQL查詢優化利刃-EXPLAIN
2020-06-08
MySql優化AI
MySQL索引與查詢優化
2019-04-13
MySql索引優化
MySQL分頁查詢優化
2020-10-13
MySql優化
mysql關聯查詢優化
2016-11-29
MySql優化
MySQL子查詢
2019-11-29
MySql
MYsql 子查詢
2018-05-15
MySql
MySQL-效能優化-索引和查詢優化
2018-06-03
MySql優化索引
MySQL——優化巢狀查詢和分頁查詢
2018-11-01
MySql優化巢狀
涉及子查詢sql的一次優化
2011-07-28
SQL優化
【資料庫】MySQL查詢優化
2019-04-21
資料庫MySql優化
Mysql 慢查詢優化實踐
2019-01-29
MySql優化
十七、Mysql之SQL優化查詢
2018-12-17
MySql優化
MySQL: 使用explain 優化查詢效能
2021-10-29
MySqlAI優化
mysql查詢效能優化總結
2020-12-23
MySql優化
MySQL查詢中分頁思路的優化BF
2022-03-21
MySql優化
MySQL聯結查詢和子查詢
2018-02-24
MySql
一個NOT EXISTS含有OR條件子查詢的優化
2014-10-10
優化
MySQL 索引及查詢優化總結
2019-01-02
MySql索引優化
MySQL分優化之超大頁查詢
2020-11-12
MySql優化
mysql update join優化update in查詢效率
2016-11-17
MySql優化
MySQL系列-- 4. 查詢效能優化
2017-10-22
MySql優化
MySQL索引原理及慢查詢優化
2015-05-03
MySql索引優化
優化mysql資料字典表查詢
2013-04-08
優化MySql
mysql效能優化-慢查詢分析、優化索引和配置
2013-12-17
MySql優化索引