MySQL 核心进阶学习笔记：事务、锁、引擎、日志与并发问题

本篇笔记聚焦 MySQL 进阶核心知识点涵盖事务 (ACID / 隔离级别)、锁机制、存储引擎、慢查询日志、重做日志及并发事务问题是数据库开发与优化的必备基础内容干货满满适合复习巩固七、事务Transaction核心特性事务是一组 SQL 操作的最小执行单元遵循「要么全部成功要么全部失败」原则用于保证数据的一致性和完整性支持COMMIT提交和ROLLBACK回滚。ACID 属性必背原子性Atomicity事务不可分割所有操作要么全部执行成功要么全部回滚不允许部分执行。一致性Consistency事务执行前后数据库的完整性约束不被破坏始终保持一致状态。隔离性Isolation并发事务之间相互隔离一个事务未提交的修改对其他事务不可见。持久性Durability事务一旦提交对数据的修改永久生效即使数据库崩溃也不会丢失。基础语法-- 开启事务 START TRANSACTION; -- 执行SQL操作增删改 INSERT INTO users (username) VALUES (john_doe); UPDATE orders SET user_id LAST_INSERT_ID() WHERE order_id 123; -- 提交事务成功执行 COMMIT; -- 回滚事务执行失败 ROLLBACK;实战案例银行账户转账1. 创建数据表CREATE DATABASE bank; USE bank; CREATE TABLE accounts ( account_number INT PRIMARY KEY, -- 账号 account_name VARCHAR(100), -- 账户名 balance DECIMAL(15, 2) -- 余额保留两位小数 );2. 编写事务存储过程-- 修改语句结束符为// DELIMITER // CREATE PROCEDURE TransferFunds( IN sourceAccount INT, -- 源账户 IN destinationAccount INT, -- 目标账户 IN transferAmount DECIMAL(15,2) -- 转账金额 ) BEGIN START TRANSACTION; -- 开启事务 SET autocommit0; -- 关闭自动提交 -- 定义变量 SET source sourceAccount; SET dest destinationAccount; SET amount transferAmount; -- 查询源账户余额 SELECT balance : balance FROM accounts WHERE account_number source; -- 余额判断 IF balance amount THEN ROLLBACK; -- 回滚 SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 余额不足转账失败; ELSE -- 扣减源账户、增加目标账户 UPDATE accounts SET balance balance - amount WHERE account_number source; UPDATE accounts SET balance balance amount WHERE account_number dest; COMMIT; -- 提交事务 END IF; END // -- 恢复结束符为; DELIMITER ;3. 调用存储过程CALL TransferFunds(101, 102, 100);事务隔离级别解决并发问题表格隔离级别脏读不可重复读幻读说明READ UNCOMMITTED读未提交✅✅✅最低级别可读取未提交数据READ COMMITTED读已提交❌✅✅仅读取已提交数据避免脏读REPEATABLE READ可重复读❌❌✅MySQL 默认级别避免脏读、不可重复读SERIALIZABLE串行化❌❌❌最高级别事务串行执行性能极低事务应用场景金融交易银行转账、股票交易订单处理订单创建 库存扣减 支付记录数据一致性维护多表关联更新八、锁Lock核心作用MySQL 中控制并发事务访问资源的机制防止数据被并发修改导致不一致保证数据安全。锁的分类1. 按锁粒度划分表锁锁定整张表粒度大、并发低MyISAM 默认使用。行锁锁定单行数据粒度小、并发高InnoDB 默认使用。间隙锁锁定索引间隙防止幻读InnoDB 可重复读级别下使用。2. 按锁性质划分共享锁读锁多个事务可同时加读锁允许并发读取禁止写入。语法SELECT ... LOCK IN SHARE MODE;排它锁写锁独占锁一个事务加锁后其他事务无法读写。语法SELECT ... FOR UPDATE;、UPDATE/DELETE自动加锁。锁的常见问题死锁多个事务互相等待对方释放锁无限僵持。✅ 解决MySQL 自动检测并回滚小事务开发中统一加锁顺序。锁等待超时事务等待锁超时自动回滚。✅ 解决优化 SQL缩短事务执行时间。锁的应用场景票务系统防止超卖金融系统账户取款并发控制事务隔离实现不同隔离级别九、数据库存储引擎MySQL 支持多种存储引擎InnoDB 是默认首选不同引擎适用场景不同。常用引擎对比表格引擎事务支持锁粒度适用场景InnoDB✅行锁高并发、金融、电商、订单系统MyISAM❌表锁读多写少、日志、静态数据Memory❌表锁临时数据、内存缓存NDB✅行锁分布式集群、高可用系统InnoDB 核心特性重点支持完整 ACID 事务行级锁 MVCC多版本并发控制高并发性能强支持故障恢复数据安全性高支持 B 树索引、外键约束常用操作命令-- 查看所有支持的引擎 SHOW ENGINES; -- 查看表使用的引擎 SHOW CREATE TABLE accounts; -- 查询指定库下表的引擎 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA bank;InnoDB 关键配置my.cnf# 默认存储引擎 default-storage-engine InnoDB # 缓冲池大小建议服务器内存70% innodb_buffer_pool_size 4G # 事务日志大小 innodb_log_file_size 512M # 每个表独立表空间 innodb_file_per_table ON # 事务提交日志策略1最高安全 innodb_flush_log_at_trx_commit 1十、慢查询日志核心作用记录执行时间超过阈值的 SQL 语句用于数据库性能优化定位慢 SQL。核心配置参数表格参数作用默认值slow_query_log开启 / 关闭慢日志OFFlong_query_time慢查询阈值秒10log_queries_not_using_indexes记录未使用索引的 SQLOFFslow_query_log_file慢日志文件路径系统默认配置方式1. 配置文件永久生效slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes ON min_examined_row_limit 10002. SQL 动态生效重启失效SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;# 系统工具分析慢日志按执行时间排序 mysqldumpslow -s t /var/log/mysql/mysql-slow.log十一、重做日志Redo Log核心作用InnoDB 专属日志保证事务持久性数据库崩溃后自动恢复数据。工作原理事务修改数据时先写入重做日志缓冲区事务提交时日志写入磁盘数据库崩溃重启后根据重做日志恢复未持久化的数据关键配置# 日志文件大小 innodb_log_file_size 512M # 日志文件数量 innodb_log_files_in_group 2 # 日志缓冲区大小 innodb_log_buffer_size 64M十二、并发事务常见问题并发执行事务时会出现数据不一致问题通过隔离级别 锁解决。1. 更新丢失多个事务修改同一数据最后提交的事务覆盖之前的修改。✅ 解决加排它锁、使用事务隔离。2. 脏读读取到其他事务未提交的数据若对方回滚数据无效。✅ 解决隔离级别≥READ COMMITTED。3. 不可重复读同一事务内多次读取同一数据结果不一致被其他事务修改。✅ 解决隔离级别≥REPEATABLE READ。4. 幻读同一事务内范围查询结果行数变化其他事务插入 / 删除数据。✅ 解决REPEATABLE READ 间隙锁 / SERIALIZABLE。隔离级别设置语法-- 设置会话隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;学习总结事务是数据安全的核心牢牢掌握 ACID 和隔离级别金融 / 订单场景必用。InnoDB是生产环境首选引擎支持事务、行锁、故障恢复适配高并发。锁用于控制并发行锁提升性能表锁简单安全间隙锁解决幻读。慢日志 重做日志是数据库优化和数据安全的双保障必须熟练配置。并发问题通过隔离级别 锁解决MySQL 默认 REPEATABLE READ 满足绝大多数场景。