作为开发者必须掌握的mysql操作sql语句优化-showprocesslistandexplain(六)

6.unique_subquery

该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。

1. mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10);
2. +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
5. | 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
6. | 2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4 | func | 1 | Using where |
7. +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
8. 2 rows in set (0.00 sec)

7.index_subquery

该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引。

1. mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10);
2. +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
5. | 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
6. | 2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5 | func | 50324 | Using index; Using where |
7. +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
8. 2 rows in set (0.00 sec)

8.range

按指定的范围进行检索，很常见。

1. mysql> explain select * from t_order where user_id in (100,200,300);
2. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
5. | 1 | SIMPLE | t_order | range | user_id | user_id | 5 | NULL | 3 | Using where |
6. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
7. 1 row in set (0.00 sec)

9.index

在进行统计时非常常见，此联接类型实际上会扫描索引树，仅比ALL快些。

1. mysql> explain select count(*) from t_order;
2. +----+-------------+---------+-------+------------