|
using index： 该值表示这个SQL语句使用了覆盖索引(覆盖索引是指可以直接在索引列中得到想要的结果，而不用去回表)，此时效率最高
- mysql> explain select id from test;
- +----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
- | 1 | SIMPLE | test | index | NULL | idx_bnet | 9 | NULL | 68505 | Using index |
- +----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
这个例子中id字段为主键，但是key那里显示走的并不是主键索引，这个是因为mysql的所有二级索引中都会包含所有的主键信息，而mysql没有单独的存储主键索引，所以扫描二级索引的开销比全表扫描更快
using where： 表示存储引擎搜到记录后进行了后过滤(POST-FILTER)，如果查询未能使用索引，using where的作用只是提醒我们mysql要用where条件过滤结果集
- mysql> explain select * from test where id > 1;
- +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
- | 1 | SIMPLE | test | range | PRIMARY | PRIMARY | 8 | NULL | 34252 | Using where |
- +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
using temporary 表示mysql需要使用临时表来存储结果集，常见于排序和分组查询
- mysql> explain select * from test where id in (1,2) group by bnet_id;
- +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
- | 1 | SIMPLE | test | range | PRIMARY,IDX(event_key-bnet_Id),idx_bnet | PRIMARY | 8 | NULL | 2 | Using where; Using temporary; Using filesort |
- +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
