mysql数据库的性能优化,topsql定位可以通过开启慢查询日志来协助分析
#查看慢日志查询开启状态
show variables like '%slow_query_log%';
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /opt/mysql/log/slow-query.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
#慢查询日志默认状态是关闭的, /opt/mysql/log/slow-query.log 是日志文件位置
#开启慢日志查询
mysql> set global slow_query_log =1;
Query OK, 0 rows affected (0.08 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /opt/mysql/log/slow-query.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
#现在可以查看为开启状态,但是这个状态只对本数据库本次有效,重启便恢复默认状态,若要长期开启,还需要修改配置文件my.cnf。
#查看慢sql阈值
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#在生产中一般sql超过3秒就已经很慢了(可以根据自己需求设置此值,默认是一秒)
set global long_query_time=3;
#发现修改后,值还为1秒。原因是需要重新连接或者开启一个新的会话才能看到修改的值。
#模拟查询慢sql
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
#查看日志
tail -10f /opt/mysql/log/slow-query.log
SET timestamp=1558330192;
commit;
/usr/sbin/mysqld, Version: 5.6.16-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 190520 15:18:31
# User@Host: root[root] @ localhost [] Id: 1075503
# Query_time: 5.000239 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1558336711;
select sleep(5);