mysql结构及执行

1、结构

MySQL 大体可以分为 客户端、Server层、存储引擎层、系统文件层

• 客户端连接器
  提供与MySQL服务器建立的支持

• Server层
  是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分

  • 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等
  • SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
  • 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
  • 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
  • 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互
  • 缓存（Cache&Buffer）：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。有一系列缓存，比如表缓存，记录缓存，权限缓存，引擎缓存等。

• 存储引擎层
  存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

• 系统文件
  该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

2、日志文件分类

日志		记录		作用
binlog	归档日志	server层日志，记录所有的ddl和dml，没有造成数据变化也会记录，不包含查询语句	写满后自动切换到下一个日志文件继续写，不覆盖，可指定有效期到期自动删除	数据恢复：数据恢复到过去的某一时刻；主从复制：主机上开启 binlog将 binlog同步给从机，从机通过 binlog来同步数据
redo log	重写日志	InnoDB存储引擎层，redo日志记录事务执行后的状态	循环写入	崩溃恢复：redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据
undo log	回滚日志	记录的是修改之前的数据，保证能恢复到数据修改之前。		undo log还可以提供多版本并发控制下的读取（MVCC），undo log主要用来回滚到某一个版本。比如：当delete一条记录时，undolog中会记录一条对应的insert记录
error log	错误日志	记录MySQL在启动、关闭或者运行过程中的错误信息
slow query log	慢查询日志	记录执行时间超过指定阈值的SQL语句
general log	一般查询日志	记录了客户端连接信息以及执行的SQL语句信息

binlog格式：

写入格式binlog_format	简称	写入条件	优点	缺点
Statement	Statement-Based Replication,SBR	只记录这条将会修改数据的执行的sql，不记录每一行数据变化，(没有造成数据变化也会记录，不包含查询语句)	减少了binglog日志量，避免大量的IO操作，提示系统性能	导致数据一致性问题，比如uuid()，主从复制时每次执行获取的数据不一致
Row	Row-Based Replication,RBR	记录每一行数据变化	不会出现数据一致性问题	会产生大量的日志，带来IO性能问题，尤其是批量操作
Mixed	Mixed-Based Replication,MBR	系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog

3、InnoDb引擎执行过程

1、建立连接

先通过连接器连接到这个数据库上。连接器负责跟客户端建立连接、校验用户名密码的正确性,同时获取该用户的权限放到缓存中、维持和管理连接

2、查询缓存

这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

缓存Select查询的结果和SQL语句
执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
即使开启查询缓存，以下SQL也不能缓存
查询语句使用SQL_NO_CACHE
查询的结果大于query_cache_limit设置
查询中有一些不确定的参数，比如now()
show variables like ‘%query_cache%’; //查看查询缓存是否启用，空间大小，限制等
show status like ‘Qcache%’; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

3、解析器

如果没有命中查询缓存，就要开始真正执行语句了
分析器先会做“词法分析”，将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义， SQL 语句是否满足 MySQL 语法，最后生成新的“解析树”

4、查询优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。MySQL使用很多优化策略生成最优的执行计划

比如，优化器在表里面有多个索引的时候，决定使用哪个索引；
或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；
使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)

5、执行器

执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

4、内部XA两阶段实现

mysql为了保证redo Log 和binlog一致性，内部事物提交采用xa两阶段提交。

MySQL中的XA实现分为：外部XA和内部XA
前者是指我们通常意义上的分布式事务实现；
后者是指单台MySQL服务器中，Server层作为TM(事务协调者)，而服务器中的多个数据库实例作为RM，而进行的一种分布式事务，也就是MySQL跨库事务；也就是一个事务涉及到同一条MySQL服务器中的两个innodb数据库(因为其它引擎不支持XA)。

以update T set c=c+1 where ID=2为栗;

• 执行器拿到引擎给的行数据（可从缓存中读取，缓存没有，则从磁盘读取后刷入到缓存中）
• 把c加1，得到新的一行数据
• 再调用引擎接口写入这行新数据之前，写入一条undo log，将ID=2的c值保存起来，用于数据回滚

第一阶段

• 调用引擎接口写入这行新数据，更新到缓存，同时写入一条redo log，将修改后的c值保存起来，此时redo log处于prepare状态，持有prepare_commit_mutex锁。然后告知执行器执行完成了，可以提交事务。

为了取得更好的读写性能，InnoDB会将数据缓存在内存中（InnoDB Buffer Pool），对磁盘数据的修改也会落后于内存

第二阶段

• 执行器生成这个操作的binlog，并把binlog写入磁盘，保存修改后的c值
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，释放prepare_commit_mutex，更新完成。

事务崩溃恢复过程如下：

• 崩溃恢复时，扫描最后一个Binlog文件，提取其中的xid；

MySQL为何只需要扫描最后一个Binlog文件呢 ？ 原因是每次在rotate到新的Binlog文件时，总是保证没有正在提交的事务，然后fsync一次InnoDB的redo log。这样就可以保证老的Binlog文件中的事务在InnoDB总是提交的

• InnoDB维持了状态为Prepare的事务链表，将这些事务的xid和Binlog中记录的xid做比较，如果在Binlog中存在，则提交；否则回滚事务。（以 binlog 的写入与否作为事务提交成功与否的标志）

问题：

在MySQL5.6之前的版本，由于引入了Binlog/InnoDB的XA，Binlog的写入和InnoDB commit完全串行化执行，当sync_binlog=1时，第二阶段的写入binlog成为瓶颈，并且持有全局锁，影响性能

sync_binlog参数来控制数据库的binlog刷到磁盘，表示每写缓冲多少次就同步到磁盘
sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷一下

Group Commit优化

5.6版本优化

• 调用引擎接口写入这行新数据，更新到缓存，同时写入一条redo log，此时redo log处于prepare状态，持有prepare_commit_mutex锁。然后告知执行器执行完成了，可以提交事务。
• flush stage： 将各个线程的binlog从cache写到文件中
• sync stage： 对binlog做fsync刷盘操作
• commit stage： 为各个线程做引擎层的事务commit，释放锁

Group Commit后，sync_binlog的含义就变了，假定设为1000，表示的不是1000个事务后做一次fsync，而是1000个事务组。
后三个步骤可以并发执行

5.7.6版本优化：

通过延迟写redo log的方式，显式的为redo log做了一次组写入，并减少了log_sys->mutex的竞争

从XA恢复的逻辑我们可以知道，只要保证InnoDB Prepare的redo日志在写Binlog前完成write/sync即可

• Step1. InnoDB Prepare，记录当前的LSN到thd中；
• Step2. 进入Group Commit的flush stage；Leader搜集队列，同时算出队列中最大的LSN。
• Step3. 将InnoDB的redo log write/fsync到指定的LSN
• Step4. 写Binlog并进行随后的工作(sync Binlog, InnoDB commit , etc)

LSN：日志的逻辑序列号(log sequence number)，随着日志的写入而逐渐增大。
thd：For each client connection we create a separate thread with THD serving as a thread/connection descriptor
准备写入Binlog时，维持一个队列，最早进入队列的是leader，后来的是follower