mysql - 优化 MySQL 查询，耗时将近 20 秒!

Question

我在配备 4GB 内存的 Macbook Pro 2.53ghz 上运行以下查询:

SELECT
    c.id            AS id,
    c.name          AS name,
    c.parent_id     AS parent_id,
    s.domain        AS domain_name,
    s.domain_id     AS domain_id,
    NULL            AS stats
FROM
    stats s
LEFT JOIN stats_id_category sic ON s.id = sic.stats_id
LEFT JOIN categories c ON c.id = sic.category_id
GROUP BY
    c.name

完成大约需要 17 秒。

解释:

alt text http://img7.imageshack.us/img7/1364/picture1va.png

表格:

信息:

Number of rows: 147397
Data size: 20.3MB
Index size: 1.4MB

表:

CREATE TABLE `stats` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `time` int(11) NOT NULL,
    `domain` varchar(40) NOT NULL,
    `ip` varchar(20) NOT NULL,
    `user_agent` varchar(255) NOT NULL,
    `domain_id` int(11) NOT NULL,
    `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `referrer` varchar(400) default NULL,
    KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=147398 DEFAULT CHARSET=utf8

信息二表:

Number of rows: 1285093
Data size: 11MB
Index size: 17.5MB

第二张表:

CREATE TABLE `stats_id_category` (
    `stats_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL,
    KEY `stats_id` (`stats_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

信息第三表:

Number of rows: 161
Data size: 3.9KB
Index size: 8KB

第三张表:

CREATE TABLE `categories` (
    `id` int(11) NOT NULL auto_increment,
    `parent_id` int(11) default NULL,
    `name` varchar(40) NOT NULL,
    `questions_category_id` int(11) NOT NULL default '0',
    `rank` int(2) NOT NULL default '0',
    PRIMARY KEY  (`id`),    
    KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=205 DEFAULT CHARSET=latin1

希望有人能帮助我加快速度。

Answer 1

我在您的查询中看到几个 WTF:

1. 您使用了两个 LEFT OUTER JOIN，但随后您按可能没有匹配项的 c.name 列分组。所以也许你真的不需要外部连接？如果是这种情况，您应该使用内部联接，因为外部联接通常速度较慢。

2. 您正在按 c.name 分组，但这会为您的选择列表中的所有其他列提供不明确的结果。 IE。在按 c.name 分组的每个分组中，这些列中可能有多个值。您很幸运您使用的是 MySQL，因为此查询只会在任何其他 RDBMS 中产生错误。

   这是一个性能问题，因为 GROUP BY 可能导致您在 EXPLAIN 中看到的“using temporary; using filesort”。这是一个臭名昭著的性能 killer ，它可能是此查询花费 17 秒的最大原因。由于不清楚您为什么要使用 GROUP BY(不使用聚合函数，并且违反了单值规则)，看来您需要重新考虑这一点。

   <

3. 您正在按 c.name 分组，它没有 UNIQUE 约束。理论上您可以有多个名称相同的类别，这些类别将集中在一个组中。我想知道如果您希望每个类别一个组，为什么不按 c.id 分组。

4. SELECT NULL AS stats:我不明白您为什么需要这个。这有点像创建一个您从未使用过的变量。它不应该损害性能，但它只是另一个 WTF 让我觉得你没有很好地考虑这个查询。

5. 您在评论中说您要查找每个类别的访问者数量。但是您的查询没有任何聚合函数，例如 SUM() 或 COUNT()。您的选择列表包括 s.domain 和 s.domain_id，每个访问者都不同，对吧？那么，如果每个类别只有一行，您期望结果集中的值是多少？这也不是真正的性能问题，它只是意味着您的查询结果没有告诉您任何有用的信息。

6. 您的 stats_id_category 表在其两列上有一个索引，但没有主键。因此您很容易得到重复的行，这意味着您的访问者数量可能不准确。您需要删除该冗余索引并改用主键。我会在该主键中首先订购 category_id，以便连接可以利用索引。

   ALTER TABLE stats_id_category DROP KEY stats_id, 
     ADD PRIMARY KEY (category_id, stats_id);
   

现在，如果您需要计算的只是访问者数量，您可以删除一个连接:

SELECT c.id, c.name, c.parent_id, COUNT(*) AS num_visitors
FROM categories c
INNER JOIN stats_id_category sic ON (sic.category_id = c.id)
GROUP BY c.id;

现在查询根本不需要读取stats 表，甚至不需要读取stats_id_category 表。它可以通过读取 stats_id_category 表的索引来简单地获取它的计数，这应该会减少很多工作。