一遍搞定MySQL,来这就对了_mysql整体概念和执行流程-CSDN博客

本文链接：https://blog.csdn.net/qq_33592535/article/details/106476514

MySQL

第一部分：通用模块。此部分对 MySQL 整体概念、数据库引擎、查询缓存、表空间、回表查询、数据类型间的区别、删除表的 n 种方式、视图、数据恢复

执行引擎：innoDB
SQL执行流程：from -> where -> group by -> ->having -> select -> order by
查询缓存：
innoDB 中query_cache_type 有三个值，0 (OFF),1(ON),2(DEMOND)
0 代表不开启缓存，1代表总是开启，2代表按需开启（在查询SQL中加入sql_no_cache，sql_cache来指定）
表空间：innoDB 默认有两种：共享表空间和每个表分离的独立表空间
共享表空间：Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在
独立表空间：独立表空间是每个表都有独立的多个数据文件，而且做到了索引和数据的分离
优缺点对比：
独立表空间可以主动释放表空间（drop table ，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间），共享表空间删除后无法删除，会产生大量空隙
独立表空间每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动
回表查询：
innoDB采用的聚集索引，会维护一棵主键的B+数，叶子节点存储的是行记录，假设id是primaryKey，name是普通索引，那么select sex from table where name = “123” 会先扫描辅助索引树，拿到id后再去扫描主键索引树，再去拿sex字段，这中就称为回表查询。这同时也是数据库优化中很重要的一个点，可以通过建辅助索引的方式去避免回表查询，上述例子中如果建立name+sex的联合索引，那么select sex from table where name = “123” 就不会再去主键索引中去回表扫描，称之为覆盖索引。

删除表的 n 种方式：drop table; truncate table;delete from table where xxx;
视图：视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

数据恢复：先查看MySQL 的bin_log日志是否开启，如果没有开启binlog，也没有预先生成回滚的sql，那就无能为力了。
如果binlog开启了（show variables like ‘%log_bin%’）那就可以通过binlog日志来恢复数据。

找到全量的binlog文件，并找到起始位点和结束位点
用mysqlbinlog命令将其导出为sql文件，并删除最后的drop命令
通过完整的sql文件来恢复数据

第二部分：索引模块。

建议思路：
1.先提出几种常用的数据结构，二叉树，B树，B+树，分别介绍其特性，最后点出为什么B+ 适合作为索引
2. 聚簇索引和非聚簇索引分别是什么？
3. 回表查询和覆盖索引（上一节）
二叉树：每个节点都有两个子节点，数据的增加必然会导致树的高度快速增加，因此不适合作为大数据量索引的数据结构
B树：一棵m阶的B树是一个平衡的m路搜索树，最重要的性质是非根节点所包含的关键字个数j满足 m/2 -1 <= j <= m-1,一个子节点的节点个数会比关键字个数多一个，这样关键字就成了子节点的分隔标识。由于数据同时存在子节点和非子节点中，无法简单完成按顺序遍历B树，必须通过中序遍历完成。
B+树：一棵m阶的B+树是一个平衡的m路搜索树，最重要的性质是非根节点所包含的关键字个数 j 满足：m/2-1<= j <= m ,子节点的个数和关键字的个数一样多，非叶节点存储的都是子树里最小的关键字。同时数据都存在于叶子节点中，且叶子节点之前增加了横向的指针，这样顺序遍历数据将会非常容易。
在看插入数据是两者的变化：
B树：数据插入时，每当一个节点满后，就需要分裂（分裂是一个递归的过程），由于B树的非叶子节点也保存了键值，所以已满节点的后的值将会分布在3个地方：原节点，原节点的父节点，原节点的新建兄弟节点。分裂有可能导致树高度的增加，也有可能不导致树高度的增加
B+树：数据插入时，当一个节点的数据满时，分配一个新的节点，将原节点1/2的数据复制到新节点，最后在父节点中增加对新节点的指针。B+树的分裂只会影响到父节点和原节点，不会影响到兄弟节点，因此不需要增加兄弟节点的指针。
B+树适合作为数据库的数据结构，完全是因为计算机的内存-机械硬盘的两层结构。内存可以完成快速的随机访问（随机访问给出任意一个地址，要求返回这个地址的数据）但是容量较小。而硬盘的随机访问要经过机械动作（1 磁头移动，2盘片转动）访问效率要低内存几个数量级，但是硬盘的容量较大。典型的数据库数据容量要远远大于可用内存的大小，这就决定了在B+树检索一条数据可能要借助几次磁盘IO操作。通常在B+树的非叶子节点在初始化阶段会被加载到内存中，以提高访问速度，同时也会提高节点之间横向遍历速度。（真实数据库中B+树的cpu计算和内存读取优化为二叉搜索树（innoDB的page directory机制））
page页：

第三部分：事务模块。

