【面试题】7.MySQL相关

白夜行515

已于 2022-05-30 17:05:56 修改

阅读量1.7k

点赞数 1

分类专栏：面试文章标签： mysql 面试题

于 2017-08-05 11:19:59 首次发布

本文链接：https://blog.csdn.net/baiye_xing/article/details/76707708

版权

7.1. SQL语句执行流程是什么

7.1.1 MySQL逻辑架构图

MySQL逻辑架构示意图

Server层：①连接器：负责跟客户端建立连接、身份认证、获取权限、维持和管理连接。②查询缓存：查询后的结果存储位置，MySQL5.7.26开始弃用查询缓存，MySQL8.0直接删除了查询缓存，因为查询缓存失效太频繁，得不偿失。③分析器：先会做词法分析，然后做语法分析，即首先提取关键字，查看是哪种操作，比如 SELECT，然后识别表名、列名等；然后判断SQL有没有语法错误，是否符合MySQL的语法规则。④优化器：多种执行策略可实现目标，系统自动选择最优进行执行。⑤执行器：判断是否有权限，打开表调用存储引擎的接口。
存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB（MySQL 5.5.5成为默认）、MyISAM、Memory等多个存储引擎。

7.1.2 一条SQL查询语句的执行构过程

SELECT * FROM article WHERE articleId = 112732440 AND title = '【MySQL深入】一条SQL的执行过程';

该示例SQL的执行过程：

连接器会先检查权限，若没有权限就直接返回错误信息，如果有权限就会先查询缓存（MySQL 8.0版本之前），以这个sql语句为key，在查询缓存中查找是否有结果，如果有直接返回结果，否则执行下一步。
通过分析器先进行词法分析，提取sql语句里面的关键字。示例SQL取的SELECT，然后提取查询的表名article ，需要查询所有字段，查询条件是articleId = 112732440，title = ‘【MySQL深入】一条SQL的执行过程’。然后进行语法分析，判断sql语句是否正确，如果有错会返回报错信息，否则执行下一步。
优化器确定执行方案。优化器根据自己的优化算法选择一个执行效率最好的一个方案。比如：优化器会去找articleId和title字段有没有索引，执行计划确定后就会执行下一步。
执行器首先会判断当前用户对article表是否有查询的权限，如果没有权限就会返回权限错误，若有权限会打开表执行，根据表的引擎定义调用引擎提供的接口，返回引擎执行结果。

SQL执行顺序

7.1.3 一条MySQL更新语句的执行过程

update article set commentcnt = commentcnt + 1 where id = 2;

示例更新SQL执行流程：（加粗的步骤是在执行器中执行的，其余是在InnoDB内部执行）
- 连接数据库，清空查询缓存（MySQL8.0之前），分析词法和语法后知道这是一条update语句，优化器决定使用ID这个主键索引
- 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。
- 如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器，否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 并刷盘，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

7.2. MySQL中的日志有哪些

WAL预写日志：先写日志，再写磁盘，将随机写转换成了顺序写，大大提升了数据库的性能。组提交机制，可以大幅度降低磁盘的IOPS消耗。

7.2.1 redo log

定义：一块固定大小的重做物理日志文件，可以循环写。记录的是“在某个数据页上做了什么修改”，是InnoDB引擎特有的日志。
工作流程：当有一条记录更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。由于前面的内容会被覆盖，一旦写满，就会触发 redo log 到磁盘的同步。将这个操作记录更新到磁盘。crash-safe机制保证异常重启不丢失数据。
文件组成：每个文件有2K的FileHeader，FileHeader之后是一个512B的Block，每个Block包含12字节BlockHeader，4字节BlockTrailer，中间则是实际redo log的内容。
redo log概念上有一个全局递增的SN和LSN，SN对应所有写入的redo log原始内容的序列号，LSN则是原始内容包含BlockHeader和BlockTrailer之后的序列号，二者可以互相转换

7.2.2 undo log

