面试题：MySQL临键锁与性能优化及死锁预防

性能优化及预防死锁策略

锁粒度控制
- 尽量使用行锁而非表锁：在高并发电商系统中，表锁会锁定整个表，严重影响并发性能。行锁只锁定操作涉及的行数据，能提高并发度。例如，在商品库存扣减操作中，只锁定对应商品库存的行，而不是整个商品表。
- 合理设置索引：合适的索引可以让MySQL更精准地定位行数据，从而使用行锁。比如在商品表中，以商品ID作为索引，当进行库存扣减操作时，MySQL就能通过商品ID索引快速定位到对应的行，进而使用行锁。
事务设计
- 减少事务持有锁的时间：将大事务拆分成多个小事务。例如，订单创建和商品库存扣减可以设计为两个事务。先进行订单创建事务，创建成功后再进行库存扣减事务。这样可以减少锁的持有时间，降低死锁风险。
- 确保事务按相同顺序访问资源：如果多个事务需要操作多个表或行，要保证它们按相同顺序访问。比如在订单创建和库存扣减操作中，所有事务都先操作订单表，再操作库存表，避免出现循环依赖导致死锁。
索引优化
- 覆盖索引：创建覆盖索引，使查询所需的数据都能从索引中获取，减少回表操作。例如，对于库存扣减操作，查询语句可能只需要商品ID和库存数量。如果创建一个包含商品ID和库存数量的覆盖索引，查询时就无需再回表获取数据，提高查询性能，进而提高锁操作的性能。
- 前缀索引：对于较长的字符串字段，使用前缀索引可以减少索引占用的空间，提高索引效率。但要注意前缀长度的选择，既要保证索引的选择性，又不能过长导致索引空间浪费。

具体实现思路及代码示例

以Java和JDBC为例，假设存在商品表 products（product_id 为主键，stock 为库存字段）和订单表 orders（order_id 为主键，product_id 关联商品表）。

库存扣减操作：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class StockReducer {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/ecommerce";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void reduceStock(int productId, int quantity) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            conn.setAutoCommit(false);
            String sql = "UPDATE products SET stock = stock -? WHERE product_id =? AND stock >=?";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, quantity);
                pstmt.setInt(2, productId);
                pstmt.setInt(3, quantity);
                int rowsAffected = pstmt.executeUpdate();
                if (rowsAffected == 0) {
                    throw new RuntimeException("Insufficient stock for product with ID: " + productId);
                }
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw new RuntimeException("Failed to reduce stock", e);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database connection error", e);
        }
    }
}

订单创建操作：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class OrderCreator {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/ecommerce";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void createOrder(int productId, int userId) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            conn.setAutoCommit(false);
            String sql = "INSERT INTO orders (product_id, user_id) VALUES (?,?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, productId);
                pstmt.setInt(2, userId);
                pstmt.executeUpdate();
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw new RuntimeException("Failed to create order", e);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Database connection error", e);
        }
    }
}

在实际应用中，可以根据业务逻辑，按照一定顺序调用 reduceStock 和 createOrder 方法，并且确保数据库表结构设计合理，有适当的索引，以提高性能并预防死锁。同时，还可以结合数据库连接池等技术来进一步优化性能。

面试题：MySQL临键锁与性能优化及死锁预防

知识考点

面试题答案

性能优化及预防死锁策略

具体实现思路及代码示例