个人水平有限可能有错欢迎大家在评论区指正和讨论。如果觉得写的还行请点点赞你的赞是我更新的动力!适用人群快速了解、复习mysql常见八股文章目录数据库基础MySQL架构MySQL存储结构常见的存储引擎事务的ACID特性事务的隔离级别及对应的问题并发事务的问题事务的隔离级别MVCC实现可重复读索引索引的概念及优缺点索引的底层数据结构索引的分类联合索引的最左匹配原则覆盖索引、回表查询、索引下推索引失效的场景索引设计的原则SQL优化Explain命令的使用分页查询优化1. 问题表现2. 底层原理核心回表导致性能损耗3. 解决方案方案一延迟关联推荐方案二记录上次 ID业务最优锁机制MySQL锁的分类间隙锁和临键锁(解决幻读)死锁的产生与解决日志与恢复MySQL常见日志其他核心问题主从复制的原理分库分表慢查询相关资料数据库基础MySQL架构执行一条 SQL 查询语句期间发生了什么答连接器查询缓存解析 SQL词法分析、语法分析执行 SQL预处理阶段、优化阶段、执行阶段MySQL存储结构表空间由**段segment)、区extent)、页page)、行(row**组成InnoDB存储引擎的逻辑存储结构大致如下图默认每个页的大小为 16KB。COMPACT行格式如下常见的存储引擎**InnoDB:**有事务、行级锁采用B作为索引。索引即是数据。**MyISAM:**通过索引找到行号再找到数据。索引与数据分开。MyISAM存储数据方式事务的ACID特性答原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性Consistency事务操作前后数据满足完整性约束数据库保持一致性状态。隔离性Isolation多个并发事务相互独立互不干扰。持久性Durability事务一旦提交对数据库的改变就是永久的即使系统故障也不会丢失。事务的隔离级别及对应的问题并发事务的问题答脏读读到了未提交的数据不可重复读同一事务内数据内容前后不一致修改幻读同一事务内数据条数前后不一致增删事务的隔离级别答​MySQL事务有4种隔离级别级别越高一致性越强性能越差读未提交能读到其他事务未提交的数据读已提交只能读到其他事务已提交的数据Oracle 默认可重复读事务看到的数据与事务启动时看到的数据保持一致MySQL 默认串行化对记录加读写锁事务依次执行性能最低对应关系读未提交一个都解决不了读已提交解决脏读可重复读解决脏读 不可重复读串行化全部解决MVCC实现可重复读答​MVCC 是多版本并发控制实现可重复读维护多个版本数据通过可见性规则让事务只看到对应版本数据。MVCC分为以下3个部分隐藏字段包含事务 ID自增记录操作事务与回滚指针指向数据上一版本。undo log存储历史版本的数据通过回滚指针串联各版本形成版本链。ReadView用于判断事务对当前版本数据的可见性RR 级别仅第一次查询生成 ReadViewRC 级别每次查询都生成。实现图加深印象索引索引的概念及优缺点答​ MySQL 索引是快速查询和检索数据的数据结构。索引的作用就相当于书的目录。优点提高查询速度减少磁盘 I/O 次数加速排序分组缺点创建和维护耗时占用存储空间索引的底层数据结构答​ InnoDB引擎采用的B树结构来存储索引。优势如下B 树阶数大、层级少磁盘IO次数少非叶子节点存子页指针叶子结点存储全部数据查询性能更稳定叶子节点形成有序双向链表方便区间查询索引的分类答主键索引聚簇索引、聚集索引B树的叶子节点保存了整行数据有且只有一个二级索引非聚簇索引B树的叶子节点保存对应的主键可以有多个联合索引多个字段组合成的索引遵循最左匹配原则唯一索引索引列的值必须唯一允许 NULL避免重复联合索引的最左匹配原则答联合索引用多个字段组成索引使用时需要遵循最左匹配原则。最左前缀匹配原则指的是在使用联合索引时MySQL会根据索引定义的字段顺序从左往右依次匹配查询条件中的字段如果能匹配就使用索引过滤数据如果遇到范围查询比如 、、like 等就会停止匹配。设计规范在使用联合索引时可以将区分度高的字段放在最左边这也可以过滤更多数据。覆盖索引、回表查询、索引下推答覆盖索引查询的字段全部包含在索引字段中无需回表查询效率极高回表查询使用二级索引查找时叶子节点仅存主键索引列的值无法覆盖SQL查询的全部字段需用通过主键再查主键索引获取完整数据。索引下推使用二级索引查找时在存储引擎层对索引中包含的字段先做判断直接过滤掉不满足条件的记录再返还给Server层从而减少回表次数。索引失效的场景联合索引未遵循最左匹配原则在索引列上进行函数操作、类型转换发生隐式类型转换模糊查询以 % 开头左模糊查询条件使用不等号索引设计的原则选择合适字段优先选择经常查询、排序、分组且区分度高、不为 null的字段。控制索引数量单表索引不宜过多避免降低增删改的性能。优先联合索引尽量使用联合索引提高覆盖索引的概率减少回表次数。考虑表的数据量选择数据量大、频繁查询的表。SQL优化Explain命令的使用答 可以采用MySQL自带的分析工具EXPLAIN通过key和key_len检查是否命中了索引索引本身存在是否有失效的情况通过type字段是表的访问方法查看sql是否有进一步的优化空间通过extra建议判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复分页查询优化table表id,A,B,C, 索引是A,B,Cselect * from table where A x limit 300000, 101. 问题表现这条sql存在**深度分页问题**offset查询性能差。2. 底层原理核心回表导致性能损耗​ 深度分页时MySQL 会扫描所有满足条件的记录。使用二级索引查找时叶子节点仅存主键和索引列需用通过主键再查主键索引获取完整数据。这样导致大量随机 I/O查询性能极差同时资源利用率极低最终仅返回少量结果前面扫描的数据全部丢弃。3. 解决方案方案一延迟关联推荐核心采用延迟关联子查询只查询主键外层再查询完整信息极大减少回表次数SELECTt.*FROMtabletJOIN(SELECTidFROMtableWHERExxxLIMIT300000,10)AStmpONt.idtmp.id;方案二记录上次 ID业务最优核心用主键 ID 条件替代大偏移量利用主键索引直接定位数据起点。-- id为自增主键记录上一页最后一条数据的IDSELECT*FROMtableWHEREid上一页最大IDLIMIT10;锁机制MySQL锁的分类MySQL中的锁按照锁的粒度分分为以下三类全局锁锁定数据库中的所有表表级锁每次操作锁住整张表表锁元数据锁MDL为了避免DML与DDL冲突保证读写的正确性意向锁目的是为了快速判断表里是否有记录被加锁自增锁AUTO-INC 保证自增主键值唯一且连续行级锁每次操作锁住对应的行数据记录锁Record Lock也就是仅仅把一条记录锁上间隙锁Gap Lock锁定一个范围但是不包含记录本身临键锁Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。间隙锁和临键锁(解决幻读)查询条件字段没有索引时InnoDB 会退化成全表扫描加锁直接锁住全表所有临键区间全表间隙锁 全表记录锁相当于锁全表任何写入都会阻塞。间隙锁只管 “不能插新数据”不管 “已有的数据能不能改 / 删”所以必须再配上记录锁一起才能完整防止幻读 防止当前读被篡改。索引类型查询类型加锁核心规则一句话锁类型特点唯一索引等值查询存在→记录锁不存在→间隙锁锁最小只锁必要位置唯一索引范围查询向右遍历扫到第一个不满足值全程临键锁最后一个可能会退化普通索引等值查询存在→对匹配记录加 next-key lock对第一个不匹配的记录退化成 间隙锁同时对主键索引加记录锁不存在→不满足值退化为间隙锁锁前后一段区间防重复普通索引范围查询同唯一索引范围注意边界即可边界注意不同索引特性锁整片满足条件的区间MySQL InnoDB 临键锁/间隙锁 核心心法最终4条·重点加粗版加锁初始规则按索引从左往右扫描等值查询与范围查询默认先加临键锁且覆盖完整范围。索引核心差异唯一索引值唯一允许锁退化普通索引值可重复需注意左右区间且要对命中记录的主键加记录锁。边界命中判断等值查询值、范围查询边界是否落在索引上锁与退化规则不同。核心设计原则在保证解决幻读的前提下尽可能使用最小粒度锁提升并发性能。死锁的产生与解决死锁的四个必要条件互斥、持有且等待、不可强占用、循环等待。解决方式互斥一般不破坏锁资源需保证互斥性。持有并等待一次性申请所有资源或加锁超时后主动释放已持有的锁。不可剥夺申请新资源失败时主动释放已有资源或高优先级线程抢占低优先级资源。循环等待对资源统一编号线程按固定顺序申请资源。日志与恢复MySQL常见日志答undo log回滚日志是Innodb存储引擎层生成的日志实现了事务中的原子性主要用于事务回滚和 MVCC。redo log重做日志是Innodb存储引擎层生成的日志实现了事务中的持久性主要用于掉电等故障恢复;binlog是Server层生成的日志主要用于数据备份和主从复制其他核心问题主从复制的原理答​ MySQL 的主从复制依赖于binlog也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。MySQL 集群的主从复制过程梳理成 3 个阶段写入 Binlog主库写 binlog 日志提交事务并更新本地存储数据。同步 Binlog把 binlog 复制到所有从库上每个从库把 binlog 写到暂存日志中。(binlog dump 线程、IO线程)回放 Binlog回放 binlog并更新存储引擎中的数据。SQL线程原理图分库分表为什么要分单表数据超千万、读写慢、存储大、并发高。两种拆分方式垂直分库按业务拆用户库、订单库垂直分表按列拆大字段拆分水平分库同一张表按规则分到不同库水平分表同一张表按规则拆成多张表常见分片算法范围分片按 ID / 时间范围适合区间查询哈希分片均匀分布不适合扩容一致性哈希解决扩容问题环 顺时针找节点映射表分片、地理位置分片等分片键选择原则覆盖大部分查询数据分布均匀值不轻易变更分库分表带来的问题高频面试跨库无法JOIN分布式事务需要分布式 ID跨库聚合group by /order by复杂主流方案手动分库分表ShardingSphereSharding-JDBC云原生 / 分布式数据库TiDB自动分库分表无感扩容慢查询慢查询优化整体分为四步定位、分析、优化、复测。定位开启 MySQL 慢查询日志设置时间阈值记录执行耗时较长的 SQL批量捕获慢语句。分析使用Explain解析执行计划核心关注几个字段type代表查询类型最差是 ALL 全表扫描要尽量优化到 ref、range 级别key实际命中的索引key_len索引有效长度rows扫描行数数值越大性能越差Extra额外信息重点规避文件排序、临时表优先出现覆盖索引。优化 优先优化 SQL 写法遵循索引规范避免索引失效 合理建立联合索引使用覆盖索引减少回表 深度分页用主键分页、游标方案优化 数据量大时做冷热分离、读写分离必要时分库分表。复测优化后在测试环境压测验证保证性能达标。相关资料推荐看一下资料进行深入学习Redis 常见面试题 | 小林coding《mysql是怎样运行的》