Mysql开启慢SQL并分析原因

下面是详细讲解如何开启慢SQL并分析原因的完整攻略:

1. 开启慢SQL日志

MySQL 提供了慢查询日志功能,可以记录执行时间超过规定阈值的 SQL 语句。通过开启慢SQL日志,可以了解到哪些SQL查询语句执行缓慢,以便优化性能,提高查询效率。

1.1 修改my.cnf配置文件

首先,我们需要修改my.cnf配置文件,以开启慢SQL日志。在使用过程中,可能需要先查看my.cnf文件的位置,可以通过如下命令查看:

mysql --help | grep -A 1 "Default options"

一般情况下,my.cnf文件的路径为 /etc/mysql/my.cnf 或 /etc/my.cnf 。我们在配置文件中添加如下内容:

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
  • slow_query_log:表示开启慢SQL日志功能
  • long_query_time:表示执行时间超过1秒的 SQL 语句可以被记录到日志文件中(设置的单位是秒)
  • log-queries-not-using-indexes:表示记录没有使用索引的查询语句
  • slow_query_log_file:表示日志文件的路径

修改完成后,保存配置文件并重启 MySQL 服务。

1.2 查看慢SQL日志

慢SQL日志文件的默认位置是 /var/log/mysql/mysql-slow.log ,我们可以通过查看该日志文件,了解到执行缓慢的 SQL 语句。例如:

# Time: 2021-12-01T10:57:22.038517Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 11.035533  Lock_time: 0.000092 Rows_sent: 10  Rows_examined: 110636
SET timestamp=1638365842;
SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';

上面的日志记录了一个执行耗时超过11秒的 SQL 查询语句。其中 Query_time 表示 SQL 语句的执行时间,Rows_sent 和 Rows_examined 分别表示返回结果行数和查询行数。

2. 分析慢SQL的原因

通过查看慢SQL日志,我们可以找到哪些 SQL 查询语句执行缓慢,但是日志并没有提供具体的优化方法。下面介绍几种常用的分析慢 SQL 查询语句的方法。

2.1 EXPLAIN

通过使用 MySQL 的 EXPLAIN 命令,可以分析 SQL 语句的执行计划,以便找到执行缓慢的原因。例如:

EXPLAIN SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';

执行上述命令后,可以看到类似于下面的输出:

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1     SIMPLE         users    NULL          ALL     NULL             NULL   NULL      NULL   18178   10            Using where

其中,每一列的含义如下:

  • id:查询的唯一标识符,同一条查询语句的标识符相同
  • select_type:查询类型
  • table:要查询的表名
  • partitions:匹配的分区
  • type:关联类型,使用的索引类型
  • possible_keys:可能使用的索引列表
  • key:实际使用的索引名称
  • key_len:使用的索引长度
  • ref:索引所用字段
  • rows:根据条件查询的行数
  • filtered:返回所有结果的百分比
  • Extra:额外的信息(如使用了索引等)

其中,关注以下几点:

  • type:查询的关联类型,一般优先使用 eq_ref、ref、range 等索引访问方式,尽量避免使用 ALL。ALL 表示全表扫描,执行效率非常低下。
  • key:实际使用的索引名称,如果该列是 NULL,则表示查询没有使用索引。需要针对该 SQL 语句添加索引。
  • Extra:如果有 Using filesort 或者 Using temporary 字段,一般会导致较差的效率,需要进行优化。

2.2 SHOW PROFILE

使用 SHOW PROFILE 命令可以查看 MySQL 执行 SQL 语句的详细执行情况,例如:

SET profiling = 1;
SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';
SHOW PROFILES;

执行上述命令,可以看到类似于下面的输出:

Query_ID   Duration   Query
1          0.000512   SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01'

其中,Query_ID 表示命令的唯一标识符,Duration 表示 SQL 查询的执行时间。

SHOW PROFILE 还可以显示更详细的信息,例如:

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

该命令可以显示命令 1 的 CPU 和 IO 信息。

示例说明

下面举两个示例来说明该攻略如何实际使用。

示例一

假设有一个名为 user 的表,其中包含如下字段:

id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
create_time DATE

现在,我们需要查询年龄大于20岁并且创建时间大于2021年11月1日的用户信息。

首先,我们需要修改 my.cnf 配置文件,开启慢 SQL 日志:

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

然后,在 mysql 命令行界面中执行如下 SQL 查询语句:

SELECT * FROM `user` WHERE age>20 AND create_time>'2021-11-01';

执行完毕后,我们需要查看慢 SQL 日志文件中是否记录了该查询语句,可以使用如下命令查看:

cat /var/log/mysql/mysql-slow.log

如果该查询语句记录在日志文件中,我们可以使用 EXPLAIN 命令分析执行计划,例如:

EXPLAIN SELECT * FROM `user` WHERE age>20 AND create_time>'2021-11-01';

通过该命令的输出结果,可以看到查询没有使用索引,需要给 age 和 create_time 字段添加索引来优化查询效率。

示例二

假设有一个名为 order_item 的表,其中包含如下字段:

