MySQL高级篇——性能分析工具

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

 导航 

【黑马Java笔记+踩坑汇总】JavaSE+JavaWeb+SSM+SpringBoot+瑞吉外卖+SpringCloud+黑马旅游+谷粒商城+学成在线+设计模式+牛客面试题

目录

1. 数据库服务器的优化步骤

2. 查看系统性能参数

2.1 SHOW STATUS LIKE '参数'

2.2 查看SQL的查询成本

3. 定位执行慢的 SQL慢查询日志

3.0 介绍 

3.1 开启慢查询日志参数

3.2 查看慢查询次数

3.5 慢查询日志分析工具mysqldumpslow

3.6 关闭慢查询日志

3.7 删除慢查询日志

4. 定位慢查询语句、查看 SQL 执行成本show profile

5. 执行计划表EXPLAIN

5.1 简介

5.2 基本语法

5.3 执行计划表介绍 

5.4 EXPLAIN四种输出格式

5.5 SHOW WARNINGS的使用

6. 分析优化器执行计划trace

7. MySQL监控分析视图-sys schema

7.1 简介

7.2 使用场景

1. 数据库服务器的优化步骤

在数据库调优中我们的目标就是响应时间更快吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。 

调优流程

  1. SHOW STATUS观察服务器状态是否存在周期性波动如果存在的话就缓存优化
  2. 如果还存在不规则延迟或卡顿的话就开启慢查询、explan分析查询语句
  3. 如果发现sql等待时间长就调优服务器参数如果发现sql执行时间长就索引优化、表优化
  4. 如果还存在不规则延迟或卡顿的话就观察sql查询是否到瓶颈了是的话就读写分离、分库分表。

三种分析工具SQL调优三步骤慢查询、EXPLAN、SHOW PROFLING 

 

整个流程划分成了观察Show status 和行动Action 两个部分。字母 S 的部分代表观察会使用相应的分析工具字母 A 代表的部分是行动对应分析可以采取的行动。

2. 查看系统性能参数

2.1 SHOW STATUS LIKE '参数'

在MySQL中可以使用SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

示例查看数据库连接次数和运行时长 

  

 中括号代表可省略。

一些常用的性能参数如下

•　Connections连接MySQL服务器的次数。

•　UptimeMySQL服务器的上线时间。重启服务器后会重置。

•　Slow_queries慢查询的次数。查询时长超过指定时间次数越少越好。

•　Innodb_rows_readSelect查询返回的行数

•　Innodb_rows_inserted执行INSERT操作插入的行数

•　Innodb_rows_updated执行UPDATE操作更新的行数

•　Innodb_rows_deleted执行DELETE操作删除的行数

•　Com_select查询操作的次数。

•　Com_insert插入操作的次数。对于批量插入的 INSERT 操作只累加一次。

•　Com_update更新操作的次数。

•　Com_delete删除操作的次数。

•　last_query_cost查询优化器上一个查询的成本最近一次删除用到数据页数量。

2.2 查看SQL的查询成本

SHOW STATUS LIKE 'last_query_cost';

SQL查询是一个动态的过程从页加载的角度来看:

1.缓冲池查询效率优于从磁盘查

如果页就在数据库缓冲池中那么效率是最高的否则还需要从内存或者磁盘中进行读取当然针对单个页的读取来说如果页存在于内存中会比在磁盘中读取效率高很多.

MySQL的缓冲池被分为多个不同的缓存池其中包括

  • 查询缓存用来缓存查询结果。
  • InnoDB缓存池用来缓存热点表和索引数据页。
  • MyISAM缓存池用来缓存表数据块。

当缓冲池中已经存储了较多的数据时MySQL会使用一种叫做缓冲池替换算法的方法将部分缓存数据替换出去以腾出空间为新的数据做缓存。

MySQL的缓冲池使用的是LRU最近最少使用算法它会优先缓存最近使用的数据。当缓冲池的空间不足时MySQL会将最不常用的数据从缓冲池中替换出去以腾出空间缓存新的数据。

2.批量顺序查询平均下来每页查询更高

如果我们从磁盘中对单一页进行随机读那么效率是很低的(差不多10ms)而采用顺序读取的方式批量对页进行读取平均一页的读取效率就会提升很多甚至要快于单个页面在内存中的随机读取。

所以说遇到IO并不用担心方法找对了效率还是很高的。我们首先要考虑数据存放的位置如果是经常使用的数据就要尽量放到缓冲池中其次我们可以充分利用磁盘的吞吐能力一次性批量读取数据这样单个页的读取效率也就得到了提升。 

