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