链接1.介绍优化方面2.如何定位慢查询方案1开源工具追踪功能方案2Mysql自带慢日志将慢sql记录到日志文件中总结生产一般不开启 损耗mysql性能3.SQL执行慢-分析方法获取查询语句信息type字段总结4.索引概念底层数据结构B树是B树的优化2是因为数据都在叶子节点查找路径差不多所以稳定3双向指针连接所以范围查询方便5.聚簇索引非聚簇索引回表查询回表查询二次总结单独给字段创建的索引一般多个6.覆盖索引超大分页优化基于二级索引二级索引的叶子一定会包含索引键 (name) 主键 id总结1超大分页-覆盖索引子查询优化总结2先分页查询获取表中的id然后对表的id进行排序能筛选出分页之后的id集合因为id是覆盖索引操作id效率高最后通过id集合去到原来的表中做关联查询效率就可以得到提升7.索引创建的原则总结一般是数据量大的基础上然后还需要查询三个复杂查询联合索引索引数量8.索引失效情况不满足最左前缀不正常情况只是命中name范围索引右边的列使用索引在‘索引列’进行运算字符串不加单引号总结最左前缀模糊范围字符串计算和单引号9.SQL优化经验表设计的优化SQL语句的优化主从复制、读写分离总结事务相关1.事务特性2.并发事务带来的问题、隔离级别并发事务带来的问题这个幻读的情况 是在解决了不可重复读问题的情况下 就是可以重复读取了 且重复读取时一致的数据假设表里只有 id1、2 两个账户事务 A 要统计余额 0 的账户总数事务 A 第一次执行SELECT * FROM account WHERE money 0查到 2 条数据此时事务 B 新开事务执行INSERT INTO account(id,money) VALUES(3,1000)并commit 提交事务 A 没有结束再次执行一模一样的SELECT * FROM account WHERE money 0这次查到 3 条事务 A 在同一个事务里同样的查询语句前后结果行数变多了仿佛凭空多出一行这就是幻读。反向场景事务 B 删除了一条符合条件的数据并提交A 第二次查询少了一行也属于幻读。即UPDATE 改已有行 → 不可重复读INSERT/DELETE 增减行 → 幻读问题操作对象触发 SQL 类型现象不可重复读已存在的单行数据其他事务 UPDATE 更新同一行的值前后不一样幻读整个范围的数据集其他事务 INSERT/DELETE 增删行查询到的总行数变多 / 变少出现 / 消失新记录解决以上问题事务隔离隔离级别串行化是强制加锁串行执行解决所有并发问题一般不用性能差总结并发事务带来的问题问题一句话根因解决思路举例脏读读到了别人还没提交的中间数据读没有隔离能读到未提交数据只允许读已提交事务A改 salary9000未提交事务B读到 9000A回滚了B读到的是脏数据不可重复读同一条数据两次读结果不一致被别人 UPDATE读未加锁两次读之间被别人改了并提交事务内读用快照MVCC事务A第一次读 salary5000事务B改成 8000 并提交事务A再读变成 8000幻读同一个范围查询两次读行数不一致被别人 INSERT/DELETE没加间隙锁范围查询被别人插了或删了并提交MVCC Next-Key Lock事务A查 age30 有 5 条事务B插入一条并提交事务A再查变成 6 条丢失更新两个事务同时修改同一行一个的更新被覆盖写没加锁后提交的覆盖了先提交的SELECT ... FOR UPDATE 加行锁事务A读 salary5000事务B也读 5000A改成 6000 提交B改成 7000 提交A 的更新丢失隔离级别与问题的对应关系隔离级别脏读不可重复读幻读怎么做到的READ UNCOMMITTED读未提交✅ 存在✅ 存在✅ 存在什么都不做直接读最新值READ COMMITTED读已提交❌ 解决✅ 存在✅ 存在每次 SELECT 生成新快照REPEATABLE READ可重复读⭐❌ 解决❌ 解决⚠️ InnoDB 通过 MVCC 间隙锁基本解决事务第一次 SELECT 生成快照后续复用当前读加 Next-Key LockSERIALIZABLE串行化❌ 解决❌ 解决❌ 解决所有读加共享锁写加排他锁事务完全串行MySQL 默认隔离级别REPEATABLE READRC 和 RR 的核心区别最关键RCREAD COMMITTED—— 每次读都看最新已提交SELECT ① → 生成快照 A 别人提交了新数据 SELECT ② → 生成快照 B能看到新数据← 所以会有不可重复读RRREPEATABLE READ—— 进事务时拍照之后只看这张照片SELECT ① → 生成快照 A 别人提交了新数据 SELECT ② → 还是快照 A看不到新数据← 所以解决了不可重复读⚠️ 标准 SQL 中 RR 级别不解决幻读但 InnoDB 用MVCC快照读 Next-Key Lock当前读两种机制配合基本消除了幻读。一句话速记口诀脏读是读了没提交的不可重复读是读了被改的幻读是读了被插的丢失更新是写了被覆盖的。RC每次读最新快照解决脏读RR锁住第一次快照解决不可重复读InnoDB再加间隙锁解决幻读串行化全部排队。3.undo log和redo log区别都是mysql的日志文件redo log内存操作完的数据没同步到磁盘属于脏页此时如果服务器宕机会导致数据同步失败内存的数据可能会消失违背事务持久化的特性mysql引入了undo log日志文件机制先写日志延迟刷磁盘数据文件undo log总结undo logredo log一句话回滚日志记录修改前的旧数据重做日志记录修改后的新值解决问题事务回滚 MVCC多版本并发控制崩溃恢复crash recovery记录内容修改前的数据旧值修改后的数据新值作用阶段事务执行过程中事务执行过程中保证特性原子性Atomicity持久性Durability生命周期事务提交后可被清理purge 线程数据刷盘后可被覆盖循环写入存储位置共享表空间ibdata或独立 undo 表空间redo log 文件ib_logfile0 / ib_logfile1文件大小动态增长固定大小循环写入关联机制MVCC通过版本链提供一致性快照WAL先写日志再写磁盘回滚应用ROLLBACK 时根据 undo log 恢复旧值—崩溃恢复—重启后重放 redo log 恢复未刷盘的数据核心记忆口诀undo 存旧值用于回滚 MVCCredo 存新值用于崩溃恢复。一个是往回看回到过去一个是往前追恢复未来。细节redo log 细节核心思想WALWrite-Ahead Logging先写日志再写磁盘。为什么因为随机 IO 慢。直接把修改写到数据页是随机写而写 redo log 是顺序写快得多。组成redo log buffer内存redo log file磁盘ib_logfile0、ib_logfile1...写入时机事务执行过程中就开始写 buffer不是提交时才写。刷盘策略innodb_flush_log_at_trx_commit这个参数控制 buffer 什么时候真正写到磁盘值行为安全性0每秒由后台线程刷盘丢1秒数据1每次提交时刷盘不丢数据最安全2提交时写到 OS 缓存每秒刷盘OS 挂才丢默认是 1面试直接说就行。特点物理日志记录的是某页某偏移处改成什么值循环写写满后回到开头覆盖旧的由checkpoint机制控制哪些部分已经可以被覆盖——就是对应数据页已经刷到磁盘了undo log 细节作用有两个不只是回滚1.事务回滚2.MVCC多版本并发控制两种类型类型对应操作何时清理insert undo logINSERT 操作事务提交后立刻可以删update undo logUPDATE / DELETE 操作不能立刻删要等没有事务再读旧版本为什么 insert 的可以立刻删因为插入的数据其他事务根本没读到过不需要旧版本。update/delete 的为什么不能删因为其他事务可能正在通过 MVCC 读那个旧版本。旧版本链每行数据被修改时旧版本通过 undo log 串成一条链当前数据 → undo log(版本3) → undo log(版本2) → undo log(版本1)事务读数据时沿着这条链找到自己看得见的那个版本。判断依据ReadViewReadView 里有四个关键东西m_ids生成 ReadView 时还在活跃的事务 ID 列表min_trx_id活跃事务中最小的 IDmax_trx_id下一个要分配的事务 ID当前最大 1creator_trx_id创建这个 ReadView 的事务 ID判断规则1.数据的 trx_id等于creator_trx_id → 自己改的可见2.数据的 trx_id小于min_trx_id → 在 ReadView 之前就提交了可见3.数据的 trx_id大于等于max_trx_id → 在 ReadView 之后才开始的事务不可见4.数据的 trx_id在 min 和 max 之间→ 看是否在 m_ids 里在 → 没提交不可见不在 → 已提交可见不可见就沿 undo log 链继续找上一个版本。Purge 线程后台有个 purge 线程专门负责清理已经没有任何事务需要的 undo log。对比总结redo logundo log类型物理日志逻辑日志内容数据页修改后数据修改前的逆操作保证持久性D原子性A MVCC写入时机事务执行中事务执行中什么时候不删checkpoint 推进后可覆盖无事务读旧版本后 purge 清理存储位置磁盘上的 ib_logfile表空间里的回滚段rollback segment面试能说清 WAL、刷盘策略、undo log 的两种类型、MVCC 的 ReadView 四个判断规则这块就稳了。4.mvcc多版本并发控制实现快照读读不阻塞写、写不阻塞读高并发性能强同时解决脏读、不可重复读配合间隙锁在 RR 隔离级别消除幻读ReadView它只管快照读。当前读根本不走 ReadView走的是另一条路——加锁概述一个版本的 trx_id 比快照里未提交事务的最小ID还小或者不在未提交事务列表里就可见否则就沿 undo log 往回找。总结MVCC 速记总结一句话读旧版本不加锁写最新版本才加锁。依赖三件套隐藏列 undo log版本链 ReadView (trx_id (旧版本串成链 (判断哪些版本 roll_ptr) 回滚用) 对我可见)版本链长什么样当前行 [TRX_ID4] ──→ undo [TRX_ID3] ──→ undo [TRX_ID2] ──→ undo [TRX_ID1] 最新版本 旧版本 更旧 最早每次修改旧值存 undo → 当前行指向新值ReadView 四个字段字段一句话m_ids此刻还没提交的事务有哪些min_trx_id这些活跃事务里最小的 IDmax_trx_id下一个要分配的新事务 IDcreator_trx_id我自己的 ID可见性判断按顺序trx_id 我自己 → 可见 ✅自己改的 trx_id min → 可见 ✅早就提交了 trx_id max → 不可见 ❌是未来事务 trx_id 在 min 和 max 之间 ├─ 在 m_ids 里 → 不可见 ❌没提交 └─ 不在 m_ids 里 → 可见 ✅已经提交了不可见就沿 roll_ptr 往回找找到可见版本为止。RC vs RR 的唯一区别RC读已提交每次 SELECT → 新建 ReadView RR可重复读第一次 SELECT → 建 ReadView后面复用这就是 RR 能可重复读的原因——不是加锁是复用快照。快照读 vs 当前读快照读走 MVCC当前读不走 MVCC普通 SELECTSELECT ... FOR UPDATE排它锁SELECT ... LOCK IN SHARE MODE共享锁INSERT / UPDATE / DELETE排它锁背下来的关键口诀存旧值靠 undo log判断能不能看靠 ReadViewRC 每次建 RR 建一次不可见就沿链往回追。主从同步原理总结分库分表目前主库和从库存的数据一样那此时是解决访问的压力但读写分开来进行访问时解决不了海量数据存储的问题分库分表策略垂直拆分就是按模块分水平拆分分摊单机压力,缩小单次检索的数据范围,路由规则很多拆分类型拆分依据部署位置表结构特点核心解决问题垂直分库业务功能模块多个独立数据库实例各库存放不同种类数据表业务解耦不同业务分摊服务器压力水平分库分片路由取模/ 范围 / 哈希等多个独立数据库实例各个库内部的表结构完全一致突破单机数据库并发、硬件瓶颈垂直分表字段冷热、字段大小同一个数据库当中拆分出多张字段不同的子表单表字段过多、大字段拖慢查询的问题水平分表分片路由取模/ 范围 / 哈希等同一个数据库当中拆分出多张结构一致的子表单张表行数太多、索引臃肿的问题总结垂直分表垂直分库水平分表水平分库一句话把一张大表的列拆成多张小表不同业务的表拆到不同数据库同一张表的数据按行拆到多张表同一张表的数据按行拆到多个数据库拆的是什么列字段整个业务模块行记录行记录拆分前1 张宽表1 个库放所有业务表1 张大表1 张大表在 1 个库拆分后多张窄表同库多个库不同机器多张结构相同的表同库多个库每个库结构相同不同机器核心驱动单表字段太多冷热数据分离不同业务耦合在一起互相影响单表数据量太大千万/亿级单库存不下或扛不住读写压力关联方式同库直接 JOIN应用层代码分别查询后组装同库直接查/UNION ALL中间件ShardingSphere路由 聚合举例user 表拆成 user_base user_profile用户库 / 订单库 / 商品库拆开orders 表拆成 orders_0 ~ orders_3orders_0 在机器Aorders_1 在机器B分片键不需要不需要需要如 user_id % 4需要如 user_id % 2