参考网址:https://blog.csdn.net/u010061060/article/details/52473244
执行计划是sql性能调优的有效工具,通过执行执行计划,我们能知道查询的瓶颈在哪里?是否需要索引以及更改改变sql语句。
一、打开执行计划
mysql执行计划默认是关闭的,需要打开
set profiling="ON";
使用show variables like "%profil%";查看是否打开执行计划
![](https://i-blog.csdnimg.cn/blog_migrate/a2cda922329031698dc107153526ea06.jpeg)
二、执行计划的应用
只需要在查询语句前添加explain 即可,mysql5.6以后支持update/delete 执行计划,以前需要写变异的select语句!!!
explain select * from t_estate where estae_id = 5;
![](https://i-blog.csdnimg.cn/blog_migrate/36aa49a2915d2be288328d509b7f89ec.jpeg)
三、名词解释
id:
SELECT识别符,数字越大越先执行,数值相同从上往下执行。
![](https://i-blog.csdnimg.cn/blog_migrate/c8506d0a766f2f077ecaf00a7886ac79.jpeg)
select_type:select类型
(1)、simple :简单类型,一般不包含子查询和union.
(2)、union : 联合查询。一般只union 后的select
(3)、primary : 主键查询。一般指外层的select。
(4)、derived :from中的select。
(5)、dependent subquery: 子查询中国的第一个select,取决于外面的查询。
..... 更多参考后面的参数表。
table: 输出的行所在的表。
type: 连接类型
system:system表只有一行数据。** 这是const的特例。
const: 使用primary key or unique索引,且只找到一条数据。
eq_ref:使用primary key or unique索引时,从前表中找到一行与之匹配。** 也就是使用primary or unique是一对多的关系,例如teacher_id设置为主键,查询该teacher所有的student,就会使用到eq_ref。
![](https://i-blog.csdnimg.cn/blog_migrate/1f19db322075a022be9e53ec89b48c1a.jpeg)
ref: ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是 UNIQUE 或 PRIMARY KEY (换句话说,如果联接不能基于关键字选择单个行的话),则使用 ref 。如果使用的键仅仅匹配少量行,该联接类型是不错的
ref_or_null
:
该联接类型如同
ref
,但是添加了
MySQL
可以专门搜索包含
NULL
值的行。在解决子查询中经常使用该联接类型的优化。
range: 只检索给定范围的行,使用一个索引来选择行。
index:
该链接类型与all相同,除了只有索引树被扫描。
all: 全表扫描。
possible_keys:找到数据所在行能够使用的索引。
keys: mysql使用的索引。
key_len:mysql使用的长度。
ref:
列显示使用哪个列或常数与
key
一起从表中选择行。
rows:mysql 认为他执行查询必须要执行的行数,
filtered:显示了通过条件过滤的行数的百分比估计值。
extra:
该列包含
MySQL
解决查询的详细信息。
d
istinct:
MySQL
发现第
1
个匹配行后,停止为当前的行组合搜索更多的行。一直没见过这个值
not exists:
range checked for each record:没有找到合适的索引。
using filesort:一般看到这里就需要优化了,一般这是是使用mysql本身的算法进行排序吧
** mysql手册解释:
MySQL
需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配
WHERE
子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
use index: 使用索引,不需要操作实际的行来检索表中的信息。
using temporary:看到这里需要优化,一般是又order by or group by 造成的,是由于对非驱动表进行排序。(
对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序
(Important!)
)
user where : where 自居用于限制那一个行匹配下一个表或发送到客户。
-------------------------------------- explain 参数表-------------------------------------------
id | SELECT识别符。这是SELECT的查询序列号 |
select_type | SELECT类型,可以为以下任何一种:
|
table | 输出的行所引用的表 |
type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
ref | 显示使用哪个列或常数与key一起从表中选择行。 |
rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
Extra | 该列包含MySQL解决查询的详细信息
|
![](https://i-blog.csdnimg.cn/blog_migrate/9973298ed20f0b648a7a4c5ff85b3b4c.png)
解决办法:
在my.cnf 里面设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
在sql_mode 中去掉only_full_group_by