面试题：MySQL全文索引限制下如何优化模糊查询

Question

假设项目中因MySQL全文索引限制，模糊查询性能不佳，你会从哪些方面提出优化方案，如查询语句改写、数据结构调整、索引策略等，详细说明思路。

Answer 1

查询语句改写

1. 精准匹配优先：在查询时，如果业务允许，尽量先使用精准匹配条件，减少模糊匹配范围。例如，先确定某些固定字段的准确值，再进行模糊查询。如在用户表中，先确定用户的性别字段，再对用户名进行模糊查询：SELECT * FROM users WHERE gender = '男' AND username LIKE '%keyword%';
2. 避免前导通配符：LIKE '%keyword%' 这种查询方式无法利用索引，尽量使用 LIKE 'keyword%'，这样可以利用索引进行前缀匹配。例如查询以“张”开头的用户：SELECT * FROM users WHERE username LIKE '张%';
3. 使用 IN NATURAL LANGUAGE MODE：如果使用 MySQL 的全文索引，使用 MATCH AGAINST 语法，并且使用 IN NATURAL LANGUAGE MODE。例如：SELECT * FROM articles WHERE MATCH(title, content) AGAINST('keyword' IN NATURAL LANGUAGE MODE); 这种方式比 LIKE 更高效，特别是在处理长文本时。

数据结构调整

1. 冗余字段：对于频繁用于模糊查询的字段，可以考虑在表中增加冗余字段。例如，对于文章表中的文章标题和内容进行模糊查询，如果性能不佳，可以在表中增加一个 title_content_combined 字段，将标题和内容拼接在一起，对这个新字段建立全文索引，查询时直接对该字段进行查询。
2. 分表：如果数据量巨大，可以按照一定规则进行分表。例如按照时间（如按月、按年）或者按照数据的某个特征（如用户表按地区）进行分表。在查询时，先确定数据所在的表，再进行模糊查询，减少单次查询的数据量。

索引策略

1. 优化现有索引：检查现有的索引，确保模糊查询的字段上的索引是合适的。例如，对于前缀匹配的模糊查询，确保建立的是前缀索引。如对用户名建立前缀索引：CREATE INDEX idx_username ON users(username(10)); 这里的 10 表示取用户名的前 10 个字符建立索引，具体长度可根据实际情况调整。
2. 使用全文索引：如果尚未使用全文索引，将适合的字段（通常是文本较长的字段）改为全文索引类型。例如对于文章表的标题和内容字段：ALTER TABLE articles ADD FULLTEXT(title, content); 全文索引在处理长文本的模糊查询时性能比普通索引要好很多。
3. 组合索引：如果查询涉及多个条件，其中包含模糊查询条件，可以考虑建立组合索引。例如查询用户时，既要根据地区又要对用户名进行模糊查询，可以建立组合索引：CREATE INDEX idx_region_username ON users(region, username); 注意组合索引的顺序要遵循最左前缀原则。

其他优化思路

1. 缓存：使用缓存技术，如 Redis。将经常查询的模糊查询结果缓存起来，下次查询时先检查缓存中是否有结果，如果有则直接返回，减少数据库的压力。
2. 搜索引擎：引入专门的搜索引擎，如 Elasticsearch。将需要进行模糊查询的数据同步到 Elasticsearch 中，利用其强大的全文搜索功能进行查询，它在处理海量数据的模糊查询时性能通常比 MySQL 更好。