测试缓冲池缓存已使用的表和索引到内存中效率高查询900001和 900001~9000100查询成本差很多查询速度差不多

学生信息表为例

CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我们想要查询 id=900001 的记录然后看下查询成本我们可以直接在聚簇索引上进行查找

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;

运行结果1 条记录运行时间为 0.042s

 然后再看下查询优化器的成本实际上我们只需要检索一个页即可

SHOW STATUS LIKE 'last_query_cost';


+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果100 条记录运行时间为 0.046s

然后再看下查询优化器的成本这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

你能看到页的数量是刚才的 20 倍但是查询的效率并没有明显的变化实际上这两个 SQL 查询的时间基本上一样就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中然后再进行查找。虽然页数量last_query_cost增加了不少但是通过缓冲池的机制并没有增加多少查询时间。

为什么第二次是直接从缓冲池查

因为mysql缓存淘汰策略lru最近最少使用会优先缓存最近查询数据优先淘汰最近最少使用数据。

使用场景它对于比较开销是非常有用的特别是我们有好几种查询方式可选的时候。

3. 定位执行慢的 SQL慢查询日志

3.0 介绍 

MySQL的慢查询日志用来记录在MySQL中响应时间超过阀值的语句具体指运行时间超过 long-query_time值的SQL则会被记录到慢查询日志中。 long_query_time的默认值为 10意思是运行10秒以上(不含10秒)的语句认为是超出了我们的最大忍耐时间值。

它的主要作用是帮助我们发现那些执行时间特别长的 SOL 查询并且有针对性地进行优化从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候检查一下慢查询日志找到那些慢查询对解决问题很有帮助。比如一条sq执行超过5秒钟我们就算慢SQL希望能收集超过5秒的sql结合explain进行全面分析

默认情况下MySQL数据库 没有开启慢查询日志 需要我们手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响
慢查询日志支持将日志记录写入文件 

3.1 开启慢查询日志参数

0.慢查询是否开启 

show variables like '%slow_query_log';

慢查询日志位置

show variables like '%slow_query_log%';

 

 慢查询阈值

1. 开启慢查询日志slow_query_log

set global slow_query_log='ON';

然后我们再来查看下慢查询日志是否开启以及慢查询日志文件的位置

你能看到这时慢查询分析已经开启同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件

中。

2. 修改慢查询阈值long_query_time

查看慢查询的时间阈值

show variables like '%long_query_time%';

查看全局慢查询的时间阈值

show global variables like '%long_query_time%';

 

临时修改慢查询的时间阈值 

当前会话

set long_query_time = 1;

全局

set global long_query_time = 1;

对于 “global” 选项是全局级别的配置参数。它可以在 MySQL 服务器启动时或 MySQL 安装时在 MySQL 配置文件中设置或者通过 SET GLOBAL 命令在运行时更改。全局级别的配置参数对所有的 MySQL 连接都有效。

永久修改重启数据库后依然有效不建议永久修改仅在优化时候打开慢查询拖性能

修改my.cnf

