MySql 的统计查询性能问题

2011-12-07 10:25心释の逍遥 | 分类：数据库DB | 浏览1076次

由于数据量非常大（单表：四五百万条数据），需要对这种类型的表进行统计查询，并插入到对应的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.TimeFROM 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.TimeFROM datarecord  a,(SELECT Id ,max(`Value`) as valueb FROM datarecord GROUP BY Id) b where a.Id=b.Id and a.`Value`=b.valueb    这是那条我现在用的语句。之前提问编辑时，多写了个字母。

分享到：

2011-12-07 13:39提问者采纳

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

追问

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

回答

我觉得方法二应该是可行的，你也许误解我意思了建一张新表,假设为temp里面只需要存五个字段id  id_max(value)  id_min(value)  id_sum(value)  id_count(*)初始数据insert into tempselect 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 value<>10min(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时速度会慢，但统计会快很多

提问者评价

尽管我是用修改存储引擎，换成MyiSAM。但是还是感谢。。。

评论|0

try4148 |七级采纳率28%

擅长：C/C++数据库DB其他编程语言

按默认排序|按时间排序

其他5条回答

2011-12-07 11:47隋龙飞|六级

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

评论|0

2011-12-13 02:51lwduapyq|二级

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

评论|0

2011-12-07 10:50flute104|四级

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

追问

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