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

chaosxin

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