MySQL
2024-10-28 15:56:04 0 举报AI智能生成
MySQL面试思维导图
作者其他创作
大纲/内容
索引
物理结构
聚集索引
数据文件和索引文件是一起，数据文件本身就是按照 B+ 树组织的索引结构，叶子结点的 data 域保存了完整的数据记录
非聚集索引
数据文件和索引文件是分开的，索引文件中的叶子结点存放的是地址值，通过地址值可以找到数据文件中对应的数据
数据结构
B 树
B+树
为什么使用 B+ 树不使用 B 树？
B 树的所有结点既存放键，也存放数据，但是 B+树只有叶子结点才存放键和值，非叶子结点只存放键
B 树在进行范围查询的时候，首先要找到查询的下限，然后对 B 树进行中序遍历，只到找到查找的上限，但是在 B+ 树上，可以直接在叶子结点通过双向链表遍历
B+ 树是矮胖的特性，相对于 B 树和二叉树来说，最大的优势在于查询效率高，因为即使在数据量很大的情况下，查询一个数据的磁盘 I/O 依然维持在3~4次
Hash
哈希索引就是采用一定的哈希算法，将键值换成新的 hash 值，直接映射到相应的位置，然后存储在 hash 表中。但是 hash 索引不支持范围查询，只能等值查询
字段特性
主键索引
普通索引
聚簇索引
索引下推
对数据检索的任务从 Server 层转到 InnoDB 引擎，在二级索引树上进行过滤，将符合条件的结果直接返回给 Server 层
索引跳跃
即使不满足最左匹配原则，但是在某些条件的情况下可以只在二级索引树上进行检索
索引失效
对索引使用函数
对索引进行表达式计算
对索引隐式类型转换
非最左匹配原则
where 条件使用 or
SQL优化
硬件优化
架构优化
使用缓存
分布式
读写分离
表调优
索引
索引覆盖
explain 关键字查看
type
extra
长索引使用前缀索引
严格约束字段长度
数字能用 tinyint 就使用 tinyint
日期类型尽量使用 timestamp
日志
undo log
记录事务在执行更新操作前的数据，防止事务还没有完成，MySQL发生宕机，不能恢复到操作前的数据，实现事务的原子性和实现MVCC
事务的原子性
MVCC
通过undo log生成的版本链和 read view 来实现多版本并发控制，实现的机制在于 read view 生成的时机
redo log
记录进行了什么操作，做了什么样的修改，防止在数据修改之后，虽然redo log 刷新到磁盘，但是在 Buffer Pool 中的脏页还没有刷新到磁盘，导致数据丢失，完成故障恢复
WAL技术：MySQL的写操作不是直接写到磁盘上，而是先写到日志文件，然后在合适的时间再写到磁盘中
binlog
记录所有数据库表结构的变更和数据的变更，有三种格式的日志文件，主要用它来实现数据备份和主从复制
主从复制
优点
实现读写分离，对于读多写少的业务，从从库进行读，在主库进行写，避免因为频繁的访问数据库到时压力过大
主库出现问题，可以切换到从库
数据备份的时候，对从库进行备份，可以保证主库的业务不会停滞
实现
主库在收到客户端提交的事务请求的时候，将数据变更以二进制的形式写入到binlog当中，再提交事务
从库当中的 I/O 线程从主库读取到binlog文件，将文件写入到从库的中继日志文件中
从库的 SQL 线程从中继日志文件中读取 binlog数据，回放 binlog 实现主从复制
两阶段提交
为什么这样做？
防止 redo log 和 binlog 没有一起刷新到磁盘中，发生了宕机，使得恢复之后发生主从数据不一致
prepaer 阶段
将 redo log 设置为 prepare，然后将 redo log 持久化到磁盘中
commit 阶段
将 binlog 持久化到磁盘中，redo log 设置为 commit，调用事务提交接口
事务
ACID特性
原子性
通过 undo loog 实现原子性
持久性
通过 redo log 实现持久性
隔离性
通过 MVCC 或 锁机制 实现隔离性
一致性
原子性 + 持久性 + 隔离性
并发事务引发的问题
脏读（一个事务读到了另一个未提交事务修改的数据）
不可重复读（一个事务前后读取同一个数据出现不一致）
幻读（一个事务在前后查询符合条件的记录得到的数量不一致）
解决并发事务带来的问题设计出了隔离级别
读未提交
上面三种问题都不能解决
读已提交
解决脏读问题
每次查询生成一个新的 Read View，结合版本链来实现
可重复读
解决脏读和不可重复读
如何解决：当前事务开启的时候就生成一个 Read View ，在这个事务结束之前，一直使用这个 Read View，再结合版本链来实现
有没有解决幻读：解决了大部分情况的幻读
快照读（平常的 select）：就是通过 MVCC 解决的
当前读（select ... for update）：通过临键锁（记录锁 + 间隙锁）实现
串行化
都解决，性能最差
锁
全局锁
锁住整个数据库，一般在备份的时候使用，这个期间DDDL、DML都会阻塞，只能进行一些查询
带来问题：业务停滞
解决办法：
使用主从架构，将从库中的数据进行备份
使用 MVCC ，在备份的时候生成一个 Read View ，备份期间一直使用这个读视图
表级锁
表锁
锁住整张表
共享读锁
独占写锁
意向锁
为了防止DML执行时，加入的行锁和表锁发生冲突，快速判断表里是否有记录被加锁
意向共享锁
意向独占锁
元数据锁（MDL）
防止某个用户在执行 CRUD 操作的时候，其他线程修改表结构
MDL 读锁（进行 CRUD操作的时候自动加锁）
MDL 写锁（对表结构做变更的时候自动加锁）
行级锁
记录锁
锁住一条记录
间隙锁
锁一个区间，左右开区间
临键锁（next-key lock）
记录锁 + 间隙锁 ，左开右闭
行锁是怎么加的？
加锁的基本单位是 next-key lock，但是有些情况会退化为记录锁或者间隙锁，怎么退化其实就是看怎么能避免幻读现象的发生
唯一索引等值查询
有结果：将next-key lock 退化为记录锁
没有结果：将 next-key lock 退化为间隙锁
唯一索引范围查询
大于：查到和查不到都是 next-key lock
大于等于：对于等值查询，如果能找到，就将那一条记录的锁退化为记录锁，找不到就是 next-key lock，其他都是next-key lock
小于：
没有结果：将扫描到的最后一个记录的锁退化为间隙锁，其他都是 next-key lock
有结果：将扫描到的最后一个记录的锁退化为间隙锁，其他都是 next-key lock
小于等于：
没有结果：将扫描到的最后一个记录的锁退化为间隙锁，其他都是 next-key lock
有结果：
看条件值，也就是等值条件有没有记录，如果没有，将这条记录的锁退化为间隙锁，其他都是 next-key lock
如果有，全部都是 next-key lock
非唯一索引等值查询
没有结果：将扫描到第一条不符合条件的记录的二级索引的next-key lock 退化为间隙锁，主索引树不加锁
有结果：在二级索引树符合条件的记录的锁都是 next-key lock，第一条不符合记录的锁退化为间隙锁，同时将主索引树上符合条件的记录退化为记录锁
非唯一索引范围查询
不管有没有结果，二级索引加的锁都是 next-key lock
没有加索引的查询
会将整张表锁住
0 条评论
下一页
