MySQL高级特性（下）

1.事务TransactionACID 四大特性特性解释例子原子性事务被视为最小的且不可分割的工作单位 要么全做要么全不做转账扣钱和加钱必须一起完成它们是一项不可以分割的任务一致性事务前后数据保持合法状态什么是合法状态呢比如转账后总金额不变而且不能出现负数余额隔离性多个事务同时执行时互不干扰如果银行同时处理多笔转账A的转账和B的转账之间不难互相干扰。不能因为A的操作导致B的转账金额出错。持久性事务一旦提交数据永久保存转账成功后即使数据库断电钱也不会丢事务的生命周期分为三步开始事务拿word举例子我们打开了一个空文档开始了各种操作提交事务操作完成之后点击了保存按钮回滚事务操作了半天发现不满意按下了CtrlZ撤销或者是直接关闭文档编辑并没有保存那他还是空的并发事务当多个事务同事访问同一份数据时如果没有正确的隔离机制就可能会出现问题隔离级别解决并发问题隔离级别英文名称核心定义解决问题遗留 / 新增问题性能特点1. 读未提交Read Uncommitted一个事务可以读取另一个事务未提交的数据。无必现脏读最高2. 读已提交Read Committed一个事务只能读取另一个事务已提交的数据。解决脏读可能出现不可重复读高3. 可重复读Repeatable Read在一个事务内多次读取同一数据的结果是一致的。解决不可重复读可能出现幻读中4. 串行化Serializable事务串行执行完全排队读写加锁。解决幻读无并发问题最低. 通俗类比理解读未提交偷看草稿。你还没写完作业同桌就看了你的草稿结果你后来改了同桌看到的就是错的脏数据。读已提交看最终卷。必须等你写完并上交提交后同桌才能看。但同桌看的时候你可能正在改卷子导致同桌前后看到的卷子内容不一样不可重复读。可重复读截图快照。同桌在开始看之前把你的作业 “截图” 保存了。无论你怎么改同桌看到的始终是截图里的内容。但如果同桌在统计总数时你新增了一道题同桌可能会惊讶地发现总数变了幻读。串行化轮流使用。一次只允许一个人看作业其他人排队等候。完全安全但效率极低​MySQL 的特殊实现默认级别MySQL InnoDB 默认的隔离级别是 3. 可重复读 (Repeatable Read)。解决幻读在 MySQL 中InnoDB 通过 MVCC多版本并发控制 和 Next-Key Locks临键锁 机制在 可重复读 级别下实际上已经解决了幻读问题。因此在 MySQL 实战中通常不需要降级到最低的 “读未提交” 或仅靠 “读已提交”也很少需要直接升级到最严格的 “串行化”。2. 锁Lock—— “抢厕所的规则”为什么需要锁多个事务同时操作同一行数据时会产生冲突。锁就像厕所门上的锁谁进去就把门锁上别人只能等。锁的分类分类方式类型解释例子按粒度表锁锁住整张表别人不能动整张表做备份时行锁只锁住一行其他行可操作你改自己的订单不影响别人改别的订单间隙锁锁住两个值之间的“空隙”防止别人在 3~5 之间插入新数据避免幻读按性质共享锁读锁大家都能读不能写多人同时看一份文档排它锁写锁只有一个人能写别人不能读也不能写你在修改文档别人不能看也不能改死锁的例子互相等待事务 A 锁住了行 1想锁行 2 事务 B 锁住了行 2想锁行 1 两人都在等对方释放锁 →死锁。 MySQL 会自动检测并回滚其中一个事务。3. 数据库引擎常用三种引擎对比引擎事务锁粒度场景比喻InnoDB默认支持行锁银行、电商高并发、需要事务豪华轿车功能全安全MyISAM不支持表锁只读报表、数据仓库老爷车跑得快但不能倒车无事务回滚Memory不支持表锁临时缓存、会话数据电动车超快但一断电数据全没查看当前表的引擎SHOW TABLE STATUS WHERE Name users;如何选择需要事务、高并发、数据安全→ InnoDB只有大量查询不修改数据 → MyISAM但新项目一般也用 InnoDB临时数据丢了也没关系 → MemoryInnoDB 核心特性精简总结InnoDB 是 MySQL 5.5 起的默认存储引擎专为高并发 OLTP 核心业务如银行交易、电商订单设计核心优势是完整支持事务与 ACID 特性还具备多项关键技术能力行级锁仅锁定事务涉及的数据行而非整张表避免并发操作阻塞大幅提升并发效率。双日志机制redo log重做日志物理日志记录数据页的修改操作保障事务的持久性。undo log回滚日志逻辑日志记录事务操作的相反动作用于事务回滚与 MVCC 实现保障事务的原子性与一致性。MVCC多版本并发控制修改数据时不直接覆盖旧数据而是生成新版本并维护版本链事务可读取适配版本实现无阻塞读写是高性能的核心支撑。也就是读写互不阻塞。外键约束保障数据完整性适配企业级数据管理需求。比如你不能在表里插入一个不存在的用户ID。综上InnoDB 凭借高性能、高可靠、高并发的特性成为 MySQL 默认引擎的核心原因。4. 慢日志 —— “抓出偷懒的 SQL”记录执行时间超过设定阈值的 SQL 语句。就像老师记下考试答题超时的学生。帮你找到那些慢查询然后优化它们加索引、改写法-- 开启慢日志设置超过 2 秒就记录SET GLOBAL slow_query_log ON;SET GLOBAL long_query_time 2;SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;mysqldumpslow工具mysqldumpslow -s t /var/log/mysql/slow.log # 按时间排序看最慢的 SQL一、慢 SQL 定位工具官方工具可用于统计分析例如筛选执行次数最多的 Top10 慢 SQL。第三方工具pt-query-digest能生成更详细、可视化的慢查询分析报告。二、慢 SQL 优化核心步骤定位慢查询通过慢查询日志筛选出执行耗时超标的 SQL 语句。分析执行计划使用 EXPLAIN 命令解析 SQL 执行计划重点排查 是否命中索引 是否存在全表扫描 连接类型、扫描行数等关键指标针对性优化最常见的慢 SQL 诱因是索引问题需根据执行计划结果优化索引、SQL 语句或表结构。 三、完整优化流程 慢日志定位慢查询 → EXPLAIN 分析执行计划 → 针对性优化 SQL / 索引这是 MySQL 性能优化的标准工作流。三、完整优化流程慢日志定位慢查询 → EXPLAIN 分析执行计划 → 针对性优化 SQL / 索引这是 MySQL 性能优化的标准工作流。pt-query-digest是 Percona Toolkit 中的核心工具是 DBA 日常分析慢日志的必备神器支持聚合统计、按耗时 / 次数排序等多维度分析。EXPLAIN输出中的type列是判断索引使用情况的核心指标从优到劣依次为systemconsteq_refrefrangeindexALL出现ALL即代表全表扫描需重点优化。5. 重做日志Redo Log—— “记账本”InnoDB 特有的日志记录每一次修改操作为什么需要它防止数据库突然崩溃导致数据丢失。工作流程修改数据时先写重做日志缓冲区内存事务提交时把缓冲区的内容写入磁盘的重做日志文件数据库崩溃重启后读取重做日志文件重新执行那些还没写回数据文件的修改6. 常见并发问题 —— “四个坑以及怎么避”1. 更新丢失你和同事同时修改同一份报告。你先保存余额 100→150他后保存150→120你的修改被覆盖了。2. 脏读你看到别人还没提交的修改。比如别人正在转账扣了 100 但还没确认你看到余额变少了然后别人反悔回滚你看到的数据就是“脏”的。3. 不可重复读同一个事务内两次读同一数据结果不一样。你第一次读余额 100中间别人改成 80 并提交你第二次读变成 80。4. 幻读同一个事务内两次查询行数不一样。你第一次查订单数 3中间别人插入了 1 条新订单你第二次查变成 4多出来的那条就像“幻影”。