MySQL海量数据设计实践

MySQL海量数据设计实践

MySQL存储引擎原理拆解以及设计深度剖析

页结构

页头：记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针（双向链表）、页面空间使用情况等。

最小最大虚记录：比页内最大小主键还大小确定一个范围，即一个页的空间，查看时，根据和最大最小虚记录的比较结果判定是否在这个空间内。

记录堆：行记录存储区，分为有效记录和已删除记录

自由空间链表：存储记录堆中，存储的过程中可定有被删除的记录，自由空间链表的作用就是吧删除的记录链起来，方便被删除的记录有什么，找到这些被删除的空间的目的是，想要利用这些空闲的空间

Slot区：

页内记录维护

B+tree

聚簇索引：索引和数据是在一起等

顺序保证

物理有序：

一开始放入数据，主键为10，之后放入的数据主键为9，为了保证物理有序，就要向后移动10，再插入9，同理如果插入是8，就要移动9、10，同样删除的过程也涉及到复制的过程这样的效率就会很低。但是查询的数独就很快，直接指针偏移，可以采用二分查找。

逻辑有序：（链表data域，指针域）

插入只需要新申请一个空间之后，修改指针域，这样插入的效率高，但是查询遍历的过程很慢。

MySQL innodb 显然使用的是逻辑有序，查询可以优化，显然物理实在在插入的过程很困难。

页面内单向链表，保证数据有序，页面间双向链表这样就把数据有序的链接起来了

根据这个原理，之前的聚簇索引就可以加入双向链表

插入策略

​ 可用的空间有自由空间链表和未使用空间，首先要考虑把已删除的空间利用起来，但是也会产生碎片的，频繁的插入删除的表需要定期的做收缩

页内查询

逻辑连续 遍历

物理连续 二分查找，但是二分查找需要每个数据大小是等长的，通过偏移量找具体的位置。同时也可见一定是逻辑有序

最下方是Solt，分成若干个槽位，每个槽位又对应着一个链表，这样可以根据槽位进行二分查询，找到数据可能在的区间。再小范围的便利一下（类似于跳表）

MySQL InnoDB存储引擎内存管理

1预分配内存空间

2数据以页为单位加载

每次读取一页的数据到内存当中

为什么是一条：因为减少i/o操作，提高i/o性能，可能访问一页的多条数据

3数据内外存交换

内存满了，内外存交换，把内存中的变换的部分写回磁盘，不变的部分释放后加载新的页，读的就不需要写回磁盘

InnoDB技术点

内存池：预分配的内存

内存页面管理：

页面的映射：磁盘数据加载到内存，这个关系不是静态的关系是动态的过程，形成映射表，记录内存和磁盘的关系

页面管理：空闲页，数据页（clean page），脏页（加载后被修改的页，要刷回磁盘）

内存淘汰：是否刷盘

页面淘汰 LRU 热数据放到头，冷数据放到尾

首先访问P2页面把P2放到头部，当需要访问新的页面，而内存空间已经满了，只能将尾部的P1释放掉，将P7I/O到头部

思考：全表扫描堆内存的影响

eg：500g数据 50g内存（热）

select * from 表，整个内存都是表的数据，这样内存全部的热数据都会被淘汰掉，这样叫内存失效或者内存污染，MySQL做全表扫描很慢，这个慢是i/o慢，select执行完之后又会很快，这说明热数据并没有被淘汰

解决方案：避免热数据被淘汰

1.访问时间+频率

页面

A—— —— —— - —— ——

B- - - - – - - - – - - - - - - - - -

C- - - - - - - - - - -

此时按照传统LRU会淘汰A，但是显然我们需要淘汰的是C，Redis做到了按照频率淘汰，会按照频率增加，不访问又会下降

2.两个LRU表

一级放，热度到一定级别的时候加入，二级的LRU，可以认为是热数据表

InnoDB MySQL是怎么解决的

Buffer Pool ：预分配的内存

Page：Buffer Pool 的最小单位

Free list：空闲page组成的链表

Flush list： 脏页链表

Page hash 表：维护内存Page和文件page的映射关系

LRU：内存淘汰算法 链接起来clean page和dirty page进行LRU 但是是优化的

Mysql 内存管理----LRU

页面装载：先从Free list找一个空闲空间free page，建立映射关系就可以写入数据，插入LRU-old当中，即插入冷表的头部

如果没有空闲位置LRU-old的尾部淘汰，如果刚好尾部数据正在使用，找一个dirty page进行Flush操作刷盘，释放内存

Free list 取>LRU-old 淘汰>LRU Flush

dirty page进行Flush操作刷盘，释放内存,讲dirty page变为clean page，是放在LRU尾部？还是直接放在Freelist。

5.2之前是放在尾部淘汰一次，目前使用的是直接放Free list

位置移动

1.从old到new，设想访问频率高的就放到new区，但是如果select*，加载一个页面会在短时间内多次访问一个页面，这样还是会把素有的new全部替换造成内存失效，内存污染。

所以移动的时机要考虑

innodb_old_blocks_time 大于这个时间有机会进入new区，大于时间还有人访问，这样select 很快访问完就不去访问了

2.从new到old 有页面从old到new 但是midpoint始终保证5:3自然而言new到尾部就进入到old

LRU_new到操作

链表有访问就放到头部？要考虑lock！MySQL设计思路：减少移动到次数

两个重要的参考：1.freed_page_clock:Buffer Pool淘汰页数。