定义：记录的是数据被修改前的信息，且只记录逻辑变化，一个相反的操作，并不会直接恢复物理页。
原理：Undo Log中基于回滚指针(DB_ROLL_PT)维护数据行的所有历史版本。InnoDB中，undo log分为Insert Undo Log（事务提交后被立即丢弃）和Update Undo Log（在快速读或事务回滚不涉及该日志时被purge线程统一清除）
作用：Undo Log用来实现事务的原子性(回滚)和隔离性(MVCC)。
存储：InnoDB基于Rollback Segment管理Undo Log，每个Rollback Segment记录1024个Undo Segment，Rollback Segment默认存储在共享表空间中，undo log空间不足，会自动进行扩充。
事务对一条记录的修改，会导致该记录的undo log成为一条记录版本线性表(链表)，undo log的链首就是最新的旧记录，链尾就是最早的旧记录。

7.2.3 binlog

定义：server层日志，是逻辑日志，记录语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。可以追加写入，即 binlog 文件写到一定大小后会切换到下一个binlog文件，并不会覆盖以前的日志。
binlog的三种模式

模式	statement	row	mixed
定义	记录的是SQL语句	记录行的内容(记两条, 更新前和更新后都有)	statement和row的结合，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式
优点	更新时只需要记录一条SQL，减少日志量	便于恢复数据	MySQL自动选择最优模式
缺点	主从复制时某些函数uuid()或功能不能正确复制，导致出现bug	数据更新时产生大量文件，特别是alter table语句，全表数据变更	表结构变更等需要修改大量数据时使用statement，update或delete操作还是使用row模式记录

一般采用row模式，因为从库可能会出现不一致的情况，但是row更新前后都有

binlog保证完整性的方法：statement格式的binlog, 最后会有Commit标识。row格式的binlog, 最后会有一个XID event标识，在MySQL5.6.2版本引入了binlog-checksum参数, 用来验证binlog内容的正确性。

7.2.4 日志的区别

redo log和binlog的区别：①redo是物理日志，binlog是逻辑日志；②redo log是InnoDB引擎特有的，binlog是MySQL的Server层实现的，所有引擎都可以使用；③redo log循环写，空间固定会用完；binlog可以追加写入。
出现两份日志的原因：①binlog没有能力恢复“数据页”，redo log 来实现 crash-safe 能力。②redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。binlog 日志来实现归档
- MySQL系统依赖于binlog，例如：MySQL系统高可用的基础，就是binlog复制。

7.2.5 Page Cache优化

page cache是Linux内核的优化，当应用程序读文件时，系统会检查读取的文件页是否在缓存中，如果在，直接读取，否则从磁盘中读入缓存，再读取。在Mysql中的写文件（write）通常是指将数据写入page cache中，而刷盘或落盘（fsync）才真正将数据写入磁盘中的文件。
Redo log写page cache和刷盘分别由线程log_writer和log_flusher异步执行，8.0版本中还实现了写log buffer的无锁化，通过引入Link_buf来实现（一个定长数组并保证数据更新的原子性），可通过内核提供的posix_fadvise函数POSIX_FADV_DONTNEED参数（该文件近期不会被访问，换出缓存）优化。

7.2.6 两阶段提交

写入redo log分为了prepare和commit两步，目的是为了让redo log和binlog逻辑上保持一致，如果在commit时崩溃了, 虽然没有commit, 但是prepare和binlog完整, 所以重启之后会自动commit，若没有完整的binlog会回滚事务。
redo log和binlog的关联：它们都有字段XID. 崩溃恢复时, 会按顺序扫描redo log，如果只有prepare, 没有commit的redo log, 就拿着XID去binlog找对应的事务，两者都有就直接提交。
双1配置保证不丢数据：innodb_flush_log_at_trx_commit=1 和 sync_binlog=1 保证每次事务的redo log和binlog都持久化到磁盘。
优化点：两阶段提交，2次IO操作，使用binlog in redo方案，事务提交时，将事务的Binlog Events写入到Redo中，然后将Redo持久化。而Binlog文件则采用异步的方式，用单独的线程周期性的持久化到存储中。因此事务的提交过程中，减少了一次IO。