SQL中如何避免物化视图刷新阻塞业务查询-SQL-PHP中文网

加concurrently是唯一避免读锁的方案，但必须满足三个硬条件：物化视图上显式创建unique/primary key索引、索引列全为not null（null需coalesce处理）、索引覆盖全部group by列，缺一则报错拒绝执行。

sql中如何避免物化视图刷新阻塞业务查询

PostgreSQL 用 CONCURRENTLY 刷新必须先建唯一索引

不加 CONCURRENTLY 的 REFRESH MATERIALIZED VIEW 会拿 ACCESS EXCLUSIVE 锁，所有 SELECT 直接卡住，状态停在 wait_event_type = 'Lock'。加了 CONCURRENTLY 才能绕过读锁，但不是“加了就生效”——它硬性要求物化视图上存在显式创建的 UNIQUE 或 PRIMARY KEY 索引。

缺任意一条，执行直接报错：ERROR: cannot refresh materialized view "xxx" concurrently, because it does not have a unique index。这不是警告，是拒绝执行。

索引必须建在物化视图上（基表主键不会自动继承）
索引列全部为 NOT NULL；若源字段允许 NULL，得用 COALESCE(col, 0) 包裹后建索引
若物化视图定义含 GROUP BY a, b，索引必须覆盖全部分组列，只建 (a) 会导致比对逻辑错乱

Oracle 避免 Library Cache Lock 要关 atomic_refresh

Oracle 默认 atomic_refresh => TRUE，刷新时走 TRUNCATE + INSERT，TRUNCATE 是 DDL 操作，强制拿独占 library cache lock，所有查该物化视图、甚至查其基表的会话都会卡在 event = 'library cache lock'。

改成 atomic_refresh => FALSE 后，改走 DELETE + INSERT，锁粒度降为行级（ROW EXCLUSIVE），业务查询和普通 DML 基本不受影响。

前提：物化视图真支持 FAST 刷新，且已建好对应日志（MLOG$_xxx）
验证方式：SELECT mview_name, fast_refreshable FROM user_mviews WHERE mview_name = 'MV_SALES'，返回值必须是 'FAST'
若实际不满足 FAST 条件，此参数会静默退化为 COMPLETE 刷新，反而更慢更锁

MySQL 没原生物化视图，靠汇总表 + ON DUPLICATE KEY 控制刷新

MySQL 直到 8.0.23 才实验性支持 CREATE MATERIALIZED VIEW，不支持自动刷新、无增量更新、无法走索引优化——生产环境基本不用。实操中统一用普通表模拟：

建一张 summary_orders_by_day 表，主键设为分组键（如 PRIMARY KEY (order_date)），再用 INSERT ... ON DUPLICATE KEY UPDATE 实现幂等写入。

定时任务（如 crontab）只刷当天/昨日数据，避免全量扫表
若实时性要求高（秒级），改用 Redis Hash 存 date → {count: x, sum: y}，DB 只负责写原始数据
注意：汇总表结构变更时需同步改基表关联逻辑，建议用 ALTER TABLE ... COMMENT='MV_BASE:orders' 标记依赖

并发刷新仍可能失败的三个隐性坑

CONCURRENTLY 不阻塞 SELECT，但不是零代价。它底层是先全量跑一遍原始查询生成新结果集，再靠唯一索引逐行比对旧数据做 INSERT/UPDATE/DELETE，过程中有三类失败很常见：

ERROR: duplicate key value violates unique constraint：刷新期间业务写入了和即将插入的新行相同唯一键的记录
could not lock updated tuple in materialized view：最后 merge 阶段某行刚被业务 UPDATE 过，无法加锁
每次刷新多占一倍磁盘空间存临时副本，高频刷新（如每分钟）容易撑爆 pg_wal 或 base 目录

这些都不是配置错了，而是业务写入与刷新动作在时间窗口上发生了真实冲突。不能只盯着“有没有锁”，得盯住“谁在同时改同一行”。