MySQL 慢查询

基础命令

# 查看慢查询是否开启
show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/79168aa559e4-slow.log |
+---------------------+--------------------------------------+
# slow_query_log 开启状态
# slow_query_log_file 慢查询日志文件位置

# 1,开启慢查询
set global slow_query_log = on;
# 2,设置慢查询日志位置, 有点需要注意如果当前位置无权限的,会设置失败
set global slow_query_log_file = /tmp/slow.log;
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slow_query_log      | ON            |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+
# 如果设置失败,就用此命令查看目录位置
show variables like '%dir%';
# 3,设置慢日志的限定时间 3s
set global long_query_time = 3;
# 4, 查看时间设置, 需要注意的是,更改之后,需要切换一个客户端才能看到设置的3s
show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000 |
+-----------------+-----------+

测试慢查询

上面我开启的是3s所以我写一条4s的sqlselect sleep(4),这样这条sql就会进到慢查询日志中,日志文件位置就是我们上面设置的路径.

root@79168aa559e4:/tmp# cat slow.log 
/usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-01-26T15:40:23.700187Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 4.000201  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1611675619;
select sleep(4);

慢查询检测工具

1. mysqldumpslow

   // todo

2. show profile

   1. 简介

      Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

   2. 基础操作

      # 查看是否开启;
      show variables like 'profiling';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | profiling     | OFF   |
      +---------------+-------+
      # 开启profile
      set profiling = on;
      

   3. 测试

      select sleep(4);
      
      # 查看记录
      show profiles;
      +----------+------------+---------------------------------+
      | Query_ID | Duration   | Query                           |
      +----------+------------+---------------------------------+
      |        1 | 0.00122625 | show variables like 'profiling' |
      |        2 | 4.00031050 | select sleep(4)                 |
      |        3 | 0.00060675 | show databases                  |
      +----------+------------+---------------------------------+
      # 查看单条
      show profile cpu,block io for query Query_ID;
      # 例子
      show profile cpu,block io for query 3;
      +----------------------------+----------+----------+------------+--------------+---------------+
      | Status                     | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
      +----------------------------+----------+----------+------------+--------------+---------------+
      | starting                   | 0.000067 | 0.000067 |   0.000000 |            0 |             0 |
      | checking permissions       | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
      | Opening tables             | 0.000179 | 0.000179 |   0.000000 |            0 |             0 |
      | init                       | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
      | System lock                | 0.000012 | 0.000011 |   0.000000 |            0 |             0 |
      | optimizing                 | 0.000015 | 0.000016 |   0.000000 |            0 |             0 |
      | statistics                 | 0.000043 | 0.000043 |   0.000000 |            0 |             0 |
      | preparing                  | 0.000038 | 0.000038 |   0.000000 |            0 |             0 |
      | Creating tmp table         | 0.000084 | 0.000083 |   0.000000 |            0 |             0 |
      | executing                  | 0.000085 | 0.000086 |   0.000000 |            0 |             0 |
      | end                        | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
      | query end                  | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
      | waiting for handler commit | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
      | removing tmp table         | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
      | waiting for handler commit | 0.000004 | 0.000005 |   0.000000 |            0 |             0 |
      | closing tables             | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
      | freeing items              | 0.000020 | 0.000020 |   0.000000 |            0 |             0 |
      | cleaning up                | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
      +----------------------------+----------+----------+------------+--------------+---------------+
      

   4. show profile 常用参数

      1. All – 显示所有的开销
      2. Block Io – 显示块IO
      3. CONTEXT SWITCHES：上下文切换开销。
      4. CPU：显示CPU开销信息。
      5. IPC：显示发送和接收开销信息。
      6. MEMORY：显示内存开销信息。
      7. PAGE FAULTS：显示页面错误开销信息。
      8. SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
      9. SWAPS：显示交换次数开销信息。

   5. 需要重点关注部分

      1. converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
      2. Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
      3. Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
      4. locked – 锁

本作品采用《CC 协议》，转载必须注明作者和本文链接