MySQL：MySQL总结(附导图下载链接)

下载链接

链接: https://pan.baidu.com/s/1hRTh7rSesikisgRUO2GBpA?pwd=utgp 提取码: utgp

MySQL

思维导图

基础

实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record ，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该主键索引加next-key 锁，最小记录也有一个特殊的名字叫infimum record

MySQL记录存储

• 表空间结构

  • 段+区+页+行

行格式

• Redundant

  • 太老了，没人用了

• Compact

  • 相比起Redundant更紧凑

    • 重点理解这个

• Dynamic

  • 基于Compact进行改进，默认用这个

• Compressed

  • 基于Compact进行改进

Compact行格式

• 记录的额外信息

  • 变长字段列表

    • 记录头信息指针指向下一个记录的记录头信息和真实数据，逆序存储

    • 使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率

  • NULL值列表

    • 用二进制比特位表示，逆序存储，必须是字节的整数倍

• 记录的真实数据

  • 隐藏字段

    • row_id

      • 如果没有主键和唯一约束就需要这个字段，占6个字节

    • trx_id

      • 事务ID，表示这是哪个事务产生的记录，占6个字节

    • roll_ptr

      • 上一个版本的指针，MVCC机制，占7个字节

varchar(n)的取值

• 单字段情况

  • 真实数据+真实数据所占字节数+NULL标记

    • 总共最大占用65535

      • ASCII

        • 65535 - 2 - 1 = 65532

      • UTF-8

        • 65532/3 = 21844

• 多字段情况

  • 所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535

行溢出

• MySQL存储的基本单位是页，一页是16KB

  • 超过后的数据会存放到溢出页中

  • Dynamic和Compressed的主要区别

事务

事务有哪些特性？

• 原子性

  • undo log

• 隔离性

  • MVCC+锁

• 持久性

  • redo log

• 一致性

  • 上述仨

并发事务会引发什么问题？

• 脏读

  • 读取到未提交事务的数据

• 不可重复读

  • 多次读数据不一样

• 幻读

  • 多次读条数不一样

事务的隔离级别？

• 读未提交

  • 可能会脏读+不可重复度+幻读

• 读提交

  • 可能会不可重复读+幻读

• 可重复读

  • 可能会幻读

• 串行化

  • 不可能遇见问题

Read View在MVCC工作原理？

• 四个字段

• 隐藏列

  • trx_id

    • 当发生变化时，该事务的事务 id 记录在 trx_id 隐藏列里

  • roll_pointer

    • 指向旧版本的记录，方便undo log回溯

• 根据trx_id判断

  • trx_id 值小于 Read View 中的 min_trx_id

    • 该版本的记录对当前事务可见

  • trx_id 值大于 Read View 中的 max_trx_id

    • 该版本的记录对当前事务不可见

  • 在这之间

    • 看在不在m_ids中

      • 在

        • 不可见

      • 不在

        • 可见

• 这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

读提交是如何工作的？

• 执行语句生成一个Read View

可重复读是如何工作的？

• 启动事务生成一个Read View

幻读如何解决

• 快照读

  • MVCC解决幻读

• 当前读

  • next-key lock（记录锁+间隙锁）方式解决了幻读

    • 加锁直接就阻塞了

可重复度无法完全解决幻读

• 场景一

  • 事务A查不到记录，事务B插入，事务A直接update，可以更新成功

• 场景二

  • 事务A使用快照读查不到，事务B插入，事务A使用当前读能查到

• 解决方式

  • 开启事务后迅速update，插入next-key锁

锁

全局锁

• 使用

  • flush tables with read lock

    • unlock tables

• 只能查询

  • 主要用于数据库的全库逻辑备份

• 优化

  • 可重复读的隔离级别

    • 备份数据库之前先开启事务

表级锁

• 表锁

  • 表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作

  • 尽量避免，颗粒度太大，使用InnoDB的行级锁

• 元数据锁

  • 读锁和写锁

    • 写锁优先级高于读锁

  • 在进行select和变更会自动添加，事务提交释放

• 意向锁

  • 意向锁的目的是为了快速判断表里是否有记录被加锁

• AUTO-INC锁

  • 保证自增字段可以正确自增，语句执行后才释放

  • 轻量级锁：值自增后锁释放，但可能会主从冲突

  • 当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题