​ 2.LRU_new长度的1/4

LRU_new中的A页面被访问，移动到head，此时的freed_page_clock为10000，上次移动到head的freed_page_clock是8000差值是2000用差值和LRU-new长度的1/4比较，如果大于就需要移动一次，这样可以保证数据不被误淘汰，也能减少移动的次数。

MySQL事物实现原理拆解以及设计深度剖析

MySQL事务的基本概念

事务特性

1. A 原子性 （atomicity）:强调事务的不可分割. 全部成功或者失败
2. C 一致性 （consistency）:事务的执行的前后数据的完整性保持一致. 通过AID保证
3. I 隔离性 （isolation）:一个事务执行的过程中,不应该受到其他事务的干扰 并行事物之间互不干扰
4. D 持久性（durability） :事务一旦结束,数据就持久到数据库 事物提交之后永久生效

事务并发问题（隔离）

赃读（Dirty Read）：读到未提交的数据。

不可重复读（Non-repeatable read）：两次读取结果不同

幻读（Phantom read）：select操作得到的结果所表征的状态无法支撑后续的业务操作（读到数据蒙圈，不知道干什么）

隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

eg：T1读区数据100，T2修改这个数据为200，T1再次读区数据为200，T2 rollback 100

**Read Committed（读取提交内容）**读已提交

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

eg：T1度数据100，T2 update 200，T1还会读到100，直到T2commit200，T1会读到数据200，可见T1点数据受到了干扰。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

eg：T1度数据100，T2 update 200，T1还会读到100，直到T2commit200，T1还会读到数据100，两次当前读不回读到不同数据

**Serializable（可串行化）**没人用

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务实现原理

MVCC

对版本并发控制

当前读 读存储引擎读数据

快照读 历史版本

一个uid会对应多个历史版本，历史版本由GB-TRX-ID记录，DB-ROLL-PTR记录上一条数据读存储位置，可以找到快照读位置，undolog中，存储引擎只存最新的一个数据。

解决读-写冲突

隐藏列

Read View

在执行select的同时，生成一个活跃事务列表eg【9，100，110，120】，此时可见性判断，小于9时已提交的，是可以看到的，大于9小于121是还未提交的事务，在事务链表中，要回退上一版本，不在事务链表，说明已经提交可以看到，大于121时创建快照之后创建的事务。想要回滚找历史版本。

undo log 回滚机制 历史版本都会存储在undolog

回滚日志

保证事务原子性

实现数多版本：mvcc的历史版本的实现原则

delete undo log：用于回滚，提交即清理

update undo log：用于回滚，同时实现快照读，不能随便删除，因为要快照读

思考：undolog如何清理：依据系统活跃的最小事务的id Read View，都能看见了就没有必要存储历史版本了。

​ 为什么innodb count （*）这么慢？

redo log

实现事务的持久性

记录修改

用于异常恢复

循环写文件

用户client发起请求到sever层转成SQL命令，修改数据命令，先记录undo log，历史版本用于回滚数据，更新内存数据，先把修改数据这件事写到redo log，更新完成，server执行一条提交一条，commit redo log刷盘，commit日志

redo——log刷盘时机

0: 写Buffer Pool，commit写内存Buffer，每秒聚合一次写文件，并刷盘，也就少受每秒刷盘最多丢1秒，性能极高，不安全

1：每次commit都刷盘，最严格，效率极低，最多丢1条。

2:每次commit提交写文件但是写的是OS Buffer每一秒刷一次盘，性能折中。

意义

体积小，日志的体积很小，页面的记录很多，记录页的修改，比写入页的代价低，小代价记录更新

末尾追加，随机写变顺序写入，发生改变的页固定不变

MySQL锁实现原理拆解以及深度剖析

锁粒度

行级锁

作用在索引上

聚簇索引&二级索引

eg ： delete from user where uid（pk）=134 ，锁索引和phone ，或者通过二级索引锁

前提是唯一索引，所以分析锁要有前提

RC： delete from user where uid（pk）=134 ，之后插入134/114，这时候会读到114这个就出现了幻读，不可重复读，RC隔离级别

RR： delete from user where uid（pk）=134，在红色三角出插入gap锁间隙锁

GAP锁锁住的锁

【131，140】-【134，130】

【134，130】-【134，150】 134 任何记录插不进去

【134，150】-【137，100】

间隙锁 两次当前读之间，其他的事务不回插入新的满足条件的记录

解决课重复读模式下的幻读问题，两次当前读读幻读问题

GAP锁不是加在记录上

GAP锁锁住的位置，是两条记录之间的GAP

保证两次当前读返回一致的记录

表级锁

线上锁表，只有全表扫面

select from user where phone = 134

phone没有建索引会全表扫描，每一个间隙建立GAP锁，所有记录加锁返回，然后有MySQL Server 进行行过滤。但是server会释放锁

类型

共享锁（s）

读锁 可以同时被多个事务获取，阻止其他事务对记录的修改

排他锁（x）

• 写锁 只能被一个事务获取，允许获得锁的事务修改数据

多有的当前读加排他锁，都有哪些是当前读 select for update，update，delete

InnoDB加锁过程

T1: update t user SET xx=xx where name = ‘f’

T2：select * from t_user where age>33 FOR UPDATE

加锁失败，事务1持有事务2的锁 ，事务2有事务2的锁，这样事务1加锁失败，事务2也加锁失败，死锁