高级篇九、性能分析工具的使用

坚持每天学习一点

已于 2024-01-17 07:27:32 修改

阅读量115

点赞数

分类专栏： MySQL 文章标签： MySQL

于 2023-07-07 12:40:27 首次发布

本文链接：https://blog.csdn.net/hc1285653662/article/details/131594232

版权

MySQL 专栏收录该内容

27 篇文章 4 订阅

订阅专栏

9.1、数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图
在这里插入图片描述

9.2、查看系统性能参数

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

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

一些常用的性能参数如下：

Connections：连接MySQL服务器的次数
Uptime：MySQL服务器的上线时间
Slow_queries：慢查询的次数
Com_select：查询操作的次数

9.3、统计SQL的查询成本：last_query_cost

last_query_cost：查询上一次查询使用了多少个页
在这里插入图片描述

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

4.1 开启慢查询日志参数

1）开启slow_query_log

set global slow_query_log='ON';

查询慢查询日志是否开启，以及慢查询日志文件的位置

show variables like '%slow_query_log%';

在这里插入图片描述

2）修改long_query_time阈值

查询慢查询的时间阈值设置

show variables like'%long_query_time%';

在这里插入图片描述
这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句

set global long_query_time = 0.8;
show global variables like '%long_query_time%';

在这里插入图片描述

4.2 查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4.3 案例演示

1、建表

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、创建函数

随机产生字符串：

DELIMITER //
CREATE FUNCTION rand_string (n INT) RETURNS VARCHAR (255) #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ' ; DECLARE
			return_str VARCHAR (255) DEFAULT '' ; DECLARE
				i INT DEFAULT 0 ;
			WHILE i < n DO

			SET return_str = CONCAT(
				return_str,
				SUBSTRING(
					chars_str,
					FLOOR(1 + RAND() * 52),
					1
				)
			) ;
			SET i = i + 1 ;
			END
			WHILE ; RETURN return_str ;
			END//
DELIMITER ;

如果此时出现报错，请参考 https://blog.csdn.net/qq_40436854/article/details/103266365

产生随机数值：

DELIMITER //
CREATE FUNCTION rand_num (from_num INT, to_num INT) RETURNS INT (11)
BEGIN
	DECLARE
		i INT DEFAULT 0 ;
	SET i = FLOOR(
		from_num + RAND() * (to_num - from_num + 1)
	) ; RETURN i ;
	END//
DELIMITER ;

3、创建存储过程

DELIMITER //


CREATE PROCEDURE insert_stu1 (START INT, max_num INT)
BEGIN
	DECLARE
		i INT DEFAULT 0 ;
	SET autocommit = 0 ; #设置手动提交事务
	REPEAT
		#循环
	SET i = i + 1 ; #赋值
	INSERT INTO student (stuno, NAME, age, classId)
	VALUES
		(
			(START + i),
			rand_string (6),
			rand_num (10, 100),
			rand_num (10, 1000)
		) ; UNTIL i = max_num
	END
	REPEAT
		; COMMIT ; #提交事务
	END//
DELIMITER ;

4、调用存储过程

CALL insert_stu1(100001,4000000);

4.4 测试及分析

SELECT * FROM student WHERE stuno = 3455655;
查询耗费了3.569秒，超过了慢查询时间的阈值，所以该查询为慢查询
在这里插入图片描述

4.5 慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow，mysqldumpslow用来按照条件检索出慢查询日志中相应的sql语句

查看慢查询日志的位置：
在这里插入图片描述
查看慢查询日志：

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
		c: 访问次数
		l: 锁定时间
		r: 返回记录
		t: 查询时间
		al:平均锁定时间
		ar:平均返回记录数
		at:平均查询时间 （默认方式）
		ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -a -s t -t 5 /var/lib/mysql/centos7-11-slow.log;

在这里插入图片描述

工作常用参考：

#得到返回记录集最多的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

4.6 关闭慢查询日志

永久性方式

[mysqld]
slow_query_log=OFF

临时性方式

SET GLOBAL slow_query_log=off;

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

通过设置 profiling='ON’ 来开启 show profile：

set profiling = 'ON';

查看 show profile 是否开启：

show variables like 'profiling';

在这里插入图片描述

查看当前会话都有哪些 profiles：

show profiles;

在这里插入图片描述

要查看最近一次查询的开销：

show profile;

在这里插入图片描述
要查看某个query_id查询的开销：

show profile for query 2;

在这里插入图片描述
show profile的常用查询参数:

ALL：显示所有的开销信息。
BLOCK IO：显示块IO开销
CONTEXT SWITCHES：上下文切换开销
CPU：显示CPU开销信息
IPC：显示发送和接收开销信息
MEMORY：显示内存开销信息
PAGE FAULTS：显示页面错误开销信息

