MySQL子查询的优化

一、 MySQL 子查询的位置 当一个查询是另一个查询的子部分是，称之为子查询（查询语句中嵌套含有查询语句）。子查询也是使用频率比较高的一种查询类型。因此，优化子查询，对于整个系统的性能也有直接的影响。 从查询出现在 SQL 语句的位置来看，它可以出现

一、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 warnings\G
*************************** 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 warnings\G
*************************** 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 warnings\G
*************************** 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：执行情况的说明和描述。