关于mysql一个存储过程耗时很长的问题,求优化思路

zilaishuichina 2018-08-13 12:06:27
一个帐号表,账号id是自增字段(起始id为1000001),账号名,账号密码,创建时间,总共四个字段,账号名有建索引,sql如下

DROP TABLE IF EXISTS `guestcount`;
CREATE TABLE `guestcount` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ActID` varchar(64) DEFAULT NULL,
`ActPwd` varchar(64) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;


有一个存储过程,实现的功能是,输入一对账号密码,判断账号是否存在,不存在则insert这个账号,然后返回新插入的这条账号信息(主要是要拿账号id),如果存在,则直接返回这条账号信息,sql如下

DROP PROCEDURE IF EXISTS `spAccount_QueryAccount`;
DELIMITER ;;
CREATE PROCEDURE `spAccount_QueryAccount`(IN player_name VARCHAR(64),IN player_password VARCHAR(64))
BEGIN
DECLARE playerHave INT;
IF player_name!='' THEN
SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name;
IF playerHave = 0 THEN
INSERT INTO guestcount(ActID, ActPwd) VALUES(player_name, player_password);
IF LAST_INSERT_ID()<1000001 THEN
UPDATE guestcount SET ID=1000001 WHERE ID=LAST_INSERT_ID();
SELECT * FROM guestcount WHERE ID = 1000001;
ELSE
SELECT * FROM guestcount WHERE ID = LAST_INSERT_ID();
END IF;
ELSE
SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password;
END IF;
ELSE
INSERT INTO guestcount(ActID,ActPwd) VALUES(LAST_INSERT_ID() + 1, LAST_INSERT_ID() + 1);
SELECT * FROM guestcount WHERE ID = LAST_INSERT_ID();
END IF;
END
;;
DELIMITER ;


现在发现这个存储过程执行一次需要半秒左右,而实际上直接根据账号直接select一次只需要万分之几秒,如下是profile

Database changed
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> call spAccount_QueryAccount('lxl2002','lxl2002');
+---------+---------+---------+---------------------+
| ID | ActID | ActPwd | create_time |
+---------+---------+---------+---------------------+
| 1590917 | lxl2002 | lxl2002 | 2018-08-03 15:32:31 |
+---------+---------+---------+---------------------+
1 row in set (0.62 sec)

Query OK, 0 rows affected (0.62 sec)

mysql> SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002';
+---------+---------+---------+---------------------+
| ID | ActID | ActPwd | create_time |
+---------+---------+---------+---------------------+
| 1590917 | lxl2002 | lxl2002 | 2018-08-03 15:32:31 |
+---------+---------+---------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002';
+--------------+
| COUNT(ActID) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------+
| 1 | 0.26112300 | SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name |
| 2 | 0.35332525 | SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password |
| 3 | 0.00020225 | SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002' |
| 4 | 0.00017550 | SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002' |
+----------+------------+---------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql>


其中1,2是存储过程中执行的2个select,3,4是单独执行select,可以发现,时间相差1000倍左右,


所以问题是:
1、为什么差别这么大
2、如何优化
...全文
1200 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
地球太平洋 2018-08-15
  • 打赏
  • 举报
回复
你的存储过程执行环境是否有问题?随便弄个存储过程,看看执行需要多久。
zjcxc 2018-08-15
  • 打赏
  • 举报
回复
修改你的存储过程,把这句使用 EXPLAIN 把执行计划输出,并通过 show warnings 把警告信息一并输出,然后在 mysql 客户端调用存储过程看看输出结果
        ELSE
EXPLAIN SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password;
SHOW WARNINGS;
END IF;
zjcxc 2018-08-14
  • 打赏
  • 举报
回复
表中的数据量有多少? 另外,试试把这个存储过程删除重建一下,看看是否因为缓存的执行计划是不合理导致的
zilaishuichina 2018-08-14
  • 打赏
  • 举报
回复
引用 3 楼 zjcxc 的回复:
UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
---- 为什么要用 HASH 而不是 BTREE? 你这个是唯一的,直接用 BTREE 可以很容易做到记录定位的


HASH 和 BTREE 的区别会导致 这个select在存储过程中就耗时慢, 而拿出来单独执行就很快?
zjcxc 2018-08-14
  • 打赏
  • 举报
回复
UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
---- 为什么要用 HASH 而不是 BTREE? 你这个是唯一的,直接用 BTREE 可以很容易做到记录定位的
zilaishuichina 2018-08-14
  • 打赏
  • 举报
回复
引用 5 楼 zjcxc 的回复:
表中的数据量有多少? 另外,试试把这个存储过程删除重建一下,看看是否因为缓存的执行计划是不合理导致的


删除重建也是一样的,

表中数据100W条以内,尚未达到100W

目前查下来,用show profile for query 查看具体信息, 发现时间基本上全部消耗在 sending data 上

show status 发现 Handler_read_rnd_next 很大

所以 目前基本能确定问题出在存储过程里面,说明存储过程中的这个select没有用上索引, 原因不明。。。 求高手解答
zilaishuichina 2018-08-13
  • 打赏
  • 举报
回复
引用 1 楼 rucypli 的回复:
直接打印出来每一个sql的执行时间



有啊 , profile 已经显示了, 1 和 2就是存储过程中的sql, 3 和 4 是单独执行的sql


mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------+
| 1 | 0.26112300 | SELECT COUNT(ActID) INTO playerHave FROM guestcount WHERE ActID = player_name |
| 2 | 0.35332525 | SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password |
| 3 | 0.00020225 | SELECT * FROM guestcount WHERE ActID = 'lxl2002' AND ActPwd = 'lxl2002' |
| 4 | 0.00017550 | SELECT COUNT(ActID) FROM guestcount WHERE ActID = 'lxl2002' |
+----------+------------+---------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
rucypli 2018-08-13
  • 打赏
  • 举报
回复
直接打印出来每一个sql的执行时间

56,876

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