优化嵌套查询

优化嵌套查询

​ 子查询有些情况下可以被更有效的连接（JOIN）替代。因为连接（JOIN）不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。如下所示：可以看出查询关联的类型从 index_subquery 调整为 ref.

#子查询
mysql> desc select * from customer where customer_id not in(select customer_id from payment) \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: payment
   partitions: NULL
         type: index_subquery
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: func
         rows: 26
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

ERROR:
No query specified

# join 查询
mysql> desc 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
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: Using where; Not exists
2 rows in set, 1 warning (0.00 sec)