Mysql——SQL优化

292 阅读3分钟
*前两天项目接口报错,日志提示锁等待超时,自己本地测了一下,查询>1s,更新4s左右,表数据大约180万,加个索引完事,后续要把数据库的语句都排查一下*

第一步

1、mysql开启慢查询

查询MySQL是否开启慢查询:


mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | OFF                                    |
| slow_query_log_file | /home/mysql8data/mysql/3-e184-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

可以看到mysql是否开启、慢查询日志位置、慢查询记录阈值(秒)

(1)方法一:直接用sql语句修改全局变量,数据库服务不需要重启,但重启后变量重置,慢查询失效

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | ON                                     |
| slow_query_log_file | /home/mysql8data/mysql/3-e184-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

可以看到慢查询时间阈值还是10s,重新登录一下就好

[root@3-e184 ~]# mysql -uroot -h127.0.0.1  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 288
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> 

(2)方法二:修改mysql配置文件 /etc/my.cnf,添加参数后重新启动服务生效

show_query_log = 1;
show_query_log_file=/home/mysql8data/mysql/3-e184-slow.log
log_query_time=1;

一般情况下线上的数据库都不会随意重启,开启慢查询日志会损耗mysql性能,所以方法一用几天排查出问题关掉就行,如果要一直开启慢查询日志,可以方法一和方法二结合使用。

2、慢查询日志测试

开启慢查询后,可以直接在日志文件中查看到具体信息

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

mysql> select count(*) FROM T_File;
+----------+
| count(*) |
+----------+
|  1881997 |
+----------+
1 row in set (0.87 sec)

mysql> select * from T_File where GUID like '%fhska%';
Empty set (2.28 sec)

mysql> 

可以看到1、3两次查询都超过1s,应该会记录在日志中,查看日志:

[root@3-e184 ~]# cat  /home/mysql8data/mysql/3-e184-slow.log 
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /home/mysql8data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-01-12T15:54:13.921353Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:   288
# Query_time: 3.000221  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use BeiDouSoft-FileManage;
SET timestamp=1610466850;
select sleep(3);
----------
# Time: 2021-01-12T15:54:18.029566Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:   288
# Query_time: 2.279036  Lock_time: 0.000109 Rows_sent: 0  Rows_examined: 1881997
SET timestamp=1610466855;
select * from T_File where GUID like '%fhska%';
[root@3-e184 ~]# 

3、mysqldumpslow使用 常用命令

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log  //得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到访问次数最多的10个SQL 
mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log  //得到按照时间排序的前10条里面含有做了连接的查询SQL
mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more  //另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏情况

常用参数

s:是表示按照何种方式排序

c:访问次数

i:锁定时间

r:返回记录

t:查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

t:即为返回前面多少条数据

g:后边搭配一个正则匹配模式,大小写不敏感

重新生成log文件

  有时候log文件过大需要清空,直接删除log文件,本以为下次再有慢查询会自动生成文件并记录,其实并不会生成。当然重启mysql是可以生成的,但是生产环境重启mysql一定要慎重,不到万不得已不要重启mysql。在删除log文件之后,重新生成log文件需要运行 flush logs 命令。

mysql> flush logs;
Query OK, 0 rows affected