id INT PRIMARY KEY,
order_no VARCHAR(50),
product_id INT,
product_name VARCHAR(50),
quantity INT,
price DECIMAL(10, 2),
create_time DATE

现在,我们需要查询11月份所有订单的商品数量和总价。

首先,我们需要修改 my.cnf 配置文件,开启慢 SQL 日志:

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

然后,在 mysql 命令行界面中执行如下 SQL 查询语句:

SELECT SUM(quantity), SUM(price) FROM `order_item` WHERE create_time>='2021-11-01' AND create_time<'2021-12-01';

执行完毕后,我们需要查看慢 SQL 日志文件中是否记录了该查询语句,可以使用如下命令查看:

cat /var/log/mysql/mysql-slow.log

如果该查询语句记录在日志文件中,我们可以使用 SHOW PROFILE 命令查看执行情况,例如:

SET profiling = 1;
SELECT SUM(quantity), SUM(price) FROM `order_item` WHERE create_time>='2021-11-01' AND create_time<'2021-12-01';
SHOW PROFILES;

通过 show profiles 命令的输出结果,我们可以看到该 SQL 查询语句的具体执行情况,例如执行时间、CPU 时间、I/O 时间等。可以通过这些信息,找到慢 SQL 的具体原因。

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:Mysql开启慢SQL并分析原因 - Python技术站

(0)
上一篇 2023年5月19日
下一篇 2023年5月19日

相关文章

  • redis广播/订阅模式演示

    参考博客 http://www.pianshen.com/article/7183315879/     1.首先在本地启动redis服务 2.启动4个客户端 redis-cli 3.将其中三个客户端设置监听频道 test subscribe test   4.将第四个客户端作为消息发布的客户端,向频道 test 发布消息 可以看到另外三个客户端都收到了消息…

    Redis 2023年4月13日
    00
  • mysql中的几种join 及 full join问题

    【注意】:Oracle数据库支持full join,mysql是不支持full join的,但仍然可以同过左外连接+ union+右外连接实现 初始化SQL语句:   /*join 建表语句*/   drop database if exists test;   create database test;   use test;       /* 左表t1*…

    MySQL 2023年4月13日
    00
  • SQL ABS 函数

    下面我们来详细讲解SQL ABS函数的完整攻略。 ABS函数概述 ABS函数是SQL语言中的一种数字函数,用于获取指定数字的绝对值。 函数的语法如下: ABS(n) 参数解释: n – 必须。要计算绝对值的数字,可以是数字值、表达式或者是一个 SQL Server 字段。 返回值: ABS函数返回一个数值类型的结果,该结果是参数的绝对值。 ABS函数的使用示…

    database 2023年3月27日
    00
  • Sql Server 开窗函数Over()的使用实例详解

    Sql Server 开窗函数Over()的使用实例详解 简介 开窗函数 (Window Functions),顾名思义即为 “在一组 rows 中开一个窗,然后计算在这个窗口中的函数”,它是近年来 SQL 中一大特性。 在 SQL Server 2005 版本推出 Window Functions。而在 2012 版本中,提供了更多的 Window Fun…

    database 2023年5月21日
    00
  • 关于Linux的mariadb数据库

    关于 Linux 的 MariaDB 数据库攻略 MariaDB 数据库是一种开源的数据库管理系统,它是 MySQL 的分支版本。在 Linux 系统中,我们可以使用 apt-get 命令进行安装,具体的操作步骤如下: 步骤 1:添加 MariaDB apt-get 源 我们可以通过以下命令添加 MariaDB 的 apt-get 源: sudo apt-g…

    database 2023年5月22日
    00
  • 详解Docker 下开发 hyperf 完整使用示例

    本文将详细介绍在 Docker 环境下,如何使用 Hyperf 开发应用,并给出完整的使用示例。主要包括以下几个部分。 安装 Docker 本文的演示基于 Docker 环境,因此需要先安装 Docker。Docker 可以在多个平台上运行,如 Windows、macOS 和 Linux。这里以 macOS 为例进行说明。 在 macOS 上安装 Docke…

    database 2023年5月22日
    00
  • 简单实例解释Oracle分页查询

    简单实例解释Oracle分页查询 什么是分页查询 在处理大量数据时,一次性查询所有数据显然不是一个好的选择,因为这样的查询请求会占用太多的资源,而且在大多数情况下,用户仅需要查看部分数据。这时候我们可以使用分页查询来使查询更加高效。分页查询即是将数据划分成若干页,每页只显示少量数据,用户可以通过翻页的方式浏览所有数据。 Oracle分页查询示例 在Oracl…

    database 2023年5月21日
    00
  • python 3.6 +pyMysql 操作mysql数据库(实例讲解)

    请看下面的完整攻略,分为四部分: 1. 环境准备 在使用 PyMySQL 前,需要先安装Python和PyMySQL模块。安装方法如下: 安装Python 3.6 在命令行窗口中执行命令 pip3 install PyMySQL,即可安装PyMySQL模块。 2. 连接MySQL数据库 连接MySQL数据库有两种方式,一种是使用 connect() 方法,一…

    database 2023年5月22日
    00
合作推广
合作推广
分享本页
返回顶部