目前有一项工作需要对数据库进行优化,我对数据库不是很熟悉,所以请大家帮忙一下啊。具体的需求是:数据库规模至少在千万条,一般在一亿记录的规模我对数据库进行了分表,半个月一张表,所以要进行表联合。具体的sql语句如下Select uname,sum(data) as data from ((Select uname, sum(in_bytes+ out_bytes) data, id, log_time from tbl_traffic14 where log_time>='2009-08-11 00:00:00' and log_time<='2009-08-17 23:59:59' Group by uname) union (Select uname, sum(in_bytes+ out_bytes) data, id, log_time from tbl_traffic15 where log_time>='2009-08-11 00:00:00' and log_time<='2009-08-17 23:59:59' Group by uname))x group by uname order by data desc limit 0,10但这个执行的速度太慢,有能帮我优化一下的吗,谢谢了
这里面uname的类别有多少?
还有你这个子查询里面是否建立了合理的索引?
from (
Select uname,in_bytes,out_bytes
from tbl_traffic14
where log_time>='2009-08-11 00:00:00'
and log_time <='2009-08-17 23:59:59'
union all
Select uname,in_bytes,out_bytes
from tbl_traffic15
where log_time>='2009-08-11 00:00:00'
and log_time <='2009-08-17 23:59:59'
) t
group by uname
order by 2 desc
limit 10