有一个表 pageInfo 现在有2百万数据,现在想获取当天最新的数据组(根据sumID分组),sql语句如下,现在这条语句查询时间在9-10秒钟,如何提高查询效率呢? 现在表pageInfo表已经创建索引(idx-1: sumId, idx-2:addTime)语句优化??
或者数据库优化??高手帮忙看看呢?给点建议啊!!!select * from
(select id,sumid,error,shotName,Name,addTime,FROM_UNIXTIME(addTime) as creatTime
from pageInfo
where Id=457 and TO_DAYS(FROM_UNIXTIME(addTime))=TO_DAYS(NOW())
order by addTime desc)as T
group by T.sumid
order by T.sumid
我的分不多,全贡献出来,谢谢大家!
或者数据库优化??高手帮忙看看呢?给点建议啊!!!select * from
(select id,sumid,error,shotName,Name,addTime,FROM_UNIXTIME(addTime) as creatTime
from pageInfo
where Id=457 and TO_DAYS(FROM_UNIXTIME(addTime))=TO_DAYS(NOW())
order by addTime desc)as T
group by T.sumid
order by T.sumid
我的分不多,全贡献出来,谢谢大家!
addTime, FROM_UNIXTIME(addTime) as creatTime
from pageInfo
where Id=457 and
TO_DAYS(FROM_UNIXTIME(addTime))=TO_DAYS(NOW())
group by sumid
order by sumid
200w的全表扫描肯定慢.其次,试一下以下方案,看看有没有改善.
1, 确认addTime上的索引是否为降序,不是的话请修改
2,执行以下语句
select id,sumid,error,shotName,Name,addTime,FROM_UNIXTIME(addTime) as creatTime
from pageInfo
where Id=457 and TO_DAYS(FROM_UNIXTIME(addTime))=TO_DAYS(NOW())
order by addTime desc
group by T.sumid
order by T.sumid如果仍然没有改善,就看你的这个查询多不多了,如果当天的查询很多的话,那么
1、专门建一张当天的表,用于存储当天的数据,这样的话,每次查询就不是从全表去查,而只是查当天的数据
2、或者是在原标的基础上追加一列TO_DAYS(FROM_UNIXTIME(addTime)),并在这个字段上建索引
贴出
explain select ...
show index from ..
以供分析。
2、子查询的addtime条件换一个写法,这样才能用上addTime的索引
select * from
(select id,sumid,error,shotName,Name,addTime,FROM_UNIXTIME(addTime) as creatTime
from pageInfo
where Id=457
and addTime >= to_unixtime(TO_DAYS(NOW()))
and addTime < to_unixtime(TO_DAYS(NOW()) + 1)
)as T
group by T.sumid
order by T.sumid