sql - 如何为以下查询创建有效索引

Question

我已经将一个 web 服务器 access_log 转换为一个 mysql 表，它看起来像这样:

CREATE TABLE `access_log` (
`timestamp` int(11) NOT NULL default '0',
`visitorid` int(11) default NULL,
`url` int(11) default NULL,
`params` int(11) default NULL,
`status` smallint(3) NOT NULL default '0',
`bytes` int(20) NOT NULL default '0',
`referrer` int(11) default NULL,
`refparams` int(11) default NULL,
`useragentid` int(11) default NULL,
`keywords` int(11) default NULL,
`country` char(3) default '',
`crawl` int(1) NOT NULL default '0',
`sessionid` int(11) default NULL,
KEY `timestamp` (`timestamp`),
KEY `visitorid` (`visitorid`),
KEY `url` (`url`),
KEY `referrer` (`referrer`),
KEY `keywords` (`keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;

我有一个查询生成特定日期范围内的“最受欢迎的页面”报告，示例如下所示:

select url,
count(distinct visitorid) as visitors,
count(*) as hits 
from access_log where 
timestamp >=1270072800 and timestamp <=1272664799 
and crawl=0 
group by url order by visitors desc limit 100;

当表中有很多记录时，这个查询会变得很慢。

根据相对于表中记录总数的时间戳范围，优化器表示它将使用“timestamp”或“url”键。但是，它总是提到“在哪里使用；使用临时的；使用文件排序'

有什么方法可以创建组合索引来缩短此查询的执行时间？

我尝试了以下组合，但优化器似乎忽略了它们:

1. idx(时间戳，url，visitorid，抓取)
2. idx(url,visitorid,crawl,timestamp)

如有任何关于我遗漏的建议或指示，我们将不胜感激。

谢谢!

Answer 1

因此，您想要在给定时间段内按受欢迎程度对 URL 进行排名。 (URL, visitorid) 的综合索引会让你受欢迎。(timestamp,url) 上的综合索引将为您提供期间访问的网址。为什么不尝试两个索引，并针对内联 View 进行连接，就像这样(不确定 mysql 中内联 View 的确切语法):

       select distinct URL from log as Log1
                  where visitdatetime > x and visitdatetime< y


       join

       (select url, count(distinct visitorid) as DistinctVisitors
       from log
       group by url
        -- having count(distinct visitorid) > {some cutoff value greater than 1}
        -- try the composite index (url, visitorid, visitdate)
       having vistdate > x and visitdate < y 
       ) as Log2


       on Log1.url = log2.url

       order by DistinctVisitors desc