MySQL性能分析

最新推荐文章于 2024-07-15 19:44:21 发布

初衷不改~

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量576

点赞数 17

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41892775/article/details/140396970

版权

MySQL性能分析

查询SQL 执行频率

MySQL 客户端连接成功后，通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次：

-- 查询全局数据的数据
SHOW GLOBAL STATUS LIKE 'Com_______';
-- 查询当前会话的数据
SHOW SESSION STATUS LIKE 'Com_______';

以全局为例，执行结果如下：

在这里插入图片描述
以下是一些关键参数的解释：

变量名	含义解释
Com_binlog	执行 BINLOG 命令的次数
Com_commit	执行 COMMIT 语句的次数
Com_delete	执行 DELETE 操作的次数
Com_select	执行 SELECT 操作的次数
Com_insert	执行 INSERT 操作的次数
Com_update	执行 UPDATE 操作的次数

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，可以执行以下命令查看是否开启：

SHOW VARIABLES like 'slow_query_log'

在这里插入图片描述
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为5秒，SQL语句执行时间超过5秒，就会视为慢查询，记录慢查询日志
long_query_time=5
# 慢查询日志文件路径，可以自定义路径和文件名
slow_query_log_file = /var/lib/mysql/slow-query.log

配置完成后，重启mysql服务，执行下面语句可以查询慢查询日志存放的路径：

SHOW VARIABLES LIKE 'slow_query_log_file';

在这里插入图片描述
使用下面的代码可以运行一个存储过程，产生一个大表，用以模拟慢SQL的情况：

-- 创建一个新表
CREATE TABLE large_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加测试数据的存储过程
DELIMITER //
CREATE PROCEDURE generate_large_table_data(num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE name_val VARCHAR(100);
    DECLARE age_val INT;
    START TRANSACTION;
    WHILE i <= num_rows DO
        SET name_val = CONCAT('User', i);
        SET age_val = FLOOR(RAND() * 100);  -- 生成随机年龄
        INSERT INTO large_table (name, age)
        VALUES (name_val, age_val);
        SET i = i + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

-- 调用存储过程，添加100万条测试数据
CALL generate_large_table_data(1000000);

查询数据

-- 由于我使用的客户端有优化功能，会自动分页前500条，所以这里我手动分页前十万条
select * from large_table limit 100000

找到慢查询日志文件，就可以看到下面的内容：
在这里插入图片描述

profile 性能分析

profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过以下命令可以查看profiles相关配置：

-- 查询全局参数
show global variables like '%profil%';
-- 查询会话参数
show session variables like '%profil%';

在这里插入图片描述
开启profiles

-- 全局开启
set global profiling=1;
-- 会话开启
set session profiling=1;

当开启profiles后，接下来执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了，可以使用以下命令查询：

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

EXPLAIN结果解析：

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered的值越大越好。