代码之家 › 专栏 › 技术社区 › dangerousdave

带有mysql索引的基本优化

indexing optimization mysql sql

dangerousdave · 技术社区 · 15 年前

我有一个关于基本MySQL数据库优化的问题。我有3个表、文章、标签和标签(这是一个连接表)。

Articles         Taggings             Tags
id               id                   id
name             article_id           name
                 tag_id

我正在检索与指定的标记完全匹配的项目,查询如下

SELECT *, COUNT(*) AS c
FROM articles AS a
JOIN taggings AS tng ON a.id = tng.article_id
JOIN tags AS t ON t.id = tng.tag_id
WHERE t.name IN ("Red","Green")
GROUP BY a.id
HAVING c = 2

这个查询很慢,所以我做了一个解释,得到了以下结果:

alt text http://dl.dropbox.com/u/2306276/EXPLAIN%20results.png

现在,我真的不明白我在这里做什么,但我认为“type:all”不好,所以我认为我应该在taggings表中同时向项目id和标记id添加索引(btree),然后再次运行查询。 alt text http://dl.dropbox.com/u/2306276/EXPLAIN%20results%202.png 嗯,这对我的未受过教育的人来说没什么好处,行数和前一行一样多,而且类型仍然在两个案例中。

有人能告诉我哪里出了问题吗?索引对这个问题没有帮助吗?

我的标记表将保持相对较小的空间,因此我认为查询应该扫描标记表中我指定的标记,然后(通过索引)能够立即检索相关的属性,并且应该非常快,显然我的想法有问题。

谢谢

[编辑]——杰伊的评论

我添加了10万篇文章、30万个标签和6个标签,还添加了2个tag.name和taggings.tag_id上的索引,查询运行时间仍然很长,0.5-1秒,解释如下。 alt text http://dl.dropbox.com/u/2306276/EXPLAIN%20results%203.png

3 回复 | 直到 15 年前

Naktibalda 15 年前

由于tags.name是唯一一个真正减少结果集中行数的列,因此必须对它进行索引,以加快任何基于标记的搜索查询。

更新:尝试运行此查询

SELECT a.*
FROM articles AS a
JOIN taggings AS tng ON a.id = tng.article_id
JOIN tags AS t ON t.id = tng.tag_id
WHERE t.name IN ("Red","Green")
GROUP BY a.id
HAVING COUNT(DISTINCT t.id) = 2

Jay 15 年前

这里发生了一些事情。

首先,你现在的桌子显然很小。当表很小时,DBMS通常发现读取整个内容比使用任何索引更快。要获得有意义的解释结果,需要在表中获得实际的记录数。

看起来您还将“id”字段声明为主键。主键是索引的一个子类,因此这些索引应该是可用的。注意,解释计划指示它使用主键查找标记记录。

这个查询的明显起点是标记。因此,如果这是一个重要的查询,我将创建一个indexon标记(name)。然后就不需要按顺序搜索标记表了。

从那里,它应该按标签ID查找标签,所以您应该有一个索引。

然后它可以按文章ID查找文章。这是主键,因此它应该已经存在了。

所以我认为你会得到最有效的计划,有两个索引:标签(name)和标签(taggings)。

Mark Byers 15 年前

您还可以尝试使用两次联接到表,而不是使用group by。这有时会产生更快的查询:

SELECT a.*
FROM articles AS a
JOIN taggings AS tng1 ON a.id = tng1.article_id
JOIN tags AS t1 ON t1.id = tng1.tag_id AND t1.name = "Red"
JOIN taggings AS tng2 ON a.id = tng2.article_id
JOIN tags AS t2 ON t2.id = tng2.tag_id AND t2.name = "Green"