mysql中sum的优化和索引问题

[疯狂的狙击手]

//表结构
CREATE TABLE IF NOT EXISTS `radacct` (
  `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(64) NOT NULL DEFAULT '',
  `AcctSessionTime` int(12) DEFAULT NULL,
  `AcctInputOctets` bigint(12) DEFAULT NULL,
  `AcctOutputOctets` bigint(12) DEFAULT NULL,
  ...
  ......
  PRIMARY KEY (`RadAcctId`),
  KEY `UserName` (`UserName`),
  KEY `AcctSessionTime` (`AcctSessionTime`),
  KEY `AcctInputOctets` (`AcctInputOctets`),
  KEY `AcctOutputOctets` (`AcctOutputOctets`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=456017;

$sql = 'SELECT UserName, count(*) AS numOfSession, sum(AcctSessionTime) AS Time, sum(AcctInputOctets) AS Upload, sum(AcctOutputOctets) AS Download, sum(AcctInputOctets+AcctOutputOctets) AS Bandwidth FROM radacct GROUP BY UserName';


mysql> explain SELECT UserName, count(*) AS numOfSession, sum( AcctSessionTime ) AS Time, sum( AcctInputOctets ) AS Upload, sum( AcctOutputOctets ) AS Download, sum( AcctInputOctets + AcctOutputOctets ) AS Bandwidth FROM radacct GROUP BY UserName;

+----+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | radacct | ALL  | NULL          | NULL | NULL    | NULL | 456010 | Using temporary; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+

数据量大概45w左右 在sum的字段上加上索引也无法提高查询效率

加上分页什么的就更慢了

怎么优化比较好啊 先拜谢了

[WWWWA]

explain SELECT UserName, count(*) AS numOfSession, sum( AcctSessionTime ) AS Time FROM `UserName` force index (`UserName`) radacct GROUP BY UserName; 贴结果

[疯狂的狙击手]

引用 1 楼 WWWWA 的回复:

explain SELECT UserName, count(*) AS numOfSession, sum( AcctSessionTime ) AS Time FROM `UserName` force index (`UserName`) radacct GROUP BY UserName; 贴结果

应该是这样的吧 EXPLAIN SELECT UserName, count( * ) AS numOfSession, sum( AcctSessionTime ) AS Time FROM radacct `UserName` FORCE INDEX ( `UserName` ) GROUP BY UserName

+----+-------------+----------+-------+---------------+----------+---------+------+--------+-------+
| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows   | Extra |
+----+-------------+----------+-------+---------------+----------+---------+------+--------+-------+
|  1 | SIMPLE      | UserName | index | NULL          | UserName | 66      | NULL | 456010 |       |
+----+-------------+----------+-------+---------------+----------+---------+------+--------+-------+

[疯狂的狙击手]

上面还是错的

mysql> EXPLAIN SELECT UserName, count( * ) AS numOfSession, sum( AcctSessionTime ) AS Time FROM radacct FORCE INDEX ( `UserName` ) GROUP BY UserName;
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+
|  1 | SIMPLE      | radacct | index | NULL          | UserName | 66      | NULL | 456010 |       |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+

[WWWWA]

从结果上看，是用到了索引，1个1个地增加SUM，看看问题出在什么字段中

[疯狂的狙击手]

引用 4 楼 WWWWA 的回复:

从结果上看，是用到了索引，1个1个地增加SUM，看看问题出在什么字段中

如果去取数据的时候也需要force index吗 thanks

[WWWWA]

如果是按UserName分组，加上试试

[Rotel-刘志东]

可以force index的。

[疯狂的狙击手]

加上有改善 但是还不是很理想

[WWWWA]

执行时间多久，不加时间多久

[rucypli]

45w的数据算加法 再怎么优化 这块消耗都不可避免 好的方法是这种统计不要时时统计 而是预先统计 结果存到小表里面 需要的话直接读小表内容就可以了

[ACMAIN_CHM]

无论如何你的结果都是要扫描全有表记录，而在456010记录中，的UserName的分布导致这个索引根本无用！

[疯狂的狙击手]

引用 15 楼 xuboke 的回复:

count( * ) 不能换个别的字段嘛。

http://www.cnblogs.com/jdonson/archive/0001/01/01/1531410.html COUNT(*)通常是对主键进行索引扫描

[疯狂的狙击手]

引用 11 楼 ACMAIN_CHM 的回复:

无论如何你的结果都是要扫描全有表记录，而在456010记录中，的UserName的分布导致这个索引根本无用！

那是否可以考虑去优化表结构？

[ACMAIN_CHM]

45W数据，没什么好办法。 常用的解决方案是事先把这个统计做好生产一张表。比如安排一个定时任务每 凌晨 01:00 am 来执行这个统计并且放入统计结果表，然后平时查询时就直接从统计结果表中得到结果即可。

[疯狂的狙击手]

引用 13 楼 ACMAIN_CHM 的回复:

45W数据，没什么好办法。 常用的解决方案是事先把这个统计做好生产一张表。比如安排一个定时任务每 凌晨 01:00 am 来执行这个统计并且放入统计结果表，然后平时查询时就直接从统计结果表中得到结果即可。

恩 这样也灵活一点 thanks

[欢乐的尼美]

count( * ) 不能换个别的字段嘛。

[欢乐的尼美]

300多万条记录，分页的时候，前面的页数很快，点最后一页要5秒左右才出来。我也很纠结...