关于mysql一个存储过程耗时很长的问题，求优化思路-CSDN论坛

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

其他数据库开发 > MySQL

收藏回复

[问题点数：100分，结帖人zilaishuichina]

⋅关于js控制html5中的拖拽 ⋅关于mysql一个存储过程耗时很长的问题，求优化思路 ⋅linux下，c++11的regex的问题 更多帖子 关注私信空间博客 zilaishuichina 本版专家分：10 结帖率 98.21% 蓝花 2014年7月 C/C++大版内专家分月排行榜第三 2014年6月 C/C++大版内专家分月排行榜第三

一个帐号表，账号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、如何优化

2018-08-13 12:06:27

点赞 0 只看楼主引用举报楼主

⋅mongodb4.0将支持事务 ⋅csdn越来越恶心了，不仅每个帖子挂超链接，一楼回复直接是推荐链接 ⋅为什么每个帖子的回复都自动加个超链接？？想流量想疯了吧 更多帖子 关注私信空间博客 rucypli 本版专家分：106068 红花 2017年6月其他数据库开发大版内专家分月排行榜第一 2017年2月其他数据库开发大版内专家分月排行榜第一 2013年11月其他数据库开发大版内专家分月排行榜第一 2013年9月其他数据库开发大版内专家分月排行榜第一 2013年2月其他数据库开发大版内专家分月排行榜第一 黄花 2018年2月其他数据库开发大版内专家分月排行榜第二 2018年1月其他数据库开发大版内专家分月排行榜第二 2017年12月其他数据库开发大版内专家分月排行榜第二 2017年11月其他数据库开发大版内专家分月排行榜第二 2017年1月其他数据库开发大版内专家分月排行榜第二 2014年8月其他数据库开发大版内专家分月排行榜第二 2014年2月其他数据库开发大版内专家分月排行榜第二 2014年1月其他数据库开发大版内专家分月排行榜第二 2013年12月其他数据库开发大版内专家分月排行榜第二 2013年10月其他数据库开发大版内专家分月排行榜第二 2013年8月其他数据库开发大版内专家分月排行榜第二 2013年5月其他数据库开发大版内专家分月排行榜第二 2013年1月其他数据库开发大版内专家分月排行榜第二 2012年8月其他数据库开发大版内专家分月排行榜第二 2012年5月其他数据库开发大版内专家分月排行榜第二 2012年4月其他数据库开发大版内专家分月排行榜第二 2012年1月其他数据库开发大版内专家分月排行榜第二 蓝花 2017年9月其他数据库开发大版内专家分月排行榜第三 2017年7月其他数据库开发大版内专家分月排行榜第三 2017年5月其他数据库开发大版内专家分月排行榜第三 2017年3月其他数据库开发大版内专家分月排行榜第三 2016年12月其他数据库开发大版内专家分月排行榜第三 2014年11月其他数据库开发大版内专家分月排行榜第三 2014年7月其他数据库开发大版内专家分月排行榜第三 2014年6月其他数据库开发大版内专家分月排行榜第三 2014年5月其他数据库开发大版内专家分月排行榜第三 2013年7月其他数据库开发大版内专家分月排行榜第三 2013年3月其他数据库开发大版内专家分月排行榜第三 2012年7月其他数据库开发大版内专家分月排行榜第三 2012年6月其他数据库开发大版内专家分月排行榜第三 2011年12月其他数据库开发大版内专家分月排行榜第三: 直接打印出来每一个sql的执行时间

2018-08-13 13:47:11

点赞 0 只看TA 引用举报 #1得分 20

⋅关于js控制html5中的拖拽 ⋅关于mysql一个存储过程耗时很长的问题，求优化思路 ⋅linux下，c++11的regex的问题 更多帖子 关注私信空间博客 zilaishuichina 本版专家分：10 蓝花 2014年7月 C/C++大版内专家分月排行榜第三 2014年6月 C/C++大版内专家分月排行榜第三

引用 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)

2018-08-13 15:06:12

点赞 0 只看TA 引用举报 #2得分 0

⋅zjcxc--个人微信公共号同名的留言板 ⋅如果获取进程的资源使用数据？ ⋅突然发现，SQL Server 的 DBA 太少了 更多帖子 关注私信空间博客 zjcxc--个人微信公共号同名 本版专家分：27653 名人年度总版至少三次排名前十即授予名人勋章 状元 2005年总版技术专家分年内排行榜第一 2004年总版技术专家分年内排行榜第一 进士 2006年总版技术专家分年内排行榜第六 2003年总版技术专家分年内排行榜第八 金牌 2005年6月总版技术专家分月排行榜第一 2005年5月总版技术专家分月排行榜第一 2005年4月总版技术专家分月排行榜第一 2005年3月总版技术专家分月排行榜第一 2005年2月总版技术专家分月排行榜第一 2005年1月总版技术专家分月排行榜第一 2004年12月总版技术专家分月排行榜第一 2004年11月总版技术专家分月排行榜第一 2004年10月总版技术专家分月排行榜第一 2004年9月总版技术专家分月排行榜第一 2004年8月总版技术专家分月排行榜第一 2004年7月总版技术专家分月排行榜第一 2004年6月总版技术专家分月排行榜第一 2004年5月总版技术专家分月排行榜第一 2004年4月总版技术专家分月排行榜第一 2004年3月总版技术专家分月排行榜第一 2004年1月总版技术专家分月排行榜第一 2003年12月总版技术专家分月排行榜第一: UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
---- 为什么要用 HASH 而不是 BTREE？你这个是唯一的，直接用 BTREE 可以很容易做到记录定位的

