mysql存储过程+事件统计mysql增删改查qps

/*统计mysql增删改查 qps 的sql脚本*/;

DROP TABLE IF EXISTS  qpsInfo ;

CREATE TABLE qpsInfo(

id INT PRIMARY KEY AUTO_INCREMENT,

`type` INT COMMENT "1 insert，2 delete，3 update，4 select",

`count` INT COMMENT "更新的总数",

`qps` INT COMMENT "10秒钟更新的数量",

create_time DATETIME  DEFAULT NOW()

);

/*初始化表数据*/;

insert into qpsInfo(`type`,`count`,`qps`) values (1,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_insert"),0);

insert into qpsInfo(`type`,`count`,`qps`) values (2,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_delete"),0);

insert into qpsInfo(`type`,`count`,`qps`) values (3,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_update"),0);

insert into qpsInfo(`type`,`count`,`qps`) values (4,(SELECT variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_select"),0);

/*创建存储过程统计增删改查qps*/;

DROP PROCEDURE  IF EXISTS qpsCount;

DELIMITER //

CREATE PROCEDURE qpsCount()

BEGIN

DECLARE _count INT DEFAULT 0;

DECLARE _old_count INT DEFAULT 0;

DECLARE _num INT DEFAULT 0;

SELECT variable_value  INTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_insert";

SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 1 ORDER BY id DESC LIMIT 1;

SET _num = (_count - _old_count) / 10;

INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (1,_count,_num);

SELECT variable_value  INTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_delete";

SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 2 ORDER BY id DESC LIMIT 1;

SET _num = (_count - _old_count) / 10;

INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (2,_count,_num);

SELECT variable_value  INTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_update";

SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 3 ORDER BY id DESC LIMIT 1;

SET _num = (_count - _old_count) / 10;

INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (3,_count,_num);

SELECT variable_value  INTO _count FROM information_schema.GLOBAL_STATUS WHERE variable_name = "com_select";

SELECT `count` INTO _old_count FROM qpsInfo WHERE `type` = 4 ORDER BY id DESC LIMIT 1;

SET _num = (_count - _old_count) / 10;

INSERT INTO qpsInfo(`type`,`count`,`qps`) VALUES (4,_count,_num);

END //

DELIMITER ;

/*创建事件，让mysql定时运行*/;

DROP event IF EXISTS `qpsCount`;

CREATE EVENT `qpsCount` ON SCHEDULE EVERY 10 SECOND ON COMPLETION NOT PRESERVE ENABLE DO CALL qpsCount();

本文出自 “Jeff” 博客，请务必保留此出处http://zhangxz.blog.51cto.com/5490116/1610714