利用数据库存储过程统计数据库的访问量

1.新建访问量信息表

##创建表CheckDbStatus
CREATE TABLE `tbCheckDbStatus` (
`check_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`com_select` int(10) unsigned DEFAULT NULL,
`com_insert` int(10) unsigned DEFAULT NULL,
`com_update` int(10) unsigned DEFAULT NULL,
`com_delete` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`check_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

2.新建存储过程

-- 创建一个存储过来用来监控mysql的SELECT、INSERT、UPDATE、DELETE使用情况。
## 创建存储过程pCheckDbStatus1()
drop procedure if exists pCheckDbStatus1;
delimiter //
create procedure pCheckDbStatus1()
begin
select @COM_DELETE:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_DELETE';
select @COM_INSERT:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_INSERT';
select @COM_SELECT:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_SELECT';
select @COM_UPDATE:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_UPDATE';
select sleep(1);
select @COM_DELETE1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_DELETE';
select @COM_INSERT1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_INSERT';
select @COM_SELECT1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_SELECT';
select @COM_UPDATE1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_UPDATE';

 
INSERT INTO tbCheckDbStatus VALUES (NULL,now(), @COM_SELECT1-@COM_SELECT,@COM_INSERT1-@COM_INSERT,@COM_UPDATE1-@COM_UPDATE,@COM_DELETE1-@COM_DELETE );
 
end;
//
delimiter ;

3.创建定时任务执行存储过程

-- mySQL兼容问题处理
-- show variables like '%show_compatibility_56%';
-- set global show_compatibility_56=on;

-- 设置group_concategroup_concat

  -- SET GLOBAL group_concat_max_len=102400；

  -- SET SESSION group_concat_max_len=102400;

-- 查询定时任务是否开启
-- show variables like '%sche%'; 
-- set global event_scheduler=1; 

-- 定时任务 每隔59秒执行一次
CREATE EVENT if not exists e_test 
            on schedule every 59 second 
            on completion preserve 
 do call pCheckDbStatus1(); 

4.数据定时清理

## 创建存储过程pClearDbStatus1
drop procedure if exists pClearDbStatus1;
delimiter //
create procedure pClearDbStatus1()
begin
DELETE FROM tbCheckDbStatus WHERE unix_timestamp(now())-unix_timestamp(time)>86400;
end;
//
delimiter ;


 -- 定时任务 每天执行一次
CREATE EVENT if not exists e_test2 
            on schedule EVERY  1 DAY STARTS '2021-03-11 17:30:00'
            on completion preserve 
 do call pClearDbStatus1(); 

 

5. 相关知识

-- 查询数据库增删改查次数
 show   global status where Variable_name in('com_select','com_insert','com_delete','com_update')  ;

-- 查询所有的存储过程：
select name from mysql.proc where db='数据库名';

-- 查询某个存储过程：
show create procedure 存储过程名;

　　-- 查询事件（定时任务）

　　select * from information_schema.EVENTS;

select *  from mysql.event;

-- 临时关闭事件（定时任务）

ALTER EVENT e_test DISABLE;

-- 开启事件（定时任务）

ALTER EVENT e_test ENABLE;

-- 删除事件（定时任务）
DROP EVENT [IF EXISTS] event_name
例：DROP EVENT e_test;

 数据库定时任务配置

-- 查询数据库是否打开定时任务
show variables like '%event_scheduler%';

-- 开启定时任务
-- 语句方式(当前启动的实例有效，重启后无效)
set global event_scheduler=1;
-- 或配置my.cnf(windows下是my.ini)下添加如下选项(重启后有效)
[mysqld]
event_scheduler=ON 

 

参考资料  存储过程 、定时任务