mysql 30秒_mysql查询时间超过30秒

以下是my-sql命令行中“show full processlist”的输出。此查询会计算指定时间段内用户的总上传和下载带宽。 (数据库来自freeradius)。我怎样才能使查询更快。 [存储器：150G，processr：8周的centos 6.8]mysql查询时间超过30秒

15260415|radiusremote|panel.example.com:57526|radius|Query|35|Copying to tmp table

SELECT sum(acctinputoctets) as upload,sum(acctoutputoctets) as download

FROM radacct a

INNER JOIN

(SELECT acctuniqueid, MIN(radacctid) radacctid

FROM radacct

WHERE username='nyjohan'

and acctstarttime between '2016-01-15 13:50:05'

AND '2016-08-07 13:16:36'

GROUP BY acctuniqueid

) b ON a.acctuniqueid = b.acctuniqueid

AND a.radacctid = b.radacctid

有在表上创建索引，下面是索引的上表中的输出表的

mysql> show index from radacct;

+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

| radacct | 0 | PRIMARY | 1 | radacctid | A | 161791738 | NULL | NULL | | BTREE | |

| radacct | 1 | username | 1 | username | A | 15 | NULL | NULL | | BTREE | |

| radacct | 1 | framedipaddress | 1 | framedipaddress | A | 458333 | NULL | NULL | | BTREE | |

| radacct | 1 | acctsessionid | 1 | acctsessionid | A | 161791738 | NULL | NULL | | BTREE | |

| radacct | 1 | acctsessiontime | 1 | acctsessiontime | A | 46332 | NULL | NULL | YES | BTREE | |

| radacct | 1 | acctuniqueid | 1 | acctuniqueid | A | 161791738 | NULL | NULL | | BTREE | |

| radacct | 1 | acctstarttime | 1 | acctstarttime | A | 40447934 | NULL | NULL | YES | BTREE | |

| radacct | 1 | acctstoptime | 1 | acctstoptime | A | 80895869 | NULL | NULL | YES | BTREE | |

| radacct | 1 | nasipaddress | 1 | nasipaddress | A | 15 | NULL | NULL | | BTREE | |

+---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

架构

mysql> describe radacct;

+----------------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------------+-------------+------+-----+---------+----------------+

| radacctid | bigint(21) | NO | PRI | NULL | auto_increment |

| acctsessionid | varchar(32) | NO | MUL | | |

| acctuniqueid | varchar(32) | NO | MUL | | |

| username | varchar(64) | NO | MUL | | |

| groupname | varchar(64) | NO | | | |

| realm | varchar(64) | YES | | | |

| nasipaddress | varchar(15) | NO | MUL | | |

| nasportid | varchar(15) | YES | | NULL | |

| nasporttype | varchar(32) | YES | | NULL | |

| acctstarttime | datetime | YES | MUL | NULL | |

| acctstoptime | datetime | YES | MUL | NULL | |

| acctsessiontime | int(12) | YES | MUL | NULL | |

| acctauthentic | varchar(32) | YES | | NULL | |

| connectinfo_start | varchar(50) | YES | | NULL | |

| connectinfo_stop | varchar(50) | YES | | NULL | |

| acctinputoctets | bigint(20) | YES | | NULL | |

| acctoutputoctets | bigint(20) | YES | | NULL | |

| calledstationid | varchar(50) | NO | | | |

| callingstationid | varchar(50) | NO | | | |

| acctterminatecause | varchar(32) | NO | | | |

| servicetype | varchar(32) | YES | | NULL | |

| framedprotocol | varchar(32) | YES | | NULL | |

| framedipaddress | varchar(15) | NO | MUL | | |

| acctstartdelay | int(12) | YES | | NULL | |

| acctstopdelay | int(12) | YES | | NULL | |

| xascendsessionsvrkey | varchar(10) | YES | | NULL | |

+----------------------+-------------+------+-----+---------+----------------+

说明输出： -

explain SELECT sum(acctinputoctets) as upload,sum(acctoutputoctets) as download

FROM radacct a

INNER JOIN (

SELECT acctuniqueid, MIN(radacctid) radacctid

FROM radacct

WHERE username='dave137' and acctstarttime between '2016-08-03 00:00:00' and '2016-08-07 14:47:54' GROUP BY acctuniqueid

)b ON a.acctuniqueid = b.acctuniqueid

AND a.radacctid = b.radacctid ;

+----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | |

| 1 | PRIMARY | a | eq_ref | PRIMARY,acctuniqueid | PRIMARY | 8 | b.radacctid | 1 | Using where |

| 2 | DERIVED | radacct | ref | username,acctstarttime | username | 66 | | 10164 | Using where; Using temporary; Using filesort |

+----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+

3 rows in set (9.91 sec)

+2

无论如何，(acctuniqueid，radacct_id，username，acctstarttime)上的复合索引看起来像是可以完成的最好的 –

+0

你能解释一下你将要实现什么吗？我不明白你为什么要加入__total__下载并上传属于单个用户的信息：'nyjohan'？ –