一、EXPLAIN_extra
### --- extra 介绍 ——> Extra 是 EXPLAIN 另一个非常重要的输出列， ——> 该列显示MySQL查询过程中的一些详细信息
二、准备数据
CREATE TABLE users ( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(20), age INT(11) ); INSERT INTO users VALUES(NULL, 'lisa',10); INSERT INTO users VALUES(NULL, 'lisa',10); INSERT INTO users VALUES(NULL, 'rose',11); INSERT INTO users VALUES(NULL, 'jack', 12); INSERT INTO users VALUES(NULL, 'sam', 13);### --- Using filesort EXPLAIN SELECT * FROM users ORDER BY age; mysql> EXPLAIN SELECT * FROM users ORDER BY age; ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- ——> 执行结果Extra为Using filesort ，这说明获得所需的结果集， ——> 需要对所有记录进行文件排序。 ——> 这类SQL语句性能极差，需要优化。 ——> 典型的，在没有索引的列中order by，就会触发filesort， ——> 常见的优化方案是，在order by在列中添加索引，以避免每次查询都完全排序。 ——> filtered 它指返回结果的行占需要读取的行(rows列值)百分比### --- Using temporary EXPLAIN SELECT COUNT(*),sex FROM users WHERE uid > 2 GROUP BY sex; mysql> EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa'; ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- ——> 执行结果Extra为Using temporary ， ——> 这表明需要建立临时表 (temporary table) 暂存中间结果。 ——> 常见与 group by 和 order by，这类SQL语句性能低，往往需要优化。### --- Using where ~~~ 这句话的执行结果Extra为Using where，表示使用了where过滤数据的条件 EXPLAIN SELECT * FROM users WHERE age=10; mysql> EXPLAIN SELECT * FROM users WHERE age=10; ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- ### --- 需要注意的是： ——> 1. 返回所有记录SQL，不使用where高概率的条件过滤数据不符合预期， ——> 对于这类SQL通常需要优化； ——> 2. 使用了where条件的SQL，这并不意味着不需要优化， ——> 往往需要配合explain结果中的type综合判断(连接类型)。 ——> 例如本例查询的 age 没有索引，所以返回type为ALL，索引优化查询仍有优化空间。### --- Using index ——> 这句话的执行结果是Extra为Using index，说明sql所有需要返回的数据都在索引树上，无需访问实际行记录。 EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa'; mysql> EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa'; ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+### --- Using join buffer
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex =
'0') u2 ON u1.uname = u2.uname;——> 执行结果Extra为Using join buffer (Block Nested Loop) 说明，
——> 需要进行嵌套循环计算, 这里每个表都有五条记录，内外表查询的type都为ALL。
——> 问题在于 两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。
——> 常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。