为什么在MATCH...AGAINST中的全文索引比MySQL快4倍以上？

Question

我不会明白的。

我有一张有这些索引的表

PRIMARY     post_id
INDEX       topic_id
FULLTEXT    post_text

表有(仅) 346 000行。我正在尝试执行两个查询。

SELECT post_id 
FROM phpbb_posts 
WHERE topic_id = 144017 
AND post_id != 155352 
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

花了4.05秒

SELECT post_id 
FROM phpbb_posts 
WHERE topic_id=144017 
AND post_id != 155352 
AND post_text LIKE ('%http://rapidshare.com/files/5494794/photo.rar%')

需要0.027秒。

解释显示，唯一的区别是在possible_keys (fulltext包含post_text，LIKE没有)

太奇怪了。

这背后是什么？背景里发生了什么？当不使用索引时，LIKE怎么能这么快?使用索引时，全文怎么会这么慢？

UPDATE1:

实际上，现在大约需要0.5秒，也许表被锁定了，但是，当我打开分析时，显示全文初始化花费了0.2秒。出什么事啦？

我可以使用每秒10倍的LIKE查询我的表，而全文只能查询2倍。

UPDATE2:

惊喜吧！

mysql> SELECT post_id FROM phpbb_posts WHERE post_id != 2 AND topic_id = 6 AND MATCH(post_text) AGAINST ('rapidshare.com');
Empty set (0.04 sec)

所以我想问，这怎么可能？

另外，

SELECT count(*) FROM phpbb_posts WHERE MATCH(post_text) AGAINST ('rapidshare.com')

真的很慢。可以是完整的文本任何破损吗？

UPDATE3:

搞什么鬼?

SELECT forum_id, post_id, topic_id, post_text  FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

需要0.27秒

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

需要超过30秒！这里出什么问题了？

Answer 1

我认为问题可能源于全文索引本身的存在。

每次有涉及全文索引的查询时，MySQL查询优化器都倾向于将查询合并为完整的表扫描。这些年来我都见过这种情况。我还写了一篇关于全文索引中最琐碎的行为的文章。。

你可能需要做两件事：

1. 重构查询，使全文索引不会使MySQL查询优化器陷入混乱状态
2. 添加一个适当支持重构查询的附加索引。

重构查询

这是您最初的查询

SELECT post_id  
FROM phpbb_posts  
WHERE topic_id = 144017  
AND post_id != 155352  
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar') 

您需要像这样重构查询：

SELECT subqueryA.post_id
FROM
(
    SELECT post_id FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) subqueryA
INNER JOIN
(
    SELECT post_id FROM phpbb_posts
    WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
) subqueryB
USING (post_id);

创建一个新的索引

您需要一个索引来支持subqueryA。您已经在topic_id上有了一个索引。您需要按以下方式替换它：

ALTER TABLE phpbb_posts ADD INDEX topic_post_ndx (topic_id,post_id);
ALTER TABLE phpbb_posts DROP INDEX topic_id;

试试看!！

更新2012-03-19 13:08美国东部时间

先试试这个

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A;

如果运行速度较快，并返回少量行，则尝试以下嵌套子查询：

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar');

更新2012-03-19 13:11美国东部时间

比较它的运行时间：

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

有了这个

SELECT count(*) FROM phpbb_posts WHERE 1 = 1;

如果运行时间相同，那么匹配子句将在每一行上执行。正如我前面提到的，使用全文索引往往会使MySQL查询优化器尝试和贡献的任何好处无效。