需要帮助调优SQL查询

Question

我的mysql数据库因尝试执行一个特别慢的查询而变得耗费CPU。当我执行explain时，mysql会显示“使用where；使用temporary；使用filesort”。请帮忙解释并解决这个难题。

表结构：

CREATE TABLE `topsources` (
  `USER_ID` varchar(255) NOT NULL,
   `UPDATED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `URL_ID` int(11) NOT NULL,
  `SOURCE_SLUG` varchar(100) NOT NULL,
  `FEED_PAGE_URL` varchar(255) NOT NULL,
  `CATEGORY_SLUG` varchar(100) NOT NULL,
  `REFERRER` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`USER_ID`,`URL_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `FEED_PAGE_URL` (`FEED_PAGE_URL`),
  KEY `SOURCE_SLUG` (`SOURCE_SLUG`),
  KEY `CATEGORY_SLUG` (`CATEGORY_SLUG`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

表格有370K行……有时更高。以下查询需要10秒以上。

SELECT topsources.SOURCE_SLUG, COUNT(topsources.SOURCE_SLUG) AS VIEW_COUNT
FROM topsources
WHERE CATEGORY_SLUG = '/newssource'
GROUP BY topsources.SOURCE_SLUG
HAVING MAX(CASE WHEN topsources.USER_ID = 'xxxx' THEN 1 ELSE 0 END) = 0
ORDER BY VIEW_COUNT DESC;

以下是详细说明：

+----+-------------+------------+------+---------------+---------------+---------+-------+--------+----------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows   | filtered | Extra                                        |
+----+-------------+------------+------+---------------+---------------+---------+-------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | topsources | ref  | CATEGORY_SLUG | CATEGORY_SLUG | 302     | const | 160790 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+----

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

有没有办法改进这个查询？此外，有没有任何mysql设置可以帮助减少CPU负载？我可以分配更多的内存，这在我的服务器上是可用的。

- Zero

---

HAVING MAX(CASE WHEN topsources.USER_ID = 'xxxx' THEN 1 ELSE 0 END) = 0 这部分是我认为最棘手的，让我想想如何更好地处理它。 - Jester

它会有所帮助，发布从explain得出的确切输出。 - matt b

有些不对劲：您的“PRIMARY KEY”定义引用了不存在的列：“DATE_AND_HOUR”和“ITEM_ID”。 - Andriy M

我之前犯了一些复制粘贴的错误，现在已经修正过了。问题陈述仍然是一样的。 - Zero

我不是调整查询方面的专家，因此不会冒险回答，但我肯定只要尝试一下（如果您还没有尝试过），用COUNT(CASE WHEN topsources.USER_ID = 'xxxx' THEN 1 END) = 0(或COUNT(CASE topsources.USER_ID WHEN 'xxxx' THEN 1 END) = 0)替换MAX(CASE WHEN topsources.USER_ID = 'xxxx' THEN 1 ELSE 0 END) = 0不会花费太长时间。 - Andriy M

Answer 1

最有可能帮助查询的是在CATEGORY_SLUG上建立索引，特别是如果它有很多值。（也就是说，如果查询是高度选择性的。）查询需要读取整个表才能获取结果——尽管10秒似乎很长。 我认为HAVING子句不会影响查询处理。 如果您连续运行两次查询，查询是否需要同样长的时间？

- Gordon Linoff

---

已经在 CATEGORY_SLUG 上建立了索引。请阅读上面的表结构。查询几乎总是需要更长的运行时间。 - Zero

哦，有多少个类别的slug呢？有时候，索引会带来更多的伤害而不是好处，特别是当只有少数值的时候。 - Gordon Linoff

少于100个类别的slug。 - Zero

尝试使用IGNORE INDEX提示（http://dev.mysql.com/doc/refman/5.1/en/index-hints.html）来忽略索引运行查询。可能是索引的使用导致页面被不高效地读取（随机和多次）。 - Gordon Linoff

使用了 iGNORE INDEX 提示运行查询，但没有帮助。 - Zero

好的，需要扫描每个子集以获取最大值，而一个情况是将其相乘。它不能使用索引来完成这个任务。 - Jester

Answer 2

如果我正确阅读这个 MySQL 更改，那应该就能解决问题了。

SELECT topsources.SOURCE_SLUG, COUNT(topsources.SOURCE_SLUG) AS VIEW_COUNT
FROM topsources
WHERE CATEGORY_SLUG = '/newssource' and 
    topsources.SOURCE_SLUG not in (
        select distinct SOURCE_SLUG 
        from topsources 
        where USER_ID = 'xxxx'
        )
GROUP BY topsources.SOURCE_SLUG
ORDER BY VIEW_COUNT DESC;

- Jester

---

此查询返回不同的结果集。与原始查询不同。 - Zero

请尝试更新查询。 - Jester

Answer 3

如果有很多行与您的CATEGORY_SLUG条件匹配，可能很难使其变快，但这样做会更快吗？

SELECT ts.SOURCE_SLUG, COUNT(ts.SOURCE_SLUG) AS VIEW_COUNT 
FROM topsources ts
WHERE ts.CATEGORY_SLUG = '/newssource' 
  AND NOT EXISTS(SELECT 1 FROM topsources ts2
                 WHERE ts2.CATEGORY_SLUG = '/newssource'
                   AND ts.SOURCE_SLUG = TS2.SOURCE_SLUG
                   AND ts2.USER_ID = 'xxxx')
GROUP BY ts.SOURCE_SLUG 
ORDER BY VIEW_COUNT DESC;

- Set

---

查询返回“where子句中的未知列'TS2.SOURCE_SLUG'”。 - Zero

删除他的这行代码 AND ts.SOURCE_SLUG = TS2.SOURCE_SLUG。 - jay

如果我删除那行代码，那么这个查询将返回空结果集。 - Zero

Answer 4

当你不能直接向数据发送查询时，优化某些东西总是很困难的，但如果我自己做的话，这将是我的第一次尝试：

SELECT t.SOURCE_SLUG, COUNT(t.SOURCE_SLUG) AS VIEW_COUNT
FROM topsources t
LEFT JOIN (
    SELECT SOURCE_SLUG
    FROM topsources t
    WHERE CATEGORY_SLUG = '/newssource'
    AND USER_ID = 'xxx'
    GROUP BY .SOURCE_SLUG
) x USING (SOURCE_SLUG)
WHERE t.CATEGORY_SLUG = '/newssource'
AND x.SOURCE_SLUG IS NULL
GROUP BY t.SOURCE_SLUG
ORDER BY VIEW_COUNT DESC;

- Braiba