附录：mysql面试MySQL八股文连环45问,你能坚持第几问？_IT邦德的博客
一：mysql体系架构
1、client端
与mysql服务端建立连接，常见有java，C等，通过各自的api技术实现连接。
2、server端
- 连接池： 管理客户端与mysql的连接，一个线程管理一个。
- 系统管理和控制工具： 负责系统层面的东西，如： 备份恢复，安全和集群管理等。
- sql接口： 用于接收sql命令，返回结果。
- 解析器： 对接收的sql解析，生成解析树，校验解析树是否合法。
- 查询优化器： 验证合法后，交给优化器调优，然后与存储引擎交互，操作数据。
- 缓存： 由一系列小缓存组成，缓存查询结果，提升效率。
3、存储引擎
与底层系统文件交互，负责数据的存储和提取。 mysql存储引擎是插件式的，上层通过接口与之通信，避免不同引擎间的差异。 常见的如：MyISAM和InnoDB
4、系统文件
数据文件：
- db.opt文件：记录当前库默认使用的字符集和校验规则。
- frm文件：存储与表相关的元数据（meta）信息，比如：表结构的定义。 一张表对应一个frm。
- MyISAM专用文件
- MYD文件： 存放表数据，一张表一个
- MYI文件：存放索引相关信息，一张表一个
- InnoDB专用文件
- ibd文件：独立表空间数据，存放每张表的数据，索引等。
- ibdata文件： 系统表空间数据，如表的元数据，Undo日志等。
- ib_logfifile文件： redo log日志文件
日志文件：
- 错误日志：记录错误信息。show variables like '%log_error%'；
- 通用查询日志： 记录查询语句。show variables like '%general%';
- 二进制日志binary log：记录增删改的sql，用于数据恢复和主从复制
- show variables like '%log_bin%'; // 是否开启 show variables like '%binlog%'; // 参数查看 show binary logs;// 查看日志文件
- 慢查询日志
- 记录所有执行时间超时的查询 SQL ，默认是 10 秒。 show variables like '%slow_query%'; // 是否开启 show variables like '%long_query_time%'; // 时长
配置文件：
- 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等
二：Mysql运行机制
了解了大体的架构之后，来看一下每一个环节的运行机制
1、建立连接
建立连接后，mqsql客户端与服务端采用 ‘半双工’ 的通信方式。 即同一时间，要么发送数据，要么接受数据。 另外，对于建立的每一个连接，都有一个线程状态标识连接正在做什么。
线程状态：show processlist;
- id：线程id
- user：启动线程的用户
- host：客户端的ip和端口
- db：命令在哪个库执行
- command： 在执行什么命令
- state：线程状态
- updating： 正在修改记录
- sleeping：等待客户端发送新的请求
- starting： 正在执行请求
- checking table：检查数据表
- closing table：表数据刷磁盘
- locked：当前操作的记录被其他查询锁住了
- sending data：在处理select
- info：记录执行的sql语句
2、查询缓存：执行查询后，会将sql语句和结果缓存起来，下次有相同的sql直接走缓存。
常用命令:
- show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等
- show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等
有一些条件不会作缓存：
- 查询语句使用SQL_NO_CACHE
- 查询的结果大于query_cache_limit设置
- 查询中有一些不确定的参数，比如now()
3、解析器： 将sql语句解析生成解析树，并校验是否合法。
4、查询优化器：
根据解析树生成最优的sql执行计划。如：
- 5=5 and a>5 改为 a > 5
- a<b and a=5 改为 b>5 and a=5
- where id in(2,1,3) 改为 where id in(1,2,3)，因为要先排序，采用二分查找
5、查询执行引擎：
执行sql语句。根据sql中表的存储引擎类型和对应api接口，与对应的存储引擎交互，查询结构返回给客户端。 若开启查询缓存，将sql和结果缓存。
三：MySQL存储引擎
存储引擎与底层文件打交道，负责mysql的数据存储和提取。
InnoDB和MyISAM对比
- 事务和外键： InnoDB支持事务和外键，具有安全性和完整性，适合大量写操作。 MyISAM不支持，提供高速存储和检索，适合大量查询。
- 锁机制： InnoDB支持行级锁，并发能力较好。 MyISAM支持表级锁，并发能力差。
- 文件： InnoDB对应两个文件： .frm 和 .ibd MyISAM对应三个文件：.frm, MYD和.MYI
- 适用场景：
- MyISAM： 不需要事务支持，并发低，以读为主，数据一致性要求不高。
- InnoDB： 支持事务，应对数据更新频繁的场景，一致性要求较高。
InnoDB 存储结构 ： 分为内存和磁盘部分
内存结构：
内存结构主要包括Buffffer Pool、Change Buffffer、Adaptive Hash Index和Log Buffer四大组件
Buffffer Pool：缓冲池，缓存查询数据，以Page页为单位，默认16k，底层采用链表结构管理。
- Page：
- free page： 未使用的页
- clean page：被使用，数据没有被修改过
- dirty page： 被适用，数据被更改过，页数据和磁盘中不一致
- Change Buffffer： 写缓冲区，简称 CB 。在进行 DML 操作时，修改BP中的数据，如果Buffffer Pool没有其相应的 Page 数据，并不会立刻将磁盘页加载到缓冲池，而是在CB 记录缓冲变更，等合适时机数据被读取时，再将数据合并恢复到BP 中（比如要真正提交事务时，需要加载数据产生undo和redo日志的时候），后续由后台线程刷到磁盘。
- Adaptive Hash Index：InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引，提升吸能。
- Log Buffer：日志缓冲区。 DML提交事务时会产生redo和undo日志，会先记录在缓冲区，而后刷到磁盘文件。
- LogBuffffer 空间满了，会自动写入磁盘。可以通过将 innodb_log_buffffer_size 参数调大，减少磁盘IO 频率 。 innodb_flflush_log_at_trx_commit参数控制日志刷新行为，默认为 1 0 ： 每隔 1 秒写日志文件和刷盘操作（写日志文件： LogBuffffer-->OS cache ，刷盘： OS cache-->磁盘文件），最多丢失 1 秒数据 1 ：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作 2 ：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作
磁盘结构：
表空间：独立表空间、系统表空间，通用表空间，撤销表空间，临时表空间等。
数据字典：用于查找表、索引、表字段信息等。 一定程度上与表元数据文件.frm信息重叠。
双写缓冲区：位于系统表空间，在数据刷入磁盘前会存放到这里。
redo 和undo日志文件。
Innodb线程模型：
IO thread：
- write thread： 负责写操作，将缓存脏页刷新到磁盘
- insert buffffer thread ：负责将写缓冲内容刷新到磁盘
- read thread ： 负责读取操作，将数据从磁盘加载到缓存page页
- log thread：负责将日志缓冲区内容刷新到磁盘
Purge Thread: 事务提交后回收undo页
Page cleaner Thread： 调用write thread将脏数据刷到磁盘
Master Thread： 调用其他线程
Innodb数据文件：
InnoDB 数据文件存储结构：
分为一个 ibd 数据文件 -->Segment （段） -->Extent （区） -->Page （页） -->Row （行）
- Tablesapce ：表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
- Segment ：段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
- Extent ：区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不一页一页分，直接分配一个区。
- Page ：页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。
- Row ：行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息。
undo log：
- 事务开始前，将要修改的数据放入undo log。 当然，并不是记录数据本身，而是记录和DML操作相反的动作，属于逻辑日志。
- 事务提交后，不马上删除undo log，而是放到删除列表，通过后台线程purge thread异步回收。
- 多版本控制（MVCC），存放的是事务提交前版本的数据，新的事务进来，也是操作旧版本数据。
redo log：
为什么需要redo log：
通常我们需要将事务数据持久化，在buffer pool中以page为单位刷盘。 但是，一个事务可能只改变了一个page中极小部分的数据，或者改变了多个page的数据（物理地址上不联系），如果每次事务操作都直接刷盘数据，就会频繁的使用随机IO，性能极差（实际是启动一个后台io线程将缓存脏页刷到磁盘）。 这时种情况下，我们提交的事务数据并不会立即被刷到磁盘上，如果出现宕机等异常，数据会丢失。 所以，我们需要一种快速刷盘的措施，那就是redo log。先将操作记录到redo log，并将redo log快速刷盘（顺序写，性能高），异常重启时会根据redo log批量完成数据的刷盘，保证数据的完整性。
redo log在提交事务时产生，会先写入log buffer，根据配置刷入磁盘的log 文件。 我们来看看配置：show variables like '%innodb_log%';
Redo Buffffer 持久化到 Redo Log 的策略，可通过 Innodb_flflush_log_at_trx_commit 设置 （上面介绍Log Buffer时已说过流程，这里再说一次） ：
- 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
- 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
- 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。 一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据
上面的OS cache ，是系统空间的缓冲。 因为用户空间的缓冲不能直接进入磁盘
流程如下图： Log Buffer是在，每次事务提交时产生，然后根据配置再不同的时机输入磁盘
binlog日志：
上面说的redo log是属于引擎的日志，而binary log是属于mysql server自己的日志。 通常用于 主从复制和数据恢复。
Binlog 文件名默认为 “ 主机名 _binlog- 序列号 ” 格式，例如oak_binlog-000001。 文件记录模式有 STATEMENT 、 ROW 和 MIXED 三种。
- Row：基于行的记录。 能清楚记录每一行数据修改前后是什么样，但是会产生大量日志，特别alter table这种操作瞬间让日志膨胀。
- STATEMENT： 基于段的记录。 每一条修改的sql都会记录下来，slave复制等执行一遍相同sql。 日志量小，性能高，但是now()等函数会造成不一致。
- MIXED： 前两种混合。 mysql 根据执行的语句选择。
参数： show variables like 'log_bin';
Redo Log和Binlog区别 ：
- Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
- Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
- Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
- Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制，也可以作为数据恢复使用-手动恢复（通常也用不到，除非引擎介质损坏，redolog自动恢复失败，而binlog往往在做数据迁移，主从同步应用较多）