9.6、分析查询语句：EXPLAIN

1 概述

2 基本语法

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

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：
在这里插入图片描述

3 数据准备

在这里插入图片描述

4 EXPLAIN各列作用

1） table
不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问 的，所
以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该
表的表名（有时不是真实的表名字，可能是简称）。

2）id
在一个大的查询语句中，每一个SELECT关键字对应一个唯一的ID

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3） select_type
在这里插入图片描述

①、SIMPLE：普通select查询和连接查询都是simple类型
查询语句中不包含 union 或者子查询都算是simple类型
在这里插入图片描述

②、PRIMARY：对于包含union 或者 union all 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边查询的select_type 的值就是 primary
在这里插入图片描述

③、UNION：对于包含union 或者 union all 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边查询的select_type 的值就是 primary，其余小查询的 select_type就是 union

④、UNION RESULT： mysql选择使用临时表来完成 union 查询的去重工作，针对于临时表的查询，其select_type就是 UNION RESULT

⑤、DEPENDENT UNION
在包含 union 或者 union all的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的小查询之外，其余小查询的select_type都是 DEPENDENT UNION
在这里插入图片描述

⑥、SUBQUERY
如果包含子查询的查询语句不能转换成多表连接的形式，并且子查询不是相关子查询，该子查询的第一个select关键字代表的那个查询的select_type就是 SUBQUERY
在这里插入图片描述

⑦、DEPENDENT SUBQUERY
如果包含子查询的查询语句不能转换成多表连接的形式，并且子查询是相关子查询，该子查询的第一个select关键字代表的那个查询的select_type就是 DEPENDENT SUBQUERY

⑧、DERIVED：对于包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED
在这里插入图片描述

4）partitions

5） type
在这里插入图片描述

①、system
当表中只有一条记录，并且该表使用的存储引擎的统计数据时精确的，比如 MyISAM , memory , 那么对该表的访问方法就是
system
在这里插入图片描述

②、const
当我们根据主键或者唯一的二级索引与常数进行等值匹配时，对单表的访问方法就是const
在这里插入图片描述

③、eq_ref
在连接查询中，如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行访问的，则对该被驱动表的方法方法就是
eq_ref
在这里插入图片描述

④、ref
当通过普通的二级索引与常量进行等值匹配时来查询某个表，那么对该表的方法方法可能是 ref
在这里插入图片描述

⑤、ref_or_null
当通过普通的二级索引与常量进行等值匹配时，并且该索引的值也可能为null时，那么对该表的访问方法就可能是 ref_or_null

⑥、index_merge
在这里插入图片描述

⑦、unique_subquery 是针对一些包含in 子查询的查询语句中，如果查询优化器决定将 in 子查询转换成 exists 子查询，
而且子查询还可以使用主键进行等值匹配的话，那么该子查询执行计划的 type 的值就是 unique_subquery
在这里插入图片描述

⑧、index_subquery

⑨、range
使用索引获取某些范围区间的记录，那么就可能使用到 range 的访问方法
在这里插入图片描述

⑩、index
当我们使用索引覆盖，但是需要扫描全部的索引记录时，该表的访问方法就是 index

all : 全表扫描

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

6） possible_keys和key
在这里插入图片描述

7） key_len
表示实际使用到的索引长度（即：字节数），帮你检查是否充分利用了索引，值越大越好

8） ref
当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列

在这里插入图片描述

9） rows ：预估的需要读取的记录数

10）filtered：某个表经过搜索条件后剩余记录的百分比
在这里插入图片描述

11）Extra
在这里插入图片描述

No tables used
在这里插入图片描述

Impossible WHERE
查询语句的where 子句永远为 “false” 适将会提示额外信息
在这里插入图片描述

Using where
当我们使用全表扫描来执行对某个表的查询，并且where后有该表的搜索条件时，在 extra中会提示上述额外信息
在这里插入图片描述
当使用索引访问来执行某个表的查询，并且该语句的where子句中，有除了该索引包含的列之外的其他搜索条件时，在 extra中会提示上述额外信息

No matching min/max row

Using index

9.9 MySQL监控分析视图-sys schema

2、Sys schema视图使用场景

索引情况

#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;

坚持每天学习一点

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
高级篇九、性能分析工具的使用

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。last_query_cost：查询上一次查询使用了多少个页。查询慢查询日志是否开启，以及慢查询日志文件的位置。2）修改long_query_time阈值。1）开启slow_query_log。查询当前系统中有多少条慢查询记录。查询慢查询的时间阈值设置。
复制链接

扫一扫