文档章节

【mysql】mysql 调优之 ——执行计划 explain

o
 osc_a22drz29
发布于 2019/03/24 15:46
字数 1597
阅读 7
收藏 0

精选30+云产品,助力企业轻松上云!>>>

1.what is explain(explain 是个什么东东)

explain(解释),在 Mysql 中 作为一个关键词,用来解释 Mysql 是如何执行语句,可以连接 select 、delete、insert、update 语句。

通常我们使用 explain 连接 一条 select 语句,查看运行状态,判断是否需要优化。

2.how to use explain(如何使用呢)

 

栗子:

explain select s.name,s.id,s.age,s.create_time from student s;

  

输出:

  

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  

官方:

EXPLAIN  [explain_type]  explainable_stmt


explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

 

输出的列名:

  • id : select 标识符
  • select_type:select 类型

 

select_type 可选值 含义
SIMPLE 简单的 select,没有使用 UNION 或者 子查询
PRIMARY 最外一层的 select
UNION UNION 中第二个或者后面的 select 语句
DEPENDENT UNION UNION 中第二个或者后面的 select 语句,依赖于外层的 select
UNION RESULT UNION 的结果
SUBQUERY 子查询的第一个 select
DEPENDENT SUBQUERY 子查询的第一个 select,取决于外层的 select
DERIVED 派生表
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY 无法缓存结果的子查询,必须为外部查询的每一行重新计算其结果
UNCACHEABLE UNION UNION 查询中不可缓存的子查询中的第二个或者后一个 select
  • table:输出行对应的表
  • partitions:匹配的分区
  • type:join 类型
  • possible_keys:可选的索引,可以通过 show index from tbl_name 查看表有哪些索引。
  • key:实际选择的索引
  • key_len:实际使用索引的长度
  • ref:与索引比较的列
  • rows:扫描行数的预估值
  • filtered:按表条件筛选的行的百分比
  • Extra:额外信息

 3.重点关注的列

3.1 type 列

type 列描述了表的 join 类型,以下以 查询的最优到最差的排序列出了可能值:

  • system :当表只有一条数据(= system table)时,为 system 类型,是 const 类型的 特例。

  

  • const:当表最多只有一条数据相匹配时,为 const 类型。因为只有一行,所以优化器的其余部分可以将此行列中的值视为常量(constant)。const表非常快,因为它们只读一次。在使用 主键 或者 唯一索引 和常量比较时,即为 const 类型。

如下的查询,tbl_name 可以被用作 const 表:

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

  

栗子:

explain select s.* from student s where s.id = 1

输出:

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s     | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

  

  • eq_ref:通常出现在多表 join 查询,并且 关联的字段是 主键 或者 唯一非空索引,即后表 只能匹配一条数据。

下面的示例,可以使用 eq_ref join  来处理 ref_table:

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

  

  • ref:通常出现在多表 join 查询,关联使用了 最左前缀原则的索引 或者 关联的是非主键 或者 非 唯一索引(也就是说,join 不能根据索引选择 单行数据)

下面的示例,Mysql 可以使用 ref join 来处理  ref_table:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

  

  • fulltext:使用全文索引执行 join
  • ref_or_null:在 ref 的基础上 , 另外还搜索了包含空值的行

下面的示例,Mysql 可以使用 ref_or_null join 来处理  ref_table::

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

  

  • index_merge: 合并索引优化
  • unique_subquery:子查询返回唯一主键。

形如下面的示例:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

  

  • index_subquery:和 unique_subquery 类似,只不过在子查询中使用非唯一索引

形如下面的形式:

value IN (SELECT key_column FROM single_table WHERE some_expr)

  

  • range:当 索引和常量 使用 诸如=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()  进行比较时 ,可以使用 range.

例如:

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

  

  • index:和 all 类似 ,只不过 扫描的是 索引树
  • all:全表扫描,可以通过增加 索引避免 全表扫描

3.2 keys 列:真正使用的索引

3.3 rows 列:扫描的记录数

 4.使用 explain 提升查询性能案例分析

假设有如下的 sql:根据订单日期 和 店员id 查询 订单信息(已创建了订单日期的索引),查询结果返回 18条记录。

SELECT * FROM orders
  WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
  AND o_clerk LIKE '%0223';

  

Explain 输出执行计划:

问题所在:

  • 根据 type 为 ALL , 查询进行了全表扫描,被扫描的记录 rows 为  150万。
  • possible_keys 和 ky 均为空 ,订单日期索引完全失效,原因在于被索引的字段使用了处理函数导致索引失效

4.1.修改sql 保证 订单日期索引正常,

SELECT * FROM orders
  WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
  AND o_clerk LIKE '%0223';

  

重新使用 Explain 查看 执行计划:

