01 ~ 2 基础架构
MySQL基础架构示意图
大体来说MySQL分为Server层和存储引擎层。
Server层有连接器、查询缓存、分析器、优化器、执行器；存储引擎层负责数据的存储和提取。有的功能有存储过程、触发器、视图等。
通常select * from T where ID =10，内部的流程
- 连接器负责与客户端完成TCP握手后，连接器就要开始认证你的身份。用户密码认证通过后，就会查询你拥有的权限。
- 会进行缓存查询，命中则直接返回结果
- 之后分析器会先进行 词法分析+语法分析
- 之后进行优化器的处理决定使用哪个索引、各个表的连接顺序。
- 之后进入执行器的阶段，先进行权限的判断，后进行表的引擎定义去使用引擎提供的接口。
更新语句的执行流程
- 通过连接器使得客户端连接数据
- 分析器通过词法和语法得知这是一条更新语句
- 优化器决定使用ID这个索引
- 执行器负责具体执行，找到这一行进行更新
日志
redo log是 InnoDB引擎特有的，binlog是MySQL的server层实现的所有引擎都可以实现； redo log是 物理日志记录“在某个数据页上做了什么修改”，binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段+1； redolog是循环写，空间固定；binlog是可以追加写入。
执行器和InnoDB在执行update语句
- 执行器先找引擎取ID=2这一行，如果这一行的数据页本来在内存中，就直接返回执行器，否则需要先从磁盘读入内存再进行返回。
- 执行器拿到引擎给的行数据把这个值+1，调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare阶段，告知执行器执行完成，可以提交事务。
- 执行器生成这个操作的binlog，并把binlog写入磁盘。
- 执行器调用引擎的提交事务接口，引擎吧刚刚写入的redo log提交改成commit状态。更新完成。
两阶段提交
两阶段提交让两个状态保持逻辑上的一致。跨系统维持数据逻辑一致性时常用的一个方案。
保证MySQL异常后不丢失
数据不丢失 innodb_flush_log_at_trx_commit = 1 ：每次事务的redo log都持久化到磁盘 binlog不丢失 sync_binlog = 1 ：表示每次事务的binlog都持久化到磁盘
03 ~ 5 事务隔离 索引
事务隔离
当多个事务同时执行的时候，可能出现
- 脏读（dierty read）A事务正在修改数据但未提交，B事务读取的是未提交的数据
- 不可重复读（non-repeatable）A事务中两次查询同一数据的内容不同，B事务在A事务两次读取之间更改了词条数据。
- 幻读（phantom read）在同一事物中两次相同查询的数据条数不一致
隔离级别
- 读未提交：一个事务还没提交时，它的变更就不能被别的事务看到
- 读提交：一个事务提交之后，他做的变更才会被其他的事务看到
- 可重复读：一个事物执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的
- 串行化：写会加锁，读也会加锁
同一条记录在系统中存在多个版本，就是数据库的多版本并发控制MVCC。
索引
重建索引提高空间利用率 alter table T engine=InnoDB
索引上
索引的出现就是为了提高数据查询的效率，像书的目录一样。 索引有不同的数据结构，数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，先关注他的数据模型，才能从理论上分析这个数据库的适用场景。 MySQL中，索引是在存储引擎实现的。
索引的常见模型的区别
- 哈希表这种结构只适用于等值查询，范围查询不行全表扫描
- 有序数组在等值查询和范围查询场景中非常优秀，但在更新数据成本太大，只适用于静态存储引擎
- 二叉树搜索和更新的复杂度都是o(log(N))，但是如果树特别高的时候效率会比较差，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。
InnoDB的索引模型 InnoDB使用了B+树索引模型，每一个索引在InnoDB里面对应一棵B+树。
索引分类
- 主键索引：叶子结点存的是整行数据 = 聚簇索引
- 非主键索引：叶子结点内存是主键的值 = 二级索引
哪些场景使用自增主键，哪些场景不应该 not null primary key auto_increment
- 自增主键的插入数据模式，追加操作，不涉及到挪动其他记录，不会触发叶子节点的分裂。（B+树为了维护索引有序性，插入新值的时候有必要的维护）
- 业务逻辑的字段做主键，则往往不容易保证有序插入，写数据成本相对较高
- 主键越小，普通索引的叶子节点也越小，占用的空间也越小
- kv场景适用业务主键
索引下
覆盖索引 覆盖索引是指索引树已经覆盖了我们查询的需求，不需要回表，减少树的搜索次数，显著提升查询性能，是一个常用的性能优化手段。
前缀索引 B+树这种索引结构可以利用索引的最左前缀来定位记录。可以少维护一个索引。比如（a，b）相当于建立了 a、ab的索引。
索引下推
MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
06 ~ 8 全局锁、表锁、行锁
全局锁
MySQL提供了一个加全局读锁的方法，命令是flush tables with read lock FTWRL 当你需要整个库处于只读状态的时候，可以使用这个命令，之后其他线程的语句会被阻塞。 数据更新语句、数据定义语句、更新类事物的提交语句。 使用场景：全库逻辑备份。 这里就是使用事务的可重复读隔离级别，mysqldump备份工具使用 -single-transaction参数，就会开启一个事务，确保拿到一致性视图。
表锁
lock tables ... read/write
unlock tables
行锁
- MySQL的行锁是在引擎层各个引擎自己实现的，MyISAM引擎不支持行锁
- 在InnoDB事务中，行锁是在需要的时候才加上，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这叫两阶段锁协议。
- 如果你的事务中需要锁多个行，把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
两种策略
- innodb_lock_wait_timeout超时时间
- innodb_deadlock_detect=on 开启发起死锁检测，主动回滚。（耗费大量cpu资源）
解决热点行更新导致的性能问题
- 临时把开关关掉。确保业务无死锁。
- 控制并发度
"快照"在MVCC是怎么工作的
InnoDB每个事物有一个唯一的事务ID，transaction id事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。
数据库中的一行记录，有多个版本row，每个版本有自己的rowtrx_id.
视图数组和高水位组成了当前事务
例子
有效更新的事务C [1,1] => (1,2) row trx_id = 102
有效更新的事务B [1,2] => (1,3) row trx_id = 101
A查询的时候发现101，比高水位大，不可见，往上是102也不可见。往上是90是低水位可见。
所以是1. 称之为一致性读。
例子-更新逻辑
更新数据都是先读后写的，而这个读只能读点前值，称为当前读。
除了update语句外，select语句如果加锁，也是当前读。
select k from t where id=1 lock in share mode
select................................................. for update
例子2
事务C没提交写锁还没释放，事务b是当前读，需要等待。
一致性读、当前读、行锁串起来了。
一致性读依赖mvcc快照，利用事务id递增特性，来做读取数据时历史版本的选择；当前读实际上是由行锁来实现的，持有行锁的更新操作才能进行当前读，否则更新操作会阻塞。
事务的可重复读的能力怎么实现的
可重复读的核心就是一致性读，而事务更新数据的时候只能用当前读，如果当前的记录的行锁被其他事物占用的话，需要进入锁等待。
读提交隔离界别下的事务状态：在读提交的隔离级别下，每一个语句执行前都会重新计算出一个新的视图。
总结
- 对于可重复读，查询只承认在【事务】 启动前就已经提交完成的数据。
- 对于读提交，查询只承认在【语句】 启动前就已经提交完成的数据。
- 当前读，总是读取已经提交完成的最新版本。
