MySql查询、插入过程解析

1、MySQL查询SQL的执行流程

首先贴出执行流程图：

示例SQL：

select user_id,user_name,age from user where id = 1 and age = 23

以上面的SQL为例讲解，当我们执行这条SQL语句的时候，执行流程如下：

1、首先会使用用户名和密码和MySQL数据库建立连接

2、查询缓存，如果缓存命中，则直接返回

3、若缓存中不存在，则开始执行解析器

a)、先进行词法解析： 词法分析就是把一个完整的 SQL 语句分割成一个个的字符串，比如上面的那条SQL语句会被解析成：select , user_id , user_name , age , from , user , where , id , = , 1 , and , age , = , 23

b)、当词法解析完毕以后，开始进行语法分析： 语法分析器会根据语法规则做语法检查，判断你输入的这个 SQL 语句是否满足 MySQL 语法 ，比如你的select误写成了selec ，那么语法检查就不会通过，并且抛出异常 You have an error in your SQL syntax。如果语法检查通过则会生成一棵解析树。

解析树：

4、当解析器解析完毕后，开始进行预处理操作，预处理会进一步的检查解析树是否合法，比如：要查询的表是否存在，用户是否有表的操作权限等。预处理之后会重新生成一颗解析树。

5、当预处理通过以后，还需要通过查询优化器进行优化。 查询优化器的作用就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里面使 用的是基于成本模型的优化器，哪种执行计划执行时成本最小就用哪种。

**优化器都做哪些优化处理呢？**比如

1. 当有多个索引可用的时候，决定使用哪个索引；
2. 在一个语句有多表关联（join）的时候，决定各个表的连接顺序，以哪个表为基准表。

6、当通过了优化器的优化以后，得到一个查询计划， 于是就进入了执行器阶段，开始执行语句去数据库查询数据。

（a）开始执行的时候，要先判断一下你对这个表customer有没有执行查询的权限，如果没有，就会 返回没有权限的错误。 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验 证。)。

（b）如果有权限，就使用指定的存储引擎打开表开始查询。执行器会根据表的引擎定义，去使用这 个引擎提供的查询接口，提取数据。

7、查询完毕后，将查询结果缓存在缓存里面方便下次查询的时候直接从缓存命中，提升查询效率。

2、MySql数据的插入流程

先上流程图：

1、概念解释

1、脏页

简单解释，由于MySQL是按页存储数据到内存中的，每一页可能会包含多行数据，当缓存中该页的某一行数据和磁盘中存储的不一致，那么这一页就称为脏页。比如一个更新操作，首先会在内存中更新，然后再将内存中的数据刷写回磁盘，当只更新了内存，还没来得及更新磁盘，这时候内存和磁盘的数据不一致，所以就会出现脏页。

2、脏页落盘

将内存中的数据刷写回磁盘的操作就叫脏页落盘。

3、Double Write双写

原理图如上图， Double Write由两部分组成，一部分是内存中的double write buffer，大小为 2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。 在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的 double write buffer区域，之后通过double write buffer再分两次，每次1MB顺序地写入共享表 空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成 double write页的写入后，再讲double wirite buffer中的页写入各个表空间文件中。 如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间 中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

如果说Insert Buffer给InnoDB存储引擎带来了性能上的提升，那么Double Write带给InnoDB存储 引擎的是数据页的可靠性。

2、流程图解析

1、当我们执行一条update或insert语句时，并不会马上写入磁盘，而是先写入到缓存Buffer Pool中

2、然后写入到redo log Buffer(日志缓存)

3、固定时间以后（可设定），或者有事务提交以后，触发 redo log Buffer缓存刷写到磁盘中

4、Buffer Pool缓存中的数据根据是否满足检查点，不时的刷写到磁盘中

3、写入数据的常见问题

1、何时触发check point检查点，进行脏页落盘

a）、当Buffer Pool缓存满了的时候，强制性的进行脏页落盘。

b）、设置固定时间，比如每隔5秒钟刷写一次

2、何时触发redo log Buffer刷写到磁盘

a）、redo log buffer缓存满了的时候，强制性的刷写回磁盘

b）、设置固定时间，比如每秒刷写一次（innodb_flush_log_trx_commit）

3）、当有事务提交（commit）的时候，强制刷写缓存回磁盘

CheckPoint检查点机制

• 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁 盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。
• 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行 Checkpoint，将脏页也就是页的新版本刷回磁盘。 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让 其无限增大的。
• 重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机 时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需 要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

3、redo log file 的作用

redo log file文件的作用是，当文件丢失时可以从redo log 文件中恢复数据。比如此时服务器宕机了，当需要恢复数据库中的数据时就可以使用redo log文件。

只要check point落盘成功，那么redo log file中对应的日志就可以清除了

4、当数据更新的时候为什么要先写入到缓存中，而不是直接写入磁盘？为啥要先写入redo log buffer缓存中，而不直接写入到磁盘的redo log file文件？

这两个问题其实是一个问题，频繁的读写磁盘，IO开销会特别大。

5、为什么Buffer Pool缓存刷写到磁盘的速度比redo log buffer刷写到磁盘的速度慢？

这里涉及到MySQL中数据在磁盘的存放问题，Buffer Pool缓存刷写到磁盘时，由于数据是存放在不同的磁盘扇区上的，并不连续。磁头会在磁道上进行寻道，然后寻找一个个的扇区，然后往扇区里写入数据。

但是redo log buffer刷写到磁盘的时候，redo log file是存放在一片连续的空间上的，不需要在不同的磁道上切换寻道，直接在原文件中追加即可。所以比前者快得多。