发现:type 由 ALL 变为 range ,订单日期索引得以利用,被扫描的记录由 15万 降为 3.3万左右。

4.2.另一个优化点在 店员字段的过滤

为 店员字段创建索引:

CREATE INDEX i_o_clerk ON orders(o_clerk);

  

再次输出执行计划:

发现:基本上并没有什么变化,新建的索引没有被利用,原因在于 该字段是 模糊查询,过滤指定后缀的 店员信息。但是索引对于后缀过滤会失效(尽管索引对于前缀有效果)。

修改sql,全量过滤店员字段:

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';

  

再次输出执行计划:

发现:可用索引增加,真正使用的索引变为 店员字段上的索引,被扫描的行由 3.3万降为 1546。

4.3.对于多条件查询,可以考虑使用组合索引

创建如下索引:

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)

  ** :这里将 o_clerk 放在 o_orderdate 之前,因为 o_orderdate 使用了 范围,更优的放在前面。

再次输出执行计划:

发现:使用了组合索引,被扫描记录即为输出的18条记录。效率已最优化。

 

多次优化的总结:

Type Possible keys Key Rows Scanned Duration (seconds) Extra info Rows returned
all NULL NULL 1.50M 1.201 Using where 18
range i_o_orderdate i_o_orderdate 32642 0.281 Using index condition; Using where 18
range i_o_orderdate, i_o_clerk i_o_clerk 1546 0.234 Using index condition; Using where 18
range i_o_orderdate, i_o_clerk, i_o_clerk_date i_o_clerk_date 18 0.234 Using index condition 18
o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
MYSQL查看执行计划

MYSQL查看执行计划 1、 概述: 执行计划的查看是进行数据库的sql语句调优时依据的一个重要依据,mysql的执行计划查看相对oracle简便很多,功能也相对简单很多的SQL语句都不能直接查看。 本文...

xiaocao13140
2018/06/08
0
0
MySQL调优

发现低性能的SQL——慢查询日志 MySQL支持慢查询日志,记录查询时间过长的SQL语句。通过日志能发现需要优化的主体。 开启慢查询日志功能:修改my.cnf文件,并重启 log-slow-queries=/var/li...

文森特梵高
2015/08/17
68
0
PHP 性能分析第三篇: 性能调优实战

注意:本文是我们的 PHP 性能分析系列的第三篇,点此阅读 PHP 性能分析第一篇: XHProf & XHGui 介绍 ,或 PHP 性能分析第二篇: 深入研究 XHGui 。 在本系列的 第一篇 中,我们介绍了 XHProf 。...

OneAPM蓝海讯通
2015/10/23
44
0
DRDS分布式SQL引擎—执行计划介绍

摘要: 本文着重介绍 DRDS 执行计划中各个操作符的含义,以便用户通过查询计划了解 SQL 执行流程,从而有针对性的调优 SQL。

maoreyou
03/31
0
0
MySQL基础之STRAIGHT JOIN用法简介

MySQL基础之STRAIGHT JOIN用法简介 引用mysql官方手册的说法: STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used f......

smileNicky
2019/11/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

自从尝了 Rust,Java 突然不香了

Rust 是软件行业中相对而言比较新的一门编程语言,如果从语法上来比较,该语言与 C++ 其实非常类似,但从另一方面而言,Rust 能更高效地提供许多功能来保证性能和安全。而且,Rust 还能在无需...

osc_k3vwonkw
12分钟前
0
0
Java 高级 面试题 及 参考答案

一、面试题基础总结 1、 JVM结构原理、GC工作机制详解 答:具体参照:JVM结构、GC工作机制详解 ,说到GC,记住两点:1、GC是负责回收所有无任何引用对象的内存空间。 注意:垃圾回收回收的是无...

FH-Admin
12分钟前
14
0
机器学习中的AUC-ROC曲线

作者|ANIRUDDHA BHANDARI 编译|VK 来源|Analytics Vidhya AUC-ROC曲线 你已经建立了你的机器学习模型-那么接下来呢?你需要对它进行评估,并验证它有多好(或有多坏),这样你就可以决定是否...

osc_bg8v9gvf
14分钟前
8
0
音视频(消息)应用场景 :连麦交友例子

实现一个小例子: 效果类似唱吧APP里的 连麦交友功能,音视频,IM 及音视频 SDK参考融云服务商。 没有印象的可以搜索 ’连麦’ 关键字在 应用商店下载一款 连麦的软件 体验下 业务方面的需求...

T型人才追梦者
15分钟前
7
0
逛淘宝天猫想到SSO单点登录

我的原文地址:https://mp.weixin.qq.com/s/77xukPDlgkKnYpwu4LrqaA

osc_yy65eb2q
15分钟前
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部