mysql执行存储进程_mysql 执行存储过程很慢的问题

CREATE TABLE `auth_date` (

`id` bigint(20) unsigned NOT NULL auto_increment,  #唯一标识

`auth_date` datetime NOT NULL,  #认证的时间

`sn_sta` char(40) collate utf8_unicode_ci NOT NULL,#终端序列号

`mac_sta` char(12) collate utf8_unicode_ci NOT NULL,#终端的mac地址

`sn_ap` char(40) collate utf8_unicode_ci NOT NULL,  #ap的序列号

`mac_ap` char(12) collate utf8_unicode_ci NOT NULL,#ap的mac地址

`ip_ap` char(15) collate utf8_unicode_ci NOT NULL,#ap的ip地址

`ip_sta` char(15) collate utf8_unicode_ci NOT NULL,#终端的ip地址

`sta_local` tinyint(1) NOT NULL,  #是否本地认证

`auth_result` tinyint(1) NOT NULL, #认证结果

PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

这个表中的数据量很大  千万 上亿条数据

CREATE TABLE `cert_alives` (

`sn` char(40) collate utf8_unicode_ci NOT NULL,#用户证书序列号

`issuer_name` varchar(1024) collate utf8_unicode_ci NOT NULL,#颁发者名字

`begin_date` datetime NOT NULL,#证书有效期

`end_date` datetime NOT NULL,

`subject_name` varchar(1024) collate utf8_unicode_ci NOT NULL,#证书持有者名字

`status` char(1) collate utf8_unicode_ci NOT NULL,#证书状态：过期、有效、暂停。。

`create_date` datetime NOT NULL,#证书创建日期时间

`bind_mac` char(1) collate utf8_unicode_ci NOT NULL,#是否绑定mac

`mac` char(12) collate utf8_unicode_ci NOT NULL,#绑定的mac地址

`terminal_type` char(1) collate utf8_unicode_ci NOT NULL,

#终端类型：

#define TYPE_GEN_CERT 0

#define TYPE_AP_CERT 1

#define TYPE_STA_CERT 2

`reserve` varchar(32) collate utf8_unicode_ci default NULL,#保留字段

PRIMARY KEY  (`sn`),

UNIQUE KEY `sn` (`sn`),

KEY `Index_subject_name` (`subject_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DELIMITER $$

USE `asengine100`$$

DROP PROCEDURE IF EXISTS `gen_static_ap`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `gen_static_ap`(time_start DATETIME,time_end DATETIME)

MODIFIES SQL DATA

BEGIN

DECLARE ap_name VARCHAR(1024);

DECLARE ap_sn CHAR(40);

DECLARE ap_ip VARCHAR(20);

DECLARE ap_mac VARCHAR(20);

DECLARE ap_count INT;

DECLARE done INT DEFAULT 0;

DECLARE get_ap_sn CURSOR FOR SELECT sn ,subject_name FROM cert_alives WHERE terminal_type = 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

DELETE FROM auth_static;

UPDATE table_status SET START=`time_start`, STOP=`time_end` WHERE id=1;

OPEN get_ap_sn;

dept_loop1:LOOP

FETCH get_ap_sn INTO ap_sn,ap_name;

IF done=1 THEN

LEAVE dept_loop1;

END IF;

SELECT ip_ap INTO ap_ip FROM  auth_date WHERE sn_ap=ap_sn ORDER BY auth_date DESC LIMIT 1;

IF  done=1 THEN

SET done=0;

ITERATE dept_loop1;

END IF;

SELECT mac_ap INTO ap_mac FROM  auth_date WHERE sn_ap=ap_sn ORDER BY auth_date DESC LIMIT 1;

SELECT COUNT(*) INTO ap_count FROM auth_date WHERE sn_ap=ap_sn AND auth_date>=time_start AND auth_date

INSERT INTO auth_static(sn_ap,issuer_name,ip_ap,mac_ap,auth_times) VALUES(ap_sn,ap_name,ap_ip,ap_mac,ap_count);

END LOOP;

CLOSE get_ap_sn;

END$$

DELIMITER ;

这个是存储过程  执行时间

大概要3分钟

求各位大牛 帮忙优化下  小弟在此感谢了

2011年4月28日 10:37