Beyond SELECT: Creating, Optimizing, and Securing MySQL Views and Materialized Views

View Update Limits and Solutions

Basic Concept and Update Restrictions

A view is a virtual table that does not store data but derives its result from one or more base tables.

Virtual table : no physical storage.

Real‑time computation : results generated on each query.

Simplify complex queries : encapsulate SQL logic.

Data security : hide sensitive columns.

Example of creating a view:

-- 创建视图示例
CREATE VIEW employee_department_view AS
SELECT e.emp_id, e.emp_name, e.salary, d.dept_name, d.location
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Non‑updatable view scenarios (aggregation, DISTINCT, UNION, subqueries):

-- 不可更新的视图场景示例
CREATE VIEW employee_agg_view AS
SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id; -- contains aggregation, cannot update directly

INSTEAD OF Triggers

-- 为可更新视图创建INSTEAD OF触发器
DELIMITER $$
CREATE TRIGGER instead_of_insert_employee
INSTEAD OF INSERT ON employee_department_view
FOR EACH ROW
BEGIN
    DECLARE dept_exists INT;
    SELECT COUNT(*) INTO dept_exists FROM departments WHERE dept_name = NEW.dept_name;
    IF dept_exists = 0 THEN
        INSERT INTO departments (dept_name, location) VALUES (NEW.dept_name, NEW.location);
    END IF;
    INSERT INTO employees (emp_name, salary, dept_id)
    VALUES (NEW.emp_name, NEW.salary,
            (SELECT dept_id FROM departments WHERE dept_name = NEW.dept_name LIMIT 1));
END$$
DELIMITER ;

Stored Procedure Alternative

-- 创建处理视图更新的存储过程
DELIMITER $$
CREATE PROCEDURE update_employee_view(
    IN p_emp_id INT,
    IN p_emp_name VARCHAR(100),
    IN p_dept_name VARCHAR(100)
)
BEGIN
    DECLARE v_dept_id INT;
    SELECT dept_id INTO v_dept_id FROM departments WHERE dept_name = p_dept_name;
    IF v_dept_id IS NULL THEN
        INSERT INTO departments (dept_name) VALUES (p_dept_name);
        SET v_dept_id = LAST_INSERT_ID();
    END IF;
    UPDATE employees SET emp_name = p_emp_name, dept_id = v_dept_id WHERE emp_id = p_emp_id;
END$$
DELIMITER ;

Materialized View Implementation Methods

Physical Table + Scheduled Refresh

-- 1. 创建物化视图表
CREATE TABLE materialized_dept_stats (
    dept_id INT PRIMARY KEY,
    total_salary DECIMAL(15,2),
    avg_salary DECIMAL(10,2),
    emp_count INT,
    last_refresh TIMESTAMP
);

-- 2. 创建刷新存储过程
DELIMITER $$
CREATE PROCEDURE refresh_materialized_view()
BEGIN
    START TRANSACTION;
    TRUNCATE TABLE materialized_dept_stats;
    INSERT INTO materialized_dept_stats
    SELECT dept_id,
           SUM(salary) AS total_salary,
           AVG(salary) AS avg_salary,
           COUNT(*) AS emp_count,
           NOW() AS last_refresh
    FROM employees
    GROUP BY dept_id;
    COMMIT;
END$$
DELIMITER ;

-- 3. 创建事件定时刷新
CREATE EVENT refresh_mv_daily
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO CALL refresh_materialized_view();

Real‑time Updates via Triggers

-- 创建物化视图表
CREATE TABLE realtime_emp_stats (
    dept_id INT PRIMARY KEY,
    total_salary DECIMAL(15,2),
    emp_count INT
);

-- 初始化数据
INSERT INTO realtime_emp_stats
SELECT dept_id, SUM(salary), COUNT(*)
FROM employees GROUP BY dept_id;

-- 员工表插入后触发器
DELIMITER $$
CREATE TRIGGER emp_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    DECLARE existing INT;
    SELECT COUNT(*) INTO existing FROM realtime_emp_stats WHERE dept_id = NEW.dept_id;
    IF existing > 0 THEN
        UPDATE realtime_emp_stats SET total_salary = total_salary + NEW.salary,
            emp_count = emp_count + 1 WHERE dept_id = NEW.dept_id;
    ELSE
        INSERT INTO realtime_emp_stats VALUES (NEW.dept_id, NEW.salary, 1);
    END IF;
END$$

-- 员工表更新后触发器（处理部门变更和同部门薪资变更）
CREATE TRIGGER emp_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.dept_id != NEW.dept_id THEN
        UPDATE realtime_emp_stats SET total_salary = total_salary - OLD.salary,
            emp_count = emp_count - 1 WHERE dept_id = OLD.dept_id;
        CALL update_or_insert_stats(NEW.dept_id, NEW.salary, 1);
    ELSE
        UPDATE realtime_emp_stats SET total_salary = total_salary + (NEW.salary - OLD.salary)
        WHERE dept_id = NEW.dept_id;
    END IF;
END$$
DELIMITER ;

Distributed Materialized Views with FEDERATED Engine

