面试题：在复杂业务场景下MySQL自然语言与布尔全文索引的选择与调优

Question

假设存在一个电商平台的商品搜索业务场景，既要支持模糊匹配商品名称，又要能根据商品属性（如品牌、价格区间等）进行精确筛选，且数据量庞大。请详细说明在这种场景下，如何选择使用MySQL的自然语言全文索引和布尔全文索引，并阐述后续如何进行针对性的调优以满足业务高并发、高性能的需求。

Answer 1

1. 索引选择

• 模糊匹配商品名称：
  • 自然语言全文索引：适合此场景。自然语言全文索引会对文本进行分词处理，构建倒排索引。在电商平台中，用户搜索商品名称时，自然语言全文索引能够较好地处理模糊匹配，例如用户输入“手机”，它可以找到包含“手机”这个词的商品名称记录。MySQL会根据词的相关性对结果进行排序，相关性高的商品排在前面，这符合用户模糊搜索时希望先看到更匹配商品的需求。
  • 布尔全文索引：布尔全文索引虽然也能进行文本搜索，但它更侧重于布尔逻辑操作（如与、或、非），对于单纯的模糊匹配商品名称，自然语言全文索引的效果更好，因为布尔全文索引不会像自然语言全文索引那样根据词的相关性排序。
• 根据商品属性（如品牌、价格区间等）精确筛选：
  • 布尔全文索引：适合此类精确筛选场景。例如对于品牌筛选，若要搜索特定品牌的商品，可使用布尔全文索引的“与”操作，指定品牌名称为必须包含的词，这样能快速筛选出符合条件的商品。对于价格区间，虽然全文索引不是最适合处理数值范围筛选的方式，但在结合其他技术手段时，布尔全文索引可以和其他条件一起作为复合查询的一部分。例如，先通过布尔全文索引筛选出品牌符合的商品，再结合MySQL的其他条件筛选价格区间。
  • 自然语言全文索引：自然语言全文索引不太适合精确筛选，它主要是基于词的相关性进行搜索和排序，对于精确匹配的效率不如布尔全文索引。

2. 针对性调优

• MySQL配置优化：
  • 增加缓冲池大小：innodb_buffer_pool_size参数至关重要，对于数据量庞大的电商场景，增大此参数可以让更多的数据和索引缓存在内存中，减少磁盘I/O。例如，根据服务器内存情况，将其设置为物理内存的60% - 80% 。
  • 调整线程参数：适当增加max_connections参数值以应对高并发，但不宜设置过大，否则会消耗过多系统资源。同时，优化innodb_thread_concurrency参数，控制InnoDB存储引擎的并发线程数，避免过多线程竞争资源导致性能下降。
• 索引优化：
  • 复合索引：针对常见的查询组合，创建复合索引。例如，如果经常按照品牌和价格区间筛选商品，可创建包含品牌和价格字段的复合索引。注意索引字段顺序，将选择性高的字段放在前面，以提高索引的使用效率。
  • 定期维护索引：定期使用ANALYZE TABLE语句更新索引统计信息，使MySQL查询优化器能够生成更优的执行计划。同时，使用OPTIMIZE TABLE语句整理表碎片，提高索引的性能。
• 查询优化：
  • 避免全表扫描：编写查询语句时，确保条件能够使用到索引。例如，对于价格区间查询，使用BETWEEN而不是>和<的组合，以确保索引能被有效利用。同时，避免在索引字段上使用函数操作，如UPPER(brand)，这会导致索引失效，从而引发全表扫描。
  • 分页优化：对于分页查询，当数据量很大时，传统的LIMIT offset, limit方式会随着offset增大性能急剧下降。可采用书签分页，即记录上次查询的最后一条数据的某个唯一标识（如主键），下次查询时通过该标识定位到上次结束的位置继续查询，这样能大大减少查询的数据量。
• 缓存机制：
  • 查询结果缓存：启用MySQL的查询结果缓存（query_cache_type设置为1），对于频繁查询且数据变化不频繁的场景，查询结果缓存可以直接返回缓存中的数据，减少数据库的负载。但要注意，当数据发生变化时，缓存会自动失效。
  • 应用层缓存：在应用层（如使用Redis）设置缓存。对于商品搜索结果，将热门搜索词及其结果缓存起来，当用户再次搜索相同内容时，直接从缓存中获取数据，大大减轻数据库压力，提高响应速度。同时，设置合理的缓存过期时间，以保证数据的实时性。