Mysql

2025-09-08 14:00:25   7  举报





AI智能生成

MySQL是一个高效且广泛使用的开源关系数据库管理系统（RDBMS），它被设计用来处理大型数据集并支持高性能的应用程序。其核心内容涉及数据存储、查询执行、事务处理、并发控制和数据恢复机制。文件类型主要以SQL脚本为主，这些脚本定义了数据库的结构和内容。MySQL以其跨平台能力、高速性能以及对于各个主流操作系统的支持而著称，如Linux、Windows和Mac OS X等。其强大的编程功能和可伸缩性使其成为网站、电子商务以及各种数据驱动应用的首选数据库解决方案之一。

面试技巧

MySQL

InnoDB引擎

事务原理

作者其他创作

大纲/内容

索引与性能

分类

B+树索引

聚簇索引（又称一级索引）

一般是主键索引，或非空唯一索引，或者是引擎自身生成rowid所形成的索引

聚集索引的叶子节点就是数据页，数据页上存放的是完整的每行记录

隐藏的必要列

row_id

非必要。若有主键或非NULL唯一索引不生成

trx_id

修改此数据的事务id。每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列

roll_pointer

每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

优点特性

通过过聚集索引能获取完整的整行数据

对于主键的排序查找和范围查找速度非常快

非聚簇索引

普通索引（又称辅助索引、二级索引）

联合索引/复合索引

索引结构图

唯一索引

哈希索引

1.哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列的值计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
2.InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式

优点特性

通过hash算法推导出记录的地址，直接一次就能查到数据

只能用来搜索等值的查询

全文索引

将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术

倒排索引

将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序

性能优化

执行计划

特殊概念

回表

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录

覆盖索引

从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录(回表)

优点：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

注意：辅覆盖索引不是索引的一种，而是一种现象

引擎

三大特性

双写机制

MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个页数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复

Buffer Pool

自适应Hash索引

InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引值经常用，那么就认为是热数据，然后内部自己创建一个hash索引。效率优于B+树索引的3到4次的IO

记录结构

数据页

结构

一个InnoDB数据页的存储空间大致被划分成了7个部分

字段解释

Infimum + Supremum

数据页中两个虚拟的行记录，通过此可以找到数据页中最小记录和最大记录，数据页中间数据是依靠每条真实记录中的Next record进行单向关联

free space

空闲空间

User Records

每插入一条记录，都会从Free Space部分申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，就需要去申请新的页.当记录被删除时，则会修改记录头信息中的delete_mask为1，记录还在页中，在磁盘上。不立即移除是因为移除之后其他记录在磁盘上重新排列需要消耗性能。所有被删除掉的记录都会组成一个垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉

Page Directory

一个数据页中查找指定主键值的记录的过程分为两步：通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。通过记录的next_record属性遍历该槽所在的组中的各个记录

InnoDB的改进是，为页中的记录再制作了一个目录，制作过程是这样的：
1、将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
2、每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
3、将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
4、每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在1到8 条之间，剩下的分组中记录的条数范围只能在是 4到8 条之间

Page Header

数据页中存储的记录的状态信息，存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽，垃圾链表的存储位置等等

File Header

描述了一些针对各种页都通用的一些信息，比方说页的类型（Undo日志页、段信息节点、InsertBuffer空闲列表、Insert Buffer位图、系统页、事务系统数据、表空间头部信息、扩展描述页、溢出页、索引页），这个页的编号是多少，它的上一个页、下一个页是谁，页的校验和

File Trailer

和File Header配合使用，用于校验页的完整性

存储结构

行格式

概念

通常以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式被称为行格式

类型

Compact、Redundant、Dynamic（系统默认）和Compressed

字段解释

变长字段长度列表

变长字段中存储多少字节的数据是不固定的，在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来

NULL值列表

每个允许存储NULL的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL

记录头信息

由固定的5个字节组成。5个字节也就是40个二进制位

隐藏列信息

DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录

DB_TRX_ID：必须，6字节，表示事务ID

DB_ROLL_PTR：必须，7字节，表示回滚指针

真实数据列

数据溢出

Compact和Redundant只存储该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中。Dynamic和Compressed是将所有的字节都存储到其他页面中，只在存储真实数据处记录其他页面的地址

磁盘/内存数据交换

