Mysql子查询及优化

子查询：当一个查询是另一个查询的子部分时，称之为子查询。

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);
mysql> SELECT t1.c1, (SELECT t2.c2 FROM t2) FROM t1, t2;

Empty set (0.00 sec)


mysql> insert into t1 values (1,1), (2,2), (3,3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0


mysql> SELECT t1.c1, (SELECT t2.c2 FROM t2) FROM t1, t2;

ERROR 1242 (21000): Subquery returns more than 1 row


mysql> DELETE FROM T2;

Query OK, 3 rows affected (0.01 sec)


mysql> SELECT t1.c1, (SELECT t2.c2 FROM t2) FROM t1, t2;

Empty set (0.00 sec)
mysql> insert into t2 values (1,10), (2,2), (3,30);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

SELECT t1.c1, (SELECT t2.c2 FROM t2 WHERE K2=1) FROM t1, t2;

SELECT t1.c1, (SELECT t2.c2 FROM t2 WHERE c2=1) FROM t1, t2;

mysql> SELECT t1.c1, (SELECT t2.c2 FROM t2 WHERE c2>1) FROM t1, t2;

ERROR 1242 (21000): Subquery returns more than 1 row
SELECT t1.c1, (SELECT t2.c2 FROM t2 WHERE c2=10) FROM t1, t2;
mysql> INSERT INTO t2 VALUES (4,10);

Query OK, 1 row affected (0.00 sec)
mysql> SELECT t1.c1, (SELECT t2.c2 FROM t2 WHERE c2=10) FROM t1, t2;

ERROR 1242 (21000): Subquery returns more than 1 row

from子句的位置：
SELECT * FROM t1, (SELECT * FROM t2) as A_t2;

where子句位置：
SELECT * FROM t1 WHERE k1 IN (SELECT k2 FROM t2);
SELECT * FROM t1 WHERE k1 >=ANY (SELECT k2 FROM t2);

SELECT * FROM t1 WHERE k1 <=SOME (SELECT k2 FROM t2);

SELECT * FROM t1 WHERE k1 <=ANY (SELECT k2 FROM t2);

SELECT * FROM t1 WHERE NOT EXISTS (SELECT k2 FROM t2 WHERE k2=100);

子查询分类：
1.从对象间的关系看：相关子查询，非相关子查询
2.从特定谓词看：（1）[NOT]IN/ALL/ANY/SOME子查询（2）[NOT]exists子查询（3）其他子查询
3.从语句的构成复杂程度看：SPJ子查询，GROUPBY子查询，其他子查询
4.从结果的角度看：（1）标量子查询（2）单行单列子查询（3）多行单列子查询（4）表子查询
如何实现子查询优化：
1.子查询合并
在某些条件下，多个子查询能够合并成一个子查询，这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接。

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

可优化为：

select * from t1 where k1<10 and(
EXISTS (select k2 from t2 where t2.k2<5 and (t2.c2=1 or t2.c2=2)));

2.子查询展开
把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列，实质是把某些子查询重写为等价的多表连接操作。
展开的条件：
1.如果子查询中出现了聚集、GROUPBY、DISTINCT子句，则子查询只能单独求解，不可以拉到外层。
2.如果子查询只是一个简单格式的（SPJ格式）查询语句，则可以上拉子查询到外层，提高查询效率。

select * from t1,(select * from t2 where t2.k2>2) v_t2
where t1.k1<10 and v_t2.k2<20

可优化为：

select * from t1,t2
where t1.k1<10 and t2.k2<20 and t2.k2>2

3.聚集子查询消除
select * from t1 where t1.k1>(select avg(t2.k2) from t2)