自己的系统在大量用户同时注册登陆时,程序端的负载很大,因为注册和登陆时,不仅要检查用户的账户,还需要修改读取用户相关的一揽子信息表。于是打算把这一堆东西扔进存储过程和函数里,于是就写了,写出来后,发现执行一次存储过程就让mysql的cpu占用从0.1迅速蹦到1.3(linux下用top命令观察),这让我感觉不太好,后来找了个在数据库有几年经验的人看了一下我的存储过程。他提出以下观点:
1.你的存储过程里,居然有随机生成字符串,拼接字符串,sha1加密字符串这些操作,对mysql来说,负担太大
2.不要使用LAST_INSERT_ID()获取上一条插入记录的id,这个命令效率很低,远不如select
3.以上说的这些,其实换在MSSQL和oracle是没啥问题的,这两家的脚本引擎非常成熟,效率很高,而mysql的连脚本都算不上,所以在存储过程和函数里只做CRUD,别干别的,干别的话都会拖慢效率。
我想知道(尤其是第三点)是真的吗,mysql的存储过程执行效率差这么多吗
附上存储过程源码
-- 自动注册用存储过程
DROP PROCEDURE IF EXISTS `AutoRegister`;
-- 改变提交符号
delimiter //
CREATE PROCEDURE `AutoRegister`
(
_ip varchar(255) -- ip地址
)
BEGIN
DECLARE _allChar,_smallLetter,_bigLetter,_numberChar varchar(255);
DECLARE _userName,_password,_passwordSHA1,_nickName varchar(255);-- 定义用户名,密码,密码加密后,昵称
DECLARE _count,_id,_goodsId,_goodsNum INT;-- 计算记录数目,用户id,道具id,道具原始数量
DECLARE _sex tinyint;-- 性别
DECLARE _nowData DATE;-- 定义当前日期
DECLARE _boolLoginReward INT DEFAULT 0;-- 是否有登陆奖励的标志
DECLARE _curDone,_ERROR int DEFAULT 0;-- 游标完成标志符,错误符号
DECLARE goodsCur CURSOR FOR SELECT `id`,`defaultNum` FROM t_Goods WHERE t_Goods.isDelete = 0;-- 游标必须定义在变量之后,HANDLER之前
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING set _ERROR = -1; -- 有任何错误就回滚
DECLARE CONTINUE HANDLER FOR not found set _curDone = 1;-- 遍历游标结束后设置为1
-- 设置用于生成随机字符串的基础数据
SET _allChar ='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET _smallLetter = 'abcdefghijklmnopqrstuvwxyz';
SET _bigLetter = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET _numberChar = '0123456789';
start_auto_register: LOOP
SET _userName = CONCAT(fun_CreateString(6,_smallLetter),fun_CreateString(6,_numberChar));
SET _password = CONCAT(fun_CreateString(6,_smallLetter),fun_CreateString(6,_numberChar));
SET _passwordSHA1 = SHA1(_password);-- SHA1加密
SET _nickName = CONCAT('游客',fun_CreateString(4,_numberChar));-- 生成昵称
SET _sex = CAST(fun_CreateString(1,'01') AS SIGNED); -- 生成性别
-- 事务开始
START TRANSACTION;
insert into t_UserInfo (`userName`, `password`, `userNickName`, `userSex`) VALUES (_userName,_passwordSHA1,_nickName,_sex);-- 用户注册
IF (_ERROR <> 0) THEN
ROLLBACK;-- 插入出错,回滚
SET _ERROR = 0;-- 重置错误符
ITERATE start_auto_register;-- 从头再次循环
END IF;
-- 注册成功,获取刚才注册成功的玩家id
SET _id = LAST_INSERT_ID();
OPEN goodsCur;-- 打开游标
REPEAT
FETCH goodsCur INTO _goodsId,_goodsNum;-- 注意变量名不能和列名一样,否则fetch into失效
SELECT COUNT(`id`) INTO _count FROM t_GoodsInfo WHERE t_GoodsInfo.userId = _id AND t_GoodsInfo.goodsId = _goodsId;
IF (_count < 1) THEN
INSERT INTO t_GoodsInfo (`userId`,`goodsId`,`goodsNum`) VALUES (_id,_goodsId,_goodsNum);-- 初始化玩家物品信息表记录
END IF;
UNTIL _curDone >= 1
END REPEAT;
CLOSE goodsCur;-- 关闭游标
SET _curDone = 0;-- 设置游标遍历到结尾标志符号为默认值
-- 初始化游戏统计数据记录
SELECT COUNT(`id`) INTO _count FROM t_PlayRecordStatisticsInfo WHERE t_PlayRecordStatisticsInfo.`id` = _id;
IF (_count < 1) THEN
INSERT INTO t_PlayRecordStatisticsInfo (`id`) VALUES (_id);
END IF;
-- 初始化当日游戏统计记录
SET _nowData = curdate();
SELECT COUNT(`id`) INTO _count FROM t_PlayRecordStatisticsInfoDay WHERE t_PlayRecordStatisticsInfoDay.`userId` = _id AND t_PlayRecordStatisticsInfoDay.`statisticsDay` = _nowData;
IF (_count < 1) THEN
INSERT INTO t_PlayRecordStatisticsInfoDay (`userId`,`statisticsDay`) VALUES (_id,_nowData);
END IF;
-- 检查本日登陆奖励
CALL sp_LoginReward(_id,_nowData,_boolLoginReward);
-- 插入登陆纪录
INSERT INTO t_UserLoginInfo (`userId`,`userName`,`loginIP`) values (_id,_userName,_ip);
IF _ERROR = 0 THEN
COMMIT;
LEAVE start_auto_register;-- 全部数据插入正常,离开循环
ELSE
ROLLBACK;-- 插入出错,回滚
SET _ERROR = 0;-- 重置错误符
ITERATE start_auto_register;-- 从头再次循环
END IF;
END LOOP start_auto_register;
-- 返回用户基本数据
SELECT `id`,`userName`,_password AS `password`,`userNickName`,`userSex`,`email`,`headPhotoId`,`headPhoto`,`donateLevel`,`vipLevel` FROM t_UserInfo WHERE t_UserInfo.`id` = _id;
-- 返回用户持有道具表
-- 返回玩家游戏记录统计总表
-- 返回玩家当日游戏记录统计总表
-- 返回玩家已经完成的当日任务记录
-- 返回玩家的成就记录
CALL sp_GetPlayerInfo(_id,_nowData);
-- 返回登陆奖励
IF _boolLoginReward = 1 THEN
SELECT * FROM t_LoginRewardInfo WHERE t_LoginRewardInfo.`userId` = _id AND t_LoginRewardInfo.`rewardDate` = _nowData;
ELSE
SELECT NULL;
END IF;
END
//
-- 改回提交符号
delimiter ;
-- 获取玩家的相关信息
DROP PROCEDURE IF EXISTS `sp_GetPlayerInfo`;
-- 改变提交符号
delimiter //
CREATE PROCEDURE `sp_GetPlayerInfo`
(
_id INT,
_nowData DATE
)
BEGIN
-- 返回用户持有道具表
SELECT * FROM t_GoodsInfo WHERE t_GoodsInfo.`userId` = _id;
-- 返回玩家游戏记录统计总表
SELECT * FROM t_PlayRecordStatisticsInfo WHERE t_PlayRecordStatisticsInfo.`id` = _id;
-- 返回玩家当日游戏记录统计总表
SELECT * FROM t_PlayRecordStatisticsInfoDay WHERE t_PlayRecordStatisticsInfoDay.`userId` = _id AND t_PlayRecordStatisticsInfoDay.`statisticsDay` = _nowData;
-- 返回玩家已经完成的当日任务记录
SELECT * FROM t_TaskDailyInfo WHERE t_TaskDailyInfo.`userId` = _id AND t_TaskDailyInfo.`taskDate` = _nowData;
-- 返回玩家的成就记录
SELECT * FROM t_AchievementInfo WHERE t_AchievementInfo.`userId` = _id ;
END
//
-- 改回提交符号
delimiter ;