MySQL 实现点餐系统的菜品搜索功能：索引设计与查询优化

一、索引设计的目标与要点

可搜索字段与分词策略

在点餐系统的菜品检索场景中，快速定位和<强>相关性排序是核心指标。需要覆盖菜品名称、描述、配料等文本字段，以及类别、价格区间和是否在售等数值字段。通过构建全文检索 + 精确过滤的混合方案，可以在大规模数据下保持<强>低延迟。

为了支持多门店的检索，restaurant_id常作为第一维过滤条件，后续再做文本与数值条件的组合查询。另一个关键点是对文本字段的分词粒度与语言特性进行权衡，例如中文分词对结果的影响需要在索引策略中体现。

在设计阶段，生成列与全文索引的搭配可以确保应用层查询语义的同时，提升文本匹配的吞吐量与可扩展性。若数据规模继续扩大，后续还可以考虑分区与分表策略以降低单表的搜索成本。

数据建模与多租户过滤

菜品表通常包含字段如name、description、ingredients、category、price、rating、is_available 与时间元数据。restaurant_id列用于区分不同门店的数据集合，确保每次搜索只在当前门店上下文内进行。

为提升查询的可控性，可以将文本字段组合成一个生成列，或者单独建立全文索引，以便在查询时通过 MATCH AGAINST 取得初步的相关性分数。实现时需要考量存储成本、更新频率与查询场景间的权衡。

二、MySQL中的索引设计方案

全文检索与生成列的结合

通过在菜品表中引入生成列与全文索引，可以实现对name、description、ingredients的高效文本匹配。生成列用于将文本信息聚合到一个可供索引的字段上，全文索引则提供高效的文本检索能力。

下面给出在 MySQL 中常见的实现方式，包含创建表、生成列以及全文索引的示例。注意确保你的 MySQL 版本支持生成列和全文索引（InnoDB，MySQL 5.6+，更推荐 8.0+）。

