存储过程优化就学习

存储过程优化求学习
有操作到的共有4个表，一个表的数据有百万条以上，每次统计都是没有反应
以前没有写过存储过程，写了这个，但是效率超级慢，反应不过来，小的统计的时候可以返回数据，大的时候不返回了
表结构如下：

SQL code

  CREATE TABLE `tbC` (   `id` int(11) NOT NULL auto_increment,   `pid` int(10) unsigned NOT NULL,   `sType` varchar(50) NOT NULL,   `createtime` datetime default NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `tbB` (   `id` int(11) NOT NULL auto_increment,   `pid` int(10) unsigned default NULL,   `username` varchar(50) default NULL,   `password` varchar(50) default NULL,   `sGroup` varchar(50) default NULL,   `sCet` varchar(50) default NULL,   `sType` varchar(50) default '',   `realname` varchar(50) default NULL,   `detail` varchar(500) default NULL,   `dtlasttime` datetime default NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `tbA` (   `id` int(11) NOT NULL auto_increment,   `pid` int(10) unsigned default NULL,   `username` varchar(50) default NULL,   `dtltime` datetime default NULL,   `sType` varchar(50) NOT NULL,   `Prices` decimal(10,0) NOT NULL,   `sTime` varchar(50) NOT NULL,   `createtime` datetime NOT NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  CREATE TABLE `tbD` (   `id` int(11) NOT NULL auto_increment,   `pid` int(10) unsigned NOT NULL,   `datetime1` datetime NOT NULL,   `datetime2` datetime NOT NULL,   `createtime` datetime NOT NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   

欢迎大家阅读《存储过程优化就学习》,跪求各位点评，by 搞代码

存储过程：

SQL code

  CREATE PROCEDURE `get_rpb` (pid int,sid int) BEGIN      DECLARE dtime1,dtime2 datetime;      SELECT `datetime1`,`datetime2` into dtime2,dtime1 FROM `tbD` WHERE (`pid` = pid) AND (`id`=sid);      SELECT sType,sGroup,sCet,SUM(sTime) AS total,SUM(Prices) AS totalprice FROM (         SELECT              a.*,b.sGroup,B.sCet          FROM              tbA a,tbB b,tbC c         WHERE             (a.pid=b.pid) AND (a.pid=c.pid) AND (a.pid=pid) AND (a.username=b.username) AND (a.sType=c.sType)     ) AS ABC WHERE (`createtime` between dtime1 and dtime2) GROUP BY sType,sGroup,sCet; END;  

统计是调用类似：call get_rpb(31,3355);

求各位帮忙下，谢谢了!

——解决方案——————–
表a上加pid索引 username索引 atype索引 createtime索引
b上加pid索引 username索引
c上加stype索引

喜欢 (0)赏

[搞代码]

分享 (0)