MySQL百万级数据表COUNT(1)全文检索慢查询如何限制查询时长？

问题表现 #

在百万级数据量的MySQL表上执行带全文索引匹配的COUNT语句时，查询耗时随匹配结果量陡增：

• 执行以下语句耗时0.73秒，返回匹配结果54000条

SELECT COUNT(1) FROM `table` WHERE MATCH(tagline, location, country) AGAINST(' +Germany' IN BOOLEAN MODE)

• 执行以下语句耗时长达28秒，返回匹配结果330万条

SELECT COUNT(1) FROM `table` WHERE MATCH(tagline, location, country) AGAINST(' +United +States' IN BOOLEAN MODE)

需求为给计数逻辑设置100万条的结果上限，无需统计超过100万的部分，等价于统计带LIMIT 1000000的匹配查询实际返回的行数，以此压缩查询时长。

可直接落地的实现写法 #

用内层查询先按匹配条件取最多100万条，外层再统计行数即可，语句如下：

SELECT COUNT(1) FROM (
    SELECT 1 FROM `table`
    WHERE MATCH(tagline, location, country) AGAINST(' +United +States' IN BOOLEAN MODE)
    LIMIT 1000000
) AS limited_result;

性能原理与注意事项 #

• 该写法下，内层查询在全文索引扫描过程中攒够100万条符合条件的记录就会直接终止扫描，不会继续遍历剩余的匹配结果，查询耗时会和100万匹配量级的COUNT基本一致，不会出现扫完全部330万条结果才返回的长耗时问题。
• 内层查询固定写SELECT 1而非SELECT *，避免读取不必要的字段数据，进一步减少IO开销。
• 不要给内层查询添加ORDER BY等排序逻辑，否则数据库会拉取所有匹配结果完成排序后再截取前100万条，完全失去限长短时长的效果。
• 返回值逻辑：当实际匹配总数≤100万时，返回结果为真实匹配数；当实际匹配总数>100万时，返回固定值1000000，业务层可以直接对返回100万的场景展示「100万+」即可，无需额外查询真实总数。

内容的提问来源于stack exchange，提问作者Ned Hulton