mysql执行计划extra_四、MySQL优化之explain执行计划的extra属性

explain之extra

包含不适合在其他列中显示但十分重要的额外信息

using filesort：文件排序

表明MySQL会对数据使用一个外部的索引排序，

而不是按照表内的索引顺序读取，

mysql无法利用索引完成的排序操作称之为文件排序

常见于order by 排序

mysql> explain select * from tbl_person where age=23 order by address\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl_person

type: ref

possible_keys: idx_age_name_address

key: idx_age_name_address

key_len: 4

ref: const

rows: 3

Extra: Using where; Using index; Using filesort

排序时出现了useing filesort文件排序，对查询性能影响很大，需要优化

order by 排序尽量使用索引或者按复合索引的顺序使用排序字段

如：复合索引是c1_c2_c3，所以使用where c1='a' order by c2,c3这样就可以解决出现文件排序的问题

where c1='a' and c2='b' order by c3 这样的方式也可以

因为中间缺失了name所以，按age,name address顺序加上name索引即可

explain select * from tbl_person where age=23 order by name, address;

explain select * from tbl_person where age=23 and name='aa' order by address;

using temporary：临时表排序

使用了临时表保存中间结果， MySQL在对查询结果排序时使用临时表。 常见于order by 和group by

mysql> explain select * from tbl_person where age=23 GROUP BY address\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl_person

type: ref

possible_keys: idx_age_name_address

key: idx_age_name_address

key_len: 4

ref: const

rows: 3

Extra: Using where; Using index; Using temporary; Using filesort

出现using temporary临时表排序，必须优化

group by 一定尽量的按索引的个数顺序来，不然特别容易产生文件内排序临时表的创建很消耗系统性能的

因为中间缺失了name所以，按age,name address顺序加上name索引即可

explain select * from tbl_person where age=23 group by name, address;

explain select * from tbl_person where age=23 and name='aa' group by address;

using index ：查询索引文件

表示相应的select操作使用了覆盖索引(covering index), 避免访问了表的数据行，效率不错

如果同时出现useing where,表明索引被用来执行索引键值查找

如果没有同时出现using where，表示索引用来读取数据而非执行查找动作(条件)

这个状态无需优化

mysql> explain select * from tbl_person where age=23 order by name\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl_person

type: ref

possible_keys: idx_age_name_address

key: idx_age_name_address

key_len: 4

ref: const

rows: 3

Extra: Using where; Using index

这里使用age和name索引，name用作排序了

其他属性

using where:索引用作查询条件

using join buffer:使用连接缓存，join使用的多了，会出现缓存

inpossible where:where 字句的值总是false,不能获取任何数据

explain select age from tbl_person where name ='aa' and name='bb';

select table optimized away:在没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成阶段即完成优化

distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样的动作

SQL语句的explain重点分析的属性

type:访问类型，查看SQL的数据访问类型，最好优化到ref和range级别

key:实际用到的索引，判断是否使用到索引

key_len:索引长度，可以根据长度判断组合索引中哪些索引失效，与key配合使用

rows：最大扫描行数，

extra：重要的额外信息，重点优化useing filesort和useing temporary两个现象

覆盖索引

(covering index)为索引覆盖，查询列要被所建的索引覆盖

如果使用覆盖索引，一定要注意select列表中只取出需要的列，不可用select*

如果所有字段一起作索引会导致索引文件过大，查询性能下降

只要查询列表中是索引字段(主键，索引列，单个或者多个)即可

mysql> explain select age,name,address from tbl_person\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tbl_person

type: index

possible_keys: NULL

key: idx_age_name_address

key_len: 338

ref: NULL

rows: 9

Extra: Using index

该select列表中都是索引字段，使用了覆盖索引，不会扫描全表，所以尽量避免使用select*

使用到的数据库表

CREATE TABLE `tbl_person` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`address` varchar(100) NOT NULL DEFAULT '' COMMENT '地址',

`age` int(4) NOT NULL DEFAULT '0' COMMENT '年龄',

`name` varchar(10) NOT NULL DEFAULT '' COMMENT '名称',

PRIMARY KEY (`id`),

KEY `idx_age_name_address` (`age`,`name`,`address`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;