mysql慢sql定位

mysql慢sql定位

前言：当工作一段时间后，我们慢慢会遇到数据库调优，如何调优我们必须要明白两件事
1.数据库有哪些sql慢。
2.如何处理
本文章记录如何排查慢sql

step1:开启慢sql日志记录

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF                               |
| slow_query_log_file       | /var/lib/mysql/hugowu-PC-slow.log |
+---------------------------+-----------------------------------+

slow_query_log：用来控制是否开启慢sql日志记录
slow_query_log_file:慢sql日志记录地址

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

这里设置session 全局配置。此时针对全局session有效，重启后无效。若需要永久生效（开启日志需要会有资源消耗，不建议永久生效）需要配置在mysql.ini(linux my.cnf)

step2：设置慢sql的时间限制

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

这里官方默认10s,我们为了更精确点可以设置1s

set global long_query_time 1;

此时开始记录sql执行信息

step3:进行慢sql日志查询

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)

它会显示慢查询sql的数目，具体的sql就在上面的Log file日志中可以看到。
我们可以打开上述慢sql日志记录文件查看一下
vi /var/lib/mysql/hugowu-PC-slow.log

/software/mysql/bin/mysqld, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /software/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2018-12-08T03:08:23.877322Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 0.551358  Lock_time: 0.000514 Rows_sent: 1  Rows_examined: 100005
use test;
SET timestamp=1544238503;
select count(*) from users;
# Time: 2018-12-08T03:09:06.038256Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 1.401716  Lock_time: 0.000220 Rows_sent: 100005  Rows_examined: 100005
SET timestamp=1544238546;
select * from users;
# Time: 2018-12-08T03:12:03.207302Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 0.395499  Lock_time: 0.000378 Rows_sent: 30006  Rows_examined: 30006
SET timestamp=1544238723;
select * from user_address_copy;

Time ：日志记录的时间

User@Host：执行的用户及主机

Query_time：查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录条数 Rows_examined 语句扫描的记录条数

SET timestamp 语句执行的时间点

select … 执行的具体语句

慢查询分析工具

分析慢查询日志是性能调优中获取信息的主要方式之一
如果slow log比较小可以用vi,vim,less,more,cat等等linux文本查看命令使用。我们也可以用mysql官方自带的工具去对slow log进行分析
官方文档5.7版本地址：https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

执行mysqldumpslow –h可以查看帮助信息。
主要介绍两个参数-s和-t
-s 这个是排序参数，可选的有：
​ al: 平均锁定时间
​ ar: 平均返回记录数
​ at: 平均查询时间
​ c: 计数
​ l: 锁定时间
​ r: 返回记录
​ t: 查询时间

-t n 显示头n条记录。

root@hugowu-PC:/home/hugowu/Desktop# mysqldumpslow -s c -t 20 /var/lib/mysql/hugowu-PC-slow.log

Reading mysql slow query log from /var/lib/mysql/hugowu-PC-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  

ps:
还有一些其他命令：
显示当前进程

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  6 | root | localhost | NULL | Query   |    0 | starting | show processlist |

显示可用的表

mysql> show open tables
    -> ;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
| performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |
| performance_schema | replication_connection_status                        |      0 |           0 |
| performance_schema | events_waits_summary_by_account_by_event_name        |      0 |           0 |
| mysql              | engine_cost                                          |      0 |           0 |

参考链接：https://blog.csdn.net/weixin_38028611/article/details/99355039