MySQL:复合索引fulltext+btree？

Question

我想要一个在一个字段上进行全文搜索然后在不同字段上进行排序的查询(想象一下搜索一些文本文档并按发布日期排序)。该表有大约 1700 万行，它们或多或少按日期均匀分布。这将在 webapp 请求/响应周期中使用，因此查询必须在最多 200 毫秒内完成。

示意图:

SELECT * FROM table WHERE MATCH(text) AGAINST('query') ORDER BY date=my_date DESC LIMIT 10;

一种可能性是在文本字段上有一个全文索引，在发布日期上有一个 btree:

ALTER TABLE table ADD FULLTEXT index_name(text);
CREATE INDEX index_name ON table (date);

这对我来说效果不是很好。发生的事情是 MySQL 评估两个执行路径。一种是使用全文索引查找相关行，一旦选择了它们，就使用 FILESORT 对这些行进行排序。第二种是使用 BTREE 索引对整个表进行排序，然后使用 FULL TABLE SCAN 查找匹配项。他们都不好。在我的例子中，MySQL 选择了前者。问题是第一步可以选择大约 30k 个结果，然后必须对这些结果进行排序，这意味着整个查询可能需要 10 秒左右的时间。

于是我在想:FULLTEXT+BTREE的复合索引存在吗？如果您知道 FULLTEXT 索引的工作原理，它会首先标记您正在索引的列，然后为标记构建索引。在我看来，想象一个复合索引似乎是合理的，这样第二个索引是每个标记的日期 BTREE。这在 MySQL 中是否存在？如果存在，语法是什么？

红利问题:如果 MySQL 中不存在，PostgreSQL 在这种情况下会表现得更好吗？

Answer 1

在 bool 模式下使用。

日期索引没有用。无法合并这两个索引。

请注意，如果用户搜索显示在 30K 行中的内容，查询将会很慢。周围没有直接的方法。

我怀疑您在表中有一个 TEXT 列？如果是这样，就有希望了。与其盲目地执行 SELECT *，不如让我们先找到 ID 并应用 LIMIT，然后执行 * .

SELECT a.* 
    FROM tbl AS a
    JOIN ( SELECT date, id
             FROM tbl
             WHERE MATCH(...) AGAINST (...)
             ORDER BY date DESC
             LIMIT 10 ) AS x
        USING(date, id)
    ORDER BY date DESC;

连同

PRIMARY KEY(date, id),
INDEX(id),
FULLTEXT(...)

这个公式和索引应该像这样工作:

1. 使用 FULLTEXT 查找 30K 行，提供 PK。
2. 使用 PK，按 date 对 30K 行进行排序。
3. 选择最后 10 个，提供 date, id
4. 使用 PK 回到 table 上 10 次。
5. 再次排序。 (是的，这是必要的。)

更多(回应过多的评论):

我重新制定的目标是避免获取30K 行的所有 列。相反，它只获取 PRIMARY KEY，然后将其减少到 10，然后只获取 * 10 行。铲起的东西少了很多。

关于 InnoDB 表上的 COUNT:

• INDEX(col) 使得 index 扫描适用于 SELECT COUNT(*) 或 SELECT COUNT(col) 而无需哪里。
• 如果没有 INDEX(col)，SELECT COUNT(*) 将使用“最小”索引；但是SELECT COUNT(col)` 需要一个table 扫描。
• 表扫描通常比索引扫描慢。
• 注意时间——索引和/或表是否已缓存在 RAM 中会受到显着影响。

关于 FULLTEXT 的另一件事是单词前面的 + -- 表示每个单词必须存在，否则没有匹配项。这可能会减少 30K。

FULLTEXT 索引将传递date, id 是随机顺序，不是PK 顺序。无论如何，假定任何顺序都是“错误的”，因此添加 ORDER BY 是“正确的”，然后让优化器在它知道它是多余的情况下扔掉它.有时优化器可以利用 ORDER BY(不是你的情况)。

在许多情况下，仅删除 ORDER BY 会使查询运行得更快。这是因为它避免了获取 30K 行并对它们进行排序。相反，它只是提供“任意”10 行。

(我没有使用 Postgres 的经验，所以我无法回答这个问题。)