Mysql - 知识图谱总览

    想了很久不知道怎么开始Mysql系列的博客，梳理出了一张图，觉得先从整体开始，按树的结构慢慢展开吧

 

    一切从Mysql的架构图开始吧，从整体上去把控Mysql，当学习某一个知识点时知道架构图这张大背景图，在哪一个位置，哪一步不迷路。

1、Mysql架构图、InnoDB架构图

    Mysql架构图包含：连接器、分析器、缓存、优化器、执行器、可插拔的存储引擎（常用的有：MyISAM、InnoDB、Memory）。其中连接器处理与Mysql客户端的链接、权限等问题，分析器处理SQL的词法和语法分析； 缓存使用的条件比较苛刻，一般不建议使用（特别是Redis等旁路缓存的快速生长），在Mysql 8 之后废弃了缓存模块。优化器中处理索引的选择等问题；现在好多公司的Mysql数据库规范有一条就是只能使用 InnoDB引擎，有一个比较重要的方面是在执行数据库备份操作（一般是DBA等操作）时会使用Flush table with read lock(FTWRL) 操作，本身可能导致大量的操作阻塞。如果使用InnoDB的一致性视图，但是前提要求就是：所有表使用innoDB存储引擎，并且支持repeatable read事务隔离级别。详见：Mysql全局锁。

    InnoDB引擎的架构图的内部也是需要掌握的，因为InnoDB实现了事务（ACID），所以内部的主要块都跟事务相关。Redo Log、Undo Log、MVCC（多版本并发控制）、锁 + 视图一起实现了事务的ACID特性。

2、索引

    在大背景下，索引的实现与存储引擎相关，只是一般默认使用InnoDB，那么我们研究的重点基本就是InnoDB的默认实现B+树。而复杂SQL执行时可能会用到临时表，或者我们自己业务复杂时为了提高性能也可以自己创建临时表，Memory引擎的实现是Hash表索引。我们知道散列表的读写时间复杂度近似于O(1)，只是不支持区间访问。至于更多索引相关的选择和特点，可以查看：数据结构 - 索引怎么选择合适的数据结构？

3、锁

    所有并发系统都离不开锁，Mysql内部拥有大量的并发处理线程池。而这里使用到的锁都可以理解为读写锁（共享锁，排他锁），完全可以理解成Java中的 ReentrantReadWriteLock， 读锁与读锁是共享的，读锁与写锁、写锁与写锁是互斥的（排他的）。

    Mysql中锁有四个层级，全局锁是整个Mysql实例级别的，只要用于数据备份等场景。表级别锁有表锁和元数据锁，MySIAM发生写操作是就使用的表锁，或者InnoDB也可能退化成表锁；元数据锁主要用于DML操作时，防止一条普通语句（insert、update等）执行完后发现，表结构都被修改等情况。其他是就是与实务隔离级别相关的：InnoDB拥有的行锁、临建锁（Next-key Lock）、间隙锁（gap Lock）。还有就是唯一索引相关的记录锁（Record Lock）。

4、日志

    Binlog是Mysql级别的锁，并且当主从复制等场景中，Binlog也发挥着巨大的作用。 因为历史原因，Binlog早于InnoDB的Redo和Undo Log，但是为了实现实务，InnoDB就利用了已经存在的Binlog日志，事务的持久性就是利用了执行器在调用InnoDB接口时，内部基于 binlog + redo log的二阶段提交实现。

5、InnoDB 事务

   事务为了保证ACID特性，而内部持久性基于 binlog + redo log实现；原子性基于InnoDB内部的事务回滚机制，而回顾的本身就是基于版本 + undo log（重做日志）；一致性基于mvcc（多版本并发控制），本质就是使用乐观锁的思想，基于undo日志记录了每次修改的操作，加上每次操作时生成的版本。事务不是Mysql的产物，其也实现了四种事务的隔离级别，而每种隔离级别的实现方式不同，后面专门分析，并且事务的隔离级别不同浸入一致性级别也不同。

6、Buffer Pool

    内存的读写速度的磁盘的万倍到及时完倍，所有的读写操作都涉及磁盘操作那么性能将非常低。缓存池包括B+树的数据页缓存，并且使用修改后的LRU缓存淘汰策略进行维护；binlog、redo log、undo log日志也会使用缓存加速；sql执行时，join操作可能会在Join buffer中加速处理，排序操作（order by、group by）可能会在sort buffer中处理，也可能是文件排序；update语句中的字段不涉及唯一索引时会在 change buffer中完成。

7、SQL优化

    面试不免涉及到的就是sql优化，优化大部分都会涉及到索引的优化，索引是否失效等。然而了解了上面的整体Mysql 体系，其实更能全面的执行sql优化，或者了解sql执行的流程。比如：适当增大join buffer、sort buffer的对应的场景中，涉及大量的写操作时，可以增大 change buffer提高写的性能。事务的选择直接关系到了数据库的并发性能， 了解了InnoDB默认的事务隔离级别 reaptable read，会涉及 行数、临建锁、间隙锁 + 视图（undo log有关），可以更好的平衡性能和问题（脏读、不可重复读、幻读）。

    如果我们会看 Expalin执行计划，只是了解了最终的表面现象，如果我们知道了内部的执行细节，那么才能更好了理解执行计划。如果执行计划是一个黑盒子，那么当不能再进行语句优化时，就不能继续了。为了打开黑盒子，了解里面的细节，那么会有多个直接细节的分析：

    大表全部扫描执行流程（多个缓存池的处理，涉及到LRU缓存池冲击）；

    update语句执行流程（change buffer相关）；

    delete语句执行流程（数据页的合并等）；

    order by执行流程（涉及的排序算法，sort buffer还是文件排序）；

    between and或大于小于（B+树 区间查询的执行流程）；

    count执行流程（count(1)、count(*) 等）；

    join执行流程（Index Nested Loop Join，Block Nested Loop Join，MRR等）；

---

Mysql - 知识图谱总览

Mysql - 配置大全（my.cnf）

Mysql - Mysql架构图

Mysql - MySQL索引（复合索引、覆盖索引、索引下推、前缀索引）

Mysql - 优化器阶段的索引选择过程

Mysql - 争取一文讲清楚Mysql中的锁（全局锁、表级锁、行级锁）和加锁规则

Mysql - InnoDB引擎对事务ACID的实现原理分析

Mysql - binlog日志、主从复制过程

Mysql - InnoDB三大特性之Buffer Pool缓冲池

Mysql - InnoDB三大特性之双写缓冲区（Double Write Buffer）

Mysql - InnoDB三大特性之自适应Hash索引（从数据结构角度分析Mysql中的Hash索引）

Mysql - 普通索引与唯一索引之间性能差别change buffer

Mysql - 大表全表查询过程（分析底层的数据流转过程）

Mysql - delete、重建表过程以及内部的锁状态变化

Mysql - count(字段)＜count(主键 id)＜count(1)≈count(*)

Mysql - join类型和实现的效果

Mysql - join（索引和非索引）的实现原理和优化手段

Mysql - order by执行原理

Mysql - 深度分页问题的解决方案

Mysql - 范围查询过程分析底层的锁实现机制（解决幻读、版本读的不可重复读问题）

Mysql - 不同级别数据恢复问题

Mysql - 脏页刷新机制

Mysql - 慢查询和执行计划