程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL的子查詢及相干優化進修教程

MySQL的子查詢及相干優化進修教程

編輯：MySQL綜合教程

MySQL的子查詢及相干優化進修教程。本站提示廣大學習愛好者：（MySQL的子查詢及相干優化進修教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL的子查詢及相干優化進修教程正文

1、子查詢
1、where型子查詢
（把內層查詢成果看成外層查詢的比擬前提）

#不消order by 來查詢最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#掏出每一個欄面前目今最新的產物(goods_id獨一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

2、from型子查詢
(把內層的查詢成果供外層再次查詢)
#用子查詢查出掛科兩門及以上的同窗的均勻成就
思緒：

#先查出哪些同窗掛科兩門以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查詢成果，我們只需名字便可以了，所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出這些同窗了，那末再盤算他們的均勻分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

3、exists型子查詢
（把外層查詢成果拿到內層，看內層的查詢能否成立）

#查詢哪些欄面前目今有商品，欄目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

2、優化
從句式的情勢看，子查詢分為特別格局子查詢和非特別格局子查詢，特別格局的子查詢中又包含IN、ALL、ANY、SOME、EXISTS等類型的子查詢，關於有的類型的子查詢，MySQL有的支撐優化，有的不支撐，詳細情形以下。

 

示例一，MySQL不支撐對EXISTS類型的子查詢的優化：

EXISTS類型的相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1= t2.a2 AND t2.a2>10);


+----+--------------------+-------+------+------+-------------+
| id | select_type    | table | type | key | Extra    |

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

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

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

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

2 rows in set, 2 warnings (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where exists(/* select#2 */

  select 1

  from `test`.`t2`

  where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10))

)

從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。

別的的一個EXISTS類型的相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);


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

| id | select_type    | table | type | key | Extra    |

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

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

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

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

2 rows in set, 3 warnings (0.02 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where exists(/* select#2 */

  select 1

  from `test`.`t2`

  where ((`test`.`t1`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))

)

從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。

 

示例二，MySQL不支撐對NOT EXISTS類型的子查詢的優化：

NOT EXISTS類型的相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.a1= t2.a2 AND t2.a2>10);


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

| id | select_type    | table | type | key | Extra    |

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

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

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

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

2 rows in set, 2 warnings (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(exists(

  /* select#2 */ select 1

  from `test`.`t2`

  where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10))))

)

從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。

 

別的的一個NOT EXISTS類型的相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);


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

| id | select_type    | table | type | key | Extra    |

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

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

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

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

2 rows in set, 3 warnings (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(exists(

  /* select#2 */ select 1

  from `test`.`t2`

  where ((`test`.`t1`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))))

)

從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。

 

示例三，MySQL支撐對IN類型的子查詢的優化，按也有不支撐的情形存在：

IN非相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table    | type | key | Extra  |

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

| 1 | SIMPLE    | <subquery2> | ALL | NULL | NULL  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

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

3 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

從查詢履行籌劃看，表t2被物化後，與表t1履行了半銜接（semi join）。雖然有“subquery2”如許的內容看起來是子查詢，然則表t2曾經被上拉到表t1層履行了半銜接，所以MySQL支撐IN子查詢優化為半銜接操作。

 

別的一個IN非相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2=10);


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

| id | select_type | table    | type | key | Extra  |

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

| 1 | SIMPLE    | <subquery2> | ALL | NULL | Using where  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

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

3 rows in set, 1 warning (0.02 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

從查詢履行籌劃看，子查詢不存在，表t1和t2直接做了塊嵌套輪回半銜接（Block Nested Loop），把子查詢上拉到父查詢頂用嵌套輪回半銜接完成IN操作。別的，因為子查詢上拉，使得增長銜接前提“a1=a2”，而本來的前提“a2=10”可以應用常量傳遞優化技巧，使得“a1=a2=10”，所以查詢履行籌劃中，兩個索引掃描的前提分離為：a1 = 10、a2 = 10。

 

別的一個IN非相干子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1=10);


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

