基于MySQL视图结果创建表提升查询速度的优化方案咨询

方案1：优化现有方案2，解决死锁与性能问题 #

原方案2的死锁问题核心是全量INSERT INTO SELECT操作会同时锁视图依赖的多个业务表、目标实体表，冲突概率高，做如下调整即可落地：

• 给所有视图依赖的业务表加last_updated时间戳字段（更新数据时自动维护），视图定义中透出该字段
• 同步逻辑从全量写入改为增量upsert，每次仅同步last_updated大于上次同步时间的记录，使用INSERT INTO 目标表 SELECT * FROM 视图 WHERE last_updated > 上次同步时间 ON DUPLICATE KEY UPDATE语句完成数据写入，避免全表扫描与全表锁
• 同步任务不用走应用层，直接用MySQL自带的EVENT SCHEDULER在数据库层执行，完全不占用API服务器性能，同时可配置任务运行在业务低峰时段，加LOW_PRIORITY关键字降低锁优先级，避免影响线上业务

方案2：新增缓存层承载热点查询 #

如果列表接口可接受秒级数据延迟，该方案改造成本最低：

• 部署Redis实例，将6个视图的查询结果全量缓存，缓存过期时间设置为30~60秒，和你之前的同步频率一致
• 接口请求优先读缓存，缓存失效时才查询一次MySQL视图回写缓存
• 仅1000条/表的总数据量，缓存占用内存不足10M，同时可降低99%以上的视图查询请求，完全解决RDS1的查询压力问题

方案3：直接优化视图本身查询性能 #

多数视图查询慢的核心原因是依赖的业务表索引缺失，无需改动上层逻辑即可大幅提升速度：

• 取出慢查询日志中视图对应的实际执行SQL，执行EXPLAIN分析执行计划
• 针对视图中JOIN关联字段、GROUP BY维度字段、SUM/COUNT统计的筛选字段创建联合索引，通常优化后查询速度可提升5~10倍，足够支撑现有业务量级

方案4：用物化视图自动维护计算结果 #

不想自己写同步逻辑的场景可采用该方案：

• 使用开源工具Flexviews维护MySQL物化视图，工具会自动监听视图依赖的业务表的增量变更，实时计算更新物化视图的实体表结果
• 不需要修改原有视图定义，仅需将接口查询的对象从虚拟视图改为物化视图对应的实体表即可，无全量同步的性能消耗，也不会出现死锁问题

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