如何用 MySQL 实现点餐系统的评价管理功能：从表结构设计到查询优化的完整实践

1. 需求分析与系统目标（温度=0.6实验设定下的评价管理）

本节聚焦点餐系统中的评价管理功能的核心需求、边界条件及关键目标。在温度设定为0.6的实验条件下，强调可预测性、稳定性以及性能可控性，确保高并发场景下评价数据的正确性与快速查询。

目标要点包括数据一致性、可扩展性与易用性。评价管理需要覆盖用户评价、菜品与订单之间的关系，并支持后续的统计分析和报表展示。

目标与范围

目标覆盖：评价的创建、查询、统计与管理操作，并确保与菜品、订单、用户等维度的联动。系统应提供稳定的写入通道、快速的聚合查询和灵活的筛选能力。

约束要点

约束包括数据一致性、ACID事务、索引设计以及分区策略的选型。同时要考虑对历史评价的归档、以及对新颖场景（如菜品组合、促销评价）的扩展。

2. 数据库表结构设计与实体关系

设计核心实体：用户、餐厅/商家、菜品、订单与评价。这些实体之间通过外键形成清晰的关系，以支持跨表查询与统计。

良好的表结构应具备可维护性与高效查询能力。下面给出核心表的设计思路与示例，包含字段含义、数据类型与约束。

核心实体表设计

用户表用于记录评价的发起者信息，字段要覆盖唯一标识、基本信息与创建时间。

