MySQL 索引底层核心B 树核心特点分层结构非叶子节点只存索引主键不存真实行数据叶子节点保存完整数据行叶子节点双向有序链表支持快速区间查询、分页节点存储 key 更多树高度低减少磁盘 IO磁盘读取是数据库性能瓶颈特点上层节点只有索引值不存数据单页能存海量索引树高度极低千万级数据通常 2-3 层所有真实数据全部放在最底层叶子叶子节点用链表串联范围查询、分页只需要顺着链表遍历不用回溯上层节点。二叉树数据量大后树深度陡增每次查找多次读盘特点每个节点 1 个 key 对应数据只有左右两个分支范围查询要来回遍历节点。B 树每个节点既有 key 又有数据节点容纳 key 少树更高范围扫描效率弱于 B 树。特点所有节点都存储 key 行数据多路分支但非叶子节点占用空间大一页磁盘存不了多少索引范围查询需要来回跳转多个节点。三者核心对比总结二叉树分支少深度大海量数据 IO 爆炸数据库不用B 树节点带数据索引存放少树更高范围查询慢B 树非叶子轻量化、树矮、叶子有序链表适配数据库磁盘检索场景InnoDB 专用。等值查询与范围查询B树的双向有序链表B 树所有叶子节点通过双向指针连成一条有序链表每个叶子都存下一个叶子的地址等值查询根节点逐层向下二分查找直达对应叶子拿到数据范围 / 分页查询只需要找到区间第一条数据的叶子节点之后顺着链表往后遍历读取即可不用再回到上层根节点重新检索省去大量磁盘 IOB 树没有这条链表查一段连续数据时每一段数据都要重新从顶层往下搜效率很差。知识点理解测试为什么 InnoDB 选用 B 树做索引而不用二叉树、普通 B 树对比二叉搜索树二叉树单个节点仅 1 个 key数据量大时树层级极深每次查询大量磁盘 IO性能极差不适合数据库。对比 B 树B 树所有节点都同时存储索引 key 和完整行数据单磁盘页能存放的索引数量有限树高度更高且叶子无链表范围查询需要反复回溯上层节点IO 开销大。B 树优势 ① 非叶子节点只存索引键不存数据单页可存储大量索引树高度通常仅 2-3 层等值查询磁盘 IO 极少 ② 所有叶子节点通过双向有序链表串联范围查询、分页、批量检索只需定位起始节点顺着链表遍历无需重复检索上层大幅提升区间查询性能。聚簇索引、二级索引InnoDB 专属聚簇索引定义把完整行数据和索引绑定在一起索引在哪数据就在哪。规则一张表只能有 1 个聚簇索引。有主键主键自动作为聚簇索引无主键选唯一非空字段都没有InnoDB 自动生成隐藏 rowid 当聚簇索引。叶子节点存什么聚簇索引叶子节点 一整条完整数据所有字段id、手机号、短信内容、发送时间等。举例子短信表主键 id 是聚簇索引 B 树叶子直接存id1手机号 138xxx内容 xxx发送时间 xxx 全部信息。非聚簇索引二级索引对比理解普通单列 / 联合索引都属于二级索引。它的叶子不存完整数据只存「索引字段值 主键 id」。 如果查询字段不在索引里就要拿着主键回到聚簇索引查完整数据这个过程叫回表。举例子给手机号建二级索引 叶子只存手机号 短信 id想查短信内容必须用 id 去聚簇索引拿完整数据。两者对比聚簇索引1 张表仅 1 个叶子存完整数据二级索引可以建多个叶子只存主键需要回表。知识点理解测试什么是聚簇索引一张 InnoDB 表能有几个聚簇索引叶子节点存放什么内容聚簇索引将索引与完整行数据存储在一起通过索引可直接获取整条数据一张 InnoDB 表只能存在 1 个聚簇索引聚簇索引 B 树的叶子节点存储该行所有字段的完整数据。什么是非聚簇索引二级索引叶子节点存什么什么是回表非聚簇索引也叫二级索引一张表可以创建多个二级索引它的 B 树叶子节点不会存储完整行数据仅保存索引字段的值与对应主键 ID。 当 SQL 查询的字段没有全部包含在二级索引内时会拿着叶子节点中存储的主键 ID再去聚簇索引的 B 树中查询完整数据这个过程就叫回表。 举业务例子短信表单独给手机号建立二级索引叶子只存手机号 短信主键 id若查询短信内容就需要通过主键 id 回表读取完整数据。列举至少 5 种常见索引失效场景。索引列使用函数、计算操作例如DATE(send_time)、phone1隐式类型转换字符串索引传入数字查询like查询前缀带通配符如%13800138000使用or连接无索引字段条件使用not in、!、not exists联合索引不满足最左匹配原则MySQL 优化器判定全表扫描更快主动放弃索引。MVCC 与事务隔离级别MVCC 多版本并发控制基础概念MVCC 全称多版本并发控制是 InnoDB 实现快照读不加锁、提升数据库并发能力的核心机制MySQL 默认隔离级别「可重复读 RR」靠它实现。 简单说同一行数据会保存多个历史版本读操作不加锁读写互不阻塞解决锁竞争、并发慢的问题。两大核心依赖组件undo 日志回滚日志 修改数据前把修改前的旧数据副本存到 undo 日志用来生成数据历史版本事务回滚时也靠 undo 恢复数据。read view读视图 查询时生成的视图用来判断当前事务能看到哪些版本的数据隔离其他事务未提交数据解决脏读、不可重复读。两种读取区分当前读加锁读取update/delete/select ... for update读取最新已提交数据会触发行锁 / 间隙锁快照读普通 select 不加锁走 MVCC 读取历史快照无锁、并发高。业务举例短信项目运营后台查询短信记录是普通 select 快照读不加锁同时后台批量更新短信发送状态是当前读加锁两者不会互相阻塞大幅提升并发吞吐量。总结物理表只有一份最新数据旧版本存 undo 连成版本链read 视图做过滤每个事务只能看到自己权限内的数据版本不会错乱写只改最新数据加锁读走 undo 快照不加锁实现读写并行。知识点理解测试什么是 MVCC依靠哪两个核心组件实现快照读和当前读分别是什么MVCC 全称多版本并发控制是 InnoDB 实现快照读不加锁、提升并发性能的核心机制。它依靠 undo 日志与 read view 读视图两大组件实现undo 日志回滚日志修改数据前将修改前的旧数据副本保存用来生成数据历史版本事务回滚时也依靠 undo 日志恢复原始数据read view 读视图查询时生成用来判断当前事务可见的数据版本隔离其他事务未提交数据解决脏读、不可重复读问题。快照读与当前读区分快照读普通不加锁的 SELECT 查询走 MVCC 读取数据历史快照无需加锁读写互不阻塞并发性能高当前读UPDATE、DELETE、SELECT ... FOR UPDATE 等操作属于加锁读取只会读取最新已提交数据会触发行锁、间隙锁等锁机制。MySQL 四大事务隔离级别隔离级别由低到高一共四级越往后隔离性越强、并发性能越弱。读未提交Read Uncommitted能读到其他事务未提交的数据会出现脏读几乎不会使用。读已提交Read Committed只能读到其他事务已经提交的数据解决了脏读 但同一事务内多次查询可能结果不一样会出现不可重复读。可重复读Repeatable Read同一事务里多次读取同一数据结果始终一致 解决了脏读、不可重复读这是 InnoDB 默认隔离级别依靠 MVCC 实现 仍可能出现幻读。串行化Serializable最高级别完全串行执行读写都加锁 脏读、不可重复读、幻读全部解决并发性能最差。三大读问题解释脏读一个事务读取到了其他事务未提交的数据出现于「读未提交」级别。不可重复读同一个事务内多次查询同一条数据结果不一致其他事务中途更新并提交数据出现于「读已提交」级别。幻读同一个事务内按条件多次查询数据行数发生变化新增 / 删除数据「可重复读」级别仍会出现。可重复读RR如何解决幻读InnoDB 在可重复读RR级别下分两种读场景处理幻读快照读普通 SELECT依靠MVCC实现。同一事务多次查询生成同一个读视图只能看到事务启动时的数据快照不会感知到其他事务新增 / 删除的数据规避了幻读现象。当前读UPDATE、DELETE、SELECT ... FOR UPDATE 等加锁操作单纯行锁无法锁住 “间隙”依然会产生幻读。因此 InnoDB 引入间隙锁 临键锁间隙锁锁定索引记录之间的空白区间禁止其他事务在区间内插入新数据临键锁行锁 间隙锁的结合是 RR 级别默认的加锁方式。 通过锁区间的方式彻底阻止新数据插入解决当前读场景下的幻读。间隙锁优缺点优点锁定索引间隙阻止插入操作有效防止幻读。缺点锁范围扩大降低并发性能同时会增加死锁概率。补充串行化是全表 / 记录加锁串行执行和 RR 级别解决幻读的方案不一样。InnoDB 三大锁行锁、表锁、意向锁1. 行锁特点锁粒度最小只锁定单行 / 多行数据仅阻塞被锁定行。优势并发性能高必须依赖索引生效。场景日常单条数据增删改查、高并发业务。2. 表锁特点锁粒度最大锁定整张表表内所有操作都会被阻塞。优势开销小、实现简单。场景表结构修改、整表批量操作、低并发场景。3. 意向锁定位表级辅助锁分为意向共享锁 (IS)、意向排他锁 (IX)由数据库自动添加。作用仅做标记标识表内已有行锁快速判断表状态提升锁检测效率不会阻塞行锁。场景配合行锁、表锁协同工作InnoDB 内部自动使用。补充重点行锁与索引关系行锁必须依赖索引才能定位数据行。 如果操作条件不走索引MySQL 会全表扫描并逐行加锁最终行锁退化为表锁严重影响并发。知识点理解测试1.MySQL 四大事务隔离级别分别是什么默认是哪一级MySQL 四大事务隔离级别从低到高读未提交、读已提交、可重复读、串行化。InnoDB 默认隔离级别可重复读2.结合隔离级别解释什么是脏读、不可重复读、幻读。脏读一个事务读取到了其他事务未提交的数据。该问题出现在读未提交级别。不可重复读同一个事务内先后多次查询同一条数据结果不一致。原因是期间其他事务更新并提交了数据。该问题出现在读已提交级别。幻读同一个事务内多次按条件查询数据查询到的数据条数发生变化新增 / 减少数据行。可重复读级别解决了脏读、不可重复读但仍会出现幻读。读未提交存在脏读、不可重复读、幻读读已提交解决脏读存在不可重复读、幻读可重复读InnoDB 默认解决脏读、不可重复读存在幻读串行化三类问题全部解决并发最低3.InnoDB 可重复读级别下是如何解决幻读的InnoDB 可重复读级别通过两种方式解决幻读对于普通 SELECT 快照读依靠MVCC 多版本并发控制同一事务使用统一读视图读取固定数据快照避免幻读对于 UPDATE、DELETE、SELECT...FOR UPDATE 等当前读依靠间隙锁与临键锁锁定索引间隙禁止其他事务插入新数据从而解决幻读。4.简单说下间隙锁有什么优缺点优点可以锁定索引区间阻止其他事务在间隙内插入数据有效防止幻读。缺点锁的范围变大会阻塞区间内无关数据的操作降低数据库整体并发性能还可能增加死锁出现的概率。5.说一说行锁、表锁、意向锁的区别以及各自适用场景。行锁粒度最小仅锁定单行或多行数据仅阻塞对应行的操作其余行可正常访问。 适用场景并发量大、频繁增删改单条数据的业务如订单、用户数据操作。表锁粒度最大锁定整张数据表表内所有操作都会被阻塞。 适用场景数据量小、并发低或执行整表批量操作、表结构修改时使用。意向锁属于表级辅助锁分为意向共享锁、意向排他锁。它不阻塞行锁仅做标记用来快速判断表内是否存在行锁提升锁判断效率。 适用场景InnoDB 行锁机制下系统自动触发辅助行锁与表锁协同工作。6.行锁为什么必须依赖索引如果查询条件不走索引会发生什么行锁依靠索引定位具体数据行因此必须依赖索引才能生效。 若查询、更新、删除等操作的条件不走索引数据库会进行全表扫描逐行加锁最终行锁会退化为表锁严重降低并发性能。MySQL 优化优化整体方向MySQL 优化主要从六大维度开展由近到远依次为SQL 语句优化改写低效 SQL规避索引失效减少子查询、多表关联等耗时写法。索引优化合理设计索引遵循索引使用规则删除冗余、无效索引。表结构优化精选字段数据类型、控制字段长度规范主键与非空设计。服务器配置优化调整内存、连接数、缓冲区、超时时间等核心参数。架构优化搭建主从架构实现读写分离、分库分表、引入缓存分担数据库压力。运维规范开启慢查询日志、定期分析执行计划、管控长事务、清理冗余数据慢 SQL 定位与慢查询日志定位慢 SQL 的方式开启慢查询日志自动记录超时 SQL执行show processlist查看当前运行线程发现长时间执行的语句使用explain分析 SQL 执行计划定位性能瓶颈。慢查询日志作用记录执行时长超过预设阈值的 SQL是排查、优化慢 SQL 的核心依据。explain 执行计划作用解析 SQL 的执行逻辑判断是否走索引、扫描行数、执行方式等。核心关注字段type访问类型性能排序system const eq_ref ref range index all严禁出现all全表扫描。keySQL 实际使用的索引为空表示未走索引。rows预估扫描数据行数数值越小性能越好。Extra额外执行信息出现Using filesort文件排序、Using temporary临时表代表 SQL 需要优化。覆盖索引定义查询需要的所有字段都存在于索引中无需再回表查询聚簇索引。优点省去回表流程减少磁盘 IO显著提升查询效率。大表优化常用方案数据量庞大的表常用优化手段分表水平分表按行拆分数据、垂直分表按字段拆分分库按业务、时间、用户 ID 等维度拆分至不同数据库冷热数据分离将不常访问的历史冷数据单独归档缩减主表数据量精简索引删除无用索引避免索引过多拖慢写入性能规范查询禁止无条件全表查询优化分页语句读写分离主库负责写入从库承担查询压力。联合索引 最左匹配原则1.联合索引由多个字段组合创建的索引也叫复合索引常用于多条件联合查询场景。2.最左匹配原则使用联合索引时查询条件必须从索引最左侧第一个字段开始匹配依次向后延续索引才能正常生效若跳过左侧字段该联合索引整体失效。示例联合索引(a,b,c)有效where a1、where a1 and b2、where a1 and b2 and c3无效where b2、where c3、where b2 and c33.使用注意 条件顺序不影响索引生效MySQL 会自动优化 where 条件顺序尽量把查询频率高、区分度大的字段放在联合索引左侧。分页深偏移问题及优化分页深偏移使用limit offset, rows分页时当偏移量offset数值极大数据库会先扫描并丢弃前面大量数据再返回目标数据造成查询速度大幅下降。 例limit 100000,10需要先遍历 10 万条数据再取 10 条。主流优化方案主键定位法借助自增主键 / 唯一索引select * from 表 where id 100000 limit 10直接定位起始位置延迟关联先分页查询主键再通过主键关联查询完整数据减少回表开销业务限制前端限制最大可查询页码从业务层面规避深分页分页书签记录上一页最后一条数据的唯一值作为下一页查询条件。为什么不建议使用 select *读取表中全部字段产生多余的磁盘 IO 与网络传输开销增加查询耗时无法使用覆盖索引必须回表查询数据进一步降低性能表结构新增字段后会额外返回未知字段易引发程序解析异常、兼容性问题占用更多内存加重数据库与应用服务的资源负担。冗余索引 索引数量管控冗余索引已有联合索引的前置字段可以单独生效再为该字段单独创建单列索引这类重复、可被替代的索引就是冗余索引。 例已有联合索引(a,b)再单独建索引(a)(a)就是冗余索引。不建议创建过多索引的原因索引会占用磁盘空间索引越多空间消耗越大表执行insert/update/delete写入操作时需要同步更新所有索引索引越多写入性能越差大量索引会增加 MySQL 索引选择的判断耗时反而影响查询效率。临时表与文件排序Extra 高频考点Using temporary代表 SQL 执行过程中创建了临时表常见于group by、union、多表联查场景会额外消耗内存 / 磁盘资源需要优化。Using filesort代表 MySQL 无法使用索引完成排序只能在磁盘 / 内存中进行文件排序排序效率低优先通过建立合适索引优化排序字段。