行级锁

• Record Lock

  • 记录锁，仅仅把一条记录锁上

    • 无法防止插入(非唯一索引)

  • 分为S型和X型

    • 共享锁和独占锁

• Gap Lock

  • 间隙锁，锁定一个范围，但是不包含记录本身

  • 只存在于可重复读隔离级别

  • 目的是为了解决可重复读隔离级别下幻读的现象

• Next-Key Lock

  • 锁定一个范围，并且锁定记录本身

• 插入意向锁

  • 当语句被插入间隙锁后，如果还要插入语句，就会进入阻塞状态，而在这个期间就会有插入意向锁

MySQL加锁方式

• 加锁的对象是索引，加锁的基本单位是 next-key lock

  • 可能会退化

  • 加锁的本质是避免幻读

  • 插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置的下一条记录的索引上有间隙锁，才会发生阻塞

• 加锁的SQL语句

  • select … lock in share mode;

  • select … for update;

  • update table … where id = 1;

  • delete from table where id = 1;

• 行级锁的种类

  • 读已提交隔离级别

    • 记录锁

  • 可重复度隔离级别

    • 记录锁+间隙锁

• MySQL加行级锁

  • 唯一索引等值查询

    • 存在

      • 该记录索引的锁退化为记录锁

    • 不存在

      • 该记录索引的锁退化为间隙锁

  • 唯一索引范围查询

    • 大于等于且存在

      • 该记录索引的锁退化为记录锁

    • 小于或者小于等于

      • 存在

        • 小于

          • 该记录索引的锁退化为间隙锁

        • 小于等于

          • 不退化

      • 不存在

        • 该记录索引的锁退化为间隙锁

  • 非唯一索引等值查询

    • 存在

      • 一定存在索引值相同的情况，因此需要全盘扫描

      • 对扫描到的二级索引记录加的是 next-key 锁

      • 在符合查询条件的记录的主键索引上加记录锁

      • 对第一个不符合条件的二级索引会退化成间隙锁

        • 避免幻读

    • 不存在

      • 扫描到第一条不符合条件的二级索引记录
        该二级索引的 next-key 锁会退化成X型间隙锁

      • 不存在查询条件的记录，不会对主键索引加锁

  • 非唯一索引范围查询

    • 不会进行退化

  • 没有加索引的查询

    • 把整个表都锁住了

  • MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读

死锁

• 两个事务同时select … for update申请间隙锁导致死锁

• 间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁

• 而插入意向锁本质上是一个特殊的间隙锁，两个事物不能同时有插入意向锁和间隙锁，因此死锁

• 避免死锁

  • 设置事务等待锁的超时时间，超时回滚

  • 开启主动死锁检测，自动回滚

隐式锁

• 当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁

• 特殊情况会转换为显示锁

  • 记录之间加有间隙锁

    • 生成插入意向锁，等另外一个事务释放间隙锁

  • Insert 的记录和已有记录存在唯一键冲突

    • 给主键/唯一二级索引加上S型记录锁

内存

为什么要有Buffer Pool

• 每次从磁盘找数据效率太低，弄个缓存提升效率

Buffer Pool大小

• 默认是128MB

Buffer Pool存储内容

• 以16KB的页为单位

• 索引页、数据页、undo页、插入索引页、锁信息、哈希

如何管理Buffer Pool

• InnoDB 为每一个缓存页都创建了一个控制块

  • 缓存页的表空间、页号、缓存页地址、链表节点

• 使用一个Free链表，把空闲缓存页链接到一起

• 当需要从磁盘加载一个页到Buffer Pool时，就取一个空闲页，填好信息，移出链表

管理脏页

• 使用一个Flush链表，把脏页管理起来

提高命中率

• 预读失效

  • 新增一段old节点，预读会放到old中，访问了才放到young中

• Buffer Pool污染

  • 提高进入young的门槛

    • 在old区域停留一定时间才能放到young中

脏页刷新时机

• redo log满了

• Buffer Pool空间不足

• MySQL空闲

• MySQL正常关闭

日志

undo log(回滚日志)

• 用于事务回滚和MVCC，记录事务更新前信息

  • 实现原子性

redo log(重做日志)

• 用于掉电故障恢复，记录事务更新后信息

  • 实现持久化

