宋红康 MySQL高级篇 学习笔记

架构篇

1.sql的执行流程

查询缓存：有就直接返回了。 解析器进行解析：检查sql合不合语法 优化器：对sql语句进行逻辑优化，看是否使用索引，生成执行计划。 存贮引擎：myisam ,innodb去执行上述计划 当然返回的时候 也会在缓存一下结果。

索引及调优篇

1.InnoDB B+树索引的注意事项（页分裂的场景）

1.根页面万年不动（页分裂）：创建后，用户数据用完可用空间，就会新产生一个页a，并将根节点的数据复制到该页中，并页分裂，得到另一个页b,新增数据则根据排序分到a或b中，根页则作为目录页继续存在。

页分裂：确保后一页的主键值都比前一页的大。（所以在此这里 当新增的数据 主键值刚好最大和最小之间，那么就会触发页分裂的调整，页a中该移动的就会移动到页b中）。强调数据的 挪动 不是 物理上新增页，当然满了再插入就会新增页

2.前缀索引

概念：当选取字符串类型字段作为索引时，1.字符串可能很大，索引的存贮空间就很大 2.完全比较的效率也低 所以就截取部分，建立索引。

3.InnoDB 存储结构（页，区，段，表空间）

1.为什么有区？

        页之间是双向链表，物理上的位置就会可能很远，有随机IO的问题。为了让两个页之间尽可能挨得很近，就有了区的概念。

2.为什么有段？

         是为了在范围扫描时区分 叶子节点的区集合和非叶子结点的区集合。所以一个索引就会有两个段。

4.性能分析工具的使用（慢sql的定位以及explain分析）

定位：查询慢sql日志
分析：通过explain sql语句。
explain 的核心参数：访问方式type:
结果值从最好到最坏依次是： system > const > eq_ref > ref>****> range > index > ALL
SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）
记忆规律：
        大致分类：
                1.使用的索引的等值查询。 使用主键或唯一二级索引的等值就是const,普通的二级的等值就是ref,中间的eq_ref顾名思义就是连接查询下的 从表使用主键或唯一二级索引的等值。
                2.范围查询。 用到范围就是range,虽然索引覆盖但仍全部遍历该索引就是index.

事务篇

四种并发问题与隔离级别

记忆规律：
        前提：明确并发问题
        脏写，脏读（读到未提交），不可重复读（前后读的数据不一致），幻读（第二次读多了一行） 那么隔离级别就有：从第二个开始，读未提交（解决了脏写），读已提交（解决了脏读），可重复读（解决到了不可重复读），序列化（解决了幻读）。
         注：innodb默认级别是 可重复读，由于mvcc机制 它实际上也解决了幻读

事务日志

redo log是为了解决持久性 undo log是为了解决 原子性和一致性 锁 保证 隔离性

redo log

1.为什么需要redo log?

        防止已提交的事务的数据，因宕机而丢失。
但使用redo log还是会涉及刷盘的动作。还是会有丢数据的风险！那为什么还要用redo log?
        答：1.顺序io,2.数据量少；所以就不用时时刻刻刷盘了，效率高 那为什么数据丢失的风险要低很多？请看下集 - 刷盘策略。

2.不同刷盘策略的理解

`innodb_flush_log_at_trx_commit` 参数，
* `设置为1` ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ） **那么提交后 无论mysql挂了或宕机都不会丢失了（确保刷到redo log file了）
* `设置为2` ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自 己决定什么时候同步到磁盘文件。 **那么提交后mysql挂了不会丢数据，因为已经确保刷到文件系统的缓存（page cache）了,但宕机还是会可能会丢数据。
* 设置为0` ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日 志的同步） **那么提交后 mysql挂了就会丢一秒的数据。

undo log

1.生成的详细过程

insert操作：
         生成一条insert undo log,数据的回滚指针指向它，insert undo log记录了undo log 的序号，插入的主键列信息，那么回滚时 通过主键就可以删除对应的数据了。
update操作：
        1.不更新主键，生成一个undo log,回滚指针指向最新的undo log,新的指向旧的，形成链表。         
        2，更新主键，将该数据的deletemark打开，在该数据后新插入一条 新数据也会产生undo log,且undo log的序号会递增。

2.它为什么是逻辑日志，而非物理日志？

若是 物理日志 回滚整页的话 会影响并发中的其他 不需回滚的事务。 因为是一个是逻辑日志，一个是物理日志，所以 不能讲 undo log 视作 redo log 的逆过程。

3.undo log的删除

默认隔离级别下如何解决幻读

1.加锁：了解行锁中的间隙锁（禁止插入幻影记录）即可

2.mvcc机制

 一、readView规则记录规律：

        看该条undo log的trx_id 在 活跃事务id列表区间的左中右？
        看是否与readView 的creator_trx_id相不相等？
        一句话总结某个版本对当前事务是否可见：小于max_trx_id且不在活跃事务中的任一个；就是当前事务id;

二、readView在两种隔离级别解决并发问题的原理

        在read committed级别下，由于前后两次的查询 都会产生新的readView 那么第一次的[10,20] 就变成[20],10提交了，第二次10就可见了，前后不一致 就产生了 不可重复读和幻读问题了。
         在repeatable read级别下，前后两次查询的readView是一样的，就避免了不可重复读和幻读问题了。（例子见md文档）
        注：当前事务在 read uncommitted级别下的话，可以允许读未提交事务的数据，直接读最新版本的就行了。
        当前事务在 serialized隔离级别下 直接加锁的。

隐式锁转为显式锁的过程（判断数据的事务Id，是否活跃，是则加锁，自己等待）

1.为什么有隐式锁？
        是针对insert语句而言，insert不会直接加锁，但可能会引起脏读或脏写。所以就会延迟加锁，针对下面两种场景。

过程：
        并发过程中，比如有另一个查询事务要给查找它并给它加锁。

1.当它通过主键索引查询
        聚簇索引的数据记录是有trx_id的，定位到该记录后，将该记录的trx_id与当前活跃的事务id进行比较，如果相同，就会为该记录生成一个x锁，自己的锁进入等待。
2.当它通过二级索引查询
        （二级索引记录是没有trx_id的），但它不用直接二分查找整页数据，而是将当前最小的活跃事务id通过与该页的 PAGE_MAX_TRX_ID(代表对该页面做过改动的最大事务id),若比该属性大，说明对该页做过修改的事务已经提交了，否则就会 对该页的数据进行定位（因为确定要找是活跃的事务（未提交的）），并回表，并重复1的做法

日志与备份篇

binlog

与redolog 的不同：
        1.一个是innodb存储引擎层面的，一个是mysql server层面的
        2.一个是用于数据恢复的，一个还可以用于主备一致的
        3.刷盘的时机不同，binlog一定是提交后才刷盘的

两阶段提交：
        因为两者的刷盘时机不一致，即可能会出现redolog 刷了，binlog没有，导致重启后，主备的数据不一致。
        为此提出“两阶段提交”，实际上就类似redolgo开启了消息确认，当重启后发现redolog只处于prepare阶段，且没有binlog日志，就会回滚事务。（参考md文档图）

主从复制背景下的数据同步一致性的问题

产生的原因：
        从机获取到binlog的更新事件和执行relaylog的事件都很花时间，造成在从机读取到的数据不是最新的。
解决方案：
        1.异步复制：主机写完binlog直接返回了。
        2.半同步复制：主机写完，部分从机确认后再返回。
        3.组复制：（简单了解），无论RW事务都需进过组成员的投票，半数以上确认才返回。当然纯读不需投票，直接返回。