先上表结构:CREATE TABLE IF NOT EXISTS `RoleLogTbl` (
  `LogID` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `RoleID` int(4) unsigned DEFAULT NULL,
  `Name` varchar(32) DEFAULT NULL,
  `LineID` int(4) DEFAULT NULL,
  `LastAddr` varchar(16) DEFAULT NULL,
  `LastPort` int(4) DEFAULT NULL,
  `LastLogin` datetime DEFAULT NULL,
  `LastLogout` datetime NOT NULL,
  `PlayTime` bigint(8) DEFAULT NULL,
  PRIMARY KEY (`LogID`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1376500 ;
表是其他人设计的,是个登录日志表,我帮他们弄统计功能。RoleID表示用户ID,LastLogin表示最后登录时间,现有个需求,是统计本月登录过 并且 上月没有登录过 并且 前一个月登录过的用户。比如现在6月,即统计6月登录过并且4月登录过并且5月没有登录过的用户。他们是空表来的,我自己模拟插入的数据,大概是一百三十万左右。也附上插入代码了 ob_implicit_flush();
$sql='INSERT RoleLogTbl(RoleID,Name,LineID,LastAddr,LastPort,LastLogin,LastLogout,PlayTime)
 VALUES(:RoleID,:Name,:LineID,:LastAddr,:LastPort,:LastLogin,:LastLogout,:PlayTime)';
$RoleIDArr=array(1,2,3,4,5,6,7,8,9,10);
$LastAddrArr=array('192.168.1.1','192.168.1.10','192.168.1.100','192.168.1.12','192.168.1.11','192.168.1.13','192.168.1.15','192.168.1.17');
$loginArr=array('2013-05-01 15:14:49','2013-05-11 14:34:49','2013-05-22 10:57:49','2013-05-17 01:25:49');
$PlayTimeArr=array(100,123,432,59,89);

for($i=0;$i<1000000;$i++)
{
$playTime=$PlayTimeArr[mt_rand(0,4)];
$lastLogin=$loginArr[mt_rand(0,3)];
Yii::app()->db->createCommand($sql)->execute(array(
'RoleID'=>$RoleIDArr[mt_rand(0, 9)],
'Name'=>'AutoName'.$i,
'LineID'=>0,
'LastAddr'=>$LastAddrArr[mt_rand(0,7)],
'LastPort'=>80,
'LastLogin'=>$lastLogin,
'LastLogout'=>date("Y-m-d H:i:s",strtotime($lastLogin.' +'.$playTime.' minute')),
'PlayTime'=>$playTime,
));
echo '已插入',$i,'条<br>';
}
主要求这种需求的SQL思路。我先附上自己写的SQL,写的烂,别嘲笑我SELECT RoleID FROM (SELECT RoleID,LastLogin FROM RoleLogTbl  WHERE  (LastLogin>='2013-05-01 00:00:00' AND LastLogin<='2013-05-31 23:59:59') AND RoleID  IN  (SELECT RoleID FROM RoleLogTbl AS t2 WHERE  LastLogin>='2013-06-01 00:00:00' ) GROUP BY RoleID) AS t WHERE LogID NOT IN (SELECT LogID FROM RoleLogTbl WHERE LastLogin>='2013-04-01 00:00:00' AND LastLogin<='2013-04-30 23:59:59' )查询结果是10秒多一些,惨不忍睹。然后我给RoleID跟LastLogin字段加了索引
  KEY `LastLoginIndex` (`LastLogin`) USING BTREE,
  KEY `RoleIDIndex` (`RoleID`) USING BTREE
加完后执行速度好些了。0.5秒左右。加完索引后的插入速度没再测试。SQL语句有没有更进一步的优化空间?毕竟是别人设计的表,要是能在他们设计的基础上进行SQL语句优化就比较好了。