面试题：MySQL触发器与存储过程的性能优化

性能优化方面

锁机制：
- 减少锁粒度：如果可能，尽量使用行级锁而非表级锁。例如在更新操作时，MySQL默认对涉及的表加表级锁，如果可以通过索引条件精确匹配，就能使用行级锁。在触发器或存储过程中，确保查询条件使用了合适的索引，以降低锁的范围。比如，对于UPDATE users SET status = 'active' WHERE user_id = 123;这样的语句，如果user_id上有索引，就可能使用行级锁，而不是表级锁。
- 优化锁的获取顺序：在多个事务中，如果都需要获取多个锁，按照相同的顺序获取锁，可避免死锁。例如在触发器或存储过程中，涉及多个表的操作，总是先获取表A的锁，再获取表B的锁，以此类推。
查询优化：
- 索引优化：分析触发器或存储过程中的SQL查询，确保相关列上有合适的索引。例如在一个触发器中，若有SELECT * FROM orders WHERE order_date > '2023 - 01 - 01' AND customer_id = 5;，对order_date和customer_id建立复合索引CREATE INDEX idx_order_date_customer_id ON orders (order_date, customer_id);能显著提升查询性能。
- 避免全表扫描：确保查询条件能够利用索引，避免使用函数操作索引列。例如不要使用SELECT * FROM products WHERE UPPER(product_name) = 'BOOK';，应改为SELECT * FROM products WHERE product_name = 'BOOK';，如果需要不区分大小写查询，可以使用COLLATE属性来处理。
- 查询重写：对于复杂查询，可尝试重写以提高效率。比如，将子查询改写为连接查询。假设有一个存储过程中的查询SELECT product_id, product_name FROM products WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');，可以改写为连接查询SELECT p.product_id, p.product_name FROM products p JOIN categories c ON p.category_id = c.category_id AND c.category_name = 'Electronics';
资源管理：
- 减少临时表使用：在存储过程中，尽量避免创建过多临时表。如果确实需要，及时清理临时表，释放资源。例如在存储过程结束时，使用DROP TEMPORARY TABLE IF EXISTS temp_table;语句删除临时表。
- 合理分配内存：根据MySQL服务器的配置，合理设置相关参数，如innodb_buffer_pool_size，确保触发器和存储过程执行过程中有足够的内存来缓存数据和索引，减少磁盘I/O。
事务管理：
- 缩短事务长度：在触发器和存储过程中，尽量将事务中的操作精简，只包含必要的逻辑。例如，不要在事务中执行大量的非关键查询或计算，将这些操作移到事务之外。
- 恰当设置事务隔离级别：根据业务需求，选择合适的事务隔离级别。如果业务对一致性要求不是特别高，可以选择较低的隔离级别（如读已提交），以减少锁的持有时间和并发冲突。

改写示例

假设有一个触发器，在插入orders表记录后，需要更新customers表的total_orders字段（记录该客户的总订单数）。原始触发器如下：

DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET total_orders = total_orders + 1
    WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

优化思路：如果customers表数据量较大，每次更新total_orders都会锁表，可以采用批量更新的方式。假设orders表有一个batch_insert字段，用于标识是否是批量插入的订单。

改写后的触发器：

DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.batch_insert = 1 THEN
        UPDATE customers c
        JOIN (SELECT customer_id, COUNT(*) as order_count
              FROM orders
              WHERE batch_insert = 1
              GROUP BY customer_id) sub
        ON c.customer_id = sub.customer_id
        SET c.total_orders = c.total_orders + sub.order_count;
    ELSE
        UPDATE customers
        SET total_orders = total_orders + 1
        WHERE customer_id = NEW.customer_id;
    END IF;
END //
DELIMITER ;

这样在批量插入订单时，通过一次连接查询和更新操作，减少了锁的争用，提升了性能。

星途面试题库

面试题：MySQL触发器与存储过程的性能优化

知识考点

面试题答案

性能优化方面

改写示例