2018-08-14 09:34:55

点赞 0 只看TA 引用举报 #3得分 20

⋅关于js控制html5中的拖拽 ⋅关于mysql一个存储过程耗时很长的问题，求优化思路 ⋅linux下，c++11的regex的问题 更多帖子 关注私信空间博客 zilaishuichina 本版专家分：10 蓝花 2014年7月 C/C++大版内专家分月排行榜第三 2014年6月 C/C++大版内专家分月排行榜第三: 引用 3 楼 zjcxc 的回复:
UNIQUE KEY `AccountNameIndex` (`ActID`) USING HASH
---- 为什么要用 HASH 而不是 BTREE？你这个是唯一的，直接用 BTREE 可以很容易做到记录定位的

HASH 和 BTREE 的区别会导致这个select在存储过程中就耗时慢，而拿出来单独执行就很快？

2018-08-14 09:42:12

点赞 0 只看TA 引用举报 #4得分 0

⋅zjcxc--个人微信公共号同名的留言板 ⋅如果获取进程的资源使用数据？ ⋅突然发现，SQL Server 的 DBA 太少了 更多帖子 关注私信空间博客 zjcxc--个人微信公共号同名 本版专家分：27653 名人年度总版至少三次排名前十即授予名人勋章 状元 2005年总版技术专家分年内排行榜第一 2004年总版技术专家分年内排行榜第一 进士 2006年总版技术专家分年内排行榜第六 2003年总版技术专家分年内排行榜第八 金牌 2005年6月总版技术专家分月排行榜第一 2005年5月总版技术专家分月排行榜第一 2005年4月总版技术专家分月排行榜第一 2005年3月总版技术专家分月排行榜第一 2005年2月总版技术专家分月排行榜第一 2005年1月总版技术专家分月排行榜第一 2004年12月总版技术专家分月排行榜第一 2004年11月总版技术专家分月排行榜第一 2004年10月总版技术专家分月排行榜第一 2004年9月总版技术专家分月排行榜第一 2004年8月总版技术专家分月排行榜第一 2004年7月总版技术专家分月排行榜第一 2004年6月总版技术专家分月排行榜第一 2004年5月总版技术专家分月排行榜第一 2004年4月总版技术专家分月排行榜第一 2004年3月总版技术专家分月排行榜第一 2004年1月总版技术专家分月排行榜第一 2003年12月总版技术专家分月排行榜第一: 表中的数据量有多少？另外，试试把这个存储过程删除重建一下，看看是否因为缓存的执行计划是不合理导致的

2018-08-14 12:33:18

点赞 0 只看TA 引用举报 #5得分 20

⋅关于js控制html5中的拖拽 ⋅关于mysql一个存储过程耗时很长的问题，求优化思路 ⋅linux下，c++11的regex的问题 更多帖子 关注私信空间博客 zilaishuichina 本版专家分：10 蓝花 2014年7月 C/C++大版内专家分月排行榜第三 2014年6月 C/C++大版内专家分月排行榜第三: 引用 5 楼 zjcxc 的回复:
表中的数据量有多少？另外，试试把这个存储过程删除重建一下，看看是否因为缓存的执行计划是不合理导致的

删除重建也是一样的，

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

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

show status 发现 Handler_read_rnd_next 很大

所以目前基本能确定问题出在存储过程里面，说明存储过程中的这个select没有用上索引，原因不明。。。求高手解答

2018-08-14 13:10:35

点赞 0 只看TA 引用举报 #6得分 0

⋅zjcxc--个人微信公共号同名的留言板 ⋅如果获取进程的资源使用数据？ ⋅突然发现，SQL Server 的 DBA 太少了 更多帖子 关注私信空间博客 zjcxc--个人微信公共号同名 本版专家分：27653 名人年度总版至少三次排名前十即授予名人勋章 状元 2005年总版技术专家分年内排行榜第一 2004年总版技术专家分年内排行榜第一 进士 2006年总版技术专家分年内排行榜第六 2003年总版技术专家分年内排行榜第八 金牌 2005年6月总版技术专家分月排行榜第一 2005年5月总版技术专家分月排行榜第一 2005年4月总版技术专家分月排行榜第一 2005年3月总版技术专家分月排行榜第一 2005年2月总版技术专家分月排行榜第一 2005年1月总版技术专家分月排行榜第一 2004年12月总版技术专家分月排行榜第一 2004年11月总版技术专家分月排行榜第一 2004年10月总版技术专家分月排行榜第一 2004年9月总版技术专家分月排行榜第一 2004年8月总版技术专家分月排行榜第一 2004年7月总版技术专家分月排行榜第一 2004年6月总版技术专家分月排行榜第一 2004年5月总版技术专家分月排行榜第一 2004年4月总版技术专家分月排行榜第一 2004年3月总版技术专家分月排行榜第一 2004年1月总版技术专家分月排行榜第一 2003年12月总版技术专家分月排行榜第一

