14_MySQL
2021-08-29 08:51:50 33 举报AI智能生成
MySQL知识梳理
作者其他创作
大纲/内容
锁机制
多个事务并发写同一行数据，会出现所谓的脏写，需要加锁来解决
行锁
行锁是在引擎层由各个引擎自己实现的，MyISAM 引擎就不支持行锁，InnoDB 是支持行锁
顾名思义，行锁就是针对数据表中行记录的锁
形态
更新一行数据必须把他所在的数据页从磁盘文件读取到缓存中才能更新，所以此时这行数据和关联的锁数据结构，都是在内存里面
类型
独占锁（X锁）
锁数据结构，里面包含trx_id和等待状态（true/false）
1、A事务需要更新一行数据，创建一个锁数据结构，锁等待状态是false
2、B事务也想更新数据时，会检查这行数据有没有被加锁，发现有锁后，B事务也会生成一个锁数据结构，里面有他的trx_id和锁等待状态是true
3、等到事务A更新完数据后，释放锁，事务B里的锁等待状态更新为false，唤醒事务B继续执行，B事务就可以获取到锁
默认情况下，其它事务读取这行数据是不需要加独占锁的，可以基于MVCC机制，读取快照数据
查询操作加独占锁
select * from table for update
保证查询的过程中，其它事务不能更新
共享锁（S锁）
select * from table lock in share mode
保证，可以并发读，不能并发读写
互斥性
锁类型 独占锁 共享锁
独占锁 互斥 互斥
共享锁 互斥 不互斥
独占锁 互斥 互斥
共享锁 互斥 不互斥
不太建议在数据库粒度去通过锁实现复杂的业务锁机制，更加推荐使用分布式锁实现复杂业务的锁机制
元数据锁/Metadata Locks
执行DDL时，会阻塞增删改操作，执行增删改操作时，会阻塞DDL操作
表锁
类型
手动添加
LOCK TABLES xxx READ，表级共享锁
LOCK TABLES xxx WRITE，表级独占锁
很鸡肋，一般不会使用
自动添加
意向独占锁
意向共享锁
更新操作，自动添加表级意向独占锁
查询操作，自动添加表级意向共享锁
互斥性
锁机制 独占锁 意向独占锁 共享锁 意向共享锁
独占锁 互斥 不互斥 互斥 互斥
意向独占锁 互斥 不互斥 互斥 不互斥
共享锁 互斥 互斥 不互斥 不互斥
意向共享锁 互斥 不互斥 不互斥 不互斥
独占锁 互斥 不互斥 互斥 互斥
意向独占锁 互斥 不互斥 互斥 不互斥
共享锁 互斥 互斥 不互斥 不互斥
意向共享锁 互斥 不互斥 不互斥 不互斥
手动添加表级独占锁，不能执行任何更新操作
手动添加表级共享锁，不能执行任何更新操作
全局锁
全局锁就是对整个数据库实例加锁
面试题精选
基础架构
Server层
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接
如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限
查询缓存
之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中
弊端
查询缓存的失效非常频繁
适用一些系统配置表
MySQL 8.0 版本直接将查询缓存的整块功能删掉
分析器
MySQL 需要知道你要做什么
词法分析，语法分析
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
执行计划生成，索引选择
执行器
执行语句
权限检查通过，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
涵盖 MySQL 的大多数核心服务功能
以及所有的内置函数（如日期、时间、数学和加密函数等）
所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
存储引擎层
负责数据的存储和提取
架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎
最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎
不同的存储引擎共用一个 Server 层
InnoDB
缓冲池（Buffer Pool）
作用
缓存磁盘的真实数据，Java系统对数据库执行的增删改查操作，其实就是对这个内存数据结构中的缓存数据执行增删查改操作
初始化过程
数据库一启动，就会按照设置的Buffer Pool大小，稍微加大一点，去找操作系统申请一块内存区域，作为Buffer Pool的内存区域
当内存区域申请完毕后，数据库就会按照默认缓存页的16KB大小以及对应的800个字节左右的描述数据大小，在Buffer Pool中划分出来一个个缓存页和一个个他们对应的描述数据，这时候他们都还是空的
等数据库运行起来之后，才会把数据对应的页从磁盘文件中读取出来，放入Buffer Pool中的缓存页
内置链表结构
free 链表
特点
由Buffer Pool里面的描述数据块组成
有两个指针，一个是free_pre，一个是free_next，分别指向上一个free链表的节点，以及下一个free链表的节点
只有一个基础节点不属于Buffer Pool，是40字节大小的一个节点，里面存放了free链表的头节点的地址和尾节点的地址，还有free链表里当前有多少个节点，表示空闲缓存页的个数
用途
数据库为Buffer Pool设计了一个free链表，是一个双向链表数据结构，这个链表每个节点对应的是一个空闲的缓存页的描述数据块地址，数据库刚启动时，可能所有缓存页都是空闲的，此时可能是一个空的数据库，一条数据都没有，所以此时所有的缓存页描述数据块，都会被放入这个链表中，这个链表可以用来获取空闲的缓存页
磁盘读取数据页到缓存页流程梳理
第一步，基于缓存页哈希表数据结构，判断数据页有没有被缓存
第二步，从free链表中获取一个描述数据块，根据这个描述数据块找到对应的空闲缓存页
第三步，将磁盘的数据页读取到对应的缓存页，同时，把相关的一些描述数据写入缓存页的描述数据块中，数据页所属的表空间之类的信息
第四步，把这个描述数据块从free链表中移除
第五步，将这个缓存页对应的描述数据块加入到LRU链表的冷数据区域的链表头部
flush 链表
结构类似free链表，凡是被修改过的缓存页（脏页），都会把他的描述数据放入flush链表中，后续都要flush到磁盘上去
LRU 链表
特点
结构类似free链表
采取冷热分离的设计，最近被加载数据的缓存页，都会放到LRU链表的头部
被拆成两个部分，一部分是热数据，一部分是冷数据，冷数据比例由innodb_old_block_pct参数控制，默认是37，也就是说冷数据占比37%
用途
缓存数据的淘汰
free链表中没有空闲缓存页却还需要加载新的数据页到缓存页时，可以淘汰一些缓存页
淘汰的办法就是把这个缓存页被修改的数据刷到磁盘中去，让它重新空闲出来，就可以重新在这个缓存页中写入需要的新数据页
被淘汰的缓存页是那些最近访问最少的缓存页，也就是LRU（Least Recently Used）链表尾部的缓存页
运行原理
第一步，数据页第一次被加载到缓存的时候，缓存页会被放到冷数据区域的链表头部
第二步，innodb_old_blocks_time参数，默认值1000，也就是1000毫秒，可以让一个数据页在被加载到缓存页之后，指定时间1s后，再次访问这个缓存页（冷数据区停留超过1s），它会被挪动到热数据区域的链表头部，1s内访问缓存页，是不会把这个缓存页放入热数据区域的头部
第三步，保证，预读机制以及全表扫描加载的一大堆缓存页都放在LRU链表的冷数据区域
第四步，缓存页不够，找到LRU链表冷数据区域尾部的缓存页，刷入磁盘
第五，对于热数据区域的访问规则优化
热数据区域里的缓存页可能是经常被访问的，频繁的移动会影响性能
访问规则优化之后，只有在热数据区域后3/4的缓存页被访问，才会移动到链表头部去，前1/4的缓存页被访问，不会移动到链表头部
冷热分离思想解决的主要问题
MySQL预读机制
触发条件
第一个，innodb_read_ahead_threshold，默认值是56，如果顺序访问了一个区里的多个数据页，访问的数据页的数量超过这个阈值，就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去
第二个，如果buffer pool里缓存了一个区里的13个连续的数据页，而且数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区的其他的数据页都加载到缓存里去，innodb_random_read_ahead，默认值OFF，默认关闭
问题
主要是第一个条件可能触发预读机制，一下子把很多相邻数据页放到LRU链表最前面，它们其实不怎么会被访问，导致本来在缓存里的一些频繁被访问的缓存页在LRU链表的尾部
全表扫描
基础知识
表空间
表，列和行是逻辑上的概念，表空间，数据页是物理上的概念，在物理层面，表数据都放在一个表空间中，表空间由一堆磁盘上的数据文件组成
表空间中包含了很多数据区，一组数据区是256个数据区，每个数据区包含64个数据页
数据页
特性
默认大小16KB
数据页包括文件头，数据头，最小记录和最大记录，多个数据行，空闲空间，数据页目录，文件尾部
数据页在磁盘中可能是二进制或者别的特殊格式的数据，包含两个指针，一个指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址，相邻的数据页采用双向链表的格式互相引用
数据页里面的每行数据会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单向链表
数据页目录存放了各行数据的主键值和行的实际物理位置
页分裂
在增加一个新的数据页的时候，如果你的主键是自己设置的，实际上会把前一个数据页里主键值较大的，挪动到新的数据页里面，然后把新插入的主键值较小的挪动到上一个数据页里去，保证新数据页的主键值一定比上一个数据页里主键值大
缓存页
默认大小16kB，和磁盘中数据页对应
缓存页哈希表
可用表空间号+数据页号，作为一个key，然后缓存的地址作为value
通过查找这个哈希表结构，可以知道数据页是否有被缓存
描述数据
缓存页的描述信息，每个缓存页都会对应一个描述数据，包含比如数据页所属的表空间、数据页的编号以及缓存页在Buffer Pool中的地址等等
Buffer Pool中，每个描述数据在前面，缓存页在后面
描述数据大概相当于缓存页的5%左右，大概800个字节
假设设置的Buffer Pool的大小是128M，实际上Buffer Pool的大小会超出一些，因为里面还要包含一些描述数据
加锁问题
多线程并发访问这个buffer pool，都是访问内存的一些共享的数据结构，比如说缓存页，各种链表，所以必然要加锁
线程拿到锁之后进行IO操作，就会比较耗时
如果机器内存很大，就可以给Buffer Pool分配一个较大的内存，此时同时可以设置多个buffer Pool，提高数据库对并发场景的支撑能力
Buffer Pool大小设置
说明
buffer pool是由很多chunk组成的，它的大小由参数innodb_buffer_pool_chunk_size参数控制，默认是128MB
chunk由一系列的的描述数据块和缓存页构成，每个buffer pool共用一套free，flush，lru链表
buffer pool内存大小应该设置为机器的50%~60%
buffer pool总大小 = chunk大小 * 每个buffer pool包含的chunk数量 * buffer pool数量
推荐大小设置
默认是128MB，偏小，实际生产环境可对其进行调整，16核32G的机器，可以给分配个2GB的内存
碎片问题
描述
Buffer Pool大小自定义，Buffer Pool划分完全部缓存页和描述数据块后，还剩一点内存，放不下任何一个缓存页，产生内存碎片
减少碎片的方法
划分缓存页时，让所有缓存页和描述数据块都紧密的挨在一起，这样尽可能减少内存浪费，减少碎片产生
缓存页淘汰机制
第一，定时把LRU尾部的缓存页刷入磁盘
第二，定时把frush链表中的缓存页刷入磁盘
第三，没有空闲缓存页的情况，主动把LRU链表尾部的缓存页刷入磁盘
结果：缓存页加入到free链表，从frush聊表中移除，从LRU链表中移除
磁盘文件
redo log
意义
提交事务的时候，绝对要保证对缓存页做出的修改以日志形式，写入redo log中
脏页数据未刷入磁盘的话，内存数据丢失，MySQL重启之后，根据redo log在buffer pool中重做一遍，恢复出来事务当时更新的缓存页
本质上保证，提交事务之后，修改的数据绝对不会丢失
redo log，其实记录的是表空间号+数据页号+偏移量+修改几个字节的值+具体的值
Redo Log Buffer
redo log block
存储多个单行日志
一个redo log block 512字节，分为3个部分，一个是12个字节的header块头，一个是496字节的body块体，一个是4字节的trailer块尾
一个redo log block可能存储多个redo log，redo log 也可能跨redo log block存储
MySQL启动时，向操作系统申请的一块连续的内存空间，里面划分出了N个空的redo log block，写满所有redo log block，就会强制刷盘
innodb_log_buffer_size
默认值，16MB
刷盘时机
写入一条redo log的时候，先从第一个redo log block开始写入，写满一个redo log block，就开始写入下一个redo log block
第一，写入redo log buffer中的日志已经占据整个redo log buffer的总容量的一半
第二，事务提交的时候，必须把事务涉及到的redo log所在的redo log block都刷入到磁盘，保证事务提交之后，修改的数据绝对不会丢失
第三，后台线程定时刷新，每隔一秒把redo log buffer中的redo log block刷入磁盘
第四，MySQL关闭的时候，redo log buffer 中的redo log block都会被刷入磁盘
Redo 日志文件
Binlog 日志文件
执行器
Undo 日志文件
回滚日志，用于事务回滚操作
insert，delete，update操作才有undo日志
行存储格式
一条更新语句了解InnoDB的存储引擎架构设计
第一步，加载磁盘中的数据页到Buffer Pool缓存池中的缓存页，更新free链表和lru链表
第二步，写入数据的旧值到undo log日志文件，便于回滚操作
第三步，执行器执行更新操作，更新缓存页的数据，缓存页成为脏页，更新flush链表和lru链表
第四步，执行器将redo log写入Redo Log Buffer
第五步，准备提交事务，执行器将Redo Log Buffer中的redo log刷入redo log日志文件
第六步，准备提交事务，执行器将binlog写入binlog日志文件
第七步，完成redo log日志文件的commit标记
第八步，后台线程定时将Buffer Pool中的脏数据刷入磁盘，更新free链表，frush链表，lru链表
undo日志文件保证事务回滚，redo log日志文件保证脏数据未刷入磁盘MySQL宕机更新数据不会丢失，binglog日志文件保证可以备份恢复
日志系统
redo log
InnoDB 引擎特有
物理日志，记录的是“在某个数据页上做了什么修改”
循环写的，空间固定会用完
crash-safe
保证即使数据库发生异常重启，之前提交的记录都不会丢失
innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘
如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高
Write-Ahead Logging
WAL技术
先写日志，再写磁盘
binlog
MySQL 的 Server 层实现的，所有引擎都可以使用
逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
追加写入,“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
sync_binlog 这个参数设置成 1 ，表示每次事务的 binlog 都持久化到磁盘
两阶段提交
update T set c=c+1 where ID=2;
第一步，执行器先找引擎取 ID=2 这一行，ID 是主键，引擎直接用树搜索找到这一行，如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
第二步，执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
第三步，引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态，然后告知执行器执行完成，随时可以提交事务
第四步，执行器生成这个操作的 binlog，并把 binlog 写入磁盘
第五步，执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成
保证说redo log和binlog日志记录的一致性，并且定期整库备份，保证说数据库可以基于binlog日志恢复到任意时间的状态，数据也不会有问题
如果在binlog写入之前奔溃，重启之后，发现没有commit，执行回滚操作，此时没有写入binlog，数据一致
如果在commit之前奔溃，重启之后，满足prepare和binlog完整，自动commit，数据一致
事务
思考两个问题
多个事务同时执行，同时对缓存页里的一行数据更新，冲突怎么解决
多个事务同时执行，有的事务在执行查询，有的事务在执行更新，冲突怎么解决
多个并发事务带来的问题
脏写
脏读
不可重复读
幻读
注意，幻读特指事务中，查询到之前查询未看到的数据
问题的本质都是数据库的多事务并发问题，数据库使用事务隔离机制，MVCC多版本隔离机制，锁机制来解决这些问题
事务的四大特性
原子性，Atomicity
一致性，Consistency
隔离性，Isolation
隔离级别，隔离得越严实，效率就会越低
SQL标准隔离级别
读未提交（read uncommited），一个事务还没提交时，它做的变更就能被别的事务看到，出现所谓的脏读
读已提交（read committed），一个事务提交之后，它做的变更才会被其他事务看到，出现所谓的不可重复读
可重复读（repeatable read），一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的，当然，未提交变更对其他事务也是不可见的
串行化（serializable），顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行，表示对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行
MySQL默认隔离级别是RR（可重复读），并且可以避免幻读发生，MVCC多版本并发控制机制避免脏读、脏写、不可重复读和幻读问题发生
持久性，Durability
MVCC多版本并发控制机制
原理
基于undo log版本链条实现的ReadView机制
索引
索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，索引的出现是为了提高查询效率
索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样
即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同
InnoDB 的索引模型
表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表
数据都是存储在 B+ 树中，每一个索引在 InnoDB 里面对应一棵 B+ 树
索引类型
主键索引
主键索引的叶子节点存的是整行数据，在 InnoDB 里，主键索引也被称为聚簇索引
主键索引原理
针对主键的索引其实就是主键目录，把每个数据页的页号，还有数据页最小的键值放在一起，组成一个索引目录，即索引页
索引页里保存下级索引页的页号和最小主键值，通过二分查找可以找到下级索引页，如果最下层的索引页的页号太多，再次分裂，再加一层索引页
形成一个B+树数据结构
聚簇索引
索引页+数据页组成的B+树
同层级的索引页，互相之间基于指针组成双向链表
最下层的索引页，有指针引用数据页，即在大的B+树索引数据结构里，叶子节点是数据页本身
当数据页开始分裂时，会维护上层索引数据结构，在上层索引页里维护索引条目，不同数据页和最小键值
当数据页越来越多，一个索引页放不下，就会拉出新的索引页，同时加一个上层索引页，上层索引页存放的条目时下层索引页页号和最下主键值
数据量越大，索引页层级越多，一般索引页可以放很多索引条目，所以通常而言，即使是亿级大表，基本上大表建的索引层级也就三四层
如果一颗大的B+树索引结构里，叶子节点就是数据页本身，可以称这个B+树索引为聚簇索引
主键以外字段索引（二级索引）
特点
独立于聚簇索引之外的另一个B+树结构
从小到大排序，下一个数据页索引字段值大于上一个数据页索引字段值
叶子节点仅仅存放主键和索引字段的值
回表
根据索引字段找到数据后，还需要通过主键信息在聚簇索引里从根节点开始查找，找到叶子节点的数据页，才能定位到主键对应的完整数据行
联合索引（复合索引）
特点
原理同二级索引，只不过排序的顺序是从靠左边的索引字段开始排序
查找过程中遇到范围查找的时候，后面的索引字段无法排序，设计索引的时候，要把需要做范围查询的索引字段放在最右边
如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
匹配规则
等值匹配规则
最左侧匹配规则
从索引最左侧值开始匹配，不能跳过左边索引字段直接匹配右边索引字段
最左前缀匹配规则
可以用like 'xxx%'的方式匹配
范围查找规则
可以用select * from table where x1 > xx and x1 < yy这种方式匹配
等值匹配+范围匹配规则
排序/分组走索引提升查询速度
order by 后面字段顺序和联合索引从最左侧开始字段一致
group by 后面字段顺序和联合索引从最左侧开始字段一致
索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
索引中间位置插入新值
页分裂
性能问题
利用率问题
哪些场景下应该使用自增主键
自增主键是指自增列上定义的主键
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值
对于递增插入的场景，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
只有一个索引，并且该索引必须是唯一索引的场景下，优先选择业务字段作为主键
覆盖索引
覆盖索引可以减少树的搜索次数，显著提升查询性能
如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
索引下堆
索引是否越多越好
索引太多，也许查询速度可以提高，但增删改速度比较差（维护索引成本），占用磁盘空间也越多，所以索引并不是越多越好
设计索引的技巧
代码开发之后再考虑设计索引
针对where条件，order by条件，group by条件去设计索引
尽量使用基数较大的字段，就是值比较多的字段建立索引，那样才能发挥出B+树快速二分查找的优势来
对那些字段的类型比较小的列来设计索引，这样占用的磁盘空间小，搜索性能好
如果某个较大的字段要建立索引，又不想索引树占太多磁盘空间，可以考虑用这个字段的前20个字符前缀建立索引
避免在索引字段里套函数，进行计算
索引不要设计太多，建议两三个联合索引就应该覆盖掉某个表的全部查询
主键一定要是自增的，别用UUID之类的
设计低基数字段索引时，可以加个复杂辅助字段
SQL执行与优化
执行计划
看懂执行计划
优化执行计划
收藏
收藏
0 条评论
下一页
