面试题：MySQL反范式设计中的数据冗余处理

使用事务：

当商品信息发生更新时，将对商品表和订单表中冗余商品信息的更新操作放在同一个事务中。例如在Java中使用JDBC时：

Connection conn = DriverManager.getConnection(url, username, password);
try {
    conn.setAutoCommit(false);
    // 更新商品表
    String updateProductSql = "UPDATE product_table SET product_name =?, price =? WHERE product_id =?";
    PreparedStatement productStmt = conn.prepareStatement(updateProductSql);
    productStmt.setString(1, newProductName);
    productStmt.setBigDecimal(2, newPrice);
    productStmt.setInt(3, productId);
    productStmt.executeUpdate();
    // 更新订单表中冗余的商品信息
    String updateOrderSql = "UPDATE order_table SET product_name =?, price =? WHERE product_id =?";
    PreparedStatement orderStmt = conn.prepareStatement(updateOrderSql);
    orderStmt.setString(1, newProductName);
    orderStmt.setBigDecimal(2, newPrice);
    orderStmt.setInt(3, productId);
    orderStmt.executeUpdate();
    conn.commit();
} catch (SQLException e) {
    if (conn!= null) {
        try {
            conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    e.printStackTrace();
} finally {
    if (conn!= null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

这样要么所有更新都成功，要么都失败，保证了数据的一致性。

触发器：
- 在商品表上创建一个AFTER UPDATE触发器，当商品表中的数据更新时，自动触发对订单表中冗余数据的更新。例如：
```
DELIMITER //
CREATE TRIGGER update_order_product_info
AFTER UPDATE ON product_table
FOR EACH ROW
BEGIN
    UPDATE order_table
    SET product_name = NEW.product_name, price = NEW.price
    WHERE product_id = NEW.product_id;
END //
DELIMITER ;
```
- 注意，触发器虽然方便，但过度使用可能会导致数据库逻辑复杂，影响性能和可维护性。

应用层缓存：

在应用层设置缓存，如使用Redis。当查询订单时，先从缓存中获取数据。如果缓存中没有，再从数据库中查询，并将结果存入缓存。
当商品信息更新时，不仅更新数据库，同时删除缓存中相关订单数据的缓存记录。下次查询订单时，由于缓存中没有数据，会从数据库重新加载最新数据，保证数据一致性。例如在Python中使用Flask和Redis：

import redis
from flask import Flask

app = Flask(__name__)
r = redis.Redis(host='localhost', port=6379, db = 0)

@app.route('/order/<order_id>')
def get_order(order_id):
    order = r.get(order_id)
    if order is None:
        # 从数据库查询订单
        order = get_order_from_db(order_id)
        r.set(order_id, order)
    return order

def update_product(product_id, new_name, new_price):
    # 更新商品表
    update_product_in_db(product_id, new_name, new_price)
    # 删除相关订单的缓存
    orders = get_orders_with_product(product_id)
    for order in orders:
        r.delete(order['order_id'])

版本控制：

在商品表和订单表中添加版本号字段。每次商品信息更新时，版本号递增。
当查询订单时，检查商品表和订单表中商品信息的版本号是否一致。如果不一致，说明订单中的冗余商品信息可能过时，需要重新从商品表获取最新信息并更新订单表。例如：

-- 商品表
CREATE TABLE product_table (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    version INT DEFAULT 0
);

-- 订单表
CREATE TABLE order_table (
    order_id INT PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    product_version INT,
    FOREIGN KEY (product_id) REFERENCES product_table(product_id)
);

-- 更新商品表时，版本号递增
UPDATE product_table
SET product_name = 'new_name', price = 10.5, version = version + 1
WHERE product_id = 1;

-- 查询订单时检查版本号
SELECT o.*, p.version
FROM order_table o
JOIN product_table p ON o.product_id = p.product_id
WHERE o.order_id = 1;

如果o.product_version和p.version不一致，应用程序需要更新order_table中的商品信息。

星途面试题库

面试题：MySQL反范式设计中的数据冗余处理

知识考点

面试题答案