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

rtr21

/*统计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();