MySQL：复合索引全文btree？教程

我想要一个查询,该查询在一个字段上进行全文搜索,然后在另一个字段上进行排序(想象按发布日期搜索一些文本文档和顺序).该表大约有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索引对整个表进行排序,然后使用“全表扫描”查找匹配项.他们俩都不好.在我的情况下,MySQL选择前者.问题在于,第一步可以选择大约30k的结果,然后对其进行排序,这意味着整个查询可能需要10秒左右的时间.

所以我在想：FULLTEXT BTREE的复合索引是否存在？如果您知道FULLTEXT索引的工作方式,它将首先对要索引的列进行标记化,然后为标记建立索引.在我看来,想象一个复合索引使得第二个索引在每个令牌的日期中都是BTREE.这在MySQL中存在吗？如果是,语法是什么？

奖金问题：如果MySQL中不存在它,PostgreSQL在这种情况下会更好吗？

解决方法:

在BOOLEAN MODE中使用.

日期索引没有用.无法将两个索引组合在一起.

当心,如果用户搜索显示在3万行中的内容,查询将很慢.周围没有直接的距离.

我怀疑您的表中有TEXT列吗？如果是这样,就有希望.让我们首先查找ID并应用LIMIT,然后再进行*,而不是盲目地执行SELECT *.

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(...)

此公式化和索引编制应如下所示：

使用FULLTEXT查找30K行,并提供PK.
使用PK,按日期排序30K行.
选择最后10个交货日期ID
使用PK到达桌子10次.
重新排序. (是的,这是必需的.)

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

我重新制定公式的目的是避免获取3万行的所有列.取而代之的是,它仅获取PRIMARY KEY,然后将其缩减至10,然后仅获取* 10行.铲掉的东西更少了.

关于InnoDB表上的COUNT：

INDEX(col)使索引扫描适用于SELECT COUNT(*)或SELECT COUNT(col),而无需使用WHERE.
如果没有INDEX(col),SELECT COUNT(*)将使用“最小”索引；但SELECT COUNT(col)`将需要进行表扫描.
表扫描通常比索引扫描慢.
注意计时-索引和/或表是否已经缓存在RAM中会极大地影响它.

关于FULLTEXT的另一件事是单词的前面-说每个单词必须存在,否则就没有匹配项.这可能会减少30K.

FULLTEXT索引将传递日期,id是随机顺序,而不是PK顺序.无论如何,假定有任何顺序是“错误的”,因此添加ORDER BY是正确的,然后如果优化器知道它是多余的,则让它抛出.有时,优化器可以利用ORDER BY的优势(您的情况并非如此).

在许多情况下,仅删除ORDER BY可使查询运行更快.这是因为它避免了获取3万行并对其进行排序.相反,它仅提供“任何” 10行.

(我没有使用Postgres的经验,因此无法解决该问题.)