MySQL总结
MySQL的基本结构
1. 服务层
连接层
负责管理我们的连接以及连接相关的配置
-
max_connections 最大连接数，默认151（取值范围：1~10W）
-
max_execution_time 查询最大超时事件，默认0，不超时
解析器
解析SQL语句，判断语句是否符合SQL语法以及后续的权限检测
- 词法解析：构建语法树
- 语法解析：解析语法树中的语法是否正确
预处理
- 语句不变，传入的变量会变
- 预处理可以减少重复语句的解析次数，提升性能
- 预处理可以防止SQL注入
优化器
- 决定sql语句应该怎么样执行
- 支持许多优化器：比如索引下推、join优化等。
- 优化器的状态可以打开或关闭
执行器
跟存储引擎交互，调用存储引擎的API得到用户想要的结果
2. 存储引擎层
- InnoDB
- MyISAM
- Memory
InnoDB磁盘存储结构
InnoDB表空间
-
MySQL使用表空间来管理磁盘上的数据文件，表空间在磁盘上的文件可以是单个文件或者是多个文件。表空间的大小是由其中的数据文件的大小总和决定的。
-
当表空间中的数据文件不足以存储表数据时，MySQL 可以自动地扩展表空间，增加一个或多个新的数据文件，以满足表数据的存储需求。
-
表空间在 MySQL 中的底层实现方式是通过文件系统来实现的。每个表空间都被映射到一个或多个文件，在文件系统中以文件的形式存在。这些文件通常位于操作系统的文件系统中的特定目录下，
-
有哪些表空间：
- 独立表空间，默认，每个表有各自单独的文件（*.idb）
- 系统表空间，耦合非常高
- 通用表空间，可以自己创建通用表空间（可以容纳多个表），建表时指定使用哪个通用表空间
- 临时表空间，创建的临时表会放到临时表空间
InnoDB表空间结构
InnoDB内存加载
-
内存缓存结构（BufferPool）
-
内存与磁盘数据交互机制，page页作为内存跟磁盘交互的最小单位
-
取数据的流程：
- 假如现在要查询id=2的数据
- 从磁盘拿到id=2所在的这个页的数据返回给内存
- 后续读取id=2的页的其他数据时都不需要再跟磁盘交互
- 预读机制，数据预热，预读出其他可能用到的页
BufferPool内存管理
内存管理，淘汰机制
- 传统的LRU（Least Recently Used）算法：最近最少使用
- 当新数据加入，插入到链表头部；
- 每当缓存命中（即缓存数据被访问），则将数据移到链表头部；
- 当链表满的时候，将链表尾部的数据丢弃。
- 查找：通过Key使用哈希表查找对应节点，只需要O(1)的时间复杂度。
- 删除和移动：如果是单向链表，删除和移动结点是需要访问前驱节点的，而访问前驱节点就要遍历，时间复杂度是O(n)。但是如果使用双向链表，通过pre就可以访问前驱节点，操作都是指针移动，时间复杂度是O(1)，因此使用双向链表。
- 如果InnoDB使用传统的LRU会存在以下问题：
- 页的预加载，预读到缓冲区的页都会放到LRU链表的头部，但其中很多页可能并不会被真正读取。
- 缓冲区污染，很多使用频率较低的页加载到缓冲区中，会把使用频率较高的页从缓冲区中淘汰掉，比如全表扫描
- 改进的LRU
- 将传统LRU链表划分为两部分：热数据区【年轻区】、冷数据区【老年区】。可以通过innodb_old_blocks_pct配置来控制冷数据区域的占比。
- 某个页在首次加载到缓冲区中时，先淘汰掉冷数据区域尾部的数据，然后将其放到冷数据区域的头部
- 当后续访问冷数据区域的某一页与首次访问该页的时间间隔大于配置的一个窗口值，才会将该页从冷数据区移动到热数据区的头部。这样可以避免非频繁访问的数据污染热数据区。窗口值可以通过innodb_old_blocks_time来设置，默认为1000ms。
内存与磁盘同步机制
-
脏页（Dirty Page）：脏页是指在BufferPool中已被修改但尚未写回磁盘的数据页。
-
刷脏：将脏页数据同步到磁盘
-
刷脏频率
- 脏页自适应，脏页达到一定比例，刷新到磁盘（比例可以配置）
- 空闲时间刷新，当异步线程空闲时刷新
- redoLog自适应，当redoLog文件空间快写满时刷新
-
innodb最小IO操作单位是一页（默认16K），但是文件系统IO的最小单位是4K（不同操作系统不一样），因此刷脏过程中需要多次IO，中间如果出错，会导致数据不完整。innodb采用doubleWrite的机制来解决该问题。
-
doubleWrite
doulbeWrite由两部分组成，一部分是内存中的doubleWirte buffer，另一部分是磁盘上的对应的表空间。刷脏时先将数据写入到doubleWirte buffer，然后再写入磁盘对应的表空间中，可以写多份文件用于备份（默认是2份，可以配置成更多份）
InnoDB的RedoLog
- redoLog是重做日志，用来做数据恢复，保证数据的一致性、持久性，每次操作会保存redoLog日志。
- redoLog的总大小是可配置的，分为32个文件，每个文件的大小是配置的总大小的1/32。
- redoLog是循环写，当空间写满时，会覆盖旧的数据。
- redoLog只是日志，比起page页，它的数据量小很多，且是顺序IO读写（page页同步到磁盘要先找到写入位置，是随机IO），所以它的同步性能要快很多。
- 为了进一步提高性能，可以使用redoLog的logBuffer，将redoLog先写入log buffer，异步刷新到磁盘
InnoDb数据写入磁盘时崩溃，数据恢复过程
- 结合使用doubleWirte和redoLog，在恢复过程中，从表空间doublewrite中找到需要恢复的数据页的一个最近的副本，将其复制到表空间文件，再应用redoLog。
- 为什么doubleWirte和redoLog都需要？
- 对于没有写完doublewrite备份的数据页，只能通过redoLog恢复。
- redolog的设计之初，是“账本的作用”，是一种操作日志，记录的是 “在某个数据页上做了什么修改”，但如果数据页本身已经发生了损坏，redolog来恢复已经损坏的数据块是无效的。所以此时需要doublewrite的一个数据块的副本来还原该损坏的数据块，再利用重做日志进行其他数据块的重做操作。
InnoDB索引
- Clustered Index 主键索引（聚簇索引）
- 每个表都会有一个主键索引，如果没有主键，会采用非空唯一字段，如果非空唯一字段页没有，会用隐藏的row_id
- 主键索引的叶子节点保存了完整的行数据
- Secondard Index 二级索引（非聚簇索引）
- 除了主键索引之外的单列、多列索引
- 多列索引的排序规则：现根据第一个字段排序，第一个字段相等再根据第二个排序，依此类推
- 叶子节点保存的是索引列和主键值，如果SQL中的select是查询其他字段，则会再使用查到的主键值到主键索引中区查询，称为“回表”。
B+树索引优势
-
叶子节点才会有完整的行数据，而非叶子节点是目录，同样数据量非叶子节点可以存储更多的目录数据，树的高度越低，整体性能越高。
-
查询稳定，查询遍历次数等于树的高度。B+树的高度通常为2-3.
-
支持范围查询，叶子节点是从小到大排列的链表。
InnoDB事务与ACID特性
- A: atomicity 原子性
- 事务中的所有操作要么全部执行完成，要么全部不执行。
- C: consistency 一致性
- 事务执行后，数据库的完整性没有被破环，写入数据符合预期。
- innnoDB的相关实现：doubleWrite、redoLog
- I: isolation 隔离性
- 支持多个事务并发执行，互补干扰。
- D: durability 持久性
- 事务提交后，数据永久保存到磁盘中，不会丢失。
- innnoDB的相关实现：doubleWrite、redoLog
UndoLog在事务回滚的使用
- 事务中的每个增、删、改操作会记录undoLog，事务回滚时，InnoDB会从最后一条undoLog开始逆向将数据恢复到原来的状态。
事务并发产生的一致性问题
- 脏读（Dirty Read），一个事务读到了其他事务没有提交的数据
- 不可重复读（Non-repeatable Read），一个事务中，对同一条数据多次读取，在此期间，另一个事务对该数据进行了修改，导致得回的结果不一致。
- 幻读（Phantom Read），一个事务中，执行多次相同的范围查询，在此期间，另一个事务对该范围内的数据进行了新增或删除，导致返回的结果不一致。
事务隔离级别
-
数据库标准：
- 读未提交（Read Uncommitted），可简称为RU，存在脏读、不可重复读、幻读问题
- 读已提交（Read Committed），可简称为RC，存在不可重复读、幻读问题
- 可重复读（Repeatable Read），可简称为RR，存在幻读问题
- 序列化（Serializable），解决了所有问题，但是性能最低
-
InnoDB的事务隔离级别方案
- 对于InnoDB引擎，隔离级别默认使用RR（可重复读），但这里的RR解决了所有问题（脏读、不可重复读、幻读）
InnoDB解决并发问题之MVCC（Multiversion Concurrency Control）
-
通过版本号来避免同一数据在不同事务间的竞争。
-
不用加锁，并发性能较好。
MVCC具体实现逻辑
-
行数据增加隐藏字段trx_id：修改了该数据行的最后的事务ID
-
事务启动的时候，创建一个事务快照，其数据结构叫ReadView，ReaView包括：
1.max_id：即将分配的下一个事务ID（事务ID是增长的）
2.当前活跃事务的事务ID列表（活跃事务是启动了但还没提交的事务）
3.min_id：活跃事务中事务ID最小的事务ID
4.创建该ReadView事务ID
-
数据行的trx_id与ReadView的比对规则：
-
trx_id < min_id，表示这个版本是已经提交的事务生成的，这个数据是可见的
-
trx_id >= max_id，表示这个版本是由将来启动的事务生成的，这个数据是不可见的
-
min_id <= trx_id < max_id，分为3种情况：
a. 若trx_id是当前自己事务，这个数据是可见的
b. 若trx_id在活跃事务ID列表中，表示这个版本是由还没提交的事务生成的，这个数据是不可见的
c. 若trx_id不在活跃事务ID列表中，表示这个版本是已经提交了的事务生成的，这个数据是可见的
-
MVCC实现不同的隔离级别
- 读已提交（Read Committed）：在每一次进行普通SELECT操作前都会生成一个ReadView
- 可重复读（Repeatable Read）：只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。
InnoDB解决并发问题之LBCC（Lock-Base Concurrency Control）
按锁的兼容性分类：
-
共享锁 Shared Lock（读锁, S锁）
- 共享锁之间兼容，数据加了共享锁后，可以继续再加多个共享锁，但不能再加排他锁
- 手动加共享锁：select … for share
-
排他锁 Exclusive Lock（写锁，X锁）
-
数据加了排他锁后，不能加任何其他锁
-
修改数据时会默认加排他锁
-
手动加排他锁：select … for update
-
按锁的粒度分类：
- 行级锁
- 表级锁
按锁的模式划分
-
意向锁（Intention Lock）
-
当表里的数据加锁时(共享锁/排他锁)，会在表上做一个标记，就是意向锁
-
意向锁是表级锁
-
意向锁是为了提升加表锁的性能
-
-
记录锁（Record Lock）
- 单行记录加锁，锁的是索引树的记录
-
间隙锁（Gap Lock）
- 锁定的是索引记录之间的间隙，而不是具体的记录。
- 锁住的索引记录到上个索引记录之间的数据区间不能添加数据，但可以修改
-
临键锁（Next-key Lock）
- 可以理解为 记录锁 + 间隙锁
- 锁住的索引记录不能修改，且该索引记录到上个索引记录之间的数据区间不能添加数据
LBCC实现不同的隔离级别
- 读已提交（Read Committed）：禁用间隙锁
- 可重复读（Repeatable Read）：启用间隙锁
如何避免死锁
- 注意记录的加锁顺序
- 尽量避免使用大事务
- update条件使用索引列，如果没有用索引会锁住整个表里的行数据
MySQL的BinLog
-
binLog即Binary Log，是MySQL服务层级别实现的二进制日志
-
可用于主从同步
-
可用于数据恢复
-
binLog格式
- Statement：记录的是SQL语句，存在相同的SQL语句再主从数据库中得到结果不一致的情况（例如使用函数now()）
- Row：基于行数据去记录的，恢复比较慢，但数据一致性高
- Mixed：Statement与Row的结合，分析语句是否会导致数据一致性问题，然后选择使用Statement或Row。
-
binLog会先存在内存，再同步到磁盘，可以配置有多少条日志后再统一同步到磁盘
binLog和redoLog的数据一致性问题
- 使用二阶段提交解决binLog和redoLog的数据一致性问题
主从同步
同步方式
- 异步
- 主节点执行操作后直接commit，binlog异步同步到从节点，主不关心从节点是否能同步成功
- 半同步
- 主节点在执行操作后，commit之前，binlog会同步到从节点，并且要收到从节点的确认ACK，否则主节点不会commit
- 开启半同步功能需要安装插件
主从数据延迟
- 网络延迟
- 主节点的负载很高，可以分库
- 大事务导致，减少大事务
- 从节点的性能跟不上主，一般硬件配置比主节点低
2618
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
