MySQL 数据库 基础知识(二) MVCC

MySQL 数据库 基础知识(二) MVCC

2020-04-19    08'42''

主播: 橙汁儿橙汁儿。

190 2

介绍:
1.Buffer pool 除了索引之外,可以优化:合理设置 Buffer pool,为内存的 70%~80%,Buffer pool 是缓存页的,使用很多链表管理 buffer pool,比如 free 链表,每个节点表示空闲的缓存页,还有 LRU least recently used 最近最少使用链表,使用到某个缓存页时,会把该缓存页调整到 LRU 头部,分为 young 和 old 两个区域,这个比例是可以调整的,每次没有空闲的缓存页时就会释放 old 区域的页。 海量数据时关闭 innodb_buffer_pool_at_shutdown 和 innodb_buffer_pool_at_startup,这样的话在数据库关闭的时候也不会 dump 出 buffer pool 的数据,数据库开启时 也不会载入 buffer pool 的数据。 2.统计数据的存储方式 存在内存中,服务器关闭后再次登录,数据就不存在了;而如果存在磁盘中,重启后还可以看到,默认下是存在磁盘中的。 3.对 NULL 值的处理 (1)默认 认为 NULL 值是相等的。 (2)not_equal,认为所有的 NULL 都是不相等的。 (3)直接把 NULL 值忽略掉。 4.页、区、段 页 是数据库磁盘和内存交互的单位,一般是 16 KB。 连续的 64 个页是一个区,叶子节点 和 非叶子节点 是区别对待的,叶子节点会有一个区,非叶子节点会有一个区。 段是区的集合,也就是说,一个索引会有叶子节点段和非叶子节点段 5.排查慢查询 (1)查看慢查询日志,先看是否启动,没有的话可以通过 系统变量 或 配置文件开启慢查询日志。可以在 MySQL 安装路径中的 data 下看到慢查询日志的文本文档,可以看到具体的 SQL 语句,可以修改时间,默认是 10s 以上是慢查询。 (2)show prossecorlist 看进程列表,会显示持续时间,出现等待锁、死锁之类的问题,对 SQL 语句进行优化。 (3)explain 执行计划。会显示 驱动表、被驱动表、in 有无转化成 exists 、会有 state 状态表示有无死锁之类的、如果执行了全表扫描,预计扫描的行数、排序是使用索引还是文件排序。 MySQL 有查询优化器,会根据搜索条件,找到所有可能使用的索引 和 使用不同索引的代价,计算全表扫描的代价,选择成本最低的方案——执行计划。 6.undo 日志 与 MVCC 为了事务回滚操作,每个记录有隐藏的两个列:trx_id 生成这个记录的事务 ID,roll_pointer 相当于指针,会指向记录的 undo 日志,会把这个记录的历史版本连起来。 对于不同的操作 undo 日志的内容是不同的,比如说 insert 需要存插入记录的 key 值,之后需要回滚根据 key 值找到记录再删除即可, delete 需要存储完整的记录,回滚时再插入这条记录即可。(删除是分两步的:先 delete mark,把记录链到垃圾链表中,再用 purge 线程真正删除记录,并调整一些页的信息。) MVCC 是指 在 读已提交、可重复读隔离级别下,执行普通的 select 语句时,会生成 ReadView,遍历记录的版本链,与 ReadView 比较,把对当前事务可见的版本的记录作为结果。这两个隔离级别生成 ReadView 的时机不同,读已提交是在每次 select 时生成 ReadView,可重复读隔离级别是第一次 select 时生成,之后都用这个 ReadView。 ReadView 主要有 4 个属性: (1)生成这个 ReadView 的事务ID:creator_id (2)生成这个 ReadView 时,系统中还活跃的读写事务的 id 列表 (3)id 列表中,最小的事务 ID:min_id (4)系统要给下一个事务分配的 ID:max_id 遍历版本链,如果当前版本的 id 等于 creator_id,就说明事务访问的是自己修改过的记录,记录对当前事务是可见的;如果当前版本的 id 小于 min_id,说明 ReadView 生成之前记录已经提交了,说明记录对当前事务是可见的;如果当前版本的 id 大于 max_id,说明 ReadView 生成之后 记录还没有提交,记录对当前事务是不可见的;如果当前版本的 id 大于 min_id,而 小于 max_id,就要看是否在活跃列表中,如果在,说明 ReadView 生成时记录还没有提交,是不可见的,否则是可见的。遍历版本链,相当于从最新的版本开始比较,遇到可见的就放到结果中,如果遍历完毕还没有可见的,说明这个记录任何版本都不会放到结果中。 MVCC 用于 读已提交 可以避免脏读;用于 可重复读 可以避免不可重复读,但是没有解决幻读。比如:在可重复读隔离级别下,事务 A 第一次 select 时 生成了 ReadView,这个 ReadView 的 creator_id 是 事务 A,然后接下来 事务 B 插入一条记录并提交,然后 事务 A 对 记录做 update ,那么这个记录的 trx_id 就变成了 事务 A ,那事务 A 继续进行 select 时,遍历新插入的这条记录的版本链,最新版本的记录,trx_id 等于 creator_id,说明新插入的记录对于事务 A 是可见的,就会把它作为结果,这样就读到了幻影记录,出现了幻读的问题。 MVCC 的优点:使不同事务的 读-写 操作并发执行,从而提升系统性能。 7.数据库分库、分表 出现海量数据的问题,就需要进行分库、分表。 分库:把不同业务的表放置在不同的数据库中,这样可以解耦,表与表之间不会互相影响,而且分治管理。 分表:垂直分表:比如把不常使用的列放置到额外表中。但是仍没有解决一个表中有海量数据的问题。 水平分表:把记录分到不同的表中, 比如:根据数值取模,比如 4 个表,就 对 4 取余,相对均匀;但扩容时需要把数据进行迁移。 根据数值范围,比如 0~200、201~400,这样插入新纪录时只需要放置到对应的区间即可,但是如果是按时间作为范围,而且访问最新的数据的频率高,就会出现最新的表并发量大,而旧表很少访问的问题。