//表结构
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的字段上加上索引也无法提高查询效率 加上分页什么的就更慢了怎么优化比较好啊 先拜谢了 性能优化sum索引
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 | |
+----+-------------+----------+-------+---------------+----------+---------+------+--------+-------+
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+
| 1 | SIMPLE | radacct | index | NULL | UserName | 66 | NULL | 456010 | |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+-------+
常用的解决方案是事先把这个统计做好生产一张表。比如安排一个定时任务每 凌晨 01:00 am 来执行这个统计并且放入统计结果表,然后平时查询时就直接从统计结果表中得到结果即可。
COUNT(*)通常是对主键进行索引扫描