| id | select_type | table | type | Extra      |

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

| 1 | SIMPLE   | t2  | ALL | Using where; Start temporary      |

| 1 | SIMPLE   | t1  | ALL | Using where; End temporary; Using join buffer (Block Nested Loop)|

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

2 rows in set, 2 warnings (0.00 sec)

被查詢優化器處置後的語句為：
/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t2`.`a2` = 10) and (`test`.`t1`.`a1` = 10))

從查詢履行籌劃看，子子查詢不存在，表t1和t2直接做了塊嵌套輪回銜接（Block Nested Loop），但屬於半銜接操作（semi join），把子查詢上拉到父查詢頂用嵌套輪回半銜接完成IN操作。

 

示例四，MySQL支撐對NOT IN類型的子查詢的優化

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 | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.02 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(<in_optimizer>(

  `test`.`t1`.`a1`,`test`.`t1`.`a1` in (

    <materialize> (/* select#2 */

      select `test`.`t2`.`a2`

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

      having 1

    ),

    <primary_index_lookup>(

      `test`.`t1`.`a1` in <temporary table> on <auto_key>

      where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`))

    )

   )

  ))

)

從查詢履行籌劃看，表t2做了子查詢（SUBQUERY）。而子查詢被物化（materialize）。所以，MySQL關於NOT IN子查詢采取了物化的優化方法，但不支撐子查詢的清除。

 

