mysql - 缓慢的 MySQL 查询....只是有时

Question

我有一个在我们的报告系统中使用的查询，有时运行速度超过一秒，而其他时间需要 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);

它基本上是在计算唯一身份访问者，它通过计算今天的访问者，然后减去之前访问过的访问者来实现这一点。如果您知道更好的方法，请告诉我。

我只是不明白为什么有时它可以这么快，而其他时候却需要这么长时间 - 即使在相同的服务器负载下使用完全相同的查询也是如此。

这是对这个查询的解释。如您所见，它正在使用我设置的索引:

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 ;

Answer 1

@OMG Ponies 的回答与我询问您的表定义时的想法很接近。基本上，您在此查询中只需要一个 Visitors 实例。

应计算在该时间段内有一些匹配访问且没有早于该时间段的匹配访问的给定访客:

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;

您可能会受益于 Visitors(candidateid,segmentid,omit) 的索引，因为这些列在您的 WHERE 子句中使用。您还可以尝试对 Visitors(visitorid,candidateid,segmentid,omit) 建立索引。

基本上，如果您可以让查询优化说使用索引，这意味着它正在从索引数据结构中获取所需的所有数据，而根本不必读取表数据!

---

我在索引上尝试了几次上面的查询。我上面建议的索引没有帮助，它仍然想为访问者使用 cand_visitor_omit 索引。但是我通过反转列更改了访问的 visittime_visitorid 索引:

CREATE INDEX visitorid_visittime ON Visits(visitorid, visittime);

这得到了优化计划，告诉我它将使用它作为两个连接到 Visits 的覆盖索引(请参阅右侧额外字段中的“使用索引”):

+----+-------------+---------+------+---------------------------+---------------------+---------+------------------+------+--------------------------------------+
| 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 |
+----+-------------+---------+------+---------------------------+---------------------+---------+------------------+------+--------------------------------------+

以这种方式更改索引还会使 Visitors(visitorid) 上的其他单列索引变得多余，因此您可以删除该索引。