MySQL课程管理系统SQL视图单条查询性能过低优化方案咨询

MySQL的普通视图是虚拟表，仅保存查询定义，不会预存计算结果。你视图中包含了基于全表的GROUP BY聚合逻辑，当你外层加WHERE lesson_id = xxx查询单条数据时，MySQL的查询优化器无法将过滤条件下推到聚合子查询内部，会先全量统计所有课时的消费者人数，再过滤你要的单条数据，这就是全表扫描耗时高的根本原因。

直接在课时表新增冗余统计字段，避免每次查询时动态统计：

1. 给courses_classes_lessons表添加consumers_count字段：

ALTER TABLE `courses_classes_lessons` ADD COLUMN `consumers_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课时报名消费者数';

2. 给courses_classes_lessons_consumers表添加触发器，自动同步统计值：

-- 新增消费者关联时增量+1
DELIMITER //
CREATE TRIGGER `after_lesson_consumer_insert` AFTER INSERT ON `courses_classes_lessons_consumers`
FOR EACH ROW BEGIN
    UPDATE `courses_classes_lessons` SET `consumers_count` = `consumers_count` + 1 WHERE `id` = NEW.lesson_id;
END //
DELIMITER ;

-- 删除消费者关联时增量-1
DELIMITER //
CREATE TRIGGER `after_lesson_consumer_delete` AFTER DELETE ON `courses_classes_lessons_consumers`
FOR EACH ROW BEGIN
    UPDATE `courses_classes_lessons` SET `consumers_count` = `consumers_count` - 1 WHERE `id` = OLD.lesson_id;
END //
DELIMITER ;

3. 执行一次全量同步历史数据：

UPDATE `courses_classes_lessons` l
SET l.consumers_count = (
    SELECT COUNT(*) FROM `courses_classes_lessons_consumers` c WHERE c.lesson_id = l.id
);

4. 此时你的查询逻辑可以简化为单表关联，完全不需要聚合，视图可以改成：

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
SELECT
    l.id AS lesson_id,
    c.max_consumers AS class_max_consumers,
    l.consumers_count,
    CASE WHEN l.consumers_count >= c.max_consumers THEN TRUE ELSE FALSE END AS is_full,
    CASE 
        WHEN l.completed_at > NOW() THEN 'completed'
        WHEN l.cancelled_at > NOW() THEN 'cancelled'
        WHEN l.starts_at > NOW() THEN 'upcoming'
        ELSE 'incomplete'
    END AS status,
    c.max_consumers - LEAST(l.consumers_count, c.max_consumers) AS available_spaces
FROM `courses_classes_lessons` l
JOIN `courses_classes` c ON c.id = l.class_id
WHERE l.deleted_at IS NULL;

此时查询单条视图数据时，会直接走主键索引，性能完全可以达到500μs~1ms的要求。

如果业务场景不允许使用触发器，可以放弃使用视图，直接在业务代码中封装你最开始的单条查询逻辑，单条查询时直接按lesson_id过滤统计，不会全表扫描，性能也符合要求。

如果一定要保留现有视图结构，可以做以下优化降低耗时：

• 去掉is_full、available_spaces字段外层不必要的SELECT嵌套，直接用字段计算即可，减少查询优化器的解析负担
• 不要使用嵌套视图，将消费者统计逻辑直接合并到主视图中，避免多层查询展开的性能损耗
• 过滤掉已删除的课时数据，在视图定义中添加WHERE courses_classes_lessons.deleted_at IS NULL减少扫描的数据量

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