[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的闽值为3秒超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

注意 

#测试发现设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并
执行下述语句
set global long_query_time = 1;    #设置全局慢查询阈值1s
show global variables like '%long_query_time%';    #全局1s
set long_query_time=1;
show variables like '%long_query_time%';    #当前会话10s

3.2 查看慢查询次数

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

3.5 慢查询日志分析工具mysqldumpslow

`mysqldumpslow`是一个用于分析MySQL慢查询日志的命令行工具。通过解析慢查询日志可以了解到数据库的性能问题从而进行优化。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下

  • -a: 不将数字抽象成N字符串抽象成S

  • -s: 是表示按照何种方式排序

    • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间 默认方式
    • ac:平均查询次数

  • -t: 即为返回前面多少条的数据

  • -g: 后边搭配一个正则匹配模式大小写不敏感的

案例

举例按照查询时间排序查看前五条 慢查询SQL 语句这样写即可

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

举例假设想查看最慢的10个查询可以使用如下命令

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

这条命令表示按照时间排序显示最慢的10个查询/var/log/mysql/mysql-slow.log是MySQL慢查询日志的路径。

另外如果需要筛选特定的查询可以使用 `-g` 参数比如

mysqldumpslow -s t -t 10 -g "SELECT * FROM user" /var/log/mysql/mysql-slow.log

这条命令表示按照时间排序显示最慢的10个查询其中关键字为 "SELECT * FROM user"。

总的来说`mysqldumpslow` 命令提供了一种简单快捷的方式帮助开发人员、DBA等分析MySQL慢查询问题优化数据库的性能。

 其他常用案例

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

3.6 关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法

方式1永久性方式

[mysqld]
slow_query_log=OFF

mysql默认关闭慢查询日志或者把slow_query_log一项注释掉 或 删除

[mysqld]
#slow_query_log =OFF

重启MySQL服务执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2临时性方式

使用SET语句来设置。

停止MySQL慢查询日志功能具体SQL语句如下。

SET GLOBAL slow_query_log=off;

golbal全局有效。

重启MySQL服务使用SHOW语句查询慢查询日志功能信息发现慢查询日志已经关闭成功。

SHOW VARIABLES LIKE '%slow%';    #发现关闭成功
#慢查询阈值
SHOW VARIABLES LIKE '%long_query_time%';   #10s。前面改的时候没有加global所以重启服务器后阈值恢复10s。

3.7 删除慢查询日志

手动删除 

使用SHOW语句显示慢查询日志信息具体SQL语句如下。

SHOW VARIABLES LIKE `slow_query_log%`;

从执行结果可以看出慢查询日志的目录默认为MySQL的数据目录在该目录下 手动删除慢查询日志文件 即可。

自动删除 

使用命令 mysqladmin flush-logs 来重新生成查询日志文件执行完毕会在数据目录下重新生成慢查询日志文件。

重新生成慢查询日志文件直接删除旧的

mysqladmin -uroot -p flush-logs slow

提示

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意一旦执行了这个命令慢查询日志都只存在新的日志文件中如果需要旧的查询日志就必须事先备份。

4. 定位慢查询语句、查看 SQL 执行成本show profile

show profile 是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗工具的情况可用于 sql 调优的测量。默认情况下处于关闭状态并保存最近15次的运行结果。

SHOW PROFILE 是一个用于查看会话执行的查询的性能分析信息的 MySQL 命令。它可以帮助开发人员和 DBA 分析查询语句执行时的瓶颈并找出哪些部分需要优化。 

查看配置是否开启profile 

mysql > show variables like 'profiling';
  • SHOW VARIABLES 显示了 MySQL 服务器的当前配置变量包括全局配置变量和会话配置变量以及它们的值。SHOW VARIABLES 用于查看 MySQL 配置系统参数的详细信息并进行系统参数的修改。
  • SHOW STATUS 显示服务器的性能参数包括连接、线程、查询等方面的状态信息以及它们的值。 

开启 show profile:

mysql > set profiling = 'ON';

 然后执行相关的查询语句:

select * from employees

show profiles; 查询当前会话所有查询语句持续时间

mysql > show profiles;

 show profile;查询当前会话最近sql语句的执行成本

你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销可以使用

mysql > show profile;

show profile cpu for 2;查询指定QueryID的cpu信息

可以查看指定的 QueryID 的开销比如 show profile for query 2 。在SHOW PROFILE 中可以查看不同部分的开销比如 cpu、block.io 等:

mysql> show profile cpu,block io for query 2

 show profile的常用查询参数

① ALL显示所有的开销信息。

② BLOCK IO显示块IO开销。

③ CONTEXT SWITCHES上下文切换开销。

④ CPU显示CPU开销信息。

⑤ IPC显示发送和接收开销信息。

⑥ MEMORY显示内存开销信 息。

⑦ PAGE FAULTS显示页面错误开销信息。

⑧ SOURCE显示和Source_functionSource_file Source_line相关的开销信息。

⑨ SWAPS显示交换次数开销信息。

日常开发需注意

① converting HEAP to MyISAM: 查询结果太大内存不够数据往磁盘上搬了。

② Creating tmp table创建临时表。先拷贝数据到临时表用完后再删除临时表。

③ Copying to tmp table on disk把内存中临时表复制到磁盘上警惕

④ locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条则sql语句需要优化。

注意

不过SHOW PROFILE命令将被弃用我们可以从 information_schema 中的 profiling 数据表进行查看。

5. 执行计划表EXPLAIN

5.1 简介

MySQL的EXPLAIN是一种分析SQL语句查询性能的工具。当我们在MySQL中执行SELECT语句时EXPLAIN可以帮助我们查看MySQL如何执行这个查询即执行计划包括使用哪些索引、选择哪些表、以及如何读取数据等信息。通过分析EXPLAIN的输出结果我们可以更好地优化查询语句提高查询效率。

EXPLAIN的使用方式非常简单只需要在执行SELECT语句时在前面加上EXPLAIN关键字即可例如

EXPLAIN SELECT * FROM my_table WHERE my_column = 'my_value';

执行以上命令后MySQL会返回一张查询执行计划表其中包含了MySQL执行这个查询的详细信息。我们可以通过分析查询执行计划表来了解查询的性能瓶颈以及如何优化查询语句从而提高查询性能。  

注意 

  • EXPLAIN不考虑各种Cache

  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作

  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

  • 部分统计信息是估算的并非精确值

上一步用show profile定位了查询慢的 SQL 之后我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块主要功能: 通过计算分析系统中收集到的统计信息为客户端请求的Query提供它认为最优的 执行计划 (他认为最优的数据检索方式但不见得是DBA数据库管理员认为是最优的这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式比如多表连接的顺序是什么对于每个表采用什么访问方法来具体执行查询等等。MySOL为我们提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划大家看懂EXPLAIN 语句的各个输出项可以有针对性的提升我们查询语句的性能。 

1. 能做什么

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

2. 官网介绍

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

3. 版本情况

  • MySQL 5.6.3以前只能 EXPLAIN SELECT MYSQL 5.6.3以后就可以 EXPLAIN SELECTUPDATE DELETE
  • 在5.7以前的版本中想要显示 partitions 需要使用 explain partitions 命令想要显示 filtered 需要使用 explain extended 命令。在5.7版本后默认explain直接显示partitions和 filtered中的信息。

5.2 基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下

EXPLAIN SELECT select_options
#一般指定在查询时不使用缓存
EXPLAIN SELECT SQL_NO_CACHE select_options
#或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话可以在具体的查询语句前边加一个 EXPLAIN 就像这样

EXPLAIN SELECT SQL_NO_CACHE * FROM course_base;

输出的上述信息就是所谓的 执行计划。在这个执行计划的辅助下我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以 SELECT 开头的查询语句其余的 DELETE、INSERT、REPLACE 以及UPDATE 语句等都可以加上EXPLAIN用来查看这些语句的执行计划只是平时我们对 SELECT 语句更感兴趣  

5.3 执行计划表介绍 

执行计划各个列的作用

id每个SELECT子句或者join操作都会被分配一个唯一的编号编号越小优先级越高id相同的语句可以被认为是一组。id为NULL表示独立的子查询子查询优先级都比主查询高。
select_type查询的类型。详细看下文。
table表名。显示当前这行的数据是哪个表的。
partitions匹配的分区信息。如果表未分区则为NULL。
type访问类型根据索引、全表扫描等方法来执行查询的优化策略。
possible_keys

可能用到的索引。列出MySQL能够使用哪些索引来查询。

如果该列只有一个possible_keys通常意味着这个查询是高效的。

如果这个列有多个possible_keys并且MySQL只使用了其中一个则需要考虑是否需要在该列上增加一个联合索引。

key实际上使用的索引。如果没有明确的指定KEYMySQL会根据查询条件自动选择最优的索引。
key_len实际使用到索引的字节数长度。越短表示越快一般表示索引字段越小越好。
ref当使用索引列等值查询时与索引列进行等值匹配的对象信息
rows预估的需要读取的记录条数。数值越小越好表示结果集越小查询越高效。
filtered某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好说明可通过索引直接返回数据。
Extra额外信息。看有没有走索引还是全表扫描了。例如using index覆盖索引不回表using filesort需要额外的排序不能通过索引得到排序结果尽量避免。using where表示部分字段使用了索引部分字段无法走索引需要全表扫描。一般搭配type字段看。
  • select_type查询的类型有以下几种取值
    • SIMPLE不使用子查询或UNION不包含UNION ALL的简单SELECT查询。
    • PRIMARY最外层的SELECT查询。
    • DERIVED以FROM子句中的子查询方式出现的SELECT语句。
    • UNIONUNION中的第二个或之后的SELECT查询。
    • UNION RESULT从UNION的结果集中获取数据的SELECT查询。
    • SUBQUERY不在FROM子句中出现的子查询通常在SELECT语句中使用。
    • DEPENDENT SUBQUERY子查询依赖外层查询的结果集。
  • key实际上使用的索引。在MySQL中创建索引时使用的是INDEX关键字但在EXPLAIN执行计划表中显示的是KEY这是因为MySQL允许在创建索引时指定统计信息例如最小值、最大值等这些统计信息在索引中被视为索引键Index key所以在执行计划表中显示为KEY。
  • type访问类型根据索引、全表扫描等方法来执行查询的优化策略。当 type 列的取值不是 Const 时我们需要重点关注有关索引、缓存的性能调优对 SQL 语句进行优化适当修复可能的数据设计问题。
    • system只有一行数据即将被查询。这是最快的查询类型通常出现在系统表的查询中。
    • const使用主键或唯一索引查找单个行时使用此时查询只能返回一行数据。这是一种非常快的查询类型。
    • eq_ref连接使用唯一索引查找符合查询条件的数据时使用每个连接类型都需要使用唯一索引进行访问比ref执行速度更快。
    • ref使用非唯一索引查找数据时使用查询结果比eq_ref大但仍很快。
    • range使用索引范围查找数据时使用可能会查找一定范围内的数据如 使用 BETWEEN 或 > 或 > < 等操作时的查询。
    • index全表扫描没有好的索引适用时使用相比于全表扫描速度更快。
    • all表扫描扫描整个表以获得需要的数据速度最慢必须尽量避免使用。
    • unique_subquery在对查询结果进行过滤或使用 IN 操作时优化器会选择使用此类型的查询使用了 In 操作符的子查询依赖于外层查询的唯一索引。
    • index_subquery使用了 In 操作符但子查询使用的普通索引而不是唯一索引。
    • range_check在使用索引来检查外键参照时使用。

5.4 EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式 传统格式 JSON格式 TREE格式 以及 可视化输出 。用户可以根据需要选择适用于自己的格式。

1. 传统格式

传统格式简单明了输出是一个表格形式概要说明查询计划。

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

2. JSON格式

第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行好坏的重要属性 —— 成本。而JSON格式是四种格式里面输出信息最详尽的格式里面包含了执行的成本信息。

  • JSON格式在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON 。
EXPLAIN FORMAT=JSON SELECT ....
  • id查询所对应的唯一标识符id
  • select_type查询类型type
  • table正在访问的表table_name
  • partitions正在访问的分区partition_name
  • type使用的访问方法access_type
  • possible_keys可能使用的索引possible_keys
  • key实际使用的索引key
  • key_len索引长度key_length
  • ref与索引匹配的列或常数ref
  • rows估计的检索行数rows
  • filtered使用WHERE筛选后剩余行数的百分比filtered
  • Extra其他信息extra

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描述如何查询。

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75
rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index
condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)

4. 可视化输出

可视化输出可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标即可生成可视化的查询计划。

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描述如何查询。

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75
rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index
condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)