四大特征（ACID）
事务相关命令
事务隔离级别和MVCC
mybatis中如何实现事务
如何保证原子性
四大特征：原子性（A），一致性（C），隔离性(I)和持久性(D)
事务相关命令：
开启事务 start transaction
结束事务 end transaction
提交事务 commit transaction
回滚事务 rollback transaction

事务隔离级别：

未提交读（uncommitted read） -> 脏读，不可重复读，幻读
事务读到了其他事务的未提交的数据
已提交读（committed read） -> 不可重复读，幻读
一个事务内对一个字段有多次读，其他事务不断修改这个字段的值，就会导致幻读
可重复读（repeatable read） -> 幻读
串行化（serializable）无

版本链
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中读包含两个必要的隐藏列（row_id 不是必要的，只有在没有主键并且没有非空唯一主键的情况下才会有row_id列，还有一个delete bit位用于标识该条记录是否被删除，真正删除是在mysql GC的时候）

trx_id：每次对聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的日志写到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

每次对记录的改动，都会记录一条undo日志，就算该条记录的一个版本链了，随着更新次数的增多，所有的版本都会被roll_ptr属性链接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录的最新值，另外每个版本中还包含生成该版本时对应的事务id（创建时间）

在这里插入图片描述
Read View

对于READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就可以了，未实现MVCC。
对于SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来实现读写，不会使用MVCC
对于 READ COMMITTED和 REPEATABLE READ 隔离级别的事务来说，就需要用到版本链了，核心问题就在于：读的时候需要判断一下版本链中的哪个版本对当前事务可见？设计者在设计的时候提出了一个ReadView 的概念，ReadView中包含了系统中还有哪些活跃的事务id，并把这些id维护在一个m_ids列表中

如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务是不可见的，那么就会顺着版本链去找上一版本的数据，继续判断，直到最后一个为止。READ COMMITTED和REPEATABLE READ最大的不同就是生成 ReadView的时机不一样。
READ COMMITTED --> 每次读取数据都会生成一个ReadView
REPEATABLE READ —在第一次读取数据时生成一个ReadView，事务内会复用这个ReadView

MVCC 多版本并发控制。从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT（非锁定读）操作时访问记录的版本链的过程。

而对于锁定读，那就是依赖锁来保证事务之间的有序性。

具体可参考：https://blog.csdn.net/qq_38538733/article/details/88902979

第四部分：锁。锁包括：表锁、行锁、死锁、乐观锁、悲观锁，锁算法

读的方式可分为两种：快照读（读取的是当前事务时间的一个快照，也就是历史版本）和当前读（读的是当前版本）
普通的select就是一个快照读，像insert，update，delete,select … lock in shared mode,select … for update 使用的是当前读

一致性读：consistent read ，InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

锁定读：在一个事务中，标准的select 是不会对数据加锁的，但是有两种情况例外
select … lock in share mode （共享锁，其它事务只能读不能改）
select … for update (给行数据加锁，相当于update更新加锁一样)

锁算法：InnoDB的行锁的算法为以下三种
Record Locks (记录锁)：在索引记录上加锁
Gap Locks (间隙锁)：在索引之间加锁，或者在第一个之前或者在最后一个之后加锁
Next-key Locks ：在索引记录上加锁和在索引记录之前的间隙加锁，相当于记录锁+间隙锁

在默认的隔离级别（可重复读）中，普通的select 作用的是一致性读，不加锁，而对于锁定读，update和delete则需要加锁，至于加什么锁视情况而定，如果你对一个唯一索引使用了唯一索引条件，那么只会对索引记录加锁，如果没有使用唯一索引作为检索条件，或者使用了范围扫描，将会使用间隙锁或者next-key锁来阻塞对于这个范围内的数据插入。

另外还有没说的有表锁（lock table read和lock table write），行锁, 隐式提交锁（update，delete）等

具体看：https://www.jianshu.com/p/47e6b959a66e
innoDB的锁机制：https://www.cnblogs.com/null-qige/p/8664009.html

（待续…）
第五部分：日志。日志看似不起眼，却是 MySQL 主备同步和容灾恢复以及问题排除的关键，当然也是面试中必问的问题，这部分会对不同的数据库引擎中的重点日志，进行详细的介绍。
binlog
redo log
undo log

第六部分：MySQL 操作命令和内置函数。MySQL 的操作命令，对于程序员或者 DBA 来说也是必须具备的一项技能，比如，用户和权限的创建、数据库相关信息的查询等，都离不开对 MySQL 命令行的掌握。

第七部分：性能优化和分布式。性能优化和分布式是面试中决定你高度的关键指标，其中性能优化包括了慢查询的分析和处理，对分布式的掌握体现了你的技术深度。

第八部分：开放性问题。很多大公司最后也会问一下没有标准答案的开放性问题，以考察面试者的技术能力边界和对待问题的分析思路，这部分助你更平稳的获得 offer。