项目中遇到mysql数据库服务器cpu 100%的问题,希望找出是什么sql导致的问题。此时就希望能够对执行的索引sql进行记录,并进行统计,并分析可能存在性能风险的sql,以便有针对性地进行优化。以下步骤可以参考。
0. 版本信息
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.28-log |
+------------+
1 row in set
1. 开启查询日志,并记录到数据库表中
mysql> set global general_log=1;//开启sql日志
mysql> set global log_output='TABLE';//general_log支持输出到table:
2. 查询记录的sql
mysql> select * from mysql.general_log limit 1;
+----------------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------+
| 2022-03-01 18:18:32.720930 | root[root] @ bogon [192.168.0.180] | 700 | 1 | Query | SELECT @@session.transaction_read_only |
+----------------------------+------------------------------------+-----------+-----------+--------------+----------------------------------------+
1 row in set
3. 按sql统计执行数量
select * from (select argument, count(*) as number from mysql.general_log group by argument) t order by number DESC limit 10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| argument | number |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| SELECT @@session.transaction_read_only | 7352 |
| commit | 3812 |
| SET autocommit=0 | 295 |
| SET autocommit=1 | 295 |
4. 分析结束,关闭日志记录
记录sql日志本身需要耗费一定的资源,所以分析结束后,应变比日志记录。
mysql> set global general_log=0;//关闭sql日志
除了表日志以外,还可以设置为文件日志。
mysql> set global log_output='FILE';
mysql> set global general_log_file='/tmp/general.log';