4. 可视化输出

可视化输出可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标即可生成可视化的查询计划。

上图按从左到右的连接顺序显示表。红色框表示 `全表扫描` 而绿色框表示使用 `索引查找` 。对于每个表 显示使用的索引。还要注意的是每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本。 

5.5 SHOW WARNINGS的使用

在MySQL中SHOW WARNINGS是一个可以查看最近一次执行的语句中产生的警告信息的命令。当MySQL执行语句时如果发现一些不符合预期的情况会产生一些警告信息。这些警告信息可以包括非致命性错误例如某些类型的数据不能隐式转换或某些数据截断等。

当我们执行SHOW WARNINGS命令时MySQL会返回警告信息的详细列表包括

  • Warning该警告的类型
  • Level该警告的级别通常为Note、Warning或Error
  • Code警告的返回代码
  • Message警告信息的内容

可以使用SELECT的方式来查看最近一次操作的警告信息

SHOW WARNINGS;

也可以配合使用INSERT、UPDATE、DELETE、ALTER TABLE等命令检查某个具体操作产生的警告信息

INSERT INTO my_table (name, age) VALUES ('John Doe', 150);
SHOW WARNINGS;

在开发和调试的过程中SHOW WARNINGS对于定位和解决某些问题非常有用例如数据截断、类型转换等问题。

