MySQL百万级高频大表多文本匹配查询优化及duration求和需求

基础查询语句实现 #

根据需求直接修改为聚合求和语句，分两种匹配逻辑：

匹配任一目标词汇 #

布尔模式下空格默认表示OR，会匹配包含任意一个目标词汇的行：

SELECT SUM(duration) AS total_duration
FROM textsearch t
WHERE MATCH(t.search_text) AGAINST('Word1 Word2 Word3 "combined words"' IN BOOLEAN MODE)
  AND t.timer BETWEEN 'date1' AND 'date2';

匹配全部目标词汇 #

给每个词汇前加+表示必须存在，仅匹配同时包含所有目标词汇的行：

SELECT SUM(duration) AS total_duration
FROM textsearch t
WHERE MATCH(t.search_text) AGAINST('+Word1 +Word2 +Word3 +"combined words"' IN BOOLEAN MODE)
  AND t.timer BETWEEN 'date1' AND 'date2';

优化查询速度的核心方案 #

1. 减少不必要的数据读取 #

原查询用SELECT *会返回所有列，而你仅需duration的求和，直接聚合能大幅降低磁盘IO和数据传输开销，这是最基础的优化。

2. 调整全文检索参数 #

适配你的数据特征调整FTS相关参数，修改后需重建全文索引：

• innodb_ft_min_token_size：如果目标词汇有短于3个字符的，将其设为对应长度（比如2），避免短词被FTS忽略。
• innodb_ft_cache_size：服务器内存充足时，增大到64M或更高，提升检索缓存效率。
• innodb_ft_enable_stopword：若目标词汇包含常见停用词，设为OFF关闭停用词过滤。

3. 优化索引组合与执行计划 #

MySQL无法同时高效利用全文索引和B-tree索引，可根据数据分布调整：

• 给timer单独添加B-tree索引：

  CREATE INDEX idx_textsearch_timer ON textsearch(timer);
  

• 时间范围较小时，先用子查询过滤时间，再做全文检索：

  SELECT SUM(duration) AS total_duration
  FROM (
    SELECT duration, search_text
    FROM textsearch
    WHERE timer BETWEEN 'date1' AND 'date2'
  ) AS filtered_data
  WHERE MATCH(filtered_data.search_text) AGAINST('Word1 Word2 Word3 "combined words"' IN BOOLEAN MODE);
  

• 用EXPLAIN查看执行计划，必要时用FORCE INDEX强制指定最优索引。

4. 分区表优化 #

如果数据按时间自然分布，按timer做范围分区，让查询仅扫描目标日期对应的分区：

ALTER TABLE textsearch PARTITION BY RANGE (TO_DAYS(timer)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  -- 按实际日期添加更多分区
);

分区能大幅减少FTS需要检索的数据量，尤其适合百万级数据的时间范围查询。

高频写入场景的额外优化 #

平衡写入性能与查询效率：

• 调整innodb_ft_update_batch_size：增大到1000可降低FTS索引更新频率，减少写入开销，但会带来轻微检索延迟；需实时检索则减小该值。
• 避免写入时做复杂关键词预处理，除非查询效率优先级远高于写入性能。

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