別的一個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 | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(<in_optimizer>(

  `test`.`t1`.`a1`,`test`.`t1`.`a1` in (

    <materialize> (/* select#2 */

      select `test`.`t2`.`a2`

      from `test`.`t2`

      where (`test`.`t2`.`a2` = 10)

      having 1

    ),

    <primary_index_lookup>(

      `test`.`t1`.`a1` in <temporary table> on <auto_key>

      where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`))

    )

  )

  ))

)

從查詢履行籌劃看，表t2做了子查詢（SUBQUERY）。而子查詢被物化（materialize）。所以，MySQL關於NOT IN子查詢采取了物化的優化方法，但不支撐子查詢的清除。

 

示例五，MySQL支撐對ALL類型的子查詢的優化：

不相干的ALL子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ALL (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>((`test`.`t1`.`a1` <= <max>(

  /* select#2 */

  select `test`.`t2`.`a2`

  from `test`.`t2`

  where (`test`.`t2`.`a2` > 10)

  )

))

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“<= <max>”操作符限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>ALL”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。

 

不相干的ALL子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ALL (SELECT a2 FROM t2 WHERE t2.a2=10);


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

| id | select_type    | table | type | key | Extra    |

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

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

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>(<in_optimizer>(

  `test`.`t1`.`a1`,<exists>(

    /* select#2 */ select 1 from `test`.`t2`

    where ((`test`.`t2`.`a2` = 10) and

      <if>(outer_field_is_not_null,

        ((<cache>(`test`.`t1`.`a1`) <> 10) or <cache>(isnull(10))),

        true

      )

    )

    having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true)

  )

))

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則被查詢優化器處置後的語句中包括“exists”，這注解MySQL關於“=ALL”式的子查詢優化用“EXISTS strategy”方法優化，所以MySQL支撐“=ALL”式的子查詢優化。

 

不相干的ALL子查詢，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ALL (SELECT a2 FROM t2 WHERE t2.a2=10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>((`test`.`t1`.`a1` >= <min>

  (/* select#2 */

    select `test`.`t2`.`a2`

    from `test`.`t2`

    where (`test`.`t2`.`a2` = 10)

  )

))

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“>= <min>”操作符限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<ALL”式的子查詢優化，子查詢只被履行一次便可求得最小值。

 

示例六，MySQL支撐對SOME類型的子查詢的優化：

應用了“>SOME”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >SOME (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.05 sec)

被查詢優化器處置後的語句為：

 /* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

   `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>((`test`.`t1`.`a1` > (

  /* select#2 */

  select min(`test`.`t2`.`a2`)

  from `test`.`t2`

  where (`test`.`t2`.`a2` > 10)

)))

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“min”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>SOME”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。

 

應用了“=SOME”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =SOME (SELECT a2 FROM t2 WHERE t2.a2=10);


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

| id | select_type | table    | type | key | Extra  |

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

| 1 | SIMPLE    | <subquery2> | ALL | NULL | Using where  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

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

3 rows in set, 1 warning (0.01 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

`test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

從查詢履行籌劃看，沒有湧現了子查詢，表t2被物化，與表t1停止了半銜接。

 

應用了“<SOME”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <SOME (SELECT a2 FROM t2 WHERE t2.a2=10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` < (/* select#2 */

      select max(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` = 10)

    )

  )

)

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“max”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<SOME”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。

 

示例七，MySQL支撐對ANY類型的子查詢的優化：

應用了“>ANY”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ANY (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` > (/* select#2 */

      select min(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

    )

  )

)

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“min”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>ANY”式的子查詢優化，子查詢只被履行一次便可求得最小值。

 

應用了“=ANY”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ANY (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table    | type | key | Extra  |

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

| 1 | SIMPLE    | <subquery2> | ALL | NULL | NULL  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

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

3 rows in set, 1 warning (0.02 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

從查詢履行籌劃看，沒有湧現了子查詢，表t2被物化，與表t1停止了半銜接。

 

應用了“<ANY”式子的子查詢被優化，查詢履行籌劃以下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ANY (SELECT a2 FROM t2 WHERE t2.a2>10);


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

| id | select_type | table | type | key | Extra    |

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

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

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

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

2 rows in set, 1 warning (0.00 sec)

被查詢優化器處置後的語句為：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` < (/* select#2 */

      select max(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

    )

  )

)

從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“max”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<ANY”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。

  1. 上一頁:
  2. 下一頁:
MySQL綜合教程
MySQL4.0下中文亂碼的破解

我們大家都知道MySQL4.0在實際操作中會有中文亂碼的問
mysql點滴_01查看錯誤碼的方法

mysql點滴_01查看錯誤碼的方法 1 查看官方的幫助
mysql生產環境____主從同步修復案例

mysql生產環境____主從同步修復案例 一、 硬件環
WordPress &lt;4.1.2 &amp; &lt;=4.2 存儲型xss(利用mysql對特殊字符和超長字符會進行截斷的特性)，wordpress4.1.2

WordPress <4.1.2 & <
MySQL學習筆記_10_MySQL高級操作（下）

MySQL學習筆記_10_MySQL高級操作（下） &#
mysql安裝配置詳解教程（一），mysql安裝配置

mysql安裝配置詳解教程（一），mysql安裝配置MySQ
相關文章
閱讀排行榜
Mysql源碼進修筆記 竊視線程 mysql SELECT語句去除某個字段的反復信息 8、表中數據的排序與分組 MYSQL數據庫數據拆分之分庫分表總結 mysql命令行中執行sql的幾種方式總結 MySQL運用show status檢查MySQL服務器形態信息 Windows下mysql忘記密碼的解決方法 MYSQL入門學習之十二：存儲過程的基本操作 Mysql主主復制 關於MYSQL LOAD DATA LOCAL INFILE支持問題 Linux下mysql忘記root用戶密碼
熱門圖文
sql 常用語句積累 MySQL show的用法 Oracle rac是不支持一個節點是rhel5.x,另一個節點是rhel6.x的 MySQL alter用法 怎樣向 數據庫 中添加新訂單 修改MySQL root密碼5步驟介紹 SQL Server 復制需要有實際的服務器名稱才能連接到服務器 數據庫-索引、基本表創建與刪除
欄目導航
MYSQL入門知識SQL基礎知識關於MYSQL數據庫Mariadb數據庫MySQL綜合教程
Copyright © 程式師世界 All Rights Reserved