6. 分析优化器执行计划trace

在MySQL中可以使用trace命令来进行优化器执行计划的跟踪和分析。trace命令可以显示MySQL优化器在生成执行计划时所采取的决策包括哪些表被处理以及使用哪些索引、算法等。

使用trace命令需要先启用general_log和performance_schema两个系统变量其次需要使用SET语句来设置一些参数例如trace-unique-check、trace-max-protocol、trace-protocol、trace-feature、trace-feature-check等。设置完成后可以通过SET global trace_format='json'语句来选择输出结果的格式。

下面是对使用trace命令的一个简单示例

首先设置参数

SET @trace_feature = 'qa';
SET @max_execution_time=50000;
SET @trace_level = '+ddl,+engine';
SET @trace_feature_check = 1;
SET @trace_unique_check = 1;
SET @trace_protocol = 1;
SET @trace_max_protocol = 6;

然后启用general_log和performance_schema

SET global general_log = on;
SET global performance_schema = on;

接着执行查询并查看结果

SELECT *
FROM my_table
WHERE my_column = 'some_value';
SHOW SESSION STATUS LIKE 'Last_Query_Plan';

最后关闭general_log和performance_schema

SET global general_log = off;
SET global performance_schema = off;

在trace输出中我们可以看到优化器在执行计划中使用的索引、执行算法、行数估计等细节信息。通过分析trace结果我们可以找到一些性能问题的根源并进行相应的调整和优化。但要注意trace命令可能会带来额外的性能消耗和IO开销不应该在生产环境中长期启用。

