我有一个查询,用于我们的报告系统,有时运行速度超过一秒,其他时间需要1到10分钟才能运行.
这是慢查询日志中的条目：
# Query_time: 543 Lock_time: 0 Rows_sent: 0 Rows_examined: 124948974
use statsdb;
SELECT count(distinct Visits.visitorid) as 'uniques'
FROM Visits,Visitors
WHERE Visits.visitorid=Visitors.visitorid
and candidateid in (32)
and visittime>=1275721200 and visittime<=1275807599
and (omit=0 or omit>=1275807599)
AND Visitors.segmentid=9
AND Visits.visitorid NOT IN
(SELECT Visits.visitorid
FROM Visits,Visitors
WHERE Visits.visitorid=Visitors.visitorid
and candidateid in (32)
and visittime<1275721200
and (omit=0 or omit>=1275807599)
AND Visitors.segmentid=9);
它基本上是计算独特的访问者,它通过计算今天的访问者然后减去之前访问过的访问者来做到这一点.如果您知道更好的方法,请告诉我.
我只是不明白为什么有时它会这么快,而其他时间需要这么长时间 – 即使在相同的服务器负载下具有相同的确切查询.
这是关于此查询的EXPLAIN.正如您所看到的那样,它使用我设置的索引：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY Visits range visittime_visitorid,visitorid visittime_visitorid 4 NULL 82500 Using where; Using index
1 PRIMARY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where
2 DEPENDENT SUBQUERY Visits ref visittime_visitorid,visitorid visitorid 8 func 1 Using where
2 DEPENDENT SUBQUERY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where
几个星期前我尝试优化查询,并提出了一直持续约2秒的变体,但实际上它花了更多的时间,因为90％的旧查询返回得更快.每个查询两秒钟太长,因为我们每个页面加载调用查询最多50次,具有不同的时间段.
快速行为可能是由于查询保存在查询缓存中吗？我尝试在我的基准测试之间运行’RESET QUERY CACHE’和’FLUSH TABLES’,我大部分时间仍然得到快速的结果.
注意：昨晚运行查询时出现错误：无法保存结果集.我最初的研究表明,可能是由于需要修复的腐败表.这可能是我所看到的行为的原因吗？
如果您需要服务器信息：
>通过PHP 4.4.4 MysqL 4.1.22访问
>所有表都是InnoDB
>我们每周在所有表格上运行优化表
>查询中使用的两个表的总和为500 MB
MysqL配置：
key_buffer = 350M
max_allowed_packet = 16M
thread_stack = 128K
sort_buffer = 14M
read_buffer = 1M
bulk_insert_buffer_size = 400M
set-variable = max_connections=150
query_cache_limit = 1048576
query_cache_size = 50777216
query_cache_type = 1
tmp_table_size = 203554432
table_cache = 120
thread_cache_size = 4
wait_timeout = 28800
skip-external-locking
innodb_file_per_table
innodb_buffer_pool_size = 3512M
innodb_log_file_size=100M
innodb_log_buffer_size=4M
这是结构,比尔：
CREATE TABLE `Visitors` (
`visitorid` bigint(20) unsigned NOT NULL auto_increment,`ip` int(11) unsigned default '0',`candidateid` int(11) unsigned NOT NULL default '0',`omit` int(11) unsigned NOT NULL default '0',`segmentid` int(10) unsigned NOT NULL default '0',PRIMARY KEY (`visitorid`),KEY `cand_visitor_omit` (`candidateid`,`visitorid`,`omit`),KEY `ip_omit` (`ip`,`omit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2837988 ;
CREATE TABLE `Visits` (
`visitid` bigint(20) unsigned NOT NULL auto_increment,`visitorid` bigint(20) unsigned NOT NULL default '0',`visittime` int(11) unsigned NOT NULL default '0',`converted` tinyint(4) NOT NULL default '0',`superconverted` tinyint(4) NOT NULL default '0',`clickedotheroffer` tinyint(4) NOT NULL default '0',PRIMARY KEY (`visitid`),KEY `visittime_visitorid` (`visittime`,`visitorid`),KEY `visitorid` (`visitorid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3912081 ;
最佳答案
@OMG Ponies的答案与我在询问您的表格定义时的想法非常接近.基本上,在此查询中只需要一个访问者实例.
原文链接：https://www.f2er.com/mysql/433337.html如果某个访问者在该时间段内有一些匹配的访问次数,并且没有与该时间段之前的匹配访问次数,则应计算在
SELECT COUNT(DISTINCT v.visitorid) AS unique_visitor_count
FROM Visitors v
JOIN Visits current ON v.visitorid = current.visitorid
AND current.visittime BETWEEN 1275721200 AND 1275807599
LEFT JOIN Visits earlier ON v.visitorid = earlier.visitorid
AND earlier.visittime < 1275721200
WHERE v.candidateid IN (32)
AND v.segmentid = 9
AND v.omit NOT BETWEEN 1 AND 1275807598
AND earlier.visitorid IS NULL;
您可能会受益于访问者的索引(candidateid,segmentid,omit),因为这些列在WHERE子句中使用.您还可以尝试访问者的访问者(visitorid,candidateid,omit).
基本上,如果您可以使用索引来进行查询优化,则意味着它从索引数据结构中获取所需的所有数据,并且根本不必读取表数据！
我尝试了几次索引,尝试了上面的查询.我上面建议的索引没有帮助,它仍然希望使用访客的cand_visitor_omit索引.但我通过反转列更改了访问时的visittime_visitorid索引：
CREATE INDEX visitorid_visittime ON Visits(visitorid,visittime);
这得到了优化计划,告诉我它将使用它作为两个访问连接的覆盖索引(请参阅右侧额外字段中的“使用索引”)：
+----+-------------+---------+------+---------------------------+---------------------+---------+------------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------------------+---------------------+---------+------------------+------+--------------------------------------+
| 1 | SIMPLE | v | ref | PRIMARY,cand_visitor_omit | cand_visitor_omit | 4 | const | 1 | Using where |
| 1 | SIMPLE | current | ref | visitorid_visittime | visitorid_visittime | 8 | test.v.visitorid | 2 | Using where; Using index |
| 1 | SIMPLE | earlier | ref | visitorid_visittime | visitorid_visittime | 8 | test.v.visitorid | 2 | Using where; Using index; Not exists |
+----+-------------+---------+------+---------------------------+---------------------+---------+------------------+------+--------------------------------------+
以这种方式更改索引也会使访问者(visitorid)上的其他单列索引变得多余,因此您可以删除该索引.