Mysql面试知识点
2024-02-01 11:09:02 93 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
MySQL面试知识点包括：数据库的基本概念、SQL语言的编写与优化、索引的使用和管理、事务处理、锁机制、存储引擎的选择和优化等。此外，还需要了解数据库的设计原则、高可用性和数据备份恢复策略。掌握这些知识点可以帮助应聘者在面试中展示自己的专业能力和实践经验，提高获得工作机会的几率。
作者其他创作
大纲/内容
Mysql面试知识点
若索引元素时顺序的，如1234，则将退化为链表
在数据量达到千万级时，树的层度过高，查询效率低
二叉搜索树
数据结构
平衡树，比二叉搜索树好一些，不会退化成链表
同样是颗二叉树，在数据量达到千万级时，树的层度过高，查询效率低
红黑树
仅能满足=、in，不支持范围查询
hash冲突问题
Hash
缺点
一个结点存储更多的元素
结点由于存储了数据，所以存储的索引会更少
非叶子结点同样存储数据
对范围查询不友好
叶子结点无指向其他结点的指针
B树
非叶子结点不存储数据，只存储索引，相比于B树，在相同深度下存放更多的索引，MySQL中千万级数据树高只需3层
叶子结点用指针相连，提高区间访问的性能
B+树
.frm 表结构文件
.idb 索引数据文件
使用两个文件存储表
比非聚集索引效率更高(减少了一次回表)
聚集索引
叶子结点包含了完整的数据记录
普通二级索引
联合索引
二级索引的叶子结点存储的数据为主键id
InnoDB
.frm 表结构文件
.MYD 数据文件
.MYI 索引文件
使用三个文件存储表
非聚集索引（索引和数据分离）
叶子结点存储的是数据所在磁盘的地址
MyISAM
存储引擎
1.将根结点加载到内存
2.通过二分查找定位到下一个结点在磁盘上的地址
3.将该结点加载到内存，继续定位，直到找到目标数据
4.若是二级索引，则使用主键回表查找主键索引
查找算法
算法
文章：https://zijiancode.cn/archives/mysql-explain
表结构
案例
参考文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
id列的编号是 select 的序列号，有几个 select 就有几个id
id的顺序是按 select 出现的顺序增长的
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
id列
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
union：在 union 中的第二个和随后的 select
select_type列
表示 explain 的一行正在访问哪个表
当 from 子句中有子查询时，table列是 <derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行id=N 的查询
table列
表示关联类型或访问类型，即MySQL决定如何查找表中的行
一般来说，得保证查询达到range级别，最好达到ref
从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。
eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点
type列
显示查询可能使用哪些索引来查找
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询
possible_keys列
显示mysql实际采用哪个索引来优化对该表的访问
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key列
显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
char(n)：n字节长度
varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
各类型所占字节
24*3 + 2 = 74
图片
explain select * from employees where name = 'zhangsan2'
计算规则
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
key_len列
显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：order.id）
ref列
mysql估计要读取并检测的行数，注意这个只是估计，不是结果集里的行数
大概行数计算方式：rows列的值 * filtered列的值 / 100：rows*filtered/100
rows列
explain select name from employees;
Using index：使用覆盖索引
explain select * from employees where name > 'zhangsan10';
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时
Extra列
explain列
可能走name索引
explain select * from employees where name > 'zhangsan10'
走name索引
explain select * from employees where name > 'zhangsan99999'
explain select * from employees where name > 'zhangsan99999' and age = 10 and position = 'dev'
explain select * from employees where age = 10 and name > 'zhangsan99999' and position = 'dev'
走name age position索引
explain select * from employees where name like 'zhangsan99999%' and age = 10 and position = 'dev'
最左匹配原则
全值匹配
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效
少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。有时候由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以将大的范围拆分成多个小范围
范围查询优化
索引实践
执行计划详解
建立连接
获取权限
默认8小时空闲后断开
5.7版本使用`mysql_reset_connection`重置连接
维持管理连接
连接器
8.0已删除
查询缓存
词法分析
语法分析
构建语法树
解析器
解析SQL
检查 SQL 查询语句中的表或者字段是否存在
将 select * 中的 * 符号，扩展为表上的所有列
预处理器
负责将 SQL 查询语句的执行方案确定下来
优化器
主键索引查询
全表扫描
索引下推
执行器
负责建立连接、分析和执行 SQL
Server层
执行SQL
innodb
myisam
其他
存储引擎层
mysql执行流程图
索引的匹配从最左边的字段开始，匹配成功才能往右继续匹配下一个字段
最左前缀法则
在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
覆盖索引：查询的字段被索引完全覆盖
主键索引(innodb引擎)会存储完整的记录，而二级索引只存储主键。
二级索引一个叶子结点能够存放的记录会多的多，扫描二级索引比扫描主键索引的IO次数会少很多
先使用覆盖索引方式查出10条数据，再使用这10条数据连接查询
优化案例
覆盖索引优化
order by的条件不在索引列上
一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
单路排序
首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
双路排序(回表排序)
使用文件排序 use filesort(效率低)
通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式
无where条件，order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
扫描索引本身完成排序 user index(效率高)
排序方式
select * from employees where id > 90000 limit 5;
根据自增且连续的主键排序的分页查询
根据非主键字段排序的分页查询
分页查询优化
关联字段加索引
小表驱动大表
优化方法
嵌套循环连接 Nested-Loop Join(NLJ) 算法
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
常见算法
Join关联查询优化
select * from A where id in (select id from B)
当B表的数据集小于A表的数据集时，in优于exists
select * from A where exists (select 1 from B where B.id = A.id)
当A表的数据集小于B表的数据集时，exists优于in
in和exsits优化
count(*)≈count(1)>count(字段)≈count(主键 id)
字段有索引
count(*)≈count(1)>count(主键 id)>count(字段)
字段无索引
count(*)查询优化
in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
set session optimizer_trace=\"enabled=on\
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
trace工具
索引
原子性
一致性
隔离性
持久性
ACID
更新丢失(Lost Update)或脏写
脏读
不可重读
幻读
并发事务处理带来的问题
默认的事务隔离级别是可重复读
show variables like 'tx_isolation';
常看当前数据库的事务隔离级别
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
设置事务隔离级别
操作
不可重复读
读未提交
读已提交
可重复读
可串行化
隔离级别
事务隔离级别
mysql事务
从性能上分为乐观锁(用版本对比来实现)和悲观锁
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
从对数据操作的粒度分，分为表锁和行锁
分类
每次操作锁住整张表
这里的开销指的是加锁的开销
开销小，加锁快
当不同事务都修改一张表数据时不会出现死锁
begin;
update account set balance = 1 where name = 'lilei';
update user set age = 2 where name = 'aa';
session1
update user set age = 1 where name = 'bb';
update account set balance = 1 where name = 'hanmei';
session2
出现死锁
当不同事务互相持有对方表锁时出现死锁
不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低
一般用在整表数据迁移的场景
特点
查看表上加过的锁：show open tables;
删除表锁：unlock tables;
表锁
每次操作锁住一行数据
开销大，加锁慢
会出现死锁
锁定粒度最小，发生锁冲突的概率最低，并发度最高
在执行查询语句SELECT时(非串行隔离级别)，不会加锁
update、insert、delete操作会加行锁
不支持行锁
执行查询语句SELECT前自动给涉及的所有表加读锁
执行update、insert、delete操作会自动给涉及的表加写锁
引擎支持性
行锁
间隙锁，锁的就是两个值之间的空隙
间隙锁是在可重复读隔离级别下才会生效
update account set name = 'zhuge' where id > 8 and id <18
间隙锁
Next-Key Locks是行锁与间隙锁的组合
临键锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
以该表为例
begin
update account set balance = balance-5 where name = 'lilei';
在session1的update之后：update account set balance = balance-5 where name = 'hanmei';
show open tables;
此时将看到出现表锁
注意：如果只是单纯在session1操作，不会出现表锁，只有当其他session更新时才会出现
在任意session
无索引行锁会升级为表锁
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
show status like 'innodb_row_lock%';
查看事务：select * from INFORMATION_SCHEMA.INNODB_TRX;
查看锁：select * from INFORMATION_SCHEMA.INNODB_LOCKS;
查看锁等待：select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查看锁等待详细信息：show engine innodb status\\G;
释放锁：kill trx_mysql_thread_id
行锁分析
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
锁优化建议
数据库锁
指一行数据被多个事务依次修改后，MySQL会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id(事务id)和roll_pointer(指向上一条日志的指针)把这些undo日志串联起来形成一个历史记录版本链
隐藏标识：删除标识(头信息中)
图
和事务是否提交无关
只有一份，被所有事务共享
undo日志版本链
可重复级别直到事务结束也不会变化
读已提交隔离级别在每次执行查询sql时都会重新生成
当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view
只在查询时生成
begin开始事务时不生成
begin后立即修改数据也不生成
生成时机
查询时生成临时事务id
修改数据时生成实际的事务id
事务id
未提交事务id数组+最大已创建事务id
小于最小未提交事务id
已提交事务
大于等于最小未提交事务， 小于最大已创建事务id
未提交事务和已提交事务
大于最大已创建事务id
未开始事务(在当前生成视图时刻)
三个区间
组成
使用read-view比对undo日志版本链
是，可见
1.是否在已提交事务中
否，不可见
不在，可见
是，判断该记录的事务id是否在未提交事务id数组中
2.是否在未提交和已提交事务的区间中
是，不可见
3.在未开始的事务中
查看undo日志中的记录是否可见
不符合要求则使用roll_point继续比对下一行记录
如果是删除的记录，则不返回数据
查询机制
read-view
一个事务中，先查询，生成事务123456，再更新，又会生成事务123，此时之前查询的事务id没了，不会出现两个事务id同时存在。
MVCC多版本控制
update account set name = 'zhangsan' where id = 1
执行步骤
对磁盘文件是进行随机读写
BufferPool是在内存读写
redo日志和undo日志是顺序读写
为什么MySQL不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL？
作为磁盘和内存之间交互的基本单位
大小一般为 16 KB
平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式
可变长数据类型，存储数据占用字节数不固定
VARCHAR(M)
TEXT
BLOB
变长的数据类型
1个字节能表示最大的数：127
超过 255 字节并且真实存储的字节数超过 127 字节，使用 2 个字节
否则使用 1 个字节
为什么逆序？
这样在读取数据时，可以使得前面列的数据和实际长度可以在同一个cacheline中
逆序存放每个字段所占长度
占用字节数，逆序
数据格式
记录方式
变长字段长度列表
某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以可以把这些值为 NULL 的列统一管理起来
每个允许存储 NULL 的列对应一个二进制位
二进制位的值为1时，代表该列的值为NULL
二进制位的值为0时，代表该列的值不为NULL
为什么逆序？同上变长字段长度列表
逆序存放每个字段是否为null
NULL值列表
5个字节(40个二进制位)
预留位 1
预留位 2
标记该记录是否被删除
delete_mask
B+树的每层非叶子节点中的最小记录都会添加该标记
min_rec_mask
表示当前记录拥有的记录数
n_owned
表示当前记录在页的位置信息
heap_no
表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
record_type
下一条记录的位置
next_record
记录头信息
出现时机
优先使用用户自定义主键作为主键
如果没有定义主键，则选取一个Unique 键作为主键
非必须，6 字节，表示行 ID，唯一标识一条记录
DB_ROW_ID(row_id)
必须，6 字节，表示事务 ID
DB_TRX_ID
必须，7 字节，表示回滚指针
DB_ROLL_PTR
隐藏列
对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768 个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出 768 字节的那些页面也被称为溢出页。
处理数据溢出
Compact
Redundant
MySQL5.7 的默认行格式
和 Compressed 行格式大致相同，不同点在于在处理行溢出数据时的方式
不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
Dynamic
Compressed
4 种不同类型
行
数据划分为若干页
大小一般是 16KB
索引页格式
记录存储结构
索引页结构
文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
体系结构
ibdata1
系统表空间
表名.ibd
独立表空间
任何类型的页都有专门的地方保存页属于哪个表空间，同时表空间中的每一个页都对应着一个页号，这个页号由 4 个字节组成，也就是 32 个比特位，所以一个表空间最多可以拥有 2^32 个页，如果按照页的默认大小 16KB 来算，一个表空间最多支持 64TB 的数据。
连续的64 个页就是一个区
一个区默认占用 1MB
一个区就是在物理位置上连续的 64 个页
范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了
顺序 I/O
引入目的
区
每 256个区被划分成一个组
用来登记整个表空间的一些整体属性以及本组所有的区被称为 FSP_HDR，也就是 extent 0 ~ extent 255 这 256个区，整个表空间只有一个 FSP_HDR
第一个组最开始的 3 个页面的类型是固定的
一个 XDES 类型，用来登记本组256 个区的属性，FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性
其余各组最开始的 2 个页面的类型是固定的
组
不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念
区分叶子节点和非叶子节点
段
如何管理表空间
表空间
系统表空间的 extent 1 和 extent 两个区
双写缓冲区
第一遍是写到doublewrite buffer
第二遍是写到真正的数据文件中
双写
保证InnoDB 存储引擎数据页的可靠性
一种特殊文件 flush 技术
在把页写到数据文件之前
先把它们写到一个叫 doublewrite buffer（双写缓冲区）的连续区域内
在写 doublewrite buffer 完成后，InnoDB 才会把页写到数据文件的适当的位置
过程
如果直接往页里写数据
每次只能写4kb，而一页数据有16kb
中间过程如果发送意外，就会导致页损坏
如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在 doublewrite buffer 中找到完好的 page 副本用于恢复
引入双写缓冲区
为什么需要他？
在 slave上可以关闭
降低了大概 5-10%左右性能
双写缓冲区/双写机制
默认128M
show variables like 'innodb_buffer_pool_size';
最小值为 5M
参考文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
更大的缓冲池只需更少的磁盘 I/O 来多次访问相同的表数据
优化：设置为机器内存的 60%左右
show engine innodb status\\G
依据缓存命中率设置大小
大小
默认的缓存页大小和在磁盘上默认的页大小是一样的，16kb
为了更好的管理缓存页
包括该页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息
每个缓存页对应的控制信息占用的内存大小是相同的
控制块和缓存页是一一对应的
控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边
每个控制块大约占用缓存页大小的 5%
控制块
记录一下 Buffer Pool 中哪些缓存页是可用的
把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中
刚刚完成初始化的 Buffer Pool 中所有的缓存页都是空闲的，所以每一个缓存页对应的控制块都会被加入到 free 链表中
每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的 free链表节点从链表中移除，表示该缓存页已经被使用了
free 链表
要访问某个页中的数据，快速定位该页是否在bufferpool
key:表空间号 + 页号
value:缓存页
用表空间号 + 页号作为 key，缓存页作为 value 创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从 free 链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置
缓存页的哈希处理
脏页：当我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的页称为脏页
用于判断哪些页是脏页
结构和free链表相似
修改了BufferPool数据如果立即同步到磁盘的页上，会严重的影响程序的性能
所以应是在未来的某个时间点进行同步
这就需要知道 BufferPool 中哪些页是脏页
使用原因
flush 链表
Buffer Pool 对应的内存大小是有限的
free链表中已经没有多余的空闲缓存页时，需要把某些旧的缓存页从 Buffer Pool 中移除
从业务角度考虑，业务总是访问最近的数据，而很少访问历史的数据
如果该页不在 Buffer Pool 中，在把该页从磁盘加载到 Buffer Pool 中的缓存页时，就把该缓存页对应的控制块作为节点塞到 LRU 链表的头部
如果该页已经缓存在 Buffer Pool 中，则直接把该页对应的控制块移动到 LRU链表的头部
访问某个页时
只要我们使用到某个缓存页，就把该缓存页调整到 LRU 链表的头部，这样 LRU 链表尾部就是最近最少使用的缓存页
当 Buffer Pool 中的空闲缓存页使用完时，到 LRU 链表的尾部找些缓存页淘汰就行了
原理
所谓预读，就是 InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到 Buffer Pool中。
如果顺序访问了某个区（extent）的页面数超过56，就会触发一次异步读取下一个区中全部的页面到 Buffer Pool 的请求
56是变量innodb_read_ahead_threshold的默认值
线性预读
如果 Buffer Pool 中已经缓存了某个区的 13 个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中所有其他的页面到 Buffer Pool 的请求
变量innodb_random_read_ahead控制是否开启，默认off
随机预读
InnoDB 提供了预读
不小心进行了全部扫描
问题
简单LRU的问题
热数据
存储使用频率非常高的缓存页（young区）
冷数据
分存储使用频率不是很高的缓存页（old区）
划分比例：37，系统变量：innodb_old_blocks_pc
划分为两部分
被访问的缓存页位于 young 区域的 1/4 的后边，才会被移动到 LRU 链表头部
可以降低调整 LRU 链表的频率，从而提升性能
young区
当磁盘上的某个页面在初次加载到 Buffer Pool 中的某个缓存页时，该缓存页对应的控制块会被放到 old 区域的头部
针对预读到 Buffer Pool却不进行后续访问的页面就会被逐渐从 old 区域逐出，而不会影响 young 区域中被使用比较频繁的缓存页。
虽然首次被加载到 Buffer Pool 的页被放到了 old 区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域的头部，这样仍然会把那些使用频率比较高的页面给顶下去
在执行全表扫描的过程中，即使某个页面中有很多条记录，去多次访问这个页面所花费的时间也是非常少的
在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在1s间隔内，那么该页面就不会被从 old 区域移动到 young 区域的头部
1s是变量innodb_old_blocks_time默认值
解决
针对全表扫描
old区
MySQL中的LRU链路
LRU 链表
内部组成
在多线程环境下，访问 Buffer Pool 中的各种链表都需要加锁处理，在 Buffer Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响请求的处理速度
BufferPool内存小于1G时无法配置多个实例
多个 Buffer Pool 实例降低冲突
并发冲突
查看BufferPool信息：SHOW ENGINE INNODB STATUS\\G
从 LRU 链表的冷数据中刷新一部分页面到磁盘
BUF_FLUSH_LIST
BUF_FLUSH_SINGLE_PAGE
从 flush 链表中刷新一部分页面到磁盘
刷盘时机
BufferPool
底层原理
Buffer Pool
自适应 Hash 索引
三大特性
Write-ahead logging，预写式日志
MySQL中事务的具体实现机制
所有的修改都先被写入到日志中，然后再被应用到系统中
每当有操作时，在数据变更之前将操作写入 redo log
redo日志
当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态
undo日志
包含 redo 和 undo 两部分信息
WAL
redo 日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来
保证事务的持久性
type:该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志
space ID：表空间 ID
page number：页号
data：该条 redo 日志的具体内容
ib_logfile0
ib_logfile1
日志文件组
日志格式
innodb_log_file_size:修改文件大小
innodb_log_files_in_group:修改文件数量
默认2个，每个48M，交替写
512 字节
redo log block
被划分成若干个连续的redo log block
通过参数：innodb_log_buffer_size指定，默认16M
顺序写入，先写满一个block，再写下一个
日志缓冲区
占满了 log buffer 总容量的大约一半左右
事务提交时
后台线程每秒刷新
redo 日志刷盘时机
写入过程
redo日志以页面为单位恢复，hash表，key为页码，value为修改的记录
恢复
日志序列号
随着redo日志量不断递增
Log Sequence Number
占用的空间非常小
顺序写入磁盘
优点
刷新一个完整的数据页太浪费了
随机 IO 刷起来比较慢
为什么先写入redo日志而不是直接写入磁盘？
事务执行过程中遇到错误
程序进行rollback
保证事务的原子性
插入一条记录时，至少要把这条记录的主键值记下来
删除了一条记录，至少要把这条记录中的内容都记下来
修改了一条记录，至少要把修改这条记录前的旧值都记录下来
记录对数据的改动
undo tablespace
存储中表空间中
0 条评论
回复 删除
下一页
