Question

MySql 的统计查询性能问题

由于数据量非常大（单表：四五百万条数据），需要对这种类型的表进行统计查询，并插入到对应的Maxstatistics（里面是Id、Value、Time） MinStatistics 等表。
这个表的结构如下：CREATE TABLE `datarecord` (
`Id` smallint(6) NOT NULL,
`Time` datetime NOT NULL,
`Value` int(11) DEFAULT NULL,
`Quality` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`,`Time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
解释：这个datarecord 表中有1250个Id，每个Id都有将近3600条记录。而现在的工作就是通过
sql语句实现：统计出来每个Id的Value最大值，并且插入到MaxStatistics 。关键在统计Value最大值会耗费非常多的时间，需要将近5分钟的时间。再加上最小值平均值等的统计，就非常缓慢，以至于是不能够接受的。以下是小弟目前的查询插入语句，希望各位能给出些建议。谢谢。。。
insert into Maxstatistics(`Id`,`Value`,`Time` ) SELECT a.`Id`,a.`Value`,a.Time
FROM idatarecord a,(SELECT Id ,max(`Value`) as valueb FROM datarecord GROUP BY Id) b where a.Id=b.Id and a.`Value`=b.valueb
关于MySql 性能补充：如果没有索引或者改变引擎的前提下，但是从400多万的数据表中查询count(*)或者是查询max(Value) 都是要3-4分钟。非常的慢。添加了索引（索引是datarecord中Id和Value的联合索引，也试过Value的索引）在查询count（*） 与max（value）就很快了。都是1-2s解决的。不过对于刚才的语句好像没有什么显著的变化（好像说道GROUP BY 和索引有冲突，但是我也不知道怎样才能做到查询每个Id最大值）。希望大家能给出合理的建议，如果语句需要改善也请给些建议或者观点，非常感谢！！！十分渴望！！！
想问问大家遇到这种问题是如何对待的。还是说MySql单表到了几百万的级别就速度非常慢了吗？谢谢大家。
insert into Maxstatistics(`Id`,`Value`,`Time` ) SELECT a.`Id`,a.`Value`,a.Time
FROM datarecord a,(SELECT Id ,max(`Value`) as valueb FROM datarecord GROUP BY Id) b where a.Id=b.Id and a.`Value`=b.valueb 这是那条我现在用的语句。之前提问编辑时，多写了个字母。

Answer 1

我觉得至少有2这种解决方法：
1.修改datarecord的入库机制
既然datarecord里面有大量的数据，但是你需要的是统计出来每个Id的Value最大值，根据这一点
你在向datarecord存入数据时就应该先比较一下，只把比比某个ID的最大的value更大的数存到数据库里，
这种操作其实非常简单，这样的话在datarecord里面存的数据都是截止当前每个ID的最大的value，使用的时候直接使用了，但是你要把每个ID最大的value
写入，然后要把较小的那个给删掉，这样的话，在写入数据时都要查询会影响到入库的性能。

这种机制再向前推进一步，就是在内存里维持一个MAP，名为DatarecordMap,然后再创建一个class,用datarecord表的各个字段值作为属性，

用key=ID,value=Object(Time,value,Quality),
每当有一个新的数据要存入这个MAP时，比较一下，总是把每个ID,最大的value存入MAP,这样比存入如数据库的读写性能高多了

其实你总的ID才1250个,并不算大放内存里处理效率会高很多
这种方法最大的优点是只在内存中保留每个ID最大的value值(其实是整个记录)
因此实际需要的内存并不大

代码：
public class Datarecord {
private int id;
private Date time;
private int Value;
private int Quality=0;

public Datarecord(int id,Date time,int Value){
this.id = id;
this.time =time;
this.Value =Value;

}
<此处掠去set get方法>
}

import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;

public class SaveRecord {
/**
* @param args
*/
public static void main(String[] args) {
Map<Integer,Datarecord> DatarecordMap = new HashMap<Integer,Datarecord>();
Datarecord data =null;
Random rand = new Random();
int id,value;
int number=100000;
long t1 = System.currentTimeMillis();
for(int i=1;i<=number;i++){
//rand.nextInt(MAX - MIN + 1) + MIN
//模拟产生id,范围1 -- 5
id = rand.nextInt(10) + 1;

//模拟产生value，范围1 --- 10000
value = rand.nextInt(100000) + 1;
//System.out.println("id:"+id + " value:"+value);
if (DatarecordMap.containsKey(id)){
data = DatarecordMap.get(id);
//判断DatarecordMap 里面当前id对应的value是否被新的value小，如果小直接把这个小的覆盖掉
if (data.getValue()<value){
data = new Datarecord(id,new Date(),value);
DatarecordMap.put(id, data);
}
}else{
data = new Datarecord(id,new Date(),value);
DatarecordMap.put(id, data);
}
}
long t2 = System.currentTimeMillis();
System.out.println("产生"+number+"个数据,用时:"+(t2-t1)+"ms.");

System.out.println("输出每个id 的最大VALUE值========================");
long t3 = System.currentTimeMillis();
for(Integer key:DatarecordMap.keySet()){
System.out.println("id:"+key + " value:"+DatarecordMap.get(key).getValue());
}
long t4 = System.currentTimeMillis();
System.out.println("用时:"+(t4-t3)+"ms.");
}
测试结果：
产生100000个数据,用时:26ms.
输出每个id 的最大VALUE值========================
id:1 value:99995
id:2 value:99996
id:3 value:99994
id:4 value:99996
id:5 value:99992
id:6 value:99996
id:7 value:99993
id:8 value:99993
id:9 value:99990
id:10 value:99977
用时:1ms.

上面的代码模拟产生了100000个数据，用时26ms，数据库无论如何不到这样的性能，在第2个循环中输出每个ID的最大值，用时1ms,你在实际的应用中
可以把这个输出结果先生成一个数据文件，然后用mysql的load命令导入，时间会在不会大于1秒，其实每个ID的最大值汇总在一起并不多，在你的应用中有
1250个，用load命令导入的话，1秒不到就轻松搞定

2.优化数据库
其实写了上面的方法，我已经不想写这个了，这个无论咋优化都打不到第一种方法的性能
如果你觉得第一种方法datarecord表似乎已经没用了，但是又需要要把数据持久化到datarecord表里，可以定时把DatarecordMap表的数据取出，全部写到datarecord表里
但是要先清空这个表的数据，这样的话在某个周期内，datarecord表的数据总是每个ID的最大值

Answer 2

1.单表达到几百万确实已经是一个接近极限的值了。
2.这种情况下，不要让数据库来坐太多的计算了，可以考虑让一部分计算让给脚本服务器来做。有一种架构理念是这样的：数据库只做简单查询，计算服务器只计算，展示服务器只展示。这样会大大的降低数据库的压力。
3.你可以考虑拆分表例如每个表50万数据，每个表单独统计，然后再整合。

Answer 3

方案一。。
是不是可以
建一个视图，把每个Id的Value最大值等统计信息放里面，统计时只要查视图就行了
方案二。。
如果只是最大最小值的话
建立一个临时表，存放每个id最大最小值，初始值可以先通过统计放进去
建立个触发器，对每个插入或修改的value和临时表里比较，把最大或最小值更新到临时表，然后查找临时表就能得到最大最小值

追问

方案一 的时间和单另执行的时间相同，没有变化。
方案二 其实我说的最终的就是将每个Id的最大值、最小值、平均值统计进入新表。这中间速度很慢，所以可能也不实用。谢谢

追答

我觉得方法二应该是可行的，你也许误解我意思了
建一张新表,假设为
temp
里面只需要存五个字段
id id_max(value) id_min(value) id_sum(value) id_count(*)
初始数据
insert into temp
select id,max(value),min(value),sum(value),count(*) from datarecord group by id
以上操作只做一遍，也就是这张表虽然名字是temp,但并不删除
以后统计直接从这张表获取信息
现在只要使这张表信息保持最新就可以了
我的方法是在datarecord 中建立触发器
当update操作时,更改temp中max(value),min(value),sum(value)的值
update时假设原值10,新值3，temp中对应id的值是10,5,20
因为max(value)=10,所以修改后max(value)=select max(value) from datarecord where id='修改id' and value10
min(value)要等于新值3,sum(value)=sum(value)+3-10
当insert或delete操作时，更改temp中max(value),min(value),sum(value),count(*) 的值
统计时直接查询temp，其中avg(value)=sum(value)/count(*)
这样虽然每次操作表datarecord时速度会慢，但统计会快很多

Answer 4

SELECT t2.sid,'-',t2.eid,SUM(t1.count) 'sun-count'
FROM test t1
LEFT JOIN (
SELECT t1.ID sid,MIN(t2.ID) eid
FROM (
SELECT t1.ID
FROM test t1
LEFT JOIN test t2 ON t1.ID - 1 = t2.ID
WHERE t2.ID IS NULL
) t1
LEFT JOIN (
SELECT t1.ID
FROM test t1
LEFT JOIN test t2 ON t1.ID 1 = t2.ID
WHERE t2.ID IS NULL
) t2 ON t1.ID

Answer 5

需要的查询统计结果 ID sun-count 1-2 300 5-7 450 9 100 也就是将mysql: SELECT CONCAT(t2.sid,'-',t2.eid) ID,SUM(t1.count) 'sun

追问

说实话，不太懂。请教一下。