CREATE TABLE users (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  mobile VARCHAR(20) UNIQUE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

餐厅/商家表用于关联菜品与评价来源，便于进行分区和聚合分析。

CREATE TABLE restaurants (
  restaurant_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  city VARCHAR(32),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

菜品表记录可被评价的对象，便于按菜品维度汇总评分。

CREATE TABLE dishes (
  dish_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  restaurant_id BIGINT NOT NULL,
  name VARCHAR(128) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表用于建立评价与交易的线程，便于追踪来源和时效性。

CREATE TABLE orders (
  order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  restaurant_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(32),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

评价表将评价信息集中管理，便于排名、筛选与统计。

CREATE TABLE reviews (
  review_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  dish_id BIGINT NOT NULL,
  order_id BIGINT,
  rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(16) NOT NULL DEFAULT 'active',
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (dish_id) REFERENCES dishes(dish_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 评价表的字段设计与约束

字段设计要覆盖评分、文本评论、时间戳与数据状态等信息，便于各种查询与分组统计。

设计要点包括外键关联的完整性、评分取值范围与逻辑状态。确保评价与菜品、用户、订单之间的联系清晰。

字段定义与外键约束

主要字段包括 review_id、user_id、dish_id、order_id、rating、comment、created_at、status。

外键约束确保数据的一致性与可追溯性。通过 foreign key 约束将评价绑定到具体用户、菜品与订单。

ALTER TABLE reviews
  ADD CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(user_id),
  ADD CONSTRAINT fk_reviews_dish FOREIGN KEY (dish_id) REFERENCES dishes(dish_id),
  ADD CONSTRAINT fk_reviews_order FOREIGN KEY (order_id) REFERENCES orders(order_id);

4. 常用查询场景及实现（示例SQL）

常见场景包括按菜品聚合评分、筛选时间段的评价、按用户维度查看活跃度等。下面给出几组典型查询，便于后续在应用层直接调用。

按菜品获取平均评分与评价数量（最近30天）是最常见的统计场景之一。

SELECT d.dish_id,
       AVG(r.rating) AS avg_rating,
       COUNT(r.review_id) AS review_count
FROM reviews r
JOIN dishes d ON r.dish_id = d.dish_id
WHERE d.restaurant_id = ?
  AND r.created_at >= NOW() - INTERVAL 30 DAY
GROUP BY d.dish_id
ORDER BY avg_rating DESC
LIMIT 20;

按照文本关键词筛选评论（支持全文检索），可结合 FULLTEXT 索引提升搜索效率。

ALTER TABLE reviews ADD FULLTEXT KEY ft_comment (comment);

SELECT r.review_id, r.dish_id, r.comment, r.created_at
FROM reviews AS r
WHERE MATCH(r.comment) AGAINST('+delicious -unpopular' IN BOOLEAN MODE)
ORDER BY r.created_at DESC
LIMIT 50;

查看单个菜品的时间序列评分趋势（滚动窗口），利用 MySQL 8 的窗口函数。

SELECT dish_id, created_at,
       AVG(rating) OVER (PARTITION BY dish_id
                       ORDER BY created_at
                       ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_avg
FROM reviews
WHERE dish_id = ? AND created_at >= NOW() - INTERVAL 90 DAY
ORDER BY created_at ASC;

5. 查询优化策略与索引设计

良好的索引是实现高效评价查询的关键。应围绕常见查询模式建立联合索引，考虑覆盖索引与分区策略来提升性能。

核心要点包括：组合索引、覆盖索引、分区分表与统计信息的维护。尽量使查询尽量少的回表操作，并利用覆盖索引直接返回所需字段。

索引策略要点

为常用筛选与排序的组合建立复合索引，例如 (dish_id, created_at) 和 (restaurant_id, dish_id, created_at)。

-- 示例：对菜品快速聚合的覆盖索引
CREATE INDEX idx_reviews_dish_time ON reviews (dish_id, created_at, rating);

全局统计查询宜结合分区和窗口函数，减小扫描范围与提高缓存命中率。

-- 示例：按日期段分区后，按菜品聚合时的扫描更高效
CREATE INDEX idx_reviews_dish_date ON reviews (dish_id, created_at);

6. 数据一致性、事务与并发控制

在评价创建、修改与统计更新等场景下，必须正确处理并发与事务边界。使用 ACID 保证每条评价记录的原子性、持久性与一致性。

对包含多步写入的操作，使用显式事务来确保原子性。如写入评价并同步更新菜品的统计汇总字段（若存在）时，应放在同一事务中。

START TRANSACTION;
INSERT INTO reviews (user_id, dish_id, order_id, rating, comment, created_at)
VALUES (123, 456, 789, 5, '口味极佳，推荐！', NOW());

-- 可能的统计更新（示例）
UPDATE dishes SET last_review_at = NOW()
WHERE dish_id = 456;

COMMIT;

7. 分区与横向扩展

对于大规模点餐系统，合理的分区策略能显著降低单表的查询成本。常见做法是按时间维度（按月/按季度）对评价表进行分区，并结合实际访问模式选择分区键。

分区可以减少扫描数据量，提升历史数据查询的性能。

-- 典型的按月份分区（示例，具体语法以 MySQL 版本为准）
ALTER TABLE reviews
PARTITION BY RANGE ( YEAR(created_at) * 100 + MONTH(created_at) ) (
  PARTITION p202301 VALUES LESS THAN (202302),
  PARTITION p202302 VALUES LESS THAN (202303),
  PARTITION p202303 VALUES LESS THAN (202304),
  PARTITION p2024... );

8. 报表与实时统计的高效实现

报表层需要稳健的聚合与时序分析能力，窗口函数、聚合以及子查询等组合使用，可以在不影响写入路径的前提下获得实时统计数据。

通过对最近一段时间的滚动汇总，可以实现每日热度、趋势分析等报表指标。

-- 实时每日平均评分趋势（按日聚合）
SELECT DATE(created_at) AS day,
       AVG(rating) AS avg_rating,
       COUNT(*) AS review_count
FROM reviews
GROUP BY day
ORDER BY day DESC
LIMIT 360;

在设计查询时，应尽量避免跨分区的全表聚合，必要时通过中间结果表或视图提升性能。

CREATE VIEW daily_dish_stats AS
SELECT dish_id, DATE(created_at) AS day, AVG(rating) AS avg_rating, COUNT(*) AS cnt
FROM reviews
GROUP BY dish_id, day;