mysql技术内幕-innodb

表中1/16的数据发生变化

stat_modified_counter>2000000000

查看索引信息show index

alter table add/drop index

可以只对一个列的开头部分数据进行索引，alter table add key idx_b (b((100))

按照没涨表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页
非叶子节点的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录

叶子节点存放 索引的键值对，不包含行记录的全部数据，但包含一个 告诉 innodb 引擎哪里可以找到与索引相对应行数据的 书签（bookmark）

当通过辅助索引来寻找数据时 ，Innodb 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录

将查询得到的辅助索引键值存放与一个缓存中，这时缓冲中的数据是根据辅助索引键值对排序的

将缓存中的键值根据RowId进行排序

根据Rowid的排序顺序来访问实际的数据文件

倒排索引需要将 word 放到一张表中，这张表称为 auxiliary table 辅助表

为了提供检索性能，辅助表可能有多个，没张表根据word 的 latin编码进行分区

引擎根据 文档分词 将分词结果放入到FTS Index Cache(全文检索索引缓存)中，然后再根据情况刷新到 辅助表

在一个辅助表中存储单词与单词自身在一个或多个文档所在位置之间的映射

inverted file idex 其表现为{单词，[单词所在的文档ID数组] }

full inverted index   {单词，[(单词所在文档ID,在文档中的位置)]}

Innodb采用 full inverter index 的方式

每张表只能有一个全文检索的索引

由多列组合而成的全文检索的索引列必须使用相同的字符集和排序规则

不支持没有单词界定符的语言，如中文、日语、韩语等

将关键字转换为自然数，通过除法散列、乘法散列或者全域散列，数据库一般使用除法散列

链接法

数据页

undo页

系统页

事务数据页

插入缓冲位图页

插入缓冲空闲列表页

未压缩的二进制大对象页

压缩的二进制大对象页

file header 文件头

page header 页头

infimun / Supremum records

user records 用户空间，即行记录

free spack 空闲空间

page directory 页目录

file trailer 文件结尾信息

Compact

Redundant

compressed/dynamic

主要用于区分日期列的分区

Create table sales(
date datetime
money int
) engine = innodb
partiton by range (to_days(date))(
partition p202001 values less then(to_days('2020-02-01')),
partition p202002 values less then(to_days('2020-03-01'))
)

与range分区相似，但是分区列的值是离散的

create table
t(a int)
engine = innodb
partition by list(a)(
partition p0 by values in (1,2,3,4),
partition p1 by values in (5,6,7,8)
)

将数据均匀的分布到预先定义的各个分区中，不需要像range/list指定分区列的集合或范围
用户将要进行哈希分区的列值指定一个列值或者表达式，以及指定被分区的表将要被分隔成的分区数量

哈希分区中， partition不需要指定分区名，但是 partition {num} 表示 分区的数量

create table t_hash(
a int
b datetime
)engine = innodb 
partition by hash (year(b))
partition 4;

与HASH分区相似，但是不需要用户指定hash 函数，而是使用mysql提供的函数分区

create table t_key(
a int 
b datetime 
) engine = innodb
partition by key(b)
partition 4;

前面四种的分区的条件是：数据必须是整数，如果不是整数，必须通过函数将其转化为整数。

从MYSQL5.5版本开支持 columns 分区，可以直接使用非整形的数据进行分区，分区根据类型直接比较而得。

在分区的基础上在进行分区，也称为复合分区；运行在 range 和 list 分区基础上再进行 hash key 分区

每个子分区的数量必须相同

要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的子分区

每个子分区必须有一个唯一的名字

 可以通过 data directory = '/disk0/data' 来将一个表的分区文件分配到多个磁盘中，可以提升性能

create table ts (a int ,b date)
partition by range (year(b))
subpartition by hash(to_days(b))(
partiotion p0 values less than(1990)(
subpartition s0,
subpartition s1
),
partition p1 values less than maxvalues(
subpartition s2,
subpartition s3
)
)

statement

row

mixed

恢复

复制，通过复制二进制日志文件是一台远程的从数据库和一个 master  服务器进行实时同步

审计

核心线程，负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、Undo页的回收

InnoDB 使用AIO处理IO请求，提高数据库性能，IO Thread负责这些请求的回调

write/read/insert buffer/log io thread

事务被提交后，其使用的undolog可能不再需要，需要 purge thread来回收已经使用并分配的undo页

将之前版本中脏页的刷新操作都放入到单独的线程中来完成

减轻原Master Thrad 的工作及对于用户查询线程的阻塞，进一步提供InnoDB存储引擎的性能

引擎读取数据不是直接读取硬盘，而是通过一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响

读缓存，读取数据页时，先判断该页是否在缓冲区中，如果缓存命中则返回；如果不存在，那么先从磁盘读取该页到缓存池（FIX过程），再返回数据

写缓存，所有的修改操作，都先修改缓存池的值，再通过某种机制刷新到硬盘

缓冲池数据页类型

多缓冲池实例，减少数据库内部竞争，增加数据库的并发能力

内存管理，淘汰机制

InnoDB首先将重做日志放入到这个缓冲区，然后按一定的频率将其刷新到重做文件

innodb_log_buffer_size

刷新情况

数据结构本身的内存进行分配时 ，需要从该区域分配

数据库只需要到checkpoint后的日志进行恢复

每秒一次的操作

每十秒一次的操作

10+1 而不是 9+1的操作

删除无用的 undo 页 总是

合并20个插入缓冲 总是

跳回主循环 总是

不断刷新 100 个页直到符合条件 可能（跳转到 flush loop）

不断刷新 100 个页直到符合条件

跳到 suspend_ loop

没什么事 ，就挂起来

索引是辅助索引

索引不是唯一的

通过全局 insert buffer B+树，记录插入的表，key,insert buffer 顺序，插入记录的各个字段

doublewrite 有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另一部分是物理磁盘上共享表空间中连续的 128页，大小也是2M。
在对脏页进行刷新时 ，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后分两次每次一1M顺序写入共享表空间的物理硬盘，然后马上调用fsync刷盘。
在完成了共享表空间的写入后，再将doublewrite buffer 中的页写入到真实的表空间文件。

如果写入磁盘的过程发生崩溃，在恢复过程汇总，引擎会先从共享表空间中的doublewrite中找到该页的部分，将其复制到表空间文件，再应用重做日志。

表示mysql数据库关闭时，innodb需要完成所有的full purge 和 merge insert buffer，并将所有的脏页刷新会磁盘。耗时长

默认值，表示不需要完成 full purge 和 merge insert buffer 操作，但是在缓冲池中的一些数据还是会刷新会磁盘

表示不完成 full purge 和 merge insert buffer 操作，也不刷新脏页，而是将日志都写入日志。这样不会有任何的事务丢失，但是下次数据库启动时，需要进行恢复操作

允许多个事务读同一行数据

只允许一个事务读取和修改数据

将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁

意向锁是一个树状结构锁，对下层对象加锁，需要对上层对象上锁

意向共享锁 IS Lock
事务想要获得一张表中某几行的共享锁

意向排他锁 IX Lock
事务想要获得一张表中某几行的排他锁

总是锁住索引记录，就是会锁定主键

当事务隔离级别是 READ Committed时，仅采用 record lock

如果仅采用 record lock，那么别的事务仍可以插入新的数据，对于范围查询，可能两次查询的数据条数会不一样

用于解决 Phantom Problem 问题

可以显示关闭间隙锁
将事务级别设置为 read committed
将参数 innodb_locks_unsafe_for_binlog设置为1

假设有 t(id int ){1,2,4,6,7} 表数据，当执行select where id=4 for update时，除了会对 （2~4）的数据加next-key lock 外，还会对（4~6）加上 gap lock 锁
之所以这样操作，是为了防止其他事务在 （4~6）插入一条 id=4 的数据产生幻读问题

innodb对于行的查询都是采用这种算法，当查询的索引含有唯一索引时，会降级为record lock

Repeatable Read 级别下的加锁方式

事务T1将行记录r更新为V1,但是不提交

事务T2将行记录r更新为V2，不提交

事务T1提交

事务T2提交

事务T1数据丢失

采用 串行化 的隔离级别，在读取数据的时候加上 X锁

用户代码层面使用锁，依然是串行化

检查到死锁马上放弃等待，直接回滚

给死锁设置超时，超时到达后回滚
但是简单的按照超时就回滚的做法是不公平的，因为可能有的事务需要回滚的信息比另一个多

wait-for graph（等待图）

0

1

2

binlog用来进行 ponit-in-time的恢复及主从复制环境的建立

binlog是数据库的上层产生的，任何引擎都会产生二进制文件

二进制日志是逻辑日志，记录的是对应的sql文件

二进制文件的恢复不是幂等性的，而redo是
如：二进制日志记录的是事务提交的sql，如insert，两次 insert 会产生两条数据
但是 redo log 是 物理页修改记录，page(2) offset 3 val = 4，这样即使执行再多次，结果也是一样的

log sequence number ，表示日志序列号，占用8个字节，单调递增

LSN的含义

每个页的头部，有一个FIL_PAGE_LSN，记录了该页的LSN，表示该页刷新时LSN的大小，用来判断也是否需要从重做日志进行恢复操作

checkpoint技术记录了已经刷新到磁盘的数据点，因此从redo 日志恢复时只需要恢复checkpoint后面的部分

insert undo log

update undo  log 

资源管理器
resouece managers

事务管理器
Transaction manager

应用程序
application program

循环提交会导致不知道其中哪个事务发生了回滚
因为每次提交都会调用 fsync 刷新磁盘，所以性能也不好

值数据库运行中直接备份，对正在运行的数据库操作没有任何影响

在数据库停止的情况下，一般只需要复制相关的数据库物理文件即可

同样在数据库运行中进行，但是会对当前数据库操作有所影响，如加一个全局读锁以保证备份数据的一致性

指通过文件系统支持的快照功能对数据库进行备份

一本是文本文件，内容如一条条SQL
好处是可以观察导出的文件内容，适用于数据库的升级，迁移等工作
缺点是执行SQL需要较长的时间

复制数据库的物理文件，即可以是数据库运行中的复制，也可以是在数据库停止运行时直接的数据文件复制
恢复时间比逻辑备份短很多

对数据库进行一个完整的备份

在上次完全备份的基础上，对更改的数据进行备份

指对mysql数据库二进制日志的备份，通过对一个完成备份二进制日志的重做来完成数据库的point-in-time的恢复工作

mysql复制原理就是异步实时地将二进制日志重做传送并应用到从数据库中

记录备份开始时，innodb存储引擎重做日志文件监测点LSN

复制共享表空间文件以及独立表空间文件

记录复制完表空间文件后，Innodb存储引擎重做日志文件检查点的LSN

复制在备份时产生的重做日志

在线备份，不阻塞任何的SQL语句

备份新能好，备份的实质是复制数据库文件和重做日志

支持压缩备份，通过选项，可以支持不同级别的压缩

恢复表空间文件

应用重做日志

首先完成一个全备，并记录下此时检查点的LSN

在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当时检查点的LSN