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

5.Using index

这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。上面的例子中有很多这样的例子，不再多举例了。

6.Using temporary

发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。

1. mysql> explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id;
2. +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
5. | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 100395 | Using temporary; Using filesort |
6. | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | |
7. +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
8. 2 rows in set (0.00 sec)

7.Using where

当有where子句时，extra都会有说明。

8.Using sort_union(...)/Using union(...)/Using intersect(...)

下面的例子中user_id是一个检索范围，此时mysql会使用sort_union函数来进行索引的合并。而当user_id是一个固定值时，请参看上面type说明5.index_merge的例子，此时会使用union函数进行索引合并。

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

对于Using intersect的例子可以参看下例，user_id与express_type发生了索引交叉合并。

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

9.Using index for gr