MySQL：如何加速含通配符的文本查询

针对2500万行数据的模糊查询慢问题，以下是实际可落地的优化建议：

1. 优先使用MySQL全文索引（FULLTEXT） #

对于自然语言类的单词匹配，全文索引比LIKE全表扫描效率高几个数量级，InnoDB和MyISAM都支持该索引类型：

• 创建索引：

  ALTER TABLE your_table ADD FULLTEXT INDEX ft_idx_target_col(target_column);
  

• 查询替换：把LIKE '%pattern1%'替换为全文检索语法，布尔模式支持更灵活的匹配：

  SELECT * FROM your_table WHERE MATCH(target_column) AGAINST('pattern1' IN BOOLEAN MODE);
  

  若需匹配前缀（类似LIKE 'pattern%'），可在布尔模式下用+pattern*；如果是短单词（小于3字符），需要修改MySQL配置ft_min_word_len后重建索引。

2. 针对后缀匹配（LIKE '%pattern2'）的索引技巧 #

MySQL的B树索引只支持前缀匹配，对后缀匹配可以通过反向列+前缀索引绕开限制：

• 添加存储生成的反向列：

  ALTER TABLE your_table ADD COLUMN reversed_target_col VARCHAR(400) AS (REVERSE(target_column)) STORED;
  

• 为反向列创建前缀索引：

  CREATE INDEX idx_reversed_col ON your_table(reversed_target_col(100));
  

• 查询时反转匹配串，将后缀匹配转为前缀匹配：

  SELECT * FROM your_table WHERE reversed_target_col LIKE CONCAT(REVERSE('pattern2'), '%');
  

3. 数据分区减少扫描范围 #

如果表有合适的业务维度（比如时间、地区），可以按该维度分区，让查询只扫描目标分区：

• 示例：按创建日期做RANGE分区

  ALTER TABLE your_table PARTITION BY RANGE (TO_DAYS(create_time)) (
      PARTITION p_2023_q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
      PARTITION p_2023_q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
      PARTITION p_2023_q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
      PARTITION p_2023_q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))
  );
  

  注意：查询时必须带上分区键条件，否则仍会扫描所有分区。

4. 预处理高频查询或关键词 #

• 缓存高频结果：统计用户常用的查询pattern，用Redis等缓存工具存储对应结果，重复查询直接返回缓存，避免重复扫描表。
• 提取关键词到关联表：把原文本中的单词提取出来，存储到单独的关键词关联表，对关键词列建普通索引：

  CREATE TABLE table_keywords (
      id INT AUTO_INCREMENT PRIMARY KEY,
      source_id BIGINT COMMENT '原表主键ID',
      keyword VARCHAR(100) COMMENT '提取的关键词',
      INDEX idx_keyword(keyword),
      INDEX idx_source_id(source_id)
  );
  

  通过定时任务或触发器同步关键词，查询时先查关键词表获取原表ID，再关联原表：

  SELECT t.* FROM your_table t
  JOIN table_keywords k ON t.id = k.source_id
  WHERE k.keyword = 'pattern1';
  

5. 硬件与配置调优 #

• 加大内存缓存：把innodb_buffer_pool_size设置为物理内存的50%-70%（专用数据库服务器），让更多数据缓存到内存，减少磁盘IO。
• 更换SSD存储：SSD的随机读写性能远优于HDD，能大幅缩短全表扫描的耗时。
• 关闭不必要的配置：比如MySQL 8.0已移除查询缓存，5.7及以下版本若开启需注意缓存失效问题，不建议依赖查询缓存解决大表模糊查询问题。

6. 改用专业全文检索引擎 #

如果MySQL的方案满足不了复杂的子串匹配需求，可以将数据同步到Elasticsearch或Solr，这类引擎专为全文检索设计，支持任意位置的子串匹配、分词、多语言等功能，性能远超MySQL的LIKE查询。

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