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


    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;

     
    -- 临时关闭事件(定时任务)
    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 

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

  • 相关阅读:
    如何在Window上使用Git
    【坑】log4j-over-slf4j.jar AND slf4j-log4j12.jar的冲突问题
    如何查看hadoop与hbase的版本匹配关系
    为什么要用Message Queue
    Storm+kafka的HelloWorld初体验
    KafkaOffsetMonitor使用方法
    Linux虚拟机配置本地yum源
    andorid CmakeLists
    python tkinter Treeview 事件绑定
    python我的tkinter学习,玩玩
  • 原文地址:https://www.cnblogs.com/dztHome/p/14513088.html
Copyright © 2020-2023  润新知