将MATCH...AGAINST部分移动到查询的不同部分(例如，在评估联接之前)会提高性能吗？

Question

基本上，我有以下查询：

select `t`.*
from `tracking` `t` 
where exists 
(   
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`) against('+bulgaria +turkey' in boolean mode) 
    limit 1
) and ... limit 5

我遇到的问题？

FULLTEXT列有c.car_text索引，但是查询运行得有点慢(特别是对于很少发生的记录，例如搜索不存在的关键字)。

我还没有发表EXPLAIN的结果，因为我想知道我的问题背后的理论，例如针对不同的可能案例。

我将MySQL 5.6与InnoDB存储引擎结合使用，同样值得一提的是，cars表有大约160米的记录。

Answer 1

我很高兴你来这里。我一直建议使用全文索引先收集键，然后将其连接到其他表。

• Oct 25, 2011：布尔模式中忽略的带有“字数”条件的全文索引
• Jul 18, 2012：为什么全文搜索返回的行数少于以下
• May 07, 2012：MySQL解释没有显示全文的“使用索引”
• Jan 29, 2012：Mysql全文搜索my.cnf优化
• Aug 01, 2016：如何快速地按顺序进行全文搜索？

在您的特定情况下，我建议将查询重写为一个全面连接：

SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

或者也许

SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id  LIMIT 5;

如果这些查询给了您一个想法，如果您仍然想要使用WHERE EXISTS，请合并它。

请对这些查询运行解释计划，并查看不同之处

EXPLAIN select `t`.*
from `tracking` `t` 
where exists 
(   
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`)
    against('+bulgaria +turkey' in boolean mode) 
    limit 1
) and ... limit 5;

EXPLAIN SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id LIMIT 5;

EXPLAIN SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

试一试，让我们知道发生了什么！

Answer 2

这个问题相当令人困惑。你想要一个tracking_item，然后去取5条轨迹？您也不关心哪个tracking_item (没有ORDER BY)。显然你不在乎哪5次追踪。

我建议使用JOIN而不是EXISTS。目标是让MATCH成为发生的第一件事。

SELECT t.*
    FROM (
        SELECT ti.tracking_id
            FROM cars AS c
            JOIN tracking_item AS ti  ON ti.tracking_object_id = c.car_id
            WHERE MATCH(c.car_text) AGAINST('...' IN BOOLEAN MODE)
            ORDER BY ???
            LIMIT 1
         ) AS x
    JOIN tracking AS t  ON t.id = ti.tracking_id
    ORDER BY ???
    LIMIT 5

现在，它按照相同的顺序阅读，优化器将(我希望)决定做一些事情。

1. 对汽车进行全文查询，只交付少数行(如果有的话)；
2. 选择第一个匹配(不包括ORDER BY)，或者先获取所有匹配、排序和传递。
3. 加入tracking_item获得tracking_id(s)
4. 选择一个tracking_id
5. 获取5行(如果没有ORDER BY)或tracking_id上匹配的所有行，并对它们进行排序。
6. 交付最多5行的所有列。

所需索引：

tracking_item:  INDEX(tracking_object_id, tracking_id)  -- in this order
tracking:       INDEX(id)  -- (not needed if it is already the PRIMARY KEY)

至于标题中的问题--太宽泛了，不能回答是或否。可能会有很多不同的情有可原的情况。