• 为什么需要它，而不是直接写磁盘？

  • 实现事务的持久性，保证crash-safe

  • 将写操作从随机写变成了顺序写

• redo log刷盘时机？

  • MySQL正常关闭

  • 缓冲区数据大于最大限度一半时

  • 后台线程每秒刷新一次

  • 事务提交时

• redo log写满了怎么办？

  • 存储引擎中存在重做日志文件组，其中包含2个重做日志文件组ib_logfile0和ib_logfile1

  • 以循环写的方式进行写入，刷新进去就剔除

  • 如果写满了，MySQL会阻塞

binlog(归档日志)

• 用于数据的备份和主从复制

• binlog和redo log的区别？

  • 适用对象不同

    • binlog是server层的日志

    • redo log是innodb专属

  • 文件格式不同

    • binlog

      • STATEMENT

        • 每一条修改数据的 SQL 都会被记录到 binlog 中，恢复再执行，now等函数会导致数据不一致

      • ROW

        • 记录行数据最终被修改成什么样了，会导致文件非常大

      • MIXED

        • 结合上述两种模式，根据不同情况自动使用

    • redo log

      • 记录物理日志，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新

  • 写入方式不同

    • binlog是追加写

      • 写满一个文件就换一个文件接着写

    • redo log是循环写

      • 日志大小固定，写满就重新开始

  • 用途不同

    • binlog用来备份恢复和主从复制

    • redo log进行掉电故障恢复

• 主从复制流程

  • 写入binlog

    • 主库把数据进行写入binlog

  • 同步binlog

    • 从库会创建一个线程接收主库日志，写入到中继日志

  • 回放binlog

    • 从库会创建线程读取中继日志，回放日志信息，实现主从一致

• 主从复制的模型

  • 同步复制

    • 主库要等待所有从库响应后再和客户端响应，一般不用

  • 异步复制

    • 主库不等待从库响应，默认用的是这个，但主库宕机数据就会发生丢失

  • 半同步复制

    • 主库等待任意一个从库响应就可以返回客户端，这样主库宕机也有一个从库有信息

• binlog刷盘时机？

  • 事务执行中把日志写到binlog cache，事务提交后刷新

  • 一个事务只能有一个线程执行，每个线程都有binlog cache

两阶段提交

• 为什么需要两阶段提交

  • 事务提交后，redo log和binlog都要持久化到磁盘

  • 如果MySQL宕机

    • 只提交redo log，那么从库数据会无法更新

    • 只提交binlog，那么主库数据会无法更新

  • 解决方式

    • 两阶段提交把单个事务的提交拆分成了 2 个阶段，准备和提交

• 具体过程

  • prepare 阶段

    • 将内部 XA 事务的 ID写入到redo log中

    • 将 redo log 持久化到磁盘

    • 更改redo log状态为prepare

  • commit 阶段

    • 将内部 XA 事务的 ID写入到binlog中

    • 将 binlog 持久化到磁盘

    • 更改redo log状态为commit

• 如果异常重启会怎么样？

  • MySQL重启后会扫描redo log文件

  • 碰到prepare状态，就去看binlog中是否含有该XID

    • 如果没有，就说明binlog没刷盘，回滚事务

    • 如果有，说明只是没有提交，把事务提交即可

  • 两阶段提交是以 binlog 写成功为事务提交成功的标识

• 两阶段提交存在的问题

  • 磁盘 I/O 次数高

    • 每次事务提交，两个log都要和磁盘交互

  • 锁竞争激烈

    • 为了保证两个日志的提交顺序一致，要加锁

  • 解决方式

    • binlog组提交

      • 基本思路

        • 当有多个binlog要提交，合并为一组提交

      • 具体操作

        • flush阶段

          • 把多个事务按binlog顺序写入文件

        • sync阶段

          • 进行fsync操作，把文件刷新到磁盘

        • commit阶段

          • 对各个事务按顺序修改commit状态

      • 优化

        • 每个阶段用队列进行管理，这样锁只需要管理队列

    • redo log组提交

      • 备注

        • MySQL5.7版本开始才有redo log组提交的概念，在5.6版本中，redo log各自在prepare就完成了

        • 在MySQL5.7版本中，把redo log的prepare刷盘操作延迟到了commit的flush阶段

      • 具体操作

        • flush阶段

          • leader领导follower进行redo log刷盘

          • 再按照顺序写入binlog文件，不刷盘

        • sync阶段

          • 进行等待，多等几组数据一起进行刷盘

        • commit阶段

          • 修改redo log的状态

