|
using filesort： 是指mysql无法利用索引直接完成排序(排序的字段不是索引字段)，此时会用到缓冲空间来进行排序
- mysql> explain select * from test order by bnet_id;
- +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
- | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 68505 | Using filesort |
- +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
using join buffer： 强调在获取连接条件时没有用到索引，并且需要连接缓冲区来存储中间结果。(性能可以通过添加索引或者修改连接字段改进)
- mysql> explain select * from test left join test2 on test.create_time = test2.create_time;
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
- | 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 959692 | 100.00 | NULL |
- | 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 958353 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
- +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
- 2 rows in set, 1 warning (0.00 sec)
Block Nested Loop是指Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
impossible where： 表示where条件导致没有返回的行
- mysql> explain select * from test where id is null;
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------+
- | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------+
using index condition： 是mysql 5.6 之后新加的特性，结合mysql的ICP(Index Condition Pushdown)特性使用。主要是优化了可以在索引(仅限二级索引)上进行 like 查找
如果extra中出现多个上面结果，则表示顺序使用上面的方法进行解析查询 （编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
