全文的组织形式:
一、面试题and一句话总结
二、详细知识点阐述
Mysql架构
| 序号 | 问题 | 一句话解释 | 详细知识点 |
|---|---|---|---|
| 0 | Mysql的架构分层 | 客户端; 连接器、缓存层、分析器、优化器、执行器; 存储引擎层 | |
| 1 | Mysql的日志有哪些 | 常用: 重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog） 不常用: 错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log） | |
| 1 | redo log的作用 | 增加服务的吞吐量 使得服务高可用 | 极客时间<02日志系统：一条SQL更新语句是如何执行的？> |
| 2 | redo log的写入过程 | 其实redo log的写入过程就是2阶段提交(2PC). 零、执行器调用引擎写入数据，写到了redo log 一、redo log写入后，处于prepare状态 二、执行器生成binlog,并写入磁盘 三、执行器调用引擎接口提交，引擎将redo log的状态流转，从prepare -> commit | |
| 3 | redo log 写入过程中如果宕机了，怎么办？ | 1 prepare阶段 2 写binlog 3 commit 当在2之前崩溃时,重启恢复：发现没有commit，回滚。备份恢复：没有binlog 。和崩溃前一致 当在3之前崩溃重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit.备份恢复：有binlog. 一致 | |
| 3 | binlog的作用 | binlog(归档日志) 是逻辑日志, 逻辑的意思是，迁移到其他mysql实例上也行，即使其他的是MyISAM。 主要作用:备份恢复and主从复制 | |
| 4 | binlog的格式 | 3种格式， 一般推荐使用row格式 一、row : 基于行的模式，记录的是行的变化，很安全 二、statement : 基于SQL语句的模式，某些语句中含有一些函数，例如 UUID NOW 等在复制过程可能导致数据不一致甚至出错 三、mixed : 混合模式，根据语句来选用是 statement 还是 row 模式 | |
| 5 | redo log和bin log的关联 | redo log 和 binlog 有一个共同的数据字段叫 XID。崩溃恢复的时候会按顺序扫描 redo log。 如果碰到既有 prepare、又有 commit 的 redo log就直接提交 如果碰到只有 parepare、而没有 commit 的 redo log就拿着 XID 去 binlog 找对应的事务。 |
详细知识点阐述
0. Mysql的架构分层
- 1.客户端，我们自己的服务
- 2.连接器，包括授权认证、连接处理等；
- 3.缓存层，如果命中内存就直接返回了，不用查文件系统. 但查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
注意:MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了 - 4.分析器，有点像编译器的分词器，进行词法分析、语法分析
- 5.优化器，执行计划的生成，选择合适的索引等
- 6.执行器，操作API，返回结果
- 7.存储引擎层，去实现MySql的Api, 但不解析Sql。比如InnoDb和MyISAM
其中，除了引擎层，其他的都属于Server层
Mysql日志的对比
| 日志 | 别名 | 作用 | 内容 | 存储 | write方式 | 适用场景 |
|---|---|---|---|---|---|---|
| binlog | 二进制日志 | 数据的真正存储 | 逻辑格式的日志 简单理解就是sql本身，读sql不记录 还记录着sql的回滚sql，比如1条insert,也会同时有1条delete | 主从复制、数据恢复(某一刻)、集群同步 | ||
| redo log | 重做日志 | 确保事务的持久性 | 物理格式的日志，记录的是物理数据页面的修改的信息，顺序地写入物理文件中去的 | 事务的崩溃恢复 | ||
| undo log | 回滚日志 | 提供多版本并发控制下的读（MVCC），也即非锁定读 | 逻辑格式的日志 | 事务回滚 |
1. redo log的作用
- redo log（重做日志）是物理日志， 记录每次客户端的更新操作，比如update 、insert等；
写redo log的方式，是WAL技术，即Write-Ahead Logging, 核心就是:先写日志，再更新磁盘文件
因为磁盘文件更新很慢，要定位、磁盘寻址、修改，所以就先记在redo log上，有空的时候再更新。 - 作用:
- 1、增加服务的吞吐，因为写日志是增量追加，很快啊
- 2、高可用，即使服务重启、死掉，也可以从redo log 恢复，称为 crash-safe
- 注意:redo log 是InnoDb引擎引入的，Server层并没有redo log，即MyISAM引擎并没有crash-safe能力
- 注意:redo log 是InnoDb引擎引入的，Server层并没有redo log，即MyISAM引擎并没有crash-safe能力
- 1、增加服务的吞吐，因为写日志是增量追加，很快啊
索引
| 序号 | 问题 | 一句话解释 | 详细知识点 |
|---|---|---|---|
| 0 | B+ 树是什么？为什么被Mysql采用作为索引的数据结构？ | 优点：IO读取次数少（每次都是页读取），范围查找更快捷（相邻页之间有指针） | 深入理解 Mysql 索引底层原理 |
| 1 | B+树和其他树的对比 | ||
| 2 | mysql都有哪些索引？使用场景都是哪些？ | 主键索引 辅助索引 聚簇索引 非聚簇索引 全文索引 唯一索引 主键索引 组合索引 | |
| 3 | 聚簇索引（也叫聚集索引， cluster index） 是什么？为什么比普通索引快？ | 数据和索引放在一起，放在B+树的叶子节点上，不需要根据地址再去查一下具体数据是多少 | |
| 4 | InnoDB的B+树索引叶子节点的Data域存储的是什么？MyISAM的呢？ | MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式 (.MYD文件:表里面的数据文件（myisam data） .MYI文件:表里面的索引文件（myisam index）)； 树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了 Innodb引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式(.idb文件:表里面的数据+索引文件) InnoDB 只在主键索引树内使用聚簇索引，因为其他索引也存数据的话，太浪费空间了 | 深入理解 Mysql 索引底层原理 |
| 5 | Mysql索引什么时候会失效 | 一、使用联合索引a,b时，未使用最左前缀规则 二、使用like的时候没有使用左前缀，比如where subject like "%game" 三、搜索一个索引而在另一个索引上做order by，where A=a order by B 四、where A = a1 or B = b1会失效，因为是2个索引， where A = a1 or A = a2时生效 五、varchar类型的时候如果没加"",就会做类型转换，然后索引失效 | MySQL索引——分类、何时使用、何时不使用、何时失效 |
| 6 | 索引覆盖是什么意思？回表呢？ | select a,b,c where a=1 and b = 3 and c = 10就会覆盖，即查询的数据，在where条件里都有，就不需要查到主键后，再去聚簇索引查一遍数据(回表)； 但 select * from where 主键 = 3 这样的SQL也不需要回表，因为InnoDb默认聚簇索引就建立在主键上，数据和主键都在主键索引的数据节点 上 | 到底什么情况下mysql innodb会发生回表操作? |
详细知识点阐述
0. B+ 树是什么？为什么被Mysql采用作为索引的数据结构？
核心思路:
- 索引文件很大，是存在磁盘中的，一次只能读取几页的索引到内存中，所以要尽量减少IO读取次数
优点: -
- 数据都在叶子节点，查询效率稳定
-
- 相近的节点在叶子节点的逻辑上邻近的，在索引文件的物理存储中也是相近的，可以1次都加载进来，可能不需要多次读磁盘\刷内存的操作
-
- 存在叶子节点间的链表指针，方便select *进行扫库，从任意一个节点都可以遍历完全表
1. B+树和其他树的对比
- 二叉查找树(binary Search)：左子树的键值小于根的键值，右子树的键值大于根的键值； 缺点:不平衡的时候，其实和链表是一样的，效率很低
- 平衡二叉树(AVL Tree) add和remove的时候会自动旋转，用来保持平衡，即根节点两边的子节点数量尽量一致
- 平衡多路查找树(B-tree，-是分割，不是减) 所有键值分布在整个树中；叶子节点不需要非是2;
关键字越少，层数越高，IO次数越多，所以有要求【m叉查找树内，除了根结点外，任何结点至少有 m/2 个分叉，即至少含有m/2 -1个关键字】 - B+树 叶子节点不需要非是2个;不需要经常平衡; 数据全部在叶子节点上；叶子节点间加入链表指针，优化where等范围查找时的性能
B+树的创造者，Rudolf Bayer没有解释B代表什么，最常见的观点是B代表平衡(balanced)
参考文档 <<那么多的树，一文全总结>>
事务
| 序号 | 问题 | 一句话解释 | 详细知识点 |
|---|---|---|---|
| 0 | 事务隔离级别 | 须知，隔离级别越高，性能就越差 所以我们大多数时候是在性能和数据一致性之间找一个平衡，根据业务的要求不同而不同 下面的隔离级别，从上往下越来越严，性能越来越差 一、读未提交（read uncommitted）， 原理:直接返回，不额外处理 二、读提交（read committed），原理 : 视图，每个SQL执行的时候创建的视图 三、可重复读（repeatable read），原理 : 视图， 事务启动时创建的唯一的一个视图 四、串行化（serializable ），原理 : 读写锁，后来的线程需要等待 | 三歪连MVCC和事务隔离级别的关系 |
| 1 | 事务的隔离级别怎么设置 | 配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值 | |
| 2 | MVCC是什么？和事务隔离级别的关系? | 在查询同一条记录的时候，不同时刻启动的事务会有不同的 read-view。 同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC） | |
| 3 | 事务的ACID特性 | ACID（Atomicity、Consistency、Isolation、Durability， 即原子性、一致性、隔离性、持久性） |
日常使用
| 序号 | 问题 | 一句话解释 | 详细知识点 |
|---|---|---|---|
| 0 | 怎么处理慢Sql | ||
| 1 | explain语句怎么用 | ||
| 2 | 分库分表 | ||
| 3 | 触发器是什么，什么场景下使用？ |
join 语句
| 序号 | 问题 | 一句话解释 | 详细知识点 |
|---|---|---|---|
| 0 | join关联查询有哪些，使用场景？ | 有innner join (inner join = join), straight join (功能同join类似，但能让左边的表来驱动右边的表, 能change优化器对于联表查询的执行顺序); select * from t1 straight_join t2 on (t1.a=t2.a); left join ; right join； 其中内连接包括隐式内连接（where a.id = b.id）， 显式内连接(inner join, straight join); 外连接包括left join, right join MySQL 认为任何一个查询都是一次 “关联”，就算是单表查询也是"关联" | mysql指引（四）：join关联查询底层原理（上） |
| 1 | join的底层原理 | mysql优化器给出执行计划，然后执行引擎根据计划来执行。也就是优化器给出代码，执行引擎运行代码。所以，这里的关联查询底层原理实际就是看 优化器 给出的执行计划是什么。 驱动表的概念:mysql中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql默认的优化器就是这么粗暴地，以小表驱动大表的方式来决定执行顺序的。 join算法的执行计划有以下几种： 一、Simple Nested-Loop Join Mysql没有使用，因为太慢了，这里只是介绍 每条驱动表t1的数据，都要去全表扫描被驱动表1次， 时间复杂度 = O(Nt1 * Nt2) 二、Index Nested-Loop Join 使用被驱动表的索引，选择和匹配是走的磁盘里的索引文件 三、Block Nested-Loop Join :在被驱动表没有用到索引的时候，使用这个算法。 使用join buffer，把数据分块读到内存中再比较是否符合where条件， join buffer的size默认是256K 时间复杂度和Simple是一样的，但是因为放到内存中，耗时更短 但相比index差距还是很大，还是要尽量避免Block的 四、Hash Join Mysql 8.0加入，还没看 | 极客时间<<到底可不可以使用join>>？ mysql指引（五）：join关联查询底层原理（下） |
| 2 | 为什么很多DBA不建议使用join语句 | 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的； 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。 | |
| 3 | 什么是小表 | 两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表” | |
| 4 | 为什么要小表驱动大表 | 结论:无论是Block还是Index，都是小表驱动大表的效率更高 假设小表的行数是 N，大表的行数是 M。 一、无论是哪种算法，两个表都要做一次全表扫描，所以总的扫描行数是 M+N；内存中的判断次数是 M*N 。从这个维度看，M和N的大小互换，是不影响性能的 二、假设一个buffer_size的大小不够加载2张表的数据，只能分段加载，假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。注意，这里的 K 不是常数，N 越大 K 就会越大，因此把 K 表示为 λ*N ， 显然λ的取值范围是 (0,1)。所以，在这个算法的执行过程中：扫描行数是 N+λ*N*M ；内存判断 N*M 次。 显然，N 小一些，整个算式的结果会更小 当然λ作为系数才是影响范围更大的，所以如果可以的话，buffer size越大越好，这样 λ就会很小。 | |
| 5 | BKA 算法 |
