【数据库】MySQL基础知识全解
系列综述:
💞目的:本系列是个人整理为了秋招面试
的,整理期间苛求每个知识点,平衡理解简易度与深入程度。
🥰来源:材料主要源于拓跋阿秀、小林coding等大佬博客进行的,每个知识点的修正和深入主要参考各平台大佬的文章,其中也可能含有少量的个人实验自证。
🤭结语:如果有帮到你的地方,就点个赞和关注一下呗,谢谢🎈🎄🌷!!!
🌈【C++】秋招&实习面经汇总篇
文章目录
- 基本篇
- 索引篇
- 事务篇
- 锁篇
- 日志篇
- 内存篇(Buffer Pool)
- 其他之其他
- 参考博客
😊点此到文末惊喜↩︎
基本篇
- 关系型数据库
- 定义:是一种基于
ACID事务模型
的数据库,采用表格形式
存储数据,通过表格中的关系连接
实现数据的查询和管理。- 模型:采用ACID事务模型,强调数据的完整性和一致性
- 数据存储:采用表格形式存储数据,每一行表示一条记录,每一列表示一个属性,数据结构清晰,易于理解和维护。
- 数据管理:通过表格中的关系连接进行数据查询和管理,主要有索引、视图、触发器等技术
- 定义:是一种基于
- 非关系型数据库(NoSQL)
- 定义:是一种基于
BASE模型
的数据库,采用多种形式
存储数据,通过分布式计算技术
实现高可扩展性、高性能和高可用性- 模型:采用BASE模型,强调高可用性和性能的平衡
- 数据存储:采用多种形式的数据存储模型,例如键值对、文档、列族、图形等
- 数据管理:通过分布式计算技术,实现高可扩展性、高性能和高可用性
- 定义:是一种基于
- MySQL 执行一条 SQL语句的流程 / MySQL的内部构造
- 客户端:发送请求与连接器进行 TCP 三次握手建立
长连接
- 服务层(Server)
- 连接器:主要用于连接管理,如连接的创建、维护、销毁、身份验证和权限控制等问题
- 查询缓存:存在则返回结果,不存在则继续执行
- 解析器:对SQL语句进行进行词法分析、语法分析,并构建语法树
- 预处理器:检查 SQL 查询语句中的表或者字段是否存在,并展开
*
- 优化器:确定SQL语句的执行方案、选择索引
- 执行器:根据SQL语句读取存储引擎数据,返回结果给客户端
- 存储引擎:负责数据的存储和读写。是插件式的可以使用多种引擎,其中InnoDB是MySQL 默认的存储引擎
- 客户端:发送请求与连接器进行 TCP 三次握手建立
- 客户端和数据库的TCP三次握手连接方式
- 长连接
- 问题:减少连接建立和释放的开销,但可能会占用过多内存被系统强制杀掉。
- 解决:通过TCP心跳机制进行连接的维护。
// 短连接 连接 mysql 服务(TCP 三次握手) 执行sql 断开 mysql 服务(TCP 四次挥手)// 长连接 连接 mysql 服务(TCP 三次握手) 执行sql 执行sql 执行sql .... 断开 mysql 服务(TCP 四次挥手)
- 长连接
- 数据库文件的存放
- MySQL 存储的行为是由存储引擎实现的,在这里只讨论MySQL的默认数据库
- 数据库文件存在于
/var/lib/mysql/ 自定义数据库名
目录下- db.opt:存储当前数据库的默认字符集和字符校验规则。
- 自定义表名.frm:存储每个表的表结构定义
- 自定义表名.ibd:参数 innodb_file_per_table为1存放在该独占表空间文件中,为0则存储在该共享表空间文件中
- 表空间文件
.ibd
的结构- 段(segment)
- 索引段:存放 B + 树的非叶子节点的区的集合;
- 数据段:存放 B + 树的叶子节点的区的集合;
- 回滚段:存放的是回滚数据的区的集合
- 区(extent)
- 将连续的64个页划分到一个区,链表中相邻的页的物理位置也相邻
- 物理相邻的页可以使用顺序 I/O,提高范围查询的扫描性能
- 页(page)
- innoDB数据库一次读写的单位是页(16KB)
- 行(row)
- 数据库表中的记录都是按行(row)进行存放的,每行记录根据不同的行格式,有不同的存储结构。
- 段(segment)
- compact行格式是如何存储数据的?
- 控制部分
- 变长字段长度列表:逆序存储变长字段实际存储的数据的长度
- NULL 值列表(非必须):逆序存储代表列是否为NULL的二进制位
- 记录头信息
- delete_mask :标识此条数据是否被逻辑删除(物理上还存在)
- next_record:下一条记录的位置。指向的是下一条记录的控制部分和数据部分之间的位置(方便向左读控制部分,向右读数据部分,提高cache命中率)
- record_type:表示当前记录的类型,0表示普通记录,1表示B+树非叶子节点记录,2表示最小记录,3表示最大记录
- 数据部分
- row_id
- 如果我们建表的时候指定了主键或者唯一约束列,那么就没有 row_id 隐藏字段了。如果既没有指定主键,又没有唯一约束,那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的,占用 6 个字节。
- trx_id
- 事务id,表示这个数据是由哪个事务生成的。 trx_id是必需的,占用 6 个字节。
- roll_pointer
- 这条记录上一个版本的指针。roll_pointer 是必需的,占用 7 个字节。
- 控制部分
- 在MySQL中varchar(n) 字段类型
- 组成:存储头部(变长字段长度列表 + NULL值列表)+ 数据部分
- 该字段类型一行最大为65535字节容量,其中n为字符个数,具体大小与字符集有关
- MySQL 的行溢出
- 原因
- MySQL 中磁盘和内存交互的基本单位是页,一个页的大小一般是 16KB
- 一个页无法存储一个大对象的一条记录,会发生行溢出
- 解决:将溢出的数据存储到溢出页中,使用原页中的20字节存储溢出页地址
- 原因
索引篇
- 索引
- 定义:提升查询性能的数据结构。
- 优点和缺点
- 加速查询,适合
WHERE 查询的字段
、GROUP BY 和 ORDER BY 作用的字段
- 增加数据处理的灵活性,将随机IO变为顺序IO(本质是数据,索引是形式)
- 索引的创建和维护需要耗费时间,所以经常更新的字段不适合建立索引
- 加速查询,适合
- 索引分类
- 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类:聚集索引(聚簇索引)、非聚集索引(辅助索引)
- 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类:单列索引、联合索引。
- 其他:
- 每一种存储引擎支持的索引类型不一定相同
- InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎
- B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。
- B+Tree 数据结构(MySQL InnoDB 的默认索引数据结构)
- 定义:B+树可以分两部分进行理解
- 由
内部结点组成的多路平衡查找树
用于快速查找
操作 - 由
叶子结点组成的按主键顺序的双链表
可以进行高效的数据的增删操作
和基于范围的顺序查找
- 由
- 多路平衡查找树
多路
表示是一棵多叉树(m>2),每个非叶子结点由两部分组成按主键顺序的子节点索引链表
:每个子节点索引指向一个子节点,且索引结点键值等于索引子节点的最小键值(类似目录查找)。最大和最小键值
:主要用于快速定位和过滤查询请求的。
平衡
表示B+树的每个子树的高度是相等的,尽可能“矮胖”查找
表示B+树的快速查找能力较强。相比B树非叶子结点不存放数据
:可以存放更多的索引,使得树更加“矮胖”,极大减少比较耗时的I/O操作。- 搜索复杂度为O(logdN),当最大分支数d大于100时,千万级数据量的查询操作也只需做
3~4
次的磁盘 I/O 操作,即树的高度只有3~4层
。
- 双链表
- 数据记录的快速定位:一个叶子结点通常存储多个数据记录的物理地址(数据存在在数据页中),页目录由槽组成,每个槽索引数据记录分组后的最后一条。数据记录分组是有序的,所以通过二分法可以进行快速查找。
- 提高磁盘IO效率:数据页是磁盘IO的基本单位(默认为16KB),通常与叶子结点一一对应,但可以根据业务场景进行调节
- 顺序查找和增删快:B+Tree 的层内结点均
按指定键顺序进行双链表链接
。可以高效满足数据的增删操作
和基于范围的顺序查找
- 其他:数据记录的索引和存储
- B+树索引中,每个叶子节点存储一个或多个数据记录索引,而每个数据记录实际上是存储在数据页中的。
- 一个数据页中包含了多条数据记录,每条数据记录都对应着B+树中的一个叶子节点。当进行索引查询时,InnoDB会在B+树的叶子节点中查找对应的数据记录,而这些数据记录实际上都存储在数据页中。
- 通过将多个数据记录存储在一个数据页中InnoDB能够更高效地利用磁盘和内存空间,提高数据存储和查询性能。
- 构建位置:在磁盘上构建B+树时,它的节点通常被分成多个磁盘块,每个磁盘块可以被读写到内存中进行操作,因此磁盘I/O是构建和查询B+树的瓶颈。为了提高查询性能,B+树通常使用缓存机制,将部分磁盘块缓存在内存中,以便在查询时快速访问。需要注意的是,尽管B+树通常是在磁盘中构建的,但是在某些情况下,也可以将B+树存储在内存中,用于处理小规模数据或需要快速更新的数据结构。
- 定义:B+树可以分两部分进行理解
- 按存储方式划分的索引:聚集索引(聚簇索引)和非聚集索引(辅助索引)
- 聚集索引
- 数据存储:聚集索引的索引行和数据行存储在一起,在一个表中只能有一个聚集索引
- 性能:增删慢,查询快
- 非聚集索引
- 数据存储:非聚集索引的索引行是单独存储的,需要通过索引找到数据行
- 性能:增删快,查询慢
- 聚集索引
- 按字段特性分类
- 主键索引
- 值唯一的聚簇索引,用于索引主键字段。一张表只能有一个主键索引
- 查询性能高:值唯一,并按主键自动排序,可以使用二分法进行查找
- 唯一索引
- 值唯一的非聚簇索引,可以包含null值
- 普通索引
- 用于数据库的查询加速的非聚集索引,并且值可以重复
- 前缀索引
- 支持基于前缀搜索的模糊匹配, 空间复杂度低,搜索效率高。
- 主键索引
- 按字段个数分类
- 单列索引:建立在单列上的索引
- 联合索引:建立在多列上的索引,遵循最左匹配原则
- 最左匹配原则:查询条件中没有联合索引的第一个字段,则索引不会被使用。遇到范围查询(如 >、<)的时候会停止匹配,区间匹配需要扫描整个索引,而且这种操作代价相当高,因此会使用其他方式。
- 下推优化:在联合索引遍历过程中,对联合索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数
- 区分度优化:建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。
- 索引优化的方法
- 前缀索引优化
- 减小索引字段大小,但order by 就无法使用前缀索引,也无法把前缀索引用作覆盖索引。
- 覆盖索引优化
- 直接操作已索引字段,减少读取数据页的磁盘I/O操作
- 自增主键索引
- 在尾部追加式的插入,可以避免索引表的分裂和维护,提高插入性能
- 防止索引失效
- 前缀索引优化
- 索引列要设置为 NOT NULL 约束,否则引擎会放弃索引进行全表扫描
- MySQL 索引使用的注意事项
- MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度
- 不要在列上使用函数和计算,这将导致索引失效而进行全表扫描
- 尽量避免使用 != 或 not in或 <> 等否定操作符,都会导致索引失效而进行全表扫描
- 尽量避免使用or进行条件连接,会导致索引失效而进行全表扫描
- MySQL 只能使用一个索引,多列索引可以使用复合索引,复合索引遵守最左匹配原则,不是按照索引的最左列开始查找,则无法使用索引。
- 索引不会包含有NULL值的列,若复合索引中有列含有NULL值,会导致该索引失效。尽量不要让字段默认值为NULL
- 查询条件左右两侧类型不匹配的时候会发生隐式转换,隐式转换带来的影响就是可能导致索引失效而进行全表扫描
like "%value"
匹配值第一个为%,会导致全表查询- 删除长期未用的索引,避免检索的额外损耗
事务篇
- 事务的四个特性ACID
- 原子性(Atomicity):一个事务中的所有操作,要么全做,要么全不做。如果在中间状态发生错误会回滚到开始状态。
- 一致性(Consistency):事务执行前后,数据均处于合法的状态(完整性约束)
- 隔离性(Isolation):事务提交前,更新的数据对其他事务不可见。
- 持久性(Durability):已提交的事务的更新是持久的,发生故障可恢复
- 数据库如何保证原子性
- 实现:
Innodb 的回滚日志(undo log)
负责记录
事务执行的反向操作
- 约束:
undo log必须先于数据持久化到磁盘
(谋而后动),优先保证回滚日志的完整性
- 实现:
- 数据库如何保证一致性
- 锁机制:保证并发读写共享数据的正确性
- 触发器机制:通过约束避免违反数据一致性的修改(类似断言机制)
- 事务机制:通过事务执行的原子性和隔离性保证修改操作的一致性
- 数据库如何保证隔离性
- 使用
四种隔离级别
保证事务的隔离
- 使用
锁机制
保证数据的隔离
- 使用
MVCC(多版本并发控制
)保证并发的访问隔离
- 使用
- 数据库如何保证持久性
- undo log(回滚日志):存储
执行中事务的修改操作
,用于事务回滚的撤销操作 - redo log(重做日志):循环存储
已提交事务的修改操作
(循环区满会被覆盖),用于短时的故障恢复 - bin log (归档日志):存储
所有已提交事务的修改操作
(通常来源于重做日志),用于完整的数据恢复和主从复制
- undo log(回滚日志):存储
- 事务的四个隔离级别(自顶向下性能下降,安全性增加)
未提交读(read uncommitted)
- 概述:事务未提交,修改即可见(被其他事务读到)
- 原理:事务开始时,数据库为该事务创建一个独立工作区。对于读操作,直接从数据库中读取。对于写操作,修改到工作区中而不提交到数据库中,其他并发事务读取工作区中的数据,但是如果该事务未提交而回滚,会导致其他并发事务的脏读。
提交读(read committed)
- 概述:事务提交时,修改才可见
- 原理:事务
修改前先获取写锁
,并将修改保存到事务日志中,然后再提交,从而保证其他事务只能读取已经提交的事务所修改的数据。
可重复读(repeatable read)
- 概述:事务执行期间多次读到的相同数据是一致的,是MySQL InnoDB 引擎的默认隔离级别;
- 原理:事务通过
读写锁
共享访问数据,事务修改数据前要使用写锁,保证其他事务无法同时修改数据。事务读取数据前加一个读锁,保证其他事务无法修改数据
串行化(serializable )
- 概述:并发事务的执行结果与各事务按序单独执行结果相同(可线性化)
- 原理:MVCC可以通过为每个修改操作创建一个版本,并在读取时根据版本来确定读取的数据是否被修改过,从而实现串行化。在MVCC中,每个事务都可以看到自己开始之前的数据库状态,但是不能看到其他事务的更新。
- 事务为什么要隔离 / 并发事务执行导致的问题(严重程度自上而下)
脏读
:A事务执行更新数据操作后发生了回滚,而B事务读到了更新后的数据,但是由于A事务的回滚,导致B事务读取数据和数据库中的不一致。
不可重复读
:在一个事务内多次读取同一个数据,如果出现前后两次读到的数据不一样的情况
幻读
:一个事务多次查询某个符合条件的「记录数量」,出现前后两次查询「记录数量」不一致的情况
- 不同事务隔离级别的能力
- 在「读未提交」隔离级别下,可能发生脏读、不可重复读和幻读现象;
- 在「读提交」隔离级别下,可能发生不可重复读和幻读现象,但是不可能发生脏读现象;
- 在「可重复读」隔离级别下,可能发生幻读现象,但是不可能脏读和不可重复读现象;
- 在「串行化」隔离级别下,脏读、不可重复读和幻读现象都不可能会发生。
锁篇
- 全局锁
- 锁的生命周期:执行
flush tables with read lock
加锁,会话结束或者执行unlock tables
会自动释放 - 加锁后阻塞修改命令:
- 对数据的增删改操作,比如 insert、delete、update等语句;
- 对表结构的更改操作,比如 alter table、drop table 等语句。
- 应用场景
- 数据库的逻辑备份,备份期间只读,会影响业务。
- 可重复读隔离+MVCC:备份数据前创建快照并锁定数据库只读,其他事务执行期间使用该快照进行可重复读隔离的数据处理,最后再将快照合并到数据库中。
- 锁的生命周期:执行
- 表级锁
- 表级读写锁:读锁会阻塞写,写锁会阻塞读和写。生命周期从加锁到释放锁或会话结束,粒度较大影响并发性能
- 元数据锁:对表操作加读锁,对表结构操作加写锁。事务执行期间一直持有,直到事务提交。由于执行的写优先机制,如果出现写锁等待会阻塞其后的读操作,导致线程栈溢出,所以对表结构变更时,先将其他执行中的长事务kill掉。
- 意向锁:对表中的记录加读锁前,先对整个表加意向读锁。对表中的记录加写锁前,先对整个表加意向写锁。可以快速判断表里是否有记录被加锁。
- AUTO-INC 锁(5.1.22后变成字段锁):插入数据前,被 AUTO_INCREMENT 修饰的字段加上轻量级锁,然后给该字段赋值一个自增的值后即释放,而不需要等待整个插入语句执行完后才释放锁
- 行级锁(innoDB支持,基于索引实现)
- Record Lock:针对一条记录的读写锁
- Gap Lock:用于可重复读隔离级别解决下幻读问题,是对一个范围的读写锁
- Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身
日志篇
- MySQL的关键日志
- undo log(回滚日志):保证事务执行的原子性,主要用于事务回滚和MVCC。
- redo log(重做日志):实现了事务中的持久性,主要用于掉电等故障恢复;
- bin log (归档日志):是 Server 层生成的日志,主要用于数据备份和主从复制;
- 回滚日志undo log
- 事务回滚
- 原理
- 记录
未提交事务在数据库中的修改操作
,若发生数据库崩溃
或执行ROLLBACK语句
,通过undo log将数据库恢复到事务开始前的状态。 - 先思后做:回滚日志必须先于数据持久化到磁盘
- 记录
- 具体操作
插入
记录时,存储记录的主键
,回滚时只需要删除主键对应的记录删除
记录时,存储记录的全部内容
,回滚时将该记录再插入更新
记录时,存储记录的旧值
,回滚时再更新为旧值
- 原理
- MVCC多版本并发控制原理
- 原理:通过undo log记录的数据版本链保存事务对数据记录修改的多个版本,每个事务通过隔离级别和快照时间选择合适的版本读取。
- 具体操作
- 「读提交」隔离级别:事务执行中对同一数据的selet读都会生成一个快照读
- 「可重复读」隔离级别:整个事务执行期间使用同一个快照读(slect语句)
- 事务回滚
- 重做日志 redo log(数据结构和算法(原理和问题,解决方式))
- 基本概念:
- 原理概述:记录
已提交事务的所有修改操作
,当数据库发生故障时,系统会将重做日志中的操作重新执行,直到崩溃前的状态,最大限度地减少数据的丢失。 - crash-safe(崩溃恢复):避免内存断电导致未刷盘的数据丢失
- 原理概述:记录
- WAL (Write-Ahead Logging)技术:MySQL 的写操作并不是立刻写到磁盘上,而是先写日志,然后在合适的时间再写到磁盘上。
- undo log 和redo log的区别
- 事务提交之前发生了崩溃,重启后会通过 undo log 回滚事务,事务提交之后发生了崩溃,重启后会通过 redo log 恢复事务
- 执行流程:
- 执行SQL语句,如果在Buffer Pool中未命中缓存页,则将数据页从磁盘加载到BufferPool中
- 修改缓存页并置为脏页,同时将当前执行事务的修改操作写入redo log buffer中
- 数据库选择一个合适的时机将脏页和redo log buffer刷盘
- redo log刷盘效率
- redo log buffer 是一个循环链表构成的缓冲区,
写满时
将最早写入的记录刷盘并覆盖
- 写满阻塞问题
- 原因:如果系统并发量比较大,会导致redo log 缓冲文件经常满并要进行刷盘而导致MySQL更新操作被阻塞
- 解决方式:通过调整
innodb_log_Buffer_size 参数
增加redo log容量(默认16 MB)
- 刷盘方式:redo log刷盘是以追加方式的顺序写,磁盘执行效率高
- redo log buffer 是一个循环链表构成的缓冲区,
- redo log 刷盘时机(刷盘过多影响效率,刷盘过少影响安全)
- MySQL 正常关闭时;
- 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时,会触发落盘;
- InnoDB 的后台线程每隔 1 秒,将 redo log buffer 持久化到磁盘。
- 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘
- 基本概念:
- bin log
- 定义:MySQL 的 Server 层实现的日志,用于记录已提交事务的修改操作。
- 写入方式:binlog 是追加写,写满一个文件,就创建一个新的文件继续写,保存全部日志
- 执行流程
- 创建binlog cache:MySQL 中的每个线程都用于缓冲 binlog的 binlog cache缓存,由
binlog_cache_size 参数
控制,超过缓存大小就要暂存到磁盘。 - 提交时写入:事务执行过程中,先把修改记录写入binlog 的Server 层cache,事务提交的时候,再把 binlog cache中的完整事务执行记录写到 binlog 文件中,并清空binlog cache(一个事务的binlog不能拆开,保证从库执行的原子性)
- 层级写入:每个线程将自己 binlog cache增加事件头区分,然后由binlog dump线程写入同一个 binlog 文件,最后通过fsync,将数据持久化到磁盘
- 创建binlog cache:MySQL 中的每个线程都用于缓冲 binlog的 binlog cache缓存,由
- 主从复制机制
- 原理:主数据库将所有修改操作记录到Binlog中,从数据库请求并从主数据库获取Binlog并写入本地的中继日志relay log中,然后启动一个IO线程不断读取并更新从数据库
- 作用
- 保证从数据库和主数据库的数据一致性,可用于数据备份、灾难恢复等
- 分布式读取:写数据时只写主库,在读数据时只读从库,这样即使写请求会锁表或者锁记录,也不会影响读请求的执行
- 两阶段提交协议(two phase commit protocol,2PC)
- 定义:是一种分布式事务一致性协议,
- 原理:将事务的提交分成两个阶段
- prepare表决阶段:协调者将通知所有参与者准备提交或取消事务,在表决过程中,参与者将告知协调者自己的决策。
- commit 提交阶段:协调者将基于第一个阶段的投票结果进行决策,当且仅当所有的参与者同意提交事务协调者才通知所有的参与者提交事务,否则协调者将通知所有的参与者取消事务。参与者在接收到协调者发来的消息后将按事务提交顺序执行相应的操作。
- 作用:保证事务提交后的redo log 和 binlog的一致性。 redo log 影响主库的数据,binlog 影响从库的数据,所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。
- 两段提交的问题及解决方式
- 磁盘 I/O 次数高:每个事务提交都会进行两次 fsync(刷盘),一次是 redo log 刷盘,另一次是 binlog 刷盘。可通过 组提交(group commit)机制将多个事务binlog刷盘操作合并成一次刷盘
- 锁竞争激烈:两阶段提交在「多事务」的情况下,需要加一个锁来保证提交的日志提交顺序的一致性。将提交阶段进行细分,减小锁的粒度,提高并发度。
- MySQL 磁盘 I/O 很高,有什么优化的方法?
- 设置组提交的两个参数: binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数,延迟 binlog 刷盘的时机,从而减少 binlog 的刷盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但即使 MySQL 进程中途挂了,也没有丢失数据的风险,因为 binlog 早被写入到 page cache 了,只要系统没有宕机,缓存在 page cache 里的 binlog 就会被持久化到磁盘。
- 将 sync_binlog 设置为大于 1 的值(比较常见是 100~1000),表示每次提交事务都 write,但累积 N 个事务后才 fsync,相当于延迟了 binlog 刷盘的时机。但是这样做的风险是,主机掉电时会丢 N 个事务的 binlog 日志。
- 将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时,都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件,注意写入到「 redo log 文件」并不意味着写入到了磁盘,因为操作系统的文件系统中有个 Page Cache,专门用来缓存文件数据的,所以写入「 redo log文件」意味着写入到了操作系统的文件缓存,然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是,主机掉电的时候会丢数据
- 具体更新一条记录 UPDATE t_user SET name = ‘xiaolin’ WHERE id = 1; 的流程如下:
- 执行器负责具体执行,会调用存储引擎的接口,通过主键索引树搜索获取 id = 1 这一行记录:
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中,就直接返回给执行器更新;
- 如果记录不在 buffer pool,将数据页从磁盘读入到 buffer pool,返回记录给执行器。
- 执行器得到聚簇索引记录后,判断更新前后数据是否相同,若相同则不继续执行,若不同则将更新前后的记录都当作参数传给 InnoDB 层,让 InnoDB 真正的执行更新记录的操作;
- InnoDB 层先将数据库修改操作记录到相应的 undo log,因为这是更新操作,需要把被更新的列的旧值记下来,也就是要生成一条 undo log,undo log 会写入 Buffer Pool 中的 Undo 页面,不过在内存修改该 Undo 页面后,需要记录对应的 redo log。
- InnoDB 层开始更新记录,会先更新内存(同时标记为脏页),然后将记录写到 redo log 里面,这个时候更新就算完成了。为了减少磁盘I/O,不会立即将脏页写入磁盘,后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术,MySQL 的写操作并不是立刻写到磁盘上,而是先写 redo 日志,然后在合适的时间再将修改的行数据写到磁盘上。
- 在一条更新语句执行完成后,然后开始记录该语句对应的 binlog,此时记录的 binlog 会被保存到 binlog cache,并没有刷新到硬盘上的 binlog 文件,在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
- 事务提交(为了方便说明,这里不说组提交的过程,只说两阶段提交):
- prepare 阶段:将 redo log 对应的事务状态设置为 prepare,然后将 redo log 刷新到硬盘;
- commit 阶段:将 binlog 刷新到磁盘,接着调用引擎的提交事务接口,将 redo log 状态设置为 commit(将事务设置为 commit 状态后,刷入到磁盘 redo log 文件);
- 执行器负责具体执行,会调用存储引擎的接口,通过主键索引树搜索获取 id = 1 这一行记录:
内存篇(Buffer Pool)
详细解释:MySQL中数据是以页为单位,你查询一条记录,会从硬盘把一页的数据加载出来,加载出来的数据叫数据页,会放入到Buffer Pool中。后续的查询都是先从Buffer Pool中找,没有命中再去硬盘加载(page fault?),减少硬盘IO开销,提升性能。更新表数据的时候,也是如此,发现Buffer Pool里存在要更新的数据,就直接在Buffer Pool里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存(redo log buffer)里,接着刷盘到redo log文件里。
- Buffer Pool缓存机制
- 前提: InnoDB 中的数据
以页为基本单位
进行磁盘和内存的交互,一个页的默认大小为 16KB。 - 初始化:MySQL 启动时,InnoDB 会为 Buffer Pool 申请一片连续的内存空间。
默认配置
下为128MB
,并默认以16KB为单位大小划分缓冲页。可通过调整innodb_buffer_pool_size
参数来设置 Buffer Pool 的大小,一般建议设置成可用物理内存的 70%左右 - 访问方式:查询一条记录,先查找缓存,若没有则InnoDB会将
整个数据页
加载到Buffer Pool中,通过索引你定位到具体数据页,再使用页目录定位具体的数据记录 - 内存关系:MySQL 刚启动时,虚拟内存空间很大,而使用到的物理内存空间却很小,因为只有这些逻辑页被访问发生缺页中断,系统才会申请物理内存,并建立虚拟地址和物理地址的映射关系。
- 前提: InnoDB 中的数据
- 原理
- 读取数据:若查询的数据记录在Buffer Pool命中,则直接使用,否则再去磁盘中读取。(
先查缓存,命中再去磁盘读
) - 修改数据:如果数据记录在 Buffer Pool命中,则直接修改并设置为脏页,再在一个
合适的时机
将脏页刷盘。
- 读取数据:若查询的数据记录在Buffer Pool命中,则直接使用,否则再去磁盘中读取。(
- Buffer Pool的页面管理方式
- 空闲链表:连接
空闲缓冲页的控制块的索引
的双链表 - Flush链表:连接
脏页的控制块的索引
的双链表,后台线程就可以遍历 Flush 链表,将脏页写入到磁盘 - LRU链表:每次将
即将访问的页
移动到链表头部,如果该页不在链表中,则淘汰链表末尾页 - 脏页同时存在于 LRU 链表和 Flush 链表。
- 空闲链表:连接
- 简单的 LRU 算法并没有被 MySQL 使用的原因
- 预读失效
- 问题:由于空间局部性,会将要访问的数据页的相邻页面也加载到LRU链表首部,但可能并未被访问导致缓存命中率下降
- 解决方式:划分这两个区域后,预读的页就只需要加入到 old 区域的头部,当页被真正访问的时候,才将页插入 young 区域的头部,默认young 区域与 old 区域比例是 63:37,即old加载进来的局部页拥有更高的淘汰速度。
- Buffer Pool 污染(分类解决)
- 问题:一个扫描大量数据的SQL语句会将LRU内的大量热点数据淘汰,当热点数据再次被访问时,由于缓存未命中会产生大量磁盘IO,导致性能急速下降
- 解决方式:将进入到 young 区域条件增加了一个停留在 old 区域的时间判断,避免非热点数据的进入(目标清晰)
- 预读失效
- 脏页什么时候会被刷入磁盘?
- 当 redo log 日志满了的情况下,会主动触发脏页刷新到磁盘;
- Buffer Pool 空间不足时,需要将一部分数据页淘汰掉,如果淘汰的是脏页,需要先将脏页同步到磁盘;
- MySQL 认为空闲时,后台线程会定期将适量的脏页刷入到磁盘;
- MySQL 正常关闭之前,会把所有的脏页刷入到磁盘;
- Buffer Pool 的主要数据结构
其他之其他
- Innodb为什么要用自增id作为主键?
- 提高性能:按序插入的自增主键可以减少InnoDB存储引擎中B+数的分裂和重建,提高插入数据效率
- 避免数据冲突:避免主键冲突造成的数据插入失败
- 增加灵活性:方便对表的扩展和优化,是一种非业务主键
- MyISAM和InnoDB实现B树索引方式的区别是什么?
- MyISAM引擎是非聚集索引,数据文件和索引文件分开存储。仅支持表级锁,适合读操作多,并发度低的场景
- InnoDB引擎是聚集索引,主键索引与数据存储在叶子节点中。支持事务机制和行级锁,拥有更强的并发处理能力
- 说一说Drop、Delete与Truncate的共同点和区别
- Drop操作:删除整个表(包括表结构),删除速度快但无法回滚
- Delete操作:根据指定条件进行删除,会产生undo日志可以进行回滚操作
- Truncate 删除表中的所有数据,但是会保留表结构、索引和约束等元素
- MySQL如何进行性能优化
- 存储引擎:根据业务场景选择合适的存储引擎和配置参数
- 数据结构:设计合适的数据库结构,允许部分数据冗余,并进行分库分表
- 局部性处理:对热点数据使用缓存备份和索引,使用主从读写分离
- 优化SQL语句:避免使用 Select *,列出需要查询的字段
- B+树相比于B树的优点
- 顺寻查找快:B树顺序查找需要进行中序遍历,而B+树的数据按指定键顺序以双向链表形式存储在叶子节点
- 查询稳定好:任何关键字的查找必须走一条从根结点到叶子结点,所有关键字查询的路径长度相同
- 磁盘IO次数低:B+树索引节点可以存储更多的索引指针,更加矮胖
- Hash树适合唯一值查找,但是常见业务场景是一次查多值和顺序查找
- 视图和游标
- 视图:是一个逻辑上的虚拟表,由查询语句定义,可以将多个表中的数据进行组合
- 游标:是一个临时的数据库缓存对象,缓存查询的结果集,游标可以更方便的执行结果集相关的操作
- 数据库悲观锁和乐观锁的原理和应用场景分别有什么?
- 悲观锁(先获取锁,再进行业务操作):数据库执行SELECT … FOR UPDATE时会获取被选中的数据行的行锁
- 乐观锁:不对数据进行加锁,通过时间戳避免ABA问题
- MySQL索引类型
- 哈希索引:通过
哈希表
实现单条记录
的快速查询 - BTree索引:通过
B+树
组织索引值,实现任意数据记录
的查询 - 全文索引(FULLTEXT ):对文本中的所有单词建立索引,大大提高文本搜索效率
- RTREE索引:将空间数据分解成不同层级的矩形区域,并将矩形区域组织成树形结构,处理高维数据可能出现维度灾难
- 哈希索引:通过
- 数据库分表
- 作用:将表细粒度化,减少表单的访问负载,提高查询性能
- 分表策略
- 水平拆分:解决表中数据量太大的问题
- 取模分表:数据相对比较均匀,不容易出现热点和并发访问的瓶颈。
- 连续分表:按照时间维度进行划分
- 垂直拆分:解决表间的IO竞争问题
- 将不同的表放在不同的服务器上
- 水平拆分:解决表中数据量太大的问题
- 问题:数据迁移和扩容需要将表进行汇总和排序,会有比较大的开销
- 数据库中有那些键的类型
- 超键:在关系中能唯一标识元组的属性集(在表中,元组表示一行,属性表示一列)
- 候选键:可以唯一标识每个元组的一组属性,该组属性中删除任一个都无法唯一标识了
- 主键:唯一标识每个元组的一个或多个属性集合
- 外键:在其他表中是主键,可以保证相关表的数据完整性
- 数据库中的三大范式
- 1NF(原子性):不存在多值属性,即每个属性值不能再分
- 2NF(唯一性):1NF且不存在非主属性对主键的部分依赖(依赖主键中的一部分)
- 3NF(主键决定一切):1NF且不存在非主属性对主键的传递依赖(a依赖b,b依赖c,则c传递依赖于a)
- BCNF:所有属性不存在传递依赖。BCNF一定是3NF
- MySQL中CHAR和VARCHAR的区别有哪些
- 存储长度:char是固定长度,实际字符不足用空白字符填充;vachar只占用实际存储长度,不占用多余空间
- 访问速度:char的访问速度通常比较快(内存对齐?)
- 数据库结构优化
- 范式优化: 比如消除冗余(节省空间。。)
- 反范式优化:比如适当加冗余等(减少join)
- 限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围内。
- 读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
- 拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
🚩点此跳转到首行↩︎
参考博客
- 版权规范
- 一文讲清楚MySQL事务隔离级别和实现原理,开发人员必备知识点
- 小林coding
- 待定引用
- 待定引用
- 待定引用
- 待定引用
- 待定引用
相关文章:

【数据库】MySQL基础知识全解
系列综述: 💞目的:本系列是个人整理为了秋招面试的,整理期间苛求每个知识点,平衡理解简易度与深入程度。 🥰来源:材料主要源于拓跋阿秀、小林coding等大佬博客进行的,每个知识点的修…...

【golang】调度系列之goroutine
前面的两篇,从相对比较简单的锁的内容入手(也是干货满满),开始了go的系列。这篇开始,进入更核心的内容。我们知道,go应该是第一门在语言层面支持协程的编程语言(可能是我孤陋寡闻),goroutine也完全算的上是go的门面。g…...

A 股个股资金流 API 数据接口
A 股个股资金流 API 数据接口 全量股票资金流数据,全量A股数据,最长30日历史数据 1. 产品功能 支持所有A股资金流数据查询;每日定时更新数据;支持多达 30 日历史数据查询;超高的查询效率,数据秒级返回&am…...

【前端】Layui动态数据表格拖动排序
目录 一、下载layui-soul-table 二、使用 三、Layui实际使用 1、html代码 2、JS代码 3、PHP后台代码 目的:使用Layui的数据表格,拖动行进行排序。 使用插件:layui-soul-table 和 Layui 1.layui-soul-table文档:https://…...

Linux 忘记密码解决方法
很多朋友经常会忘记Linux系统的root密码,linux系统忘记root密码的情况该怎么办呢?重新安装系统吗?答案是不需要进入单用户模式更改一下root密码即可。 步骤如下: 重启linux系统 3 秒之内要按一下回车,出现如下界面 …...

【计算机组成 课程笔记】2.1 设计自己的计算机
课程链接: 计算机组成_北京大学_中国大学MOOC(慕课) 2 - 1 - 201-设计自己的计算机(14‘24’‘)_哔哩哔哩_bilibili 什么是指令系统体系结构?这个问题其实非常简单,但要想解释清楚也没有那么容易。我们还是从一个小故事…...
vb房屋销售管理系统设计与实现
摘 要 当今社会经济高速发展,人们的生活节奏日益加快。随着人们生活水平的提高,相应地人们对住房的需求也随之增大,对于购房者来说,如何在琳琅满目的商品房中方便快捷的选择到自己称心如意的家居便成了一个难题;对于房屋开发商和销售商来说,如何对众多的房屋产品进行科…...
SpringCloud学习笔记(十三)_Zipkin使用SpringCloud Stream以及Elasticsearch
在前面的文章中,我们已经成功的使用Zipkin收集了项目的调用链日志。但是呢,由于我们收集链路信息时采用的是http请求方式收集的,而且链路信息没有进行保存,ZipkinServer一旦重启后就会所有信息都会消失了。基于性能的考虑…...
重仓“AI”的百度迎来收获季?
今年以来,由AIGC引发的“行业旋风”持续席卷各行各业,给沉闷已久的互联网赛道带来了一股暖流。这场AI旋风对于重仓押注AI的玩家而言,更是如同“久旱逢甘霖”,终于迎来了“柳暗花明”的一天。 作为重仓押注AI赛道的头部玩家&#x…...

Linux 通过 Docker 部署 Nacos 2.2.3 服务发现与配置中心
目录 环境准备Nacos 数据库创建Docker 部署 Nacos1. 创建挂载目录2. 下载镜像3. 创建和启动容器4. 访问控制台 导入 Nacos 配置SpringBoot 整合 Nacospom 依赖application.yml 配置 参考官方链接微服务商城源码 环境准备 名称版本IP端口Nacos2.2.3192.168.10.218848、9848MySQ…...

macOS上制作arm64的jdk17镜像
公司之前一直用的openjdk17的镜像,docker官网可以直接下载,但是最近对接的一个项目,对方用的是jdk17,在对接的时候有加解密异常的问题,为了排查是不是jdk版本的问题,需要制作jdk17的镜像。docker官网上的第…...

对话永洪科技CEO何春涛:专注BI,决胜AI时代丨数据猿专访
大数据产业创新服务媒体 ——聚焦数据 改变商业 大数据、云计算、人工智能为代表的新一代信息技术走向普及,数据驱动业务,逐渐成为现代化企业管理、运作的日常。对于年均复合增长率超过20%的国内商业智能(BI)市场而言,…...
Redis 数据类型详细解析
Redis是一个开源的、内存中的数据结构存储系统,可用作数据库、缓存和消息代理。Redis支持多种类型的数据结构,包括字符串(String)、哈希(Hashes)、列表(Lists)、集合(Set…...

NOR型flash vs NAND型flash
FLASH是一种存储芯片,全名叫Flash EEPROM Memory,通过程序可以修改数据,即平时所说的“闪存”。 闪存可以在软件的控制下写入和擦写数据。其存储空间被分割成相对较大的可擦除单元,成为擦除块(erase block)…...

基于FPGA的图像sobel边缘提取算法开发,包括tb测试文件以及matlab验证代码
目录 1.算法运行效果图预览 2.算法运行软件版本 3.部分核心程序 4.算法理论概述 5.算法完整程序工程 1.算法运行效果图预览 2.算法运行软件版本 vivado2019.2 matlab2022a 3.部分核心程序 timescale 1ns / 1ps // // Company: // Engineer: // // Create Date: 202…...

设计模式-7--代理模式(Proxy Pattern)
一、什么是代理模式(Proxy Pattern) 代理模式(Proxy Pattern)是一种结构型设计模式,它允许一个对象(代理)充当另一个对象(真实对象)的接口,以控制对该对象的…...

音频——I2S 左对齐模式(三)
I2S 基本概念飞利浦(I2S)标准模式左(MSB)对齐标准模式右(LSB)对齐标准模式DSP 模式TDM 模式 文章目录 I2S left波形图逻辑分析仪抓包 I2S left I2S 左对齐标准 标准左对齐格式的数据的 MSB 没有相对于 BCLK 延迟一个时钟。左对齐格式的左右声道数据的 MSB 在 LRCLK 边沿变化后…...
css-grammar
语法格式 选择器 {属性名称 : 属性值; 属性名称 : 属性值;...}语法特点: CSS声明总是以键值对(key\value)形式存在。CSS声明总是以分号(;)结束。声明组以大括号({})括起来。为了让CSS可读性更强,每行只描述一个属性。 CSS 注释 注释是用来解释你的代码ÿ…...
ubuntu创建自定义开机服务
创建启动脚本 如/usr/sbin/hikcam.sh 里面写要开机执行的命令 chmod 777 赋予权限 /lib/systemd/system下创建 .service文件 [Unit] Description"bringup hikcam" Afternetwork.target[Service] EnvironmentLD_LIBRARY_PATH/opt/MVS/lib/aarch64 Typesimple ExecS…...

Cocos独立游戏开发框架中的日志模块:Bug无所遁形
引言 本系列是《8年主程手把手打造Cocos独立游戏开发框架》,欢迎大家关注分享收藏订阅。 在Cocos独立游戏开发框架中,一个强大的日志模块是不可或缺的组成部分。日志不仅仅是记录应用程序的运行状态,还可以用于故障排除、性能监测和安全审计…...
Python|GIF 解析与构建(5):手搓截屏和帧率控制
目录 Python|GIF 解析与构建(5):手搓截屏和帧率控制 一、引言 二、技术实现:手搓截屏模块 2.1 核心原理 2.2 代码解析:ScreenshotData类 2.2.1 截图函数:capture_screen 三、技术实现&…...

(LeetCode 每日一题) 3442. 奇偶频次间的最大差值 I (哈希、字符串)
题目:3442. 奇偶频次间的最大差值 I 思路 :哈希,时间复杂度0(n)。 用哈希表来记录每个字符串中字符的分布情况,哈希表这里用数组即可实现。 C版本: class Solution { public:int maxDifference(string s) {int a[26]…...
测试markdown--肇兴
day1: 1、去程:7:04 --11:32高铁 高铁右转上售票大厅2楼,穿过候车厅下一楼,上大巴车 ¥10/人 **2、到达:**12点多到达寨子,买门票,美团/抖音:¥78人 3、中饭&a…...
linux 错误码总结
1,错误码的概念与作用 在Linux系统中,错误码是系统调用或库函数在执行失败时返回的特定数值,用于指示具体的错误类型。这些错误码通过全局变量errno来存储和传递,errno由操作系统维护,保存最近一次发生的错误信息。值得注意的是,errno的值在每次系统调用或函数调用失败时…...

【Linux】Linux安装并配置RabbitMQ
目录 1. 安装 Erlang 2. 安装 RabbitMQ 2.1.添加 RabbitMQ 仓库 2.2.安装 RabbitMQ 3.配置 3.1.启动和管理服务 4. 访问管理界面 5.安装问题 6.修改密码 7.修改端口 7.1.找到文件 7.2.修改文件 1. 安装 Erlang 由于 RabbitMQ 是用 Erlang 编写的,需要先安…...
【把数组变成一棵树】有序数组秒变平衡BST,原来可以这么优雅!
【把数组变成一棵树】有序数组秒变平衡BST,原来可以这么优雅! 🌱 前言:一棵树的浪漫,从数组开始说起 程序员的世界里,数组是最常见的基本结构之一,几乎每种语言、每种算法都少不了它。可你有没有想过,一组看似“线性排列”的有序数组,竟然可以**“长”成一棵平衡的二…...
ThreadLocal 源码
ThreadLocal 源码 此类提供线程局部变量。这些变量不同于它们的普通对应物,因为每个访问一个线程局部变量的线程(通过其 get 或 set 方法)都有自己独立初始化的变量副本。ThreadLocal 实例通常是类中的私有静态字段,这些类希望将…...
Python 高级应用10:在python 大型项目中 FastAPI 和 Django 的相互配合
无论是python,或者java 的大型项目中,都会涉及到 自身平台微服务之间的相互调用,以及和第三发平台的 接口对接,那在python 中是怎么实现的呢? 在 Python Web 开发中,FastAPI 和 Django 是两个重要但定位不…...
CppCon 2015 学习:REFLECTION TECHNIQUES IN C++
关于 Reflection(反射) 这个概念,总结一下: Reflection(反射)是什么? 反射是对类型的自我检查能力(Introspection) 可以查看类的成员变量、成员函数等信息。反射允许枚…...
shell脚本质数判断
shell脚本质数判断 shell输入一个正整数,判断是否为质数(素数)shell求1-100内的质数shell求给定数组输出其中的质数 shell输入一个正整数,判断是否为质数(素数) 思路: 1:1 2:1 2 3:1 2 3 4:1 2 3 4 5:1 2 3 4 5-------> 3:2 4:2 3 5:2 3…...