-- DDL: 使用生成列与全文索引实现文本检索
CREATE TABLE dishes (
  id BIGINT NOT NULL AUTO_INCREMENT,
  restaurant_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  ingredients TEXT,
  category VARCHAR(100),
  price DECIMAL(10,2) NOT NULL,
  rating DECIMAL(3,2) DEFAULT 0,
  is_available BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- 生成列聚合文本信息，便于全文检索
  search_text VARCHAR(1024) AS (CONCAT_WS(' ', COALESCE(name,''), COALESCE(description,''), COALESCE(ingredients,''))) VIRTUAL,
  PRIMARY KEY (id),
  -- 基于生成列的全文索引
  FULLTEXT KEY ft_search (search_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

若不使用生成列，也可以直接在如 name、description、ingredients 上建立联合全文索引，如下所示：

ALTER TABLE dishes ADD FULLTEXT INDEX ft_basic (name, description, ingredients);

在实际场景中，全文索引经常与其他过滤条件（如 restaurant_id、category、price 区间）一起使用，以实现“文本匹配 + 结构化过滤”的混合查询。

组合索引与覆盖索引策略

除了全文检索，组合索引可以显著提升常见的精确过滤查询效率。设计时优先考虑将高基数的过滤字段放在前部，并让经常返回的列尽量出现在索引键中，从而实现“覆盖查询”。在 InnoDB 中，二级索引会包含主键作为隐式列，因此把主键列与其他过滤列组合在一起，可以实现更好的覆盖效果。

-- 组合索引示例：覆盖查询常见字段
ALTER TABLE dishes ADD INDEX idx_restaurant_name_price (restaurant_id, name, price);
ALTER TABLE dishes ADD INDEX idx_restaurant_category_price (restaurant_id, category, price);

使用这类索引后，你的查询可以有更高的命中率，且在某些情况下无需再回访主表就能获得需要的字段（若查询只涉及索引中的字段）。同时，索引的选择性也决定了是否需要引入全文索引作为文本检索的主手段。

三、查询优化实践与示例

基础查询示例

在文本检索与结构化过滤并存的场景中，典型的查询会结合MATCH ... AGAINST与其他过滤条件，并通过<强>LIMIT控制返回规模。可以按门店分区后再做排序，以确保高并发下的响应时间稳定。

-- 使用全文检索与排序的基础示例
SELECT id, name, price, rating
FROM dishes
WHERE restaurant_id = 42
  AND MATCH(search_text) AGAINST ('辣味' IN NATURAL LANGUAGE MODE)
ORDER BY rating DESC, price ASC
LIMIT 20;

如果希望根据文本相关性得到明确的评分，可以在 SELECT 结果中输出分数，并以分数为主排序依据。

-- 输出相关性分数的示例
SELECT id, name, price, rating,
       MATCH(search_text) AGAINST ('辣味' IN NATURAL LANGUAGE MODE) AS score
FROM dishes
WHERE restaurant_id = 42
  AND MATCH(search_text) AGAINST ('辣味' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC, price ASC
LIMIT 20;

复杂过滤与排序策略

对于需要同时满足文本匹配和数值/分类过滤的查询，可以组合使用全文检索、范围过滤与有序分页。常见做法是在 WHERE 子句中同时包含 MATCH AGAINST 条件与其他筛选条件，并将排序分解为“相关性分数优先”与“二级字段排序”的两阶段处理。

-- 结合文本与分类、价格过滤的查询
SELECT id, name, price, rating,
       MATCH(search_text) AGAINST ('香辣味' IN NATURAL LANGUAGE MODE) AS score
FROM dishes
WHERE restaurant_id = 42
  AND MATCH(search_text) AGAINST ('香辣味' IN NATURAL LANGUAGE MODE)
  AND category = '主菜'
  AND price BETWEEN 20 AND 50
ORDER BY score DESC, price ASC
LIMIT 20;

通过 EXPLAIN（JSON 格式）分析查询计划，可以观察到是否使用了正确的索引，以及是否存在回表操作或全表扫描。以下示例展示如何对上述查询进行计划分析。

EXPLAIN FORMAT=JSON
SELECT id, name, price, rating,
       MATCH(search_text) AGAINST ('辣味' IN NATURAL LANGUAGE MODE) AS score
FROM dishes
WHERE restaurant_id = 42
  AND MATCH(search_text) AGAINST ('辣味' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC, price ASC
LIMIT 20;

四、监控与性能调优要点

使用 Explain 与索引诊断

日常监控中，EXPLAIN 与 慢查询日志 是最直接的诊断工具。通过分析输出的type、key、rows等字段，可以判断是否对查询使用了符合预期的索引，以及是否存在回表的情况。

常见优化点包括：确保文本检索不会导致大量回表、尽量使用覆盖索引、减少返回字段、对高基数字段建立前缀索引，以及在高并发场景下使用查询缓存或外围缓存层。

-- 启用慢查询日志（示例，具体执行需视运行环境而定）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 1 秒以上的查询记为慢查询

-- 使用 EXPLAIN FORMAT=JSON 分析查询计划
EXPLAIN FORMAT=JSON
SELECT id, name, price
FROM dishes
WHERE restaurant_id = 42
  AND MATCH(search_text) AGAINST ('辣味' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

缓存与水平扩展的考虑

对热度较高的文本搜索结果，可以将常用的查询结果缓存到 Redis、本地缓存 或应用层缓存中，减少对 MySQL 的重复计算。缓存的粒度通常是 门店 + 关键词 的组合，以及相应的排序和分页信息。

对于极端规模的场景，分区表、分表策略 或跨数据库分布式方案可以进一步降低单表负载，同时结合分布式缓存和读写分离提高并发处理能力。

五、数据维护与版本迁移注意事项

索引变更与回滚

在上线新索引或调整现有索引时，建议先在测试环境验证性能提升点，确保 在线 DDL 能够满足业务无缝切换的需求。对于大表，考虑使用 pt-online-schema-change 等工具进行无锁变更，避免对在线点餐服务造成影响。

-- 在线添加新索引的简化示例（具体实现依赖 MySQL 版本与工具）
ALTER TABLE dishes ADD INDEX idx_restaurant_name_price (restaurant_id, name, price);

变更后，务必执行回滚计划，包括备份、测试回滚、以及对现有监控指标的对比，确保发现问题可快速恢复。

在线 DDL 与回滚策略

现代版本的 MySQL 提供了更好的在线 DDL 支持，结合应用层无缝切换，可以在不中断服务的情况下完成结构变更。与此同时，制定清晰的回滚策略，如 - 保留旧索引的同时上线新索引、在变更失败时快速撤销等，是保障稳定性的重要步骤。