将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。

表空间

概念

表空间是一个抽象的概念，系统表空间对应一个或多个实际文件；独立表空间对应一个名为表名.ibd的实际文件表空间中的每一个页都对应着一个页号（FIL_PAGE_OFFSET），这个页号由4个字节组成，也就是32个比特位，所以一个表空间最多可以拥有2³²个页，如果按照页的默认大小16KB来算，一个表空间最多支持64TB的数据

分类

独立表空间

既可以在数据目录，也可以独立于数据目录之外，存储单张表的索引和数据文件，以ibd形式，不可以跨库

系统表空间

在数据目录中，存储多张表的索引和数据文件，以ibdata1，2，3的形式，可以跨多个数据库使用

整个MySQL进程只有一个系统表空间，记录系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面，相当于是表空间之首，所以它的表空间 ID（Space ID）是0

双写缓冲区

在InnoDB将页写到InnoDB数据文件的正确位置之前，InnoDB将页从缓冲池写到该区域（双写缓冲区）类似于做个数据备份

系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页面被称为Doublewrite buffer，也就是双写缓冲区

InnoDB的页大小一般是16KB，而操作系统写文件是以4KB作为单位的，系统写磁盘无法保证页的原子性

与redolog的比较

redolog：恢复数据需要原数据页是完整的，redolog不包含数据页丢失的原数据
双写缓冲区：在数据页被破坏时，用整个双写缓冲区的数据页替换被损坏的数据页

InnoDB数据字典

元数据，记录表空间信息。例如某个表属于哪个表空间，有多少列，多少字段，文件路径等

Buffer Pool

概念 | 结构

InnoDB为了缓存磁盘中的页，在MySQL启动时就向操作系统申请了一片连续的内存，叫做Buffer Pool，默认只有8M大小，bp大于1G可设置多个独立pool，支持多线程并发

InnoDB为每一个缓存页创建一控制块，包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息等。控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边

主要作用

数据都是以页的形式存放在表空间中，即存储在磁盘上的。但磁盘速度慢，当需要访问某个页的数据时，会把完整的页的数据全部加载到内存中，读写访问后并不把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了

缓存页的哈希处理

用表空间号 + 页号作为key，缓存页控制块作为value创建一个哈希表，用于判断该页在不在Buffer Pool中

free链表

空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作free链表（或者说空闲链表）

每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除，表示该缓存页已经被使用了

flush链表

存储脏页的链表，修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页

LRU(Least Recently Used)链表

当Buffer Pool中不再有空闲的缓存页时，此链表是按照最近最少使用的原则去淘汰缓存页的。只要使用到某个缓存页，就把该缓存页调整到LRU链表的头部，这样LRU链表尾部就是最近最少使用的缓存页

预读

预先加载后续页数据到bufferpool

线性预读

顺序访问当前区56（默认）页，自动异步加载下一个区全部数据页

随机预读

缓存了某个区的13个连续的页面（不限顺序读取），都会异步读取本区中所有其他的页面到Buffer Pool

change buffer

作用：在进行DML(写)操作(insert/update/delete)时，如果请求的是辅助索引（非唯一键索引）没有在缓冲池中时，并不会立刻将磁盘页加载到缓冲池，而是在Change Buffer记录缓冲变更，等未来数据被读取时，再将数据合并恢复到Buffer Pool中

锁

锁类型

锁定读（LBCC）

当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录。select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁/独占锁)、delete (排他锁)、串行化事务隔离级别

共享锁(Shared Locks)，简称S锁。在事务要读取记录时，先获取该记录的S锁
独占锁(Exclusive Locks)，简称X锁。在事务要改动记录时，先获取该记录的X锁

锁的粒度

行锁

针对记录的，也就是只对单个数据行有影响

表锁

针对整张表记录

意向锁

概念/作用

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录

读数据表数据时，先给表上IS锁（表级锁），然后再给具体数据上S锁（行锁）写数据表数据时，先给表上IX锁（表级锁），然后再给具体数据上X锁（行锁）

分类

意向共享锁

Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁

意向独占锁

Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

表级别的AUTO-INC锁

为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值

间隙锁

间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁

基础知识

范式设计

范式设计与反范式设计的优缺点比较

字段数据类型选择

int/整数类型

小数类型

字符串类型

