mysql 连接查询 和 子查询

子查询形式的执行计划

mysql> explain select * from user where id = (select uid from uu where id = 3)\G

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
table: user
         type: const
possible_keys: PRIMARY
key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
table: uu
         type: const
possible_keys: PRIMARY
key: PRIMARY
      key_len: 4
          ref:
         rows: 1
        Extra:
2 rows in set (0.00 sec)

子查询的结果

mysql> select * from user where id = (select uid from uu where id = 3);
+----+------+------+
| id | name | age  |
+----+------+------+
|  9 | qw13 |   18 |
+----+------+------+

连接查询的执行计划

mysql> explain select * from user left join uu on user.id = uu.uid where uu.id=3
 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
table: uu
         type: const
possible_keys: PRIMARY,uid
key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
table: user
         type: const
possible_keys: PRIMARY
key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
2 rows in set (0.02 sec)

链接查询的结果

mysql> select * from user left join uu on user.id = uu.uid where uu.id=3;
+----+------+------+----+------+-------+
| id | name | age  | id | uid  | other |
+----+------+------+----+------+-------+
|  9 | qw13 |   18 |  3 |    9 | asd   |
+----+------+------+----+------+-------+

未完待续……