mysql高级-6-性能分析工具的使用

前言

上一篇，我们学习了索引的相关内容，在生产过程中，我们还可以使用一些方法来查看一条sql执行的具体情况，本篇内容我们将学习如何使用性能分析工具分析sql的执行。文章课程链接：MySQL数据库教程天花板，mysql安装到…

1、数据库优化步骤

见文章 【MySQL从入门到精通】【高级篇】（二十一）数据库优化步骤_查看系统性能参数

补充：统计sql的查询成本- last_query_cost
如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量来得到当前查询的成本，它通常也是我们评价一个查询的执行效率的常用指标—— SQL 语句所需要读取的页的数量

SHOW STATUS LIKE 'last_query_cost';

值为读取的数据页

2、定位执行慢的SQL：慢查询日志

慢查询指的是运行时间超过 long_query_time 值的SQL，它会被记录到慢查询日志中，然后可以使用explain等对其进行分析，long_query_time的默认值为10s。默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启它或多或少会带来一定的性能影响。
补充：慢查询的条件由 long_query_time 和 min_examined_row_limit（扫描过的最少记录数，默认为0） 组成，默认为0时表示哪怕一个记录也没扫描过，只要执行时间超过10s，都要被记录到慢查询日志中。

2.1 开启慢查询日志参数

1. 打开slow_query_log
   在使用前，我们需要先看下慢查询是否已经开启

show variables like '%slow_query_log';

如果value 为 OFF，则需要开启

2. 开启慢查询
   设置变量值的时候需要使用global

set global slow_query_log='ON';

执行后，会提升文件（slow_query_log_file）保存在 /var/lib/mysql/xxx-slow.log 文件中

3. 修改 long_query_time 阈值
   默认的慢查询是10s，我们可能用不到这么大，修改阈值

show variables like '%long_query_time'; // 查看当前值
set global long_query_time = 3; // 设置为3秒
set long_query_time = 3; // 还需设置session级别的才能生效

当然，我们也可以通过配置文件，让其永久生效，修改 my.cnf文件，修改 long_query_time、slow_query_log、slow_query_log_file，然后重启MySQL服务器。

4. 查看慢查询条目
   查询当前系统中有多少条慢查询记录

show global status like '%slow_queries';

5. 慢查询日志分析工具：mysqldumpslow
   查看 mysqldumpslow 的帮助信息，注意：该命令不在mysql下执行

mysqldumpslow --help;
mysqldumpslow -a -s t -t 5 /var/lib/mysql/xxx-slow.log; // 查看日志

想要详细使用可查询相关文章

6. 删除慢查询日志
   当我们调优结束，应该及时关闭慢查询，并删除慢查询日志

mysqladmin -uroot -p flush-logs slow; // 删除重建慢查询日志文件，执行后，新的慢查询日志会生成到新的文件，旧的会丢失

3、查看SQL执行成本：SHOW PROFILE

Show profile 是MySQL提供的用来分析当前会话中SQL做了什么，执行资源消耗情况的工具，包括

BLOCK IO：块IO的开销
CONTEXT SWITCHES：上下文切换开销
CPU：显示CPU的开销时间
MEMORY：显示内存的开销时间

等等
使用 show profiles 可以查看最近执行的sql
使用 show profile 显示最后一条SQL执行的成本
如果想分析具体的某一条SQL，可使用 show profile for query 2（2为Query_ID）

4、EXPLAIN的使用（重点）

当我们找出了慢查询的SQL，就可以使用 EXPLAIN 或 DESCRIBE 对其进行分析，两者的语法一样，且分析结果一样。MySQL中有专门负责优化SELECT语句的优化器模块，主要功能是通过计算分析系统中收集到的统计信息，为客户端请求的查询提供它认为最优的执行计划（可能DBA不是这么认为的）
MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，并给出以下结果

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

语法：

EXPLAIN SQL; // sql可以是查询、删除或修改（5.6.3版本后新增的删除和修改，但一般都是用于查询的分析）

执行后，会得出 id、select_type、table、type等信息，网上已经有非常多的文章了，这里就不再记录了，可访问EXPLAIN用法和结果分析

5、EXPLAIN四种输出格式

EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。
传统格式输出中缺少了一个衡量执行计划好坏的成本属性。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。
具体访问 EXPLAIN四种输出格式&MySQL监控分析视图——sys schema

end…

如果总结的还行，就点个赞呗 @_@ 如有错误，欢迎指正！