7. MySQL监控分析视图-sys schema

7.1 简介

MySQL在8.0版本引入了sys schema该模式包含用于监视和分析MySQL服务器性能的视图和函数。sys schema提供了一组易于使用的视图和函数可以帮助我们更好地理解和分析MySQL数据库的行为和性能。

以下是sys schema中一些常用的监控分析视图

  • sys.statements_with_sorting: 显示哪些语句使用了排序操作包括使用哪些排序操作、每个语句排序的次数以及排序操作的资源消耗。
  • sys.statements_with_runtimes_in_95th_percentile: 显示执行时间最长的语句。
  • sys.io_global_by_file_by_bytes: 显示每个文件的磁盘IO字节数可以用来检测IO瓶颈。
  • sys.memory_by_host_by_current_bytes: 显示每个客户端的当前内存使用情况可以用于检测内存泄漏或内存占用高的情况。
  • sys.waits_global_by_latency: 显示哪些等待操作最耗费时间可以帮助我们找到性能问题的瓶颈所在。
  • sys.processlist: 显示当前正在运行的线程和进程的信息包括执行的语句、查询ID、用户、主机、线程ID和状态等信息。

总的来说sys schema中包含的视图和函数为我们提供了更深入的MySQL性能分析和监控功能可以帮助我们更好地理解MySQL数据库的行为和性能瓶颈。 

  1. 主机相关以host_summary开头主要汇总了IO延迟的信息。
  2. Innodb相关以innodb开头汇总了innodb buffer信息和事务等待innodb锁的信息。
  3. I/o相关以io开头汇总了等待I/O、I/O使用量情况。
  4. 内存使用情况以memory开头从主机、线程、事件等角度展示内存的使用情况
  5. 连接与会话信息processlist和session相关视图总结了会话相关信息。
  6. 表相关以schema_table开头的视图展示了表的统计信息。
  7. 索引信息统计了索引的使用情况包含冗余索引和未使用的索引情况。
  8. 语句相关以statement开头包含执行全表扫描、使用临时表、排序等的语句信息。
  9. 用户相关以user开头的视图统计了用户使用的文件I/O、执行语句统计信息。
  10. 等待事件相关信息以wait开头展示等待事件的延迟情况。

7.2 使用场景

索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：聊聊我在阿里第一年375&晋升的心得

下一篇：SpringBoot中的依赖注入和自动注入

“MySQL高级篇——性能分析工具” 的相关文章

Windows安装mysql详细步骤（通俗易懂，简单上手）_windows安装mysql1年前 (2023-02-02)
eclipse连接MySql数据库(菜鸟入门之详细步骤)1年前 (2023-02-02)
mysql like性能优化1年前 (2023-02-02)
测试人员如何在linux服务器中查询mysql日志？1年前 (2023-02-02)
基于springboot+mybatis+mysql+jsp房屋租赁管理系统（含论文）1年前 (2023-02-02)
MySQL数据库基本操作-DML1年前 (2023-02-02)
PHP MySQL 简介1年前 (2023-02-02)
浅谈如何设计MySQL索引1年前 (2023-02-02)
mysql快速生成100W条测试数据（7）虚拟网站、IP地址并存入mysql数据库1年前 (2023-02-02)
MySQL之排序查询与分页查询Python解释器详解实现代理池的API模块1年前 (2023-02-02)

阿里云国际版