mysql 之general_log统计与优化

忘是亡心i 2022-05-16 11:25 191阅读 0赞

# mysql 之general\_log统计与优化 #

general统计与优化：  
1,开始日志双模式：  
show variables like 'log\_output'; \#记录以前的日志格式，便于恢复。  
set global log\_output='TABLE,FILE,NONE'; \#增加table模式，开始统计。  
2,开启general\_log日志：  
set global general\_log=on;  
建议开启zabbix监控和自动关闭的触发功能，防止意外情况忘记关闭该参数。  
3,关闭日志，恢复状态：  
开启一段时间后关闭：  
set global general log=off;  
set global log\_output='FILE'  
  
  
4,统计数据：  
将表的数据导入新表（因为csv引擎大小写敏感):  
1，使用  
use tmp;  
2，建表  
CREATE  TABLE tmp.general\_log SELECT \* FROM mysql.general\_log;   
3,alter table tmp.general\_log change argument argument varchar(2500); \#blob不支持大小写转换，需要换成varchar。  
4,update tmp.general\_log set argument=lower(argument);  
  
  
临时表的方法(退出后会清空):  
1,use tmp;  
2,CREATE TEMPORARY TABLE tmp\_table SELECT \* FROM mysql.general\_log;  
3,alter table tmp\_table  change argument argument varchar(2500);  
4,update tmp\_table set argument=lower(argument);  
  
  
5,统计数据:  
查总数：select count(1) from tmp.general\_log;   
  
  
select \* from tmp.general\_log where argument like 'rollback'; \#30  
查询非增删改查的操作数：  
select  count(1) number ,argument from (  
select \* from tmp.general\_log where lower(argument) not like 'select%' and lower(argument) not like 'insert%' and  lower(argument) not like 'update%'   and  lower(argument) not like 'delete%'  ) not\_iuds group by argument order by number desc ;   
  
  
select查询进行排序：  
select count(1) count ,argument from (  
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(argument,"1",'N'),'2','N'),'3','N'),'4','N'),'5','N'),'6','N'),'7','N'),'8','N'),'9','N'),'0','N') argument from tmp.general\_log where argument like 'select%'  and event\_time = '2017-05-03 10:00:56.762357'   
) t group by argument order by count desc ;  
  
  
update查询进行排序：  
select count(1) count ,argument from (  
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(argument,"1",'N'),'2','N'),'3','N'),'4','N'),'5','N'),'6','N'),'7','N'),'8','N'),'9','N'),'0','N') argument from tmp.general\_log where argument like 'update%'  and event\_time = '2017-05-03 10:00:56.762357'   
) t group by argument order by count desc ;  
  
insert查询进行排序：  
select count(1) count ,argument from (  
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(argument,"1",'N'),'2','N'),'3','N'),'4','N'),'5','N'),'6','N'),'7','N'),'8','N'),'9','N'),'0','N') argument from tmp.general\_log where argument like 'insert%'  and event\_time = '2017-05-03 10:00:56.762357'   
) t group by argument order by count desc ;  
  
  
delete查询进行排序：  
select count(1) count ,argument from (  
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(argument,"1",'N'),'2','N'),'3','N'),'4','N'),'5','N'),'6','N'),'7','N'),'8','N'),'9','N'),'0','N') argument from tmp.general\_log where argument like 'delete%'  and event\_time = '2017-05-03 10:00:56.762357'   
) t group by argument order by count desc ;  
  
  
6,通过navicat进行表查询。  
select \* from information\_schema.\`TABLES\` where TABLE\_NAME = 'dbversion'; \#查看dbversion是否是目标数据的查询。  
  
  
7,分析语意，必要时和开发人员沟通具体原因。  
如有必要，可以添加索引进行优化。