-
脏读
-
当前事务可以读到其他事务修改但尚未提交的数据行
-
-
不可重复读
-
当前事务中，读取同一行数据两次，读取的结果不同。在两次读中的时间段，这行数据被其他事务修改并提交。一般发生在读时候不上读锁或者执行完读操作，但事务还没提交就释放读锁的情况下。
-
-
幻读
-
当前事务中，两次读取中的时间段，由其他事务新增或删除相关数据，导致两次读取数据数量不同。
-
不可重复读与幻读的区别
Non-repeatable reads are when your transaction reads committed UPDATES from another transaction. The same row now has different values than it did when your transaction began.
Phantom reads are similar but when reading from committed INSERTS and/or DELETES from another transaction. There are new rows or rows that have disappeared since you began the transaction.
- MySQL由以下几部分组成
-
-
连接池组件□ 管理服务和工具组件
-
SQL接口组件
-
查询分析器组件
-
优化器组件
-
缓冲（Cache）组件
-
插件式存储引擎
-
物理文件
-
**存储引擎是基于表的，不是基于数据库的
-
Master Thread
-
主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等
-
-
IO Thread
-
负责这些IO请求的回调（call back）处理
-
-
Purge Thread
-
回收undo页
-
-
Page Cleaner Thread
-
刷新脏页
-
对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发
通过show engine innodb status;可以查看内存状态
-
Free List: Free List中是可用的空闲页
-
LRU Lists: 在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，使用LRU算法对缓冲池进行管理。稍有不同的是InnoDB存储引擎对传统的LRU算法做了一些优化。在InnoDB的存储引擎中，LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置(默认新读取的页插入到LRU列表尾端的37%的位置 即old blocks)。这个算法在InnoDB存储引擎下称为midpoint insertion strategy
midpoint前半部分定义为new blocks，后半部分定义为old blocks
midpoint防止热点数据被刷出缓存
被读到LRU List里，后半部分的数据，要等待一段时间（innodb_old_blocks_time default 1000ms）后，如果再被读取时候还在list中，才会被加入到前半部分
InnoDB存储引擎首先将重做日志信息先放入到redo log buffer，然后按一定频率将其刷新到重做日志文件
- 这三种情况会讲buffer刷到磁盘
-
-
Master Thread每一秒将重做日志缓冲刷新到重做日志文件
-
每个事务提交时会将重做日志缓冲刷新到重做日志文件
-
当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件
-
- Write Ahead Log
-
-
当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D（Durability持久性）的要求。
-
倘若每次一个页发生变化，就将新页的版本刷新到磁盘,IO开销很大，若热点数据集中在某几个页中，先写入缓存，然后定期把缓存刷入磁盘，就合并了一个页中的多次修改，减少了磁盘IO
innoDB设有checkpoint，就是分时段把缓存中的脏页刷到磁盘，innoDB中有两种checkpointSharp Checkpoint和Fuzzy Checkpoint
Sharp Checkpoint将所有脏页全量刷新到磁盘，对数据库可用性影响很大，默认只在数据库关闭时候执行
Fuzzy Checkpoint将部分脏页增量刷新到磁盘，数据库运行时候都是进入Fuzzy CheckPoint
- innoDB中有一下集中Fuzzy CheckPoint
-
-
Master Thread Checkpoint
-
Master Thread中发生的Checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，异步非阻塞
-
-
FLUSH_LRU_LIST Checkpoint
-
InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用，如果没有，那就要移除LRU List尾部的页，如果有脏页，就需要进入checkpoint刷脏页。Page Cleaner中执行，异步非阻塞
-
-
Async/Sync Flush Checkpoint
-
重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的，Page Cleaner Thread，异步非阻塞
-
-
Dirty Page too much Checkpoint
-
脏页太多时进入checkpiont，默认脏页数据超过75%时进入
-
-
-
错误日志
-
记录mysql运行的错误信息，默认启动
-
-
慢查询日志
-
记录慢查询，默认不启动
-
-
查询日志
-
记录所有对mysql的请求信息
-
-
二进制日志（bin log）
-
记录对mysql执行更改的所有操作，默认不启动，对mysql性能影响不大
- bin log的主要作用
-
-
恢复数据
-
复制数据，实现主从，master→slave、master→slave→slave 多级主从
-
检查是否有注入攻击
-
-
bin_log_format设bin log的格式，有3种格式
-
statement
-
记录的sql语句，会导致主从机器上的rand、uuid函数结果不同
-
-
row
-
记录的表行的变更情况，避免随机的问题，但会导致日志文件变大很多
-
-
mixed
-
mysql自行选择格式，大多数时候用statement，小部分情况用row
-
页中的虚拟行记录，用来界定记录边界，Infimum是下界，它的主键比页中所有主键都小，Supremum是上界，它的主键比页中所有主键都大，这两个界限值都是在页创建时候被建立
一个稀疏目录，存储指向页中record的指针，这些指针称为slot，用于快速查找页中的record。每个slot追踪了6个record，slot中按照主键的逻辑顺序去追踪record。由于slot是按主键排序，并且固定尺寸的，所以在页中很容易通过二分查找查找数据。
由于页中使用的是稀疏目录，所以在slot间进行二分查找只能得到一个粗略的位置，即slot中主键序最小的数据（二分查找最终查找到一个slot，slot中最多有6条数据）。之后InnoDB会利用record header中的n_owned属性确定当前slot的实际size，之后按数量遍历。
-
通过二级索引，在b-tree中找到目标记录的主键id
-
通过聚簇索引在b-tree中找到主键id所在的数据页
-
若数据页不在缓冲池中，加载到缓冲池
-
在数据页通过二分查找在slots中找到目标数据所在的slot
-
通过slot中第一条数据的n_owned数据确定当前slot的实际size，之后遍历，找到目标数据行
假设表有1000w数据，对主键做10个hash的分区，每个表约有100w数据。
此时执行select * from table where pk = @pk
通过主键查询可以只在一个分区中查找数据，确实速度更快了
但是由于b-tree结构的树高不高，可能100w数据和1000w数据的b-tree高度都是2，那实际io次数都是2，并不会变快。
如果1000w数据的b-tree高度为3，那确实减少1次io，提高速度
如果执行select * form table where key = @key
这是分区表需要扫描所有的10个分区，假设每个分区2次io，也需要20次io，而单表只需要2-3次io
所有记录节点按照键值大小顺序存放在叶子节点上，相邻叶子节点通过指针相连。
插入时候可能需要调整树结构，有3种情况
例：
向5-6中插入键为28的节点
匹配情况1，叶子页和索引页都没满，直接插入
向5-7中插入键为70的节点
匹配情况2，叶子页满，索引页没满，拆分叶子页，找到中间的节点60，放到索引页，小于中间节点的记录放左边，大于等于中间节点的记录放右边
向5-8中插入键为95的节点
匹配情况3：叶子页满，索引页也满，先插入叶子页，叶子页满，拆分叶子页，找到中间节点85，小于中间节点的记录放左边，大于等于中间节点的记录放右边，中间节点85放到索引页，此时索引页满，拆分索引页，小于中间节点的记录放左边，大于中间节点的记录放右边，中间节点放入上一层的索引页
B+树旋转
插入时候可能产生大量的页分裂，导致大量IO，B+树通过旋转来减少页分裂
当叶子页满，但该页的左右兄弟页没满的时候，B+树不会拆分页，而是将记录移动到兄弟页上。通常左兄弟页会被首先检查用来做旋转操作
向5-7中插入键为70的节点-旋转
叶子页满，左兄弟页没满，最左节点移动到左兄弟，替换索引节点中的值为新的最左节点，向叶子页中添加值为70的节点
B+树使用填充因子控制树的删除变化，假设填充因子为50%，意味着在页中节点被删除后，如果页中节点数 / 扇出 < 50%，就会进行页的合并
B+树删除有3种情况：
删除5-9中值为70的节点
匹配情况1：删除后，叶子页的节点数 / 扇出 !< 50%，直接删除
删除5-11中值为25的节点
匹配情况1：删除后，叶子页的节点数 / 扇出 !< 50%，直接删除，同时25为索引页节点，25右边的节点28替换索引页中节点
删除5-12中值为60的节点
匹配情况3：删除后，更新索引页中值为60的节点为65，叶子页的节点数 / 扇出 < 50%，合并该节点和左兄弟节点，同时合并索引页60节点的左右孩子
create table t (
a int not null,
b varchar(8000),
c int not null,
primary key(a),
key idx_c(c)
) engine=innodb
insert into t select 1, repeat('a', 7000), -1;
insert into t select 2, repeat('a', 7000), -2;
insert into t select 3, repeat('a', 7000), -3;
insert into t select 4, repeat('a', 7000), -4;B+树聚簇索引存储逻辑图
在InnoDB中，B+中插入数据并不总是从页中间分裂，这可能导致频繁的分裂
InnoDB基于数据库场景，对B+树插入做了一些改进
-
若插入是随机的，则取页中间记录作为分裂点
-
若往统一方向进行插入的记录数量 >= 5，且目前已定位到的记录之后存在 >= 3条记录，则分裂点为定位到的记录后的第三条记录
-
若往统一方向进行插入的记录数量 >= 5，且目前已定位到的记录之后存在 < 3条记录，则分裂点为待插入记录
匹配条件2的分裂
匹配条件3的分裂
把通过辅助索引查出来的主键进行排序后，再回表，将随机io转化为顺序io，提升io性能
MRR带来的好处
-
回表时候对主键排序，随机io变为顺序io
-
减少缓冲池中页被替换的次数
-
批处理主键回表的查询操作
InnoDB中实现了4种锁
-
共享锁 S Lock
-
排他锁 X Lock
-
意向共享锁 IS Lock
-
意向排他锁 IX Lock
其中 共享锁、排他锁都是行锁，意向锁是锁
意向锁
意向锁代表事务希望在更细粒度上加锁
例：事务在表t的某一页上加了IS Lock，表示事务希望在这一页上的某行或某些行上加S Lock
所以在InnoDB中，如果把数据库存储结构看作一颗树 数据库 - 表 - 页 - 数据行，那么如果要对数据行上 S Lock 或 X Lock，那么就要对其所在的库、表、页都上相应的意向锁
例：如果要对页上的记录r上X锁，那么分别要戳数据库A、表、页上意向锁IX，最后对记录r上X锁
意向锁的作用：用来快速判断 行锁是否需要等待表锁 或 表锁是否需要等待行锁
例：事务1对表1上S锁，事务2需要对表1的数据行r上x锁，那么事务2在锁住行r之前，需要对表1上IX锁，此时IX锁需要等待s锁释放。
例：事务1对表1的行r上X锁，同时会对表1上IX锁，如果此时事务2需要在表1上S锁，那么事务2需要等待表1上的所有IX锁释放
因此，在InnoDB中，意向锁只会导致表锁阻塞，而只有表锁可能阻塞意向锁
如何分析锁占用情况
通过表information_schema.INNODB_TRX可以查看当前执行中的事务及锁等待状态，通过表information_schema.INNODB_LOCKS可以查看锁对象的详情
InnoDB通过多版本控制（MVC）实现了非锁定读，当读取正在delete或update的行数据时，不需要等待X锁释放，可以通过快照读取。底层是通过事务中的undo段实现的。
非锁定读提升了并发效率，InnoDB在默认的隔离级别下 默认是开启非锁定读的
这种通过多版本控制技术，控制并发事务的行为叫做多版本并发控制（MVCC）
InnoDB中有3种锁算法：Record Lock、Gap Lock、Next-Key Lock
Record Lock，行锁，最基础的锁，锁住单独的一行
Gap Lock，间隙锁，锁定一个范围但不包括记录本身
Next-Key Lock，临键锁，锁范围同时锁定记录本身
InnoDB中，对于非唯一索引上行的锁默认都是使用一个Next-Key Lock + 一个Gap Lock实现的，对于唯一索引上行的锁都是通过Recork Lock实现的。
Gap Lock，Next-Key Lock 都是为了防止多个事务讲记录插入到统一范围内，避免幻读
唯一索引锁定示例：
create table t (a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;执行sql
#事务A，不提交事务
select * from t where a = 5 for update;
#事务B，提交事务，不会被阻塞
insert into t select 4;由于列a是唯一索引，因此事务A执行时Next-Key Lock降级为Record Lock，只锁定a=5一行，之后事务B的插入不会被阻塞
非唯一索引锁定示例：
create table z (a int, b int, primary key(a), key(b));
insert into z select 1, 1;
insert into z select 3, 1;
insert into z select 5, 3;
insert into z select 7, 6;
insert into z select 10, 8;执行sql
#事务A，不提交事务
select * from z where b = 3 for update;
#事务B，执行这些sql都会被阻塞
#a=5被加上Record Lock
select * from z where a = 5 lock in share mode;
#(1, 3]被加上Next-Key Lock
insert into z select 4,2;
#(3, 6)被加上Gap Lock
insert into z select 6,5;事务A执行时，会对辅助索引上范围(1, 3]加Next-Key Lock，对(3, 6)加Gap Lock。对主键索引a=5加上Record Lock
由于数据库中的锁并不是完全互斥的，所以会带来一些问题，主要包括脏读、不可重复读、幻读，参考 读现象
Atomicity
原子性，事务中的操作要么都执行，要么都不执行
Consistency
一致性，事务只能讲数据库从一种一致状态转换到另一种一致状态，确保事务不能破坏数据库约束（例如主键约束、唯一键约束、非空约束等）
Isolation
隔离性，多个事务间操作的数据要隔离，通常数据库都设有隔离级别，参考事务隔离级别
Durability
持久性，事务一旦提交，其产生的变化就是永久的
事务分类：
-
扁平事务
-
带有保存点的扁平事务
-
链式事务
-
嵌套事务
-
分布式事务
扁平事务可以看作是只有一个保存点的保存点事务，即在事务开始时候设置一个保存点，只能回滚到这个保存点
带有保存点的扁平事务，即在事务执行过程中，可以设置多个保存点，回滚时候可以指定保存点回滚到指定位置
链式事务，在提交一个事务的时候直接开启下一个事务，可以看作是只能回滚到最近一个保存点的保存点事务
嵌套事务，一颗事务树，只有在root提交后，下层节点提交的事务才能生效。某一节点的事务回滚后，会引起它所有的子事务回滚，可以通过保存点事务实现
ACID通过redo log和undo log实现
InnoDB通过Force Log at Commit机制实现持久性，即在事务提交时必须先事务日志写入到redo log 和 undo log，才算事务完成。其中redo log用来保证D，undo log 用来保证AC，同时基于undo实现了MVCC，而基于MVCC保证了I
redo用来恢复数据，undo用来回滚数据
redo由两部分组成：redo log buffer、redo log file，将重做日志缓冲刷到重做日志磁盘的时候，操作系统会先将日志从重做日志缓冲刷到文件系统缓冲，这时InnoDB会调用一次fsync，把日志从文件系统缓冲刷到磁盘
InnoDB默认是在每一次事务提交的时候都会调用一次fsync。（master线程中每1秒也会进行一次redo的fsync）
而在redis中如果采用aof持久化，默认是将日志写入缓存，每1秒执行一次fsync
fsync这个io很花时间，所以即使mysql 的缓冲池足够大，性能可能也仍然比不上redis。但InnoDB在每次事务提交都调用ysync的行为确保了数据更加不易丢失。
在InnoDB和redis中，调用调用fsync的频率都是可以通过参数控制的
-
redo log是InnoDB在引擎层实现的，bin log是mysql服务层实现的，因此所有的存储引擎都由bin log
-
redo log记录的是页的修改，bin log记录的是sql语句
-
redo log写入磁盘不仅在事务提交时候执行，master线程每1秒、10秒都会执行，而bin log写入磁盘只在事务提交时候执行一次
无论是在缓冲池还是磁盘，redo log都是一block格式保存的，每个block大小设置为512，和磁盘扇区大小一致，因此写入磁盘可以保证原子性，这是InnoDB对写入磁盘做的设计优化
redo log buffer 由一个redo log block 数组组成，
redo log中的logblock在哪些时候被刷到磁盘：
-
事务提交时
-
当log buffer中由一半的内存空间已经被使用时
-
log checkpoint时
Log Sequence Number 日志序列号，表示写入重做日志的字节总量，单位是字节
例：当前redo log的LSN=1000，事务t1写入了100字节的redo log，此时LSN=1100，之后事务t2写入了200字节的redo log，此时LSN=1300
在页的header中页记录着该页的LSN，表示该页最后刷新时候的LSN
数据库启动时，假如InnoDB检测到redo log 中LSN=1300，并且该事务已经提交，而页P中LSN=1000，那么数据库会对页P进行数据恢复，将重做日志应用到P
恢复数据时，redo log是物理页修改，是幂等的，bin log是执行sql语句，不是幂等的（例如 insert操作，bin log执行多次会插入多条）
与redo不同，undo不是存放在log file中，而是存放在共享表空间中的 undo segment中
undo也是逻辑日志，恢复数据时候只是通过sql逻辑的恢复到原来的数据。例：在执行insert语句后，undo会记录 一个delete语句，执行rollback，undo会执行delete语句，恢复数据
InnoDB中的mvcc同过undo实现，当用户读取一条数据时候，如果已经被其他事务锁定，当前事务可以通过undo读取之前版本的数据，实现非锁定读
InnoDB的共享表空间（ibdata1）中有定义 rollback segment，每个rollback segment中有1024个undo log segment，每个undo log segment中分配undo page记录undo log
class DefaultTableSpace {
}
class RollBackSegment {
}
class UndoLogSegment {
}
class UndoPage {
List<UndoLog> undoLogs
}
DefaultTableSpace *--> RollBackSegment : 老版本只有1个RBS，新版本可以支持128个RBS
RollBackSegment *--> "0...1024" UndoLogSegment
UndoLogSegment *--> "0...n" UndoPage
由于undo log存放在页中，是需要持久化的，而redo log是基于页修改产生的日志，所以在产生undo log时，也会产生redo log
在事务提交时候会执行2个操作：
-
将undo log放入history list中，之后由purge线程清理。但此时undo log还不能马上被删除，因为可能有其他事务需要通undo log访问之前版本的数据。因此，是否删除、哪时候删除都由purge线程判断执行
-
判断undo log所以在页是否可以重用，若undo log页的使用空间小于3 / 4，就可以重用，之后的undo log会继续记录在该页
事务提交时候会通过头插将undo log放入到history list中，因此history list中的undo log实质是按事务提交时间排序的
purge线程在清理undo log时候，会先从history list尾找到一个需要被清理的undo log，然后清理，然后清理该undo log页中其他可以被清理的undo log，之后继续向前遍历history list
这种先通过history list找undo log，再从该页找undo log的方式，再一次遍历中，每个undo log页只需要被加载到内存1次，减小了磁盘IO，提高purge效率
explain 按照mysql读取表数据的顺序依次为sql语句中的每一个表返回一条数据
mysql的联表：mysql从第一个表中读取一行，然后在第二个表中找到匹配的行，然后在第三个表中找到匹配的行，以此类推。当所有的表被处理完后，MySQL输出所选的列，并通过表列表回溯，直到找到一个有更多匹配行的表。从该表中读取下一行，然后继续处理下一个表。（深度优先遍历）
id
id
select_type
查询类型，主要的几种类型：
-
simple:简单查询，不含子查询和联合查询
-
primary:嵌套查询和联合查询中的最外层查询
table
表名
partitions
分区
type
join type，表示表是如何连接的，在联表查询的时候很关键
-
system
查只有一行数据的系统表的查询，当常量连接
-
const
最多只能读取到一行数据的查询，当常量连接
只会发生在将 主键=常量 或 唯一键=常量的时候 xx字段=常量的时候，可能也只读取一行数据，但是不满足最多只能读取一行数据
-
eq_ref
在链表时候，深度优先遍历时，对于前面表中的每个行组合，只从该表中读取一行。 当连接使用索引的所有部分，并且索引是主键或UNIQUE NOT NULL索引时使用。
-
ref
对于前面表中的每个行组合，将从该表中读取具有匹配索引值的所有行
-
fulltest
使用全文索引做连接
和ref类似，但是同时也判断是否为空
-
index_merge
使用了index_merge，使用一张表上的多个索引查询数据，之后合并。
-
unique_subquery
-
index_subquery
-
range
只检索给定范围内的行，使用索引选择行
-
index
使用索引扫描
-
all
对前面表中的每个行组合进行全表扫描
possible_key
mysql可能选择的索引
key
mysql实际决定使用的索引
key_len
mysql实际决定使用的索引的长度，通过key_len可以确定 在一个联合索引中，mysql实际使用了左前缀的那些部分
ref ref表示了某些列（列名）、常量集合（使用> 或< 等，展示数量）、某个常量（展示const）用来和索引进行比较
rows 执行查询时候需要扫描的行数，是个估计值
filtered 被条件过滤掉的数据百分比，是个估计值
extra 额外信息
-
Using filesort 需要进行一次额外的排序
-
Using index 可以从索引获取到全部数据，不需要根据主键回表（聚簇索引、覆盖索引）
-
Using index condition 使用Index Condition Pushdown
-
Using MRR 使用Multi-Range Read
-
Using temporary mysql会创建临时表来保存结果，通常在查询中的group by 和 order by的字段不一样的时候会发生
-
Using where WHERE子句用于限制哪些行与下一个表匹配或发送给客户端。除非您特别想从表中获取或检查所有行，否则如果Extra值不是Using where且表连接类型为all或index，则查询中可能会出现问题。