MySQL物化视图：预计算查询结果的定期刷新

-- 创建结果存储表（核心物化载体）
CREATE TABLE order_summary_mv (
    product_id INT PRIMARY KEY,
    total_sales DECIMAL(12,2),
    avg_rating FLOAT,
    last_refresh TIMESTAMP
) ENGINE=InnoDB;

-- 初始化预计算数据
INSERT INTO order_summary_mv 
SELECT 
    p.id AS product_id,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    AVG(r.rating) AS avg_rating,
    NOW()
FROM products p
JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id;

DELIMITER $$
CREATE PROCEDURE refresh_order_mv()
BEGIN
    -- 只刷新最近1小时变更的商品
    INSERT INTO order_summary_mv (product_id, total_sales, ...)
    SELECT ... 
    FROM products p
    WHERE p.last_updated > (
        SELECT MAX(last_refresh) FROM order_summary_mv
    )
    ON DUPLICATE KEY UPDATE 
        total_sales = VALUES(total_sales),
        avg_rating = VALUES(avg_rating);
END$$
DELIMITER ;

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- 每天凌晨2点全量刷新
CREATE EVENT event_refresh_mv
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
DO CALL refresh_order_mv();

-- 使用MaxWell捕获binlog
CREATE TABLE inventory_mv (
    warehouse_id INT,
    sku VARCHAR(20),
    stock INT,
    PRIMARY KEY(warehouse_id, sku)
);

-- 通过Kafka消费binlog事件
INSERT INTO inventory_mv 
VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE stock = VALUES(stock);

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
slave_preserve_commit_order=1

-- 原复杂查询
SELECT user_id, COUNT(DISTINCT device_id), AVG(amount) 
FROM transactions t 
JOIN devices d ON t.user_id = d.owner_id
WHERE t.create_time > NOW() - INTERVAL 1 HOUR
GROUP BY user_id;

-- 创建物化视图
CREATE TABLE risk_indicator_mv (
    user_id INT PRIMARY KEY,
    device_count INT,
    avg_amount DECIMAL(10,2),
    update_time TIMESTAMP
);

-- 优化后查询
SELECT * FROM risk_indicator_mv 
WHERE update_time > NOW() - INTERVAL 5 MINUTE;

# Kubernetes CronJob配置
apiVersion: batch/v1
kind: CronJob
spec:
  schedule: "*/15 * * * *" # 基础定时刷新
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: refresh-mv
            image: mysql-client
            command: 
              - "/bin/sh"
              - "-c"
              - "if [ $(loadavg | awk '{print $1}') < 1.0 ]; then 
                   mysql -e 'CALL refresh_mv()'; 
                 else 
                   echo 'Delayed by high load'; 
                 fi"

┌──────────────┬───────────────────────┬──────────────────┐
│ 评估维度     │ 适用场景              │ 风险规避         │
├──────────────┼───────────────────────┼──────────────────┤
│ 数据实时性   │ 容忍分钟级延迟        │ 设置TTL强制刷新  │
│ 存储成本     │ 存储空间 > 计算资源   │ 采用列式压缩     │
│ 维护复杂度   │ 基表结构稳定          │ 建立变更监听机制 │
└──────────────┴───────────────────────┴──────────────────┘