Mysql 面经

---

一、Mysql查询过程

MySQL逻辑架构整体分为三层，分别为客户端层、核心服务层、存储引擎层，共同协作完成。

1. 客户端层，比如：连接处理、授权认证、安全等功能等。
2. 核心服务层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等)，另外，所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
3. 存储引擎，负责数据存储和提取，中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

总体流程：

• 客户端发送一条查询SQL给服务器；
• 服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果；
• 服务器层进行SQL解析、预处理，再由优化器生成对应的执行计划；
• 查询执行引擎根据优化器生成的执行计划，调用存储引擎的API来执行查询；
• 将结果返回给客户端；

二、Innodb 和 MyISAM

1.Innodb 和 MyISAM区别

innodb：

• 支持事务、支持行级别锁定、支持外键。
• 没有存储表的行数，count(*)时需要遍历整个表
• MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。
• 当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

MyISAM：

• 不支持事务、不支持行级别锁定、不支持外键。 因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
• MyISAM中存储了表的行数。
  I

2.InnoDB 与 MyISAM 索引存储结构的区别

MyISM和InnoDB索引都是由B+树实现的，但在索引管理数据方式上却有所不同。

• nnoDB的主键是聚集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据 =
  整个表数据文件，通过主键索引到整个记录，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
• MyISAM的主键索引和辅助索引都是非聚集索引，也是使用B+Tree作为索引结构，但索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的，不需要进行回表查询。

3.事务的四种特性

• 原子性 （Atomicity） 事务开始后所有操作，要么全部做完，要么全部不做。通过undo log 实现
• 一致性 （Consistency）
  指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后，数据库的完整性约束没有被破坏。例如工号带有唯一属性，如果经过一个修改工号的事务后，工号变的非唯一了，则表明一致性遭到了破坏。
• 隔离性 （Isolation）
  多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
  注：MySQL 通过锁机制来保证事务的隔离性。
• 持久性 （Durability） 事务一旦提交，则其结果就是永久性的。即使发生宕机的故障，数据库也能将数据恢复。 注：MySQL 使用redo log 来保证事务的持久性。

4.事务的四种隔离级别

• 读未提交 一个事务还没提交时，它做的变更就能被别的事务看到。
• 读已提交 一个事务提交之后，它做的变更才会被其他事务看到。
  一个事务从开启事务到提交事务之前，对其他事务都是不可见的，因此在同一个事务中的两次相同查询结果可能不一样。故这种隔离级别有时候也叫不可重复读。
• 可重复读 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
  当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 可串行化 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当
  出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
  
  脏读（读取未提交数据）
  不可重复读（前后多次读取，数据内容不一致）
  幻读（前后多次读取，数据总量不一致）

不可重复读和幻读到底有什么区别呢？
(1) 不可重复读是读取了其他事务更改的数据，针对update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
(2) 幻读是读取了其他事务新增的数据，针对insert和delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

四种隔离级别的实现方式：

读未提交：读不加锁，写加排他锁，并到事务结束之后释放。
读已提交：MVCC 执行每条语句之前都要创建一致性视图
可重复读：MVCC 只在事物开启时创建一致性视图
串行化：读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

MVCC具体实现：通过版本号和undo log 以及一致性视图(快照) 实现

---

三、Mysql三大日志binlog、redo log和undo log

Binlog：

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。
binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。
binlog使用场景
主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。
数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录binlog
mysql 通过 sync_binlog 参数控制 binlog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
• N：每N个事务，才会将 binlog 写入磁盘。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。
在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

• STATMENT：基于SQL 语句的复制，每一条会修改数据的sql语句会记录到binlog 中 。
  优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO, 从而提高了性能；
  缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。

• ROW：基于行的复制，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
  优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
  缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨

• MIXED：基于STATMENT 和 ROW 两种模式的混合复制，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存binlog

Redo log：

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

为什么会有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log 和 binlog 区别：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog
  文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

解决 binlog 和 redo log 的数据一致性的问题

两阶段提交原理描述:

1. 首先redo log 写盘，事务进入 prepare 状态。
2. 如果事务 prepare 成功，binlog 写盘，如果持久化成功，那么事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

redo log 和 binlog 是怎么关联起来的?

redo log 和 binlog 有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

Undo log:

undo log 有两个作用：提供回滚(原子性）和MVCC。

回滚：undo log是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行 rollback 时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
行版本控制：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

undo log记录delete和update操作：
delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况：
如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

四、分库分表

垂直分表：

• 基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

水平分库分表：

• RANGE
  从0到10000一个表，10001到20000一个表；
• HASH取模
  一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
• 地理区域
  比如按照华东，华南，华北这样来区分业务。
• 时间
  按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

五、MySQL数据库cpu飙升的话，要怎么处理呢？

排查：

1. 使用top 命令观察，确定是mysql导致还是其他原因。
2. 如果是mysql导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
3. 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

1. kill 掉这些线程(同时观察 cpu 使用率是否下降)
2. 进行相应的调整(比如说加索引、改 sql、改内存参数)
3. 重新跑这些 SQL。

其他情况：
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

六、使用mysql实现分布式锁

1. 创建一个锁表，储存锁相关信息，例如：资源名、机器名、锁的次数、创建时间、修改时间等。
2. 加锁：先根据资源名等信息查询锁表，若有值，再根据机器名等信息判断是否可重入，如果可以，锁的次数加一。若没有值，则插入一条数据。
3. 解锁：如果查询到的锁次数大于一，则次数减一。若次数为一，则直接删除
4. 解决死锁问题：启动一个定时任务，定时查询锁表，根据当前时间和修改时间判断是否超时，若超时就将其直接释放。

七、主从复制

1. 主库的更新事件(update、insert、delete)被写到binlog
2. 从库发起连接，连接到主库。
3. 此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
4. 从库创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log。
5. 从relaylog里面读取内容，将更新内容写入到从库。

八、datetime 和 timestamp 的区别与选择

• timestamp即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。
• timestamp 只占 4 个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换。
• timestamp表示的时间范围更小。
• timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
• datetime以 8 个字节储存，不会进行时区的检索，表示的时间范围更大
• datetime：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

九、数据库三大范式

1. 数据库表中的字段都是单一属性的，不可再分。
2. 属性完全依赖于主键。当主键有多个属性时，非主键不能仅依赖于主键的一部分。
3. 非主键不能传递依赖于主键。例如A为主键、B依赖于A，C依赖于B，就不行。