MySQL 5.7 带GROUP BY的视图查询过慢，如何优化保证查询性能？

你遇到的性能问题确实是WHERE条件无法下推导致的：
MySQL 5.7的视图支持MERGE和TEMPTABLE两种执行算法，当视图定义中包含GROUP BY、聚合函数、DISTINCT这类语法时，MySQL无法使用MERGE算法将外层查询的过滤条件合并到视图内部逻辑中，只能使用TEMPTABLE算法：先全量计算视图的所有聚合结果存入临时表，再在外层对临时表执行过滤。你的两张表都有1亿条记录，全量聚合的开销极高，所以查询速度极慢。

你提到的在视图WHERE子句中使用函数过滤的方案不是优先选择，不仅维护成本高，也没有从本质上解决全量计算的性能问题。

以下方案均支持保留视图的调用形式，性能可达到和原生单条查询一致的水平：

• 方案一：升级到MySQL 8.0（零业务代码改动）
  MySQL 8.0对聚合视图的条件下推做了针对性优化，支持将外层WHERE条件直接下推到带GROUP BY的视图内部执行，不需要修改任何视图定义和查询语句，你原来的查询会被自动改写为等价的高效执行逻辑，性能和直接写原生SQL一致。
• 方案二：自建预聚合物化表（适合MySQL 5.7不变更版本的场景）
  你可以自己维护一张预聚合的物化表，将计算好的每个预约对应的客户数提前存下来：
  1. 创建预聚合表：

     CREATE TABLE client_counts_mv (
         appointment_id INT PRIMARY KEY COMMENT '关联appointments表的id',
         client_count INT NOT NULL DEFAULT 0 COMMENT '对应客户数'
     );
     

  2. 全量初始化历史数据（可在低峰期执行）：

     INSERT INTO client_counts_mv
     SELECT a.id, count(c.id)
     FROM appointments a
     LEFT JOIN clients c ON c.appointment_id = a.id
     GROUP BY a.id;
     

  3. 增量更新逻辑：可以在clients表上创建增删改触发器，每次数据变更时实时更新对应appointment_id的client_count值；如果业务允许一定延迟，也可以用定时任务做小时/天级的增量同步。
  4. 兼容原有视图调用：创建同名视图指向预聚合表，原有业务查询完全不需要修改：

     CREATE OR REPLACE VIEW client_counts AS 
     SELECT appointment_id AS id, client_count 
     FROM client_counts_mv;
     

  之后查询SELECT id, client_count FROM client_counts WHERE id = 499本质是主键查询，速度可以达到微秒级。
• 方案三：存储函数封装查询逻辑（适合仅单id查询的场景）
  如果你的使用场景都是单次查询单个appointment的客户数，也可以用存储函数封装查询逻辑，性能和原生查询一致：

  DELIMITER //
  CREATE FUNCTION get_client_count(p_appointment_id INT) RETURNS INT
  DETERMINISTIC
  BEGIN
      DECLARE v_count INT DEFAULT 0;
      SELECT COUNT(c.id) INTO v_count
      FROM appointments a
      LEFT JOIN clients c ON c.appointment_id = a.id
      WHERE a.id = p_appointment_id
      GROUP BY a.id;
      RETURN v_count;
  END //
  DELIMITER ;
  

  调用方式为SELECT 499 AS id, get_client_count(499) AS client_count;。如果要兼容视图调用形式，可以搭配用户变量使用，但易用性不如前两种方案。

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