mysql子查詢_MySQL子查詢的優化

一、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)

-> );

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

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

-> );

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

| 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;

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------------+

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

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------+-------------+

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------+-------------+

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

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+

| 1 | PRIMARY | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 7534 | 100.00 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+------------------------------+

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

+----+-------------+-------+-------+---------------+---------+---------+------+-------+----------+--------------------------+

+----+-------------+-------+-------+---------------+---------+---------+------+-------+----------+--------------------------+

| 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 warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where (not((`testdb`.`t1`.`a1`,`testdb`.`t1`.`a1` in ( ( select `testdb`.`t2`.`a2` from `testdb`.`t2` where (`testdb`.`t2`.`a2` > 10) ), (`testdb`.`t1`.`a1` in on 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);

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 15068 | 100.00 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

2 rows in set, 1 warning (0.03 sec)

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

mysql> show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where ((`testdb`.`t1`.`a1` <= ( 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);

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 15068 | 100.00 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+

2 rows in set, 1 warning (0.00 sec)

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

mysql> show warnings\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select `testdb`.`t1`.`a1` AS `a1`,`testdb`.`t1`.`b1` AS `b1` from `testdb`.`t1` where ((`testdb`.`t1`.`a1` > ( 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優化資料庫資料庫

mysql子查詢_MySQL子查詢的優化

繼續閱讀

妙用SQL Server聚合函數和子查詢疊代求和

【MySQL】子查詢之一

MYSQL子查詢與連接配接

MySQL 子查詢

mysql的查詢、子查詢及連接配接查詢

mysql 連接配接查詢和子查詢

mysql查詢、子查詢、連接配接查詢

MYSQL查詢--子查詢，合并查詢子查詢合并查詢

【MySQL】子查詢之一

Mysql 的子查詢子查詢：

MYSQL——子查詢

Resource interpreted as Stylesheet but transferred with MIME type text/html: "http://127.0.0.1:8004/login". 20:22:46.254 127.0.0.1/:1 Refused to execu

Mysql的子查詢相關知識，少但是精

mysql in查詢_mysql 子查詢(in,all,exists,any) (**)