MySQL 慢速分组 by date_format

Question

我有一张来自 Daloradius 的 table :

{CREATE TABLE `radacct` (
  `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(15) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctsessiontime` int(12) DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(50) DEFAULT NULL,
  `connectinfo_stop` varchar(50) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `acctstartdelay` int(12) DEFAULT NULL,
  `acctstopdelay` int(12) DEFAULT NULL,
  `xascendsessionsvrkey` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`radacctid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=1797353 DEFAULT CHARSET=latin1;

}

有 1797353 行。

该查询延迟了0.7s:

select count(distinct(right(radacct.username,17))) as Navegant,0,0
            from radacct,userinfo
            where userinfo.company='98' and userinfo.username=radacct.username   

解释:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  userinfo    ref username,company    company 203 const   4811    Using where
1   SIMPLE  radacct ref username    username    66  radius.userinfo.username    7   Using where; Using index   

但是当我添加 date_format(acctstarttime,'%H') 时，需要更多 tan 4s 来运行查询:

select   date_format(acctstarttime,'%H') as AA,count(distinct(right(radacct.username,17))) as Navegant,0,0
            from radacct,userinfo
            where userinfo.company='98' and userinfo.username=radacct.username
            group by AA

解释:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  userinfo    ref username,company    company 203 const   4811    Using where; Using temporary; Using filesort
1   SIMPLE  radacct ref username    username    66  radius.userinfo.username    7   Using where

如何使该查询更快？

Answer 1

我在 Group By 的性能方面遇到了很多问题。在某些情况下，我最终使用了子选择。这篇文章对我非常有帮助:

http://kccoder.com/mysql/join-group-by-performance/

本质上可以归结为:date_format(acctstarttime,'%H') 为 AA

从您的选择语句中取出并放入子选择中，如下所示:

选择 count(distinct(right(radacct.username,17))) 作为 Navegan,0,0 从 radacct,userinfo, 从 some_table 中选择 date_format(acctstarttime,'%H') 作为 AA
其中 userinfo.company='98' 和 userinfo.username=radacct.username

我知道我的语法不正确，但它应该可以让您了解总体思路。

希望这有帮助，布赖恩