MySQL磁盘IO很高

• 原因

  • 事务提交时，需要将redo log和binlog刷新到磁盘，需要进行磁盘IO

• 解决方式

  • 延迟redo log和binlog的刷盘时机，减少IO次数

    • 设置组：延迟binlog的刷盘时机，多等一些数据一起进行刷盘

    • 将 sync_binlog 设置为大于 1 的值，多等几个事务一起刷盘

    • redo log buffer里的redo log写到redo log文件，让操作系统进行刷盘

  • 如果掉电就可能丢数据 page cache不安全

索引

面试题

• 索引底层使用了什么数据结构和算法？

  • 考虑IO次数和时间复杂度

• 为什么MySQL InnoDB选择B+树作为索引数据结构？

  • 更矮胖

  • 插入删除简单

  • 便于范围查找

• 什么时候适用索引？

• 什么时候不需要创建索引？

• 什么情况下索引会失效？

• 有什么优化索引的方法？

概念

• 索引是数据的目录

分类

• 数据结构分类

  • B+树索引

    • 优势

      • 相比于 B 树二叉树或 Hash 索引结构

    • 如何进行聚簇索引和二级索引

  • Hash索引

  • Full-text索引

• 物理存储分类

  • 聚簇索引

  • 二级索引

    • 回表

• 字段特性分类

  • 主键索引

  • 唯一索引

  • 前缀索引

• 字段个数分类

  • 单列索引

  • 联合索引

    • 最左匹配原则

    • 全局有序和局部有序

    • 优化：select * from order where status = 1 order by create_time asc(文件排序 filesort)

创建索引的条件

• 什么时候适用索引？

  • 唯一性限制

  • where语句调用频繁

  • order by和group by调用频繁

• 什么时候不需要创建索引？

  • 不需要where、order by、group by

  • 重复数据多

  • 表中数据少

  • 更新频繁的数据

索引失效

• 失效的条件

  • 使用左或者左右模糊匹配：like %xx，like %xx%

    • B+树是按照索引值有序存储，前缀匹配

  • 对索引列进行计算，函数等操作

    • 索引使用的是原始值，不是计算后的值

    • 隐式类型转换

      • 索引值不能变，字符串->数字

  • 联合索引不遵循最左匹配原则

    • 可能全是索引字段导致直接查询联合索引树进行覆盖索引

    • 索引下推

      • 过滤不符合要求的，减少回表次数

  • where后的条件，or前是索引列，or后不是

优化索引

• 前缀索引优化

  • 使用某个字段中字符串的前几个字符建立索引

  • 对于较长的字符索引进行了更进一步的优化

• 覆盖索引优化

  • 建立联合索引，这样可以避免回表带来的IO性能损耗

• 主键索引最好自增

  • 新增一个节点直接向后插入，避免页分裂

• 索引最好为NOT NULL

  • 索引列存在空会导致优化器进行优化难度增加

  • NULL无意义，但是会占据额外的存储空间

• 防止索引失效

适合索引的数据结构

• 要求

  • 尽可能少的磁盘IO

  • 进行高效的单点查找和范围查找

• 数据结构

  • 二分查找

  • 二叉搜索树

  • AVL树

  • 红黑树

  • B树

    • 用户的记录数据的大小很有可能远远超过了索引数据，因此需要更多次磁盘IO找到有效数据

    • 使用 B 树来做范围查询，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题

  • B+树

    • 单点查询

      • B树可能可以直接就找到信息，但是浮动大

      • B+树相较而言更加矮胖，磁盘IO次数少

    • 插入和删除效率

      • B树操作可能需要大变结构

      • B+树操作只需要操作叶子节点

    • 范围查询

      • B树没有链表，需要多次IO进行范围查询

      • B+树叶子节点使用链表连接，直接横向搜索

count

• 按照性能排序

  • count(*) = count(1) > count(主键字段) > count(字段)

• count(主键字段)执行过程

• count(1)和count(*)执行过程

• count(字段)执行过程

• 优先使用二级索引，其次聚簇索引

• 优化count(*)

  • 使用近似值explain select…

  • 额外维护一个统计次数的表

因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