龙岗网站建设多少钱,设计师联盟网,新网站做百度推广,网站开发费属于研发费用吗前言
上一篇介绍了 MySQL 的日志#xff0c;这一篇将介绍内存管理和磁盘管理相关的内容。
内存管理
MySQL 的数据都是存在磁盘中的#xff0c;我们要更新一条记录的时候#xff0c;得先要从磁盘读取该记录#xff0c;然后在内存中修改这条记录。修改完这条记录后会缓存起…前言
上一篇介绍了 MySQL 的日志这一篇将介绍内存管理和磁盘管理相关的内容。
内存管理
MySQL 的数据都是存在磁盘中的我们要更新一条记录的时候得先要从磁盘读取该记录然后在内存中修改这条记录。修改完这条记录后会缓存起来下次有查询语句命中了这条记录就可以直接读取缓存中的记录不需要再从磁盘获取数据了。
Buffer Pool
MySQL 对数据的增删改查都是在内存中完成的即在 Buffer Pool 中完成的。
缓存池是 Innodb 存储引擎设计并实现的是 InnoDB 中的一块内存区域默认大小是 128M。MySQL 启动后会初始化 Buffer Pool。
当读取数据时如果数据存在于 Buffer Pool 中客户端就会直接读取 Buffer Pool 中的数据否则再去磁盘中读取。当修改数据时如果数据存在于 Buffer Pool 中那直接修改 Buffer Pool 中数据所在的页然后将其页设置为脏页该页的内存数据和磁盘上的数据已经不一致为了减少磁盘I/O不会立即将脏页写入磁盘后续由后台线程选择一个合适的时机将脏页写入到磁盘。
InnoDB 会把存储的数据划分为若干个「页」以页作为磁盘和内存交互的基本单位一个页的默认大小为 16KB。因此Buffer Pool 同样需要按「页」来划分。
Buffer Pool 除了缓存「索引页」和「数据页」还包括了 Undo 页插入缓存页、自适应哈希索引、锁信息等等。 为了更好的管理这些在 Buffer Pool 中的缓存页InnoDB 为每一个缓存页都创建了一个控制块控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点、锁信息、LSN 信息」等等。
控制块也是占有内存空间的它是放在 Buffer Pool 的最前面接着才是缓存页控制块和缓存页之间灰色部分称为碎片空间。 为了能够快速找到空闲的缓存页可以使用链表结构将空闲缓存页的「控制块」作为链表的节点这个链表称为 Free 链表空闲链表。 为了能快速知道哪些缓存页是脏的于是就设计出 Flush 链表它跟 Free 链表类似的链表的节点也是控制块区别在于 Flush 链表的元素都是脏页。
LRU
为了提高缓存命中率MySQL 改进了 LRULeast Recently Used 算法将 LRU 划分了 2 个区域old 区域 和 young 区域。young 区域在 LRU 链表的前半部分old 区域则是在后半部分。 old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pc 参数来设置默认是 37代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。 改进过后的 LRU 算法可以解决两个问题预读失效和 Buffer Pool 污染。
预读失效
预读机制程序是有空间局部性的靠近当前被访问数据的数据在未来很大概率会被访问到。所以MySQL 在加载数据页时会提前把它相邻的数据页一并加载进来目的是为了减少磁盘 IO。
但是可能这些被提前加载进来的数据页并没有被访问相当于这个预读是白做了这个就是预读失效。
划分这两个区域后预读的页就只需要加入到 old 区域的头部当页被真正访问的时候才将页插入 young 区域的头部。如果预读的页一直没有被访问就会从 old 区域移除这样就不会影响 young 区域中的热点数据。
Buffer Pool 污染
当某一个 SQL 语句扫描了大量的数据时在 Buffer Pool 空间比较有限的情况下可能会将 Buffer Pool 里的所有页都替换出去导致大量热数据被淘汰了等这些热数据又被再次访问的时候由于缓存未命中就会产生大量的磁盘 IOMySQL 性能就会急剧下降这个过程被称为 Buffer Pool 污染。
Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题即使查询出来的结果集很小也会造成 Buffer Pool 污染。
比如在一个数据量非常大的表执行了这条语句
select * from user where name like %a%;可能这个查询出来的结果就几条记录但是由于这条语句会发生索引失效所以这个查询过程是全表扫描的接着会发生如下的过程
从磁盘读到的页加入到 LRU 链表的 old 区域头部当从页里读取行记录时也就是页被访问的时候就要将该页放到 young 区域头部接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配如果符合条件就加入到结果集里如此往复直到扫描完表中的所有记录。
为了解决这个问题MySQL 对进入到 young 区域条件增加了一个停留在 old 区域的时间判断。只有在 old 区域停留时间超过一定时间才会被插入到 young 区域头部。 另外MySQL 针对 young 区域其实做了一个优化为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部只有后面的 3/4被访问了才会。 脏页刷盘
把脏数据刷回磁盘的技术又称 checkpoint 技术。
MySQL 的脏页落盘是由后台线程定期异步执行的。
当 redo log 满了的情况下会主动触发脏页刷新到磁盘Buffer Pool 空间不足时需要将一部分数据页淘汰掉如果淘汰的是脏页需要先将脏页同步到磁盘MySQL 认为空闲时后台线程回定期将适量的脏页刷入到磁盘即使非空闲时也会见缝插针地刷盘MySQL 正常关闭之前会把所有的脏页刷入到磁盘 在 MySQL 的使用过程中可能会出现抖动突然变得很慢且 CPU 资源被大量占用很大可能就是在刷盘即情况 12。 change buffer
写缓存change buffer 是 buffer pool 的一部分当需要修改的数据页不在缓存池内时会在 change buffer 中记录数据变更等未来数据被读取时再将数据 merge 到缓存池中。 在 MySQL5.5 之前叫插入缓冲insert buffer只针对 insert 做了优化现在对 delete 和 update 也有效叫做写缓冲change buffer。 使用 change buffer 之前
当需要更新的数据不在缓存池中时从磁盘中读取数据页到 buffer pool一次磁盘随机读更新数据页一次写内存将数据页更新记录到 redo logredo log 落盘。一次磁盘顺序写
使用 change buffer 之后
当需要更新的数据不在缓存池中时不需要从磁盘中读取数据页而是在写缓存中记录这个变更操作一次写内存将数据页更新记录到 redo logredo log 落盘一次磁盘顺序写当访问到该记录时先从磁盘中读取数据页再从写缓存中读取变更信息如果有多个则依次更新最后更新到缓存池中即 merge 操作。一次磁盘随机读和一次写内存将缓存页和 change buffer 的更新记录到 redo logredo log 落盘。一次磁盘顺序写 使用 change buffer在更新频率高、查询频率低的场景下且不是更新完马上查询相当于可以减少一次磁盘随机读开销。相当于只有步骤 12和少量的步骤 34 但是如果所有更新后面都马上要对这个记录进行查询那么 change buffer 反而会起到副作用。 merge 时机
数据页被读取后台线程认为数据库空闲时数据库正常关闭时
change buffer 只能用于非唯一普通索引页non-unique secondary index page。
因为如果索引设置了唯一属性在进行修改操作时InnoDB 必须进行唯一性检查。
比如要插入(4,400)记录要先判断表中是否已存 k4 记录而这必须要将数据页读入内存才能判断。如果都已经读入到内存那直接更新内存会更快就没必要使用 change buffer。
磁盘空间管理
空间碎片
MySQL 中有以下几种可能出现空间碎片的情况
修改行数据导致出现行间碎片插入和修改数据可能导致页分裂从而使数据页中有大量空余空间数据页空余空间很难避免
删除数据
delete
使用 delete 删除一条记录只是在 B 树中将记录标记为删除状态通过隐藏列中的 deleted_bit删除后的记录不会消失且可以被复用。
如果一个数据页上的所有记录都被删除了那么整个数据页就可以被复用了。而且如果相邻的两个数据页的利用率都很小系统就会把这两个页上的数据合并到其中一个页上另外一个数据页就会被标记为可复用。
所以只是删除数据占用的磁盘空间并不会减少甚至可能增加。因为 delete 操作还会写入 redo log 和 undo log从而占用更多的磁盘空间。
truncate
truncate 用于清空表内的数据但是不会删除表本身。立刻释放磁盘空间。
drop
drop 用于删除整个表/库包括表的结构、属性、索引等。立刻释放磁盘空间。
速度droptruncatedelete
空间回收
经过大量增删改的表可能存在大量的空洞数据页中可复用或未被使用的记录。目前能够回收表空间的办法仅有一个就是重建表手段包括但不限于 optimizealter table 等。alter table 的有些操作只能靠 rebuild 表来完成。
误删数据
数据行
使用 delete 语句误删数据行时可以用 Flashback 工具通过闪回把数据恢复过来即修改 binlog 的内容拿回原库重放。
能够使用这个方案的前提是需要确保 binlog_formatrow 和 binlog_row_imageFULL。
恢复数据比较安全的做法是恢复出一个备份或者找一个从库作为临时库然后在这个临时库上执行这些操作确认过数据后再恢复回主库免得出现对数据的二次破坏。
预防
设置 sql_safe_updateson当 delete 和 update 语句中没有写 where 条件或者 where 条件里面没有包含索引字段的话这条语句的执行就会报错。
数据表/库
使用 drop table 或者 truncate table 语句误删数据表时或者使用 drop database 语句误删数据库时主要有两种方式可以恢复。
方案一使用全量备份加增量日志实时备份 binlog。
恢复的流程大概如下
取最近一次全量备份用备份恢复出一个临时库从日志备份里面取出备份点之后的日志把这些日志除了误删除数据的语句外全部应用到临时库。 如果是误删表不能指定恢复某个表所以恢复的速度很慢且由于数据量很大存在回复时间不可控的问题。 方案二延迟复制备库。专门搭建延迟复制的备库只要在延迟时间内发现问题就能直接用备库快速恢复数据。
MySQL实例
使用 rm 命令误删整个 MySQL 实例时对于高可用的集群而言只要选出一个新的主库保证整个集群的正常工作然后再把节点数据恢复再接入集群即可。
最后
本文介绍了 MySQL 内存管理和磁盘管理。在内存管理部分有一篇文章写的非常全面(十二)MySQL之内存篇深入探寻数据库内存与Buffer Pool的奥妙
下一节将介绍 MySQL 存储过程和触发器。
