21-优化嵌套查询

MySQL 4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

在下面的例子中，要从客户表customer中找到不在支付表payment中的所有客户信息：

mysql> explain select * from customer where customer_id not in (select customer_id from payment )\G

1. row

id: 1

select_type: PRIMARY

table: customer

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 653

Extra: Using where

2. row

id: 2

select_type: DEPENDENT SUBQUERY

table: payment

type: index_subquery

possible_keys: idx_fk_customer_id

key: idx_fk_customer_id

key_len: 2

ref: func

rows: 12

Extra: Using index

2 rows in set (0.00 sec)

如果使用连接（JOIN）来完成这个查询工作，速度将会快很多。尤其是当payment表中对customer_id建有索引，性能将会更好，具体查询如下：

mysql> explain select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null\G

1. row

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 653

Extra:

2. row

id: 1

select_type: SIMPLE

table: b

type: ref

possible_keys: idx_fk_customer_id

key: idx_fk_customer_id

key_len: 2

ref: sakila.a.customer_id

rows: 12

Extra: Using where; Not exists

2 rows in set (0.00 sec)

从执行计划中可以看出查询关联的类型从 index_subquery调整为了 ref，在MySQL 5.5以下版本（包括MySQL 5.5），子查询的效率还是不如关联查询（JOIN）。

连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。