MySQL基础优化之explain使用

explain 详解：

 

作用：主要用来调取语句的执行计划，主要是判断语句是否走索引。

 

explain select stu_name,gender,age from stu where gender='F' and age <20;

 

mysql> explain select name,gender,age from test where gender='F' and age <20;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | inx_test      | inx_test | 7       | NULL |    1 | Using index condition |

 

explain查询结果中我们需要重点关注两个字段：type 和 extra

 

 type

需要关注的第一个字段也是最重要的字段.

 

type : 表示MySQL在表中找到所需行的方式，又称“访问类型”，

 

常见类型如下:

 

ALL, index, range, ref, eq_ref, const, system, NULL

 

从左到右，性能从最差到最好。range是我们所能接受的最低的范围，最不希望看到(ALL, index)。这是有问题的，需要优化。

 

1. ALL

Full Table Scan， MySQL将遍历全表以找到匹配的行

 

如果显示ALL，说明：查询没有走索引。原因如下：

1、语句本身的问题

2、索引的问题，没建立索引

 

2. index

index：Full Index Scan，index与ALL区别为index类型只遍历索引树

例子：

explain select count(*) from stu ;

　　

3. range

range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。

显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

where 条件中有范围查询或模糊查询时

> < >= <= between and in () or

like 'xx%'

 

当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

 

4. ref

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

 

where stu_name='xiaoming'

 

explain select * from stu where stu_name='aa';

这是我们添加完普通索引最希望看到的类型

 

5. eq_ref

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，

就是多表连接中使用primary key或者 unique key作为关联条件

 

join条件使用的是primary key或者 unique key

 

6. const、system

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

 

explain select * from city where id=1;

　　 

7. NULL

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，

例如从一个索引列里选取最小值可以通过单独索引查找完成。

 

 

Extra

如果extra中出现以下几个字段很有可能是因为没有走 索引的排序。可能是由于一部分字段没有走索引。

 

Using temporary

Using filesort

Using join buffer

 

 

排序 order by ,group by ,distinct,排序条件上没有索引

 

explain select * from city where countrycode='CHN' order by population;

# 如果population没有建立索引就会出现上述情况

 

在join 的条件列上没有建立索引

 

其他需要关注的字段

 

key_len: 越短越好，如果太长的话，建议替换为前缀索引。

rows(取到的结果所需要扫描的行数):越短越好，如果太长建议要对语句进行优化。