修改你的存储过程，把这句使用 EXPLAIN 把执行计划输出，并通过 show warnings 把警告信息一并输出，然后在 mysql 客户端调用存储过程看看输出结果

        ELSE

                EXPLAIN SELECT * FROM guestcount WHERE ActID = player_name AND ActPwd = player_password;

                SHOW WARNINGS;

        END IF;

2018-08-15 15:42:10

点赞 0 只看TA 引用举报 #8得分 20

⋅此用户还没有发表过任何帖子 关注私信空间博客 地球太平洋 本版专家分：120: 你的存储过程执行环境是否有问题？随便弄个存储过程，看看执行需要多久。

2018-08-15 17:30:09

点赞 0 只看TA 引用举报 #9得分 20

收藏回复

zilaishuichina

等级：

: 蓝花 2014年7月 C/C++大版内专家分月排行榜第三
2014年6月 C/C++大版内专家分月排行榜第三

关注私信 TA的帖子

mysql存储过程实现分页: mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页

全面深入Mysql数据库优化: 本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧、应用优化、数据库优化、数据库日志等方面的知识，并通过综合案例，对课程中的知识进行一个整合...

MySQL版SQL优化: 其中讲解了MySQL的分层、存储引擎等底层逻辑，并讲解了常见的索引优化手段。在讲解时，先通过理论对先关的优化知识进行了铺垫，然后使用实际的案例详细的演示了每一次的优化操作。并且在课程的最后，还讲解了...

mysql存储过程实例: mysql存储过程实例详细介绍了mysql存储过程的开发步骤

Mysql存储过程调试工具: Mysql存储过程调试工具，可以方便的调试MYSQL的存储过程。

SQL导出MySQL单个存储过程.txt: SQL语句对单个的MySQL存储过程导出首先确保参数secure-file-priv='' 表示不限制mysqld在任意目录的导入导出打开my.cnf或my.ini，加入如下语句后重启mysql服务。 secure_file_priv='' sql语法只修改下面的...

mysql通用存储过程sql脚本: mysql通用存储过程sql脚本，支持多表，子查询，高级查询分页

c++实现调mysql存储过程: c++实现调mysql存储过程，实现存储过程的出参入参，可以支持查询多数据返回，还有存储过程的复杂数据的增删改等

mysql存储过程调试工具: mysql存储过程调试工具，可以设置断点，方便查看变量信息等。好用，简单

mysql存储过程简单应用编写: MySQL存储过程编写总结文档，包括循环结构样式，创建临时表，删除数据，MySQL分页，动态存储过程编写

MySQL数据库从入门到实战应用: 为什么说每一个程序员都应该学习MySQL？根据《2019-2020年中国开发者调查报告》显示，超83%的开发者都在使用MySQL数据库。使用量大同时，掌握MySQL早已是运维、DBA的必备技能，甚至部分IT...

mybatis调用mysql存储过程: mybatis调用mysql中的存储过程方法详解

MySQL数据库管理高级篇(一): MySQL是一个关系型数据库管理系统，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。 MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL采用了双授权策略，分为社区版...

Mysql、SqlServer、SysBase存储过程自动生成工具: 0、完全支持Mysql、SqlServer、SysBase等数据库自动生成存储过程 1、基本完全代替编写存储过程的工作任务。 2、让不会写存储过程的测试人员，也可顺利完成加压测试工作。 3、让会写存储过程的测试人员，短时间内...

计算两经纬度之间的距离存储过程_mysql: 计算两经纬度之间的距离存储过程_mysql

mysql存储过程: 存储过程的简单介绍，通过一个具体的案例，实现了存储过程的主要写法。

linux下mysql的操作与存储过程: linux下mysql的操作与存储过程 https://blog.csdn.net/freemindhack/article/details/33347203

mysql 存储过程 实战: mysql 存储过程 实战

MYSQL存储过程.pdf（带完整目录）: MYSQL存储过程，有想了解的可以看看加深理解，有例子可看

MySQL 存储过程详解: MySQL 存储过程详解

相关热词 c# 解析cad c#数字格式化万 c#int转byte c#格式化日期 c# wpf 表格 c# 实现ad域验证登录 c#心跳包机制 c#使用fmod.dll c#dll vb 调用 c# outlook