-- 配置远程服务器访问
CREATE SERVER remote_server FOREIGN DATA WRAPPER mysql
OPTIONS (HOST 'remote_host', DATABASE 'remote_db', USER 'remote_user', PASSWORD 'remote_password', PORT 3306);

-- 创建联邦表
CREATE TABLE federated_sales_summary (
    region VARCHAR(50),
    total_sales DECIMAL(15,2),
    sales_count INT
) ENGINE=FEDERATED CONNECTION='remote_server/sales_summary';

-- 本地物化视图
CREATE TABLE local_sales_mv AS SELECT * FROM federated_sales_summary;

-- 增量更新存储过程
DELIMITER $$
CREATE PROCEDURE incremental_refresh_mv()
BEGIN
    DECLARE last_update TIMESTAMP;
    SELECT MAX(last_refresh) INTO last_update FROM local_sales_mv;
    INSERT INTO local_sales_mv
    SELECT r.*, NOW()
    FROM federated_sales_summary r
    LEFT JOIN local_sales_mv l ON r.region = l.region
    WHERE l.region IS NULL OR r.last_modified > last_update
    ON DUPLICATE KEY UPDATE total_sales = r.total_sales,
                            sales_count = r.sales_count,
                            last_refresh = NOW();
END$$
DELIMITER ;

Optimization Strategy Comparison

Performance comparison chart:

Applicable Scenarios for Regular Views

Low query frequency.

Need real‑time data.

Simplify complex query logic.

Data access control.

Applicable Scenarios for Materialized Views

Complex aggregation queries (SUM, COUNT, AVG).

Large‑scale joins.

Report systems with fixed‑time generation.

Read‑heavy, write‑light workloads.

Materialized View Optimization Tips

Incremental Refresh

-- 增量更新示例
CREATE PROCEDURE refresh_customer_summary_incremental()
BEGIN
    INSERT INTO customer_summary_mv (customer_id, name, order_count, total_amount)
    SELECT c.customer_id,
           c.name,
           COUNT(o.order_id) AS order_count,
           SUM(o.amount) AS total_amount
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
    GROUP BY c.customer_id, c.name
    ON DUPLICATE KEY UPDATE order_count = VALUES(order_count),
                            total_amount = VALUES(total_amount),
                            last_refresh = NOW();
END;

Index Optimization

-- 为物化视图表添加索引
ALTER TABLE customer_summary_mv
ADD INDEX idx_order_count (order_count),
ADD INDEX idx_total_amount (total_amount);

Partition Strategy

-- 按时间分区
ALTER TABLE customer_summary_mv
PARTITION BY RANGE (YEAR(last_refresh)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

View Permission Management

-- 创建角色
CREATE ROLE employee_role;
CREATE ROLE manager_role;
CREATE ROLE hr_role;
CREATE ROLE finance_role;

-- 为用户分配角色
CREATE USER 'emp1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'mgr1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'hr1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'finance1'@'localhost' IDENTIFIED BY 'password';

GRANT employee_role TO 'emp1'@'localhost';
GRANT manager_role TO 'mgr1'@'localhost';
GRANT hr_role TO 'hr1'@'localhost';
GRANT finance_role TO 'finance1'@'localhost';

-- 授予视图访问权限
GRANT SELECT ON employee_public_view TO employee_role;
GRANT SELECT ON manager_department_view TO manager_role;
GRANT SELECT ON hr_employee_view TO hr_role;
GRANT SELECT ON finance_salary_view TO finance_role;

-- 撤销基础表直接访问权限
REVOKE ALL PRIVILEGES ON employees_confidential FROM PUBLIC;

-- 创建列级权限视图
CREATE VIEW column_level_security AS
SELECT emp_id,
       emp_name,
       CASE WHEN CURRENT_USER() LIKE 'hr%@%' THEN salary ELSE NULL END AS salary,
       CASE WHEN CURRENT_USER() = 'admin@%' THEN ssn
            ELSE CONCAT('XXX-XX-', RIGHT(ssn, 4)) END AS ssn
FROM employees_confidential;

-- 动态权限检查存储过程
DELIMITER $$
CREATE PROCEDURE get_employee_data(IN p_emp_id INT)
BEGIN
    DECLARE user_role VARCHAR(50);
    DECLARE user_dept INT;
    SELECT role, dept_id INTO user_role, user_dept FROM user_permissions WHERE username = CURRENT_USER();
    IF user_role = 'MANAGER' THEN
        SELECT emp_id, emp_name, salary, hire_date FROM employees_confidential WHERE emp_id = p_emp_id AND dept_id = user_dept;
    ELSEIF user_role = 'HR' THEN
        SELECT emp_id, emp_name, salary,
               CONCAT('XXX-XX-', RIGHT(ssn, 4)) AS ssn,
               hire_date, dept_id FROM employees_confidential WHERE emp_id = p_emp_id;
    ELSE
        SELECT emp_id, emp_name, hire_date FROM employees_confidential WHERE emp_id = p_emp_id;
    END IF;
END$$
DELIMITER ;

MySQL does not support native materialized views, but by combining tables, stored procedures, events, triggers, and the FEDERATED engine, similar functionality can be achieved. Choose the appropriate solution based on real‑time requirements, query complexity, and data change frequency.