BLOB和TEXT类型

当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。【页溢出】

日期类型

datetime 存储日期范围：1001年~9999年
timestamp 存储日期范围：1970年~2038年,并且跟时区有关系。

B+树

本质：多叉查找平衡二叉树

左子树的所有的值小于根节点的值，右子树的所有的值大于或等于根节点的值

它的左右两个子树的高度差（平衡因子）的绝对值不超过1

目的：使得树的高度最低，因为树查找的效率决定于树的高度

预读特性

按页将磁盘数据预读到内存

目的：尽可能的多让数据顺序读写，少让数据随机读写

与B树的区别

B树数据存于所有节点，B+树数据只存叶子节点，非叶子节点只存索引，这样可以增大存储量

B+树叶子节点之间通过指针相连，有序排列。B树没有

作用总结

在磁盘设备上，通过B+树可以有效的存储数据；

所有记录都存储在叶子节点上，非叶子(non-leaf)存储索引(keys)信息；而且记录按照索引列的值由小到大排好了序

B+树含有非常高的扇出（fanout），通常超过100，在查找一个记录时，可以有效的减少IO操作

扇出：是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针

事务底层

事务特性 - ACID

事务属性最终目标

一致性（consistency）

一致性是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏

保证事务一致性的措施/条件

原子性（atomicity）

一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败

undolog保证原子性

隔离性（isolation）

一个事务的执行不能被其他事务干扰

读写锁 + MVCC保证隔离性

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中

redolog + 双写缓冲区保证持久性

事务并发问题

脏读

一个事务读取到了另外一个事务修改但未提交的数据

不可重复读

事务内相同的记录被检索两次，且两次得到的结果不同

幻读

在事务执行过程中，相同的查询条件查询出的数据量不一致

与不可重读读的区别在于不可重复读发生于其他事务对数据的修改，幻读是其他事务插入新数据引起的问题

隔离级别

未提交读（READ UNCOMMITTED）

可能发生脏读、不可重复读和幻读问题

已提交读（READ COMMITTED）

可能发生不可重复读和幻读问题，但是不可以发生脏读问题

每次读取数据前都生成一个ReadView

可重复读（REPEATABLE READ）

可能发生幻读问题，但是不可以发生脏读和不可重复读的问题

在第一次读取数据时生成一个ReadView

可串行化（SERIALIZABLE）

各种问题都不可以发生，但性能极低，因此一般公司默认设置是可重复读

MVCC（多版本并发控制）

目的

提高数据库的并发性能

同一行数据平时发生读写请求时，会上锁阻塞住。但MVCC用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁（读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁）

版本链

隐藏列

trx_id

事务id

生成机制：内存有一全局变量，加一递增，占据8字节但只用了6字节，超过2^48就重置为零（与row_id类似，因此mysql自增主键有限制），当此值为256的倍数时刷磁盘保存。系统重启时读此磁盘数据，并加上256赋值给全局变量

roll_pointer

旧版本指针

概念

记录每次更新后，都会将旧值放到一条undo日志中，算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链

版本链图

ReadView（快照读）

概念

每当有一个事务，mysql创建一快照，记录当前事务之前有哪些事务处于活跃状态，有哪些事务的历史记录能看到

重要参数

m_ids

表示在生成ReadView时当前系统中活跃的读写事务的事务id列表

low_limit_id

未来事务零界点，当前快照中最后开始的事务

up_limit_id

当前活跃事务的最小id

creator_trx_id

表示生成该ReadView的事务的事务id

min_trx_id

表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值

max_trx_id

表示生成ReadView时系统中应该分配给下一个事务的id值

已存在事务最大id（没有事务提交，就是m_ids中的最大值） + 1

比较规则，判定数据是否可见

trx_id = creator_trx_id，可见

trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问

trx_id < up_limit_id，可见

被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问

trx_id > low_limit_id，不可见

up_limit_id <= trx_id < low_limit_id

trx_id在m_ids中，不可见

trx_id不在m_ids中，可见

主要日志

redo log

概念/作用

防止因突然故障导致buffer pool中已提交的事务数据丢失，需要在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘。由于刷新一个完整的数据页太浪费且随机IO刷起来比较慢，因此只需要把修改了哪些内容记录就行，此为redo日志

优点

redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小

redo日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO