您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

100% 展示 MySQL 语句执行的神器-Optimizer Trace

时间:2020-08-03 11:32:15  来源:  作者:

在上一篇文章《用Explain 命令分析 MySQL 的 SQL 执行》中,我们讲解了 Explain 命令的详细使用。但是它只能展示 SQL 语句的执行计划,无法展示为什么一些其他的执行计划未被选择,比如说明明有索引,但是为什么查询时未使用索引等。为此,MySQL 提供了 Optimizer Trace 功能,让我们能更加详细的了解 SQL 语句执行的所有分析,优化和选择过程。

如果您想更深入地了解为什么选择某个查询计划,那么优化器跟踪非常有用。虽然 EXPLAIN 显示选定的计划,但Optimizer Trace 能显示为什么选择计划:您将能够看到替代计划,估计成本以及做出的决策。本篇文章会详细讲解 Optimizer Trace 展示的所有相关信息,并且会辅之一些具体使用案例。

基于成本的执行计划

在了解 Optimizer Trace 的之前,我们先来学习一下 MySQL 是如何选择众多执行计划的。

MySQL 会使用一个基于成本(cost)的优化器对执行计划进行选择。每个执行计划的成本大致反应了该计划查询所需要的资源,主要因素是计算查询时将要访问的行数。优化器主要根据从存储引擎获取数据的统计数据和数据字典中元数据信息来做出判断。它会决定是使用全表扫描或者使用某一个索引进行扫描,也会决定表 join的顺序。优化器的作用如下图所示。

100% 展示 MySQL 语句执行的神器-Optimizer Trace

 

优化器会为每个操作标上成本,这些成本的基准单位或最小值是从磁盘读取随机数据页的成本,其他操作的成本都是它的倍数。所以优化器可以根据每个执行计划的所有操作为其计算出总的成本,然后从众多执行计划中,选取成本最小的来最终执行。

既然是基于统计数据来进行标记成本,就总会有样本无法正确反映整体的情况,这也是 MySQL 优化器有时做出错误优化的重要原因之一。

Optimizer Trace 的基本使用

首先,我们来看一下具体如何使用 Optimizer Trace。默认情况下,该功能是关闭的,大家可以使用如下方式打开该功能,然后执行自己需要分析的 SQL 语句,然后再从 INFORMATIONSCHEMA 的 OPTIMIZERTRACE中查找到该 SQL 语句执行优化的相关信息。

# 1. 打开optimizer trace功能 (默认情况下它是关闭的)
SET optimizer_trace="enabled=on";
SELECT ...; 
# 这里输入你自己的查询语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 当你停止查看语句的优化过程时,把optimizer trace功能关闭
SET optimizer_trace="enabled=off";

这个 OPTIMIZER_TRACE 表有4个列,如下所示:

  • QUERY:表示我们的查询语句。
  • TRACE:表示优化过程的JSON格式文本。
  • MISSING_BYTES_BEYOND_MAX_MEM_SIZE:由于优化过程可能会输出很多,如果超过某个限制时,多余的文本将不会被显示,这个字段展示了被忽略的文本字节数。
  • INSUFFICIENT_PRIVILEGES:表示是否没有权限查看优化过程,默认值是0,只有某些特殊情况下才会是 1,我们暂时不关心这个字段的值。

其中,信息最多也最为重要的就是第二列 TRACE,它也是我们后续分析的重点。

TRACE 列的基本格式

TRACE 列的内容是一个超级大的 JSON 数据,直接展开然后一条一条解析估计能看到大伙脑壳疼。

100% 展示 MySQL 语句执行的神器-Optimizer Trace

 

所以,我们先来看一下这坨大 JSON 的骨架。它有三大块内容,也代表着 SQL 语句处理的三个阶段,分别为准备阶段,优化阶段和执行阶段。

100% 展示 MySQL 语句执行的神器-Optimizer Trace

 

接下来,我们详细介绍一个案例,在案例中介绍涉及到的具体字段和含义。

为什么查询未走索引而是全表扫描

首先,SQL 语句查询不使用索引的情况有很多,我们这里只讨论因为基于成本的优化器认为全表查询执行计划的成本低于走索引执行计划的情况。

如下图这个场景,明明 val 列上有索引,并且 val 现存值也有一定差异性,为什么没有使用索引进行查询呢?

100% 展示 MySQL 语句执行的神器-Optimizer Trace

 

我们按照上文使用 Optimizer Trace 找到其 joinoptimization 中 rangeanalysis 相关数据,它会展示 where 从句范围查询过程中索引的选择情况

100% 展示 MySQL 语句执行的神器-Optimizer Trace

 

由上图可以看出,MySQL 对比了全表扫描和使用 val 作为索引两个方案的成本,最后发现虽然全表扫描需要扫描更多的行,但是成本更低。所以选择了全表扫描的执行方案。

这是为什么呢?明明使用 val 索引可以少扫描 4 行。这其实涉及 InnoDB 中使用索引查询数据行的原理。

Innodb引擎查询记录时在无法使用索引覆盖(也就是需要查询的数据多与索引值,比如该例子中,我要查name,而索引列是 val)的场景下,需要做回表操作获取记录的所需字段,也就是说,通过索引查出主键,再去查数据行,取出对应的列,这样势必是会多花费成本的。

所以在回表数据量比较大时,经常会出现 Mysql 对回表操作查询代价预估代价过大而导致不使用索引的情况。

一般来说,当SQL 语句查询超过表中超过大概五分之一的记录且不能使用覆盖索引时,会出现索引的回表代价太大而选择全表扫描的现象。且这个比例随着单行记录的字节大小的增加而略微增大。

通过 range_analysis 中的相关数据也可以对 where 从句使用多个索引列,如何选择执行时使用的索引的情况进行分析。

小节

终于,介绍了有关于 MySQL 语句执行分析的 explain 和 Optimizer Trace,下一篇,我们将分析具体的死锁场景。



Tags:Optimizer Trace   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
在上一篇文章《用Explain 命令分析 MySQL 的 SQL 执行》中,我们讲解了 Explain 命令的详细使用。但是它只能展示 SQL 语句的执行计划,无法展示为什么一些其他的执行计划未被选...【详细内容】
2020-08-03  Tags: Optimizer Trace  点击:(59)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条