虚拟主机多网站,长沙网站大全,信息化建设包括网站建设,要如何做才能拥有自己的网站呢前言
了解MySQL的架构和原理对于很多的后续很多的操作会有很大的帮助与理解。并且很多知识都与底层架构相关联。
了解MySQL架构 通过上面的架构图可以得知#xff0c;Server层中主要由 连接器、查询缓存、解析器/分析器、优化器、执行器 几部分组成的#xff0c;下面将主要…前言
了解MySQL的架构和原理对于很多的后续很多的操作会有很大的帮助与理解。并且很多知识都与底层架构相关联。
了解MySQL架构 通过上面的架构图可以得知Server层中主要由 连接器、查询缓存、解析器/分析器、优化器、执行器 几部分组成的下面将主要描述下这几部分。
1、连接器
客户端想要对数据库进行操作时前提是与数据库建立好连接而连接器就是用来负责跟客户端建立连接、获取权限、维持和管理连接的。
1连接方式
MySQL既支持短连接也支持长连接。短连接就是操作完毕后马上close关掉。长连接可以保持打开减少服务端创建和释放连接的消耗后续程序访问的时候还可以使用这个连接。
2连接池
与客户端的连接池一样为了减少频繁创建和销毁连接造成的不必要的性能损失这里也采用了“池化”的思想通过数据库连接池去管理连接。一般我们会在连接池中使用长连接例如druid、c3p0、dbcp等
2、查询缓存
MySQL缓存是默认关闭的也就是说不推荐使用缓存并且在 MySQL 8.0 版本直接将查询缓存的整块功能删掉了。因为缓存中的格式是kye(sql),value(数据的方式保存的如果SQL只要有一点不同那么就不会走缓存。其次是表中的数据很多时候都是会变化的而数据变化了那么缓存也就没有意义。
3、分析/解析器
分析器的工作主要是对要执行的SQL语句进行解析最终得到抽象语法树然后再使用预处理器判断抽象语法树中的表是否存在如果存在的话在接着判断select投影列字段是否在表中存在等。
1词法分析
词法分析用于将SQL拆解为不可再分的原子符号称为Token。并根据不同数据库方言所提供的字典将其归类为关键字表达式字面量和操作符。
2语法分析
语法分析就是根据词法分析拆解出来的Token原子符号将SQL语句转换为抽象语法树。
3预处理器
预处理是用来对生成的 抽象语法树 进行语义校验语义校验就是对查询的表、select投影列字段进行校验判断表、字段是否存在等 SELECT id, name FROM t_user WHERE status ‘ACTIVE’ AND age 18 以上述SQL为例经过词法分析与语法分析后会得到的抽象语法树如下图。 4、优化器
优化器的作用主要是将SQL经过词法解析/语法解析后得到的语法树通过MySQL的数据字典和统计信息的内容经过一系列运算 最终得出一个执行计划包括选择使用哪个索引。
在优化过程中经过的一系列运算是什么呢
1逻辑变换例如SQL的where条件中存在 89那逻辑转换就是将语法树中存在的这种常量表达式直接进行化简化简为 false除了化简还有常量表达式计算等。
2代价优化就是通过付出一些数据统计分析的代价来得到这个SQL执行是否可以走索引以及走哪些索引除此之外在多表关联查询中确定最终表join的顺序等 在分析是否走索引查询时是通过进行动态数据采样统计分析出来只要是统计分析出来的那就可能会存在分析错误的情况所以在SQL执行不走索引时也要考虑到这方面的因素 5、执行器
MySQL 通过分析器知道了你要做什么通过优化器知道了该怎么做于是就进入了执行器阶段开始执行语句。执行器最终就是根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据完成SQL的执行。
开始执行的时候要先判断一下建立连接的对象对这个表有没有执行操作的权限如果没有就会返回没有权限的错误如果有就按照生成的执行计划进行执行。
InnoDB存储引擎
存储引擎是对底层物理数据执行实际操作的组件为Server服务器层提供各种操作数据的 API数据是被存放在内存或者是磁盘中的。MySQL 支持插件式的存储引擎包括 InnoDB 、MyISAM、Memory 等。一般情况下MySQL默认使用的存储引擎是 InnoDB。如下图所示InnoDB存储引擎整体分为内存架构Memory Structures和磁盘架构Disk Structures 1、Buffer Pool
Buffer Pool 缓冲池是 InnoDB 存储引擎中非常重要的内存结构类似 Redis 一样的作用起到一个缓存的作用。MySQL 的数据最终是存储在磁盘中的如果没有 Buffer Pool那么每次的数据库请求都会磁盘中查找这样必然会存在 IO 操作。但是有了 Buffer Pool在第一次查询时就会将查询的结果保存到 Buffer Pool 中这样后面再有请求时就会先从缓冲池中去查询如果没有再去磁盘中查找然后在放到 Buffer Pool 中如下图 UPDATE students SET stuName ‘小强’ WHERE id 1 以上述SQL为例那么它的执行顺序大致如下 1innodb 存储引擎先在缓冲池中查找 id1 的这条数据是否存在2如果缓存不存在那么就去磁盘中加载并将其存放在缓冲池中3该条记录会被加上一个独占锁 **??**buffer pool 和 前面提到的查询缓存的区别
1查询缓存查询缓存位于Server层MySQL Server首选会从查询缓存中查看是否曾经执行过这个SQL如果曾经执行过的话之前执行的查询结果会以Key-Value的形式保存在查询缓存中。key是SQL语句value是查询结果。我们将这个过程称为查询缓存
2Buffer Pool位于存储引擎层。Buffer Pool就是MySQL存储引擎为了加速数据的读取速度而设计的缓冲机制
2、undo log日志文件回滚日志记录数据被修改前的样子
Innodb 存储引擎的最大特点就是支持事务如果事务提交失败那么该事务中所有的操作都必须回滚到执行前的样子而这个回滚的操作就是利用 undo log文件完成的。
Undo Log用于实现事务的原子性和一致性用于撤销事务对数据的修改。
上面我们介绍了在准备更新一条SQL语句的时候该条语句对应的数据已经被加载到 Buffer pool 中了实际上这里还有这样的操作就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo log 日志文件中插入一条日志也就是将 id1 的这条记录的原来的值记录下来便于事务失败后进行回滚 到这一步我们执行的 SQL 语句对应的数据已经被加载到 Buffer Pool 中了然后开始更新这条语句更新的操作实际是在Buffer Pool中执行的。那问题来了更新完数据之后Buffer Pool缓冲池中的中的数据就会和数据库中的数据库不一致那就是说Buffer Pool 中的数据成了脏数据没错目前这条数据就是脏数据Buffer Pool 中的记录是“小强”数据库中的记录是“旺财” 这种情况 MySQL是怎么处理的呢我们接着往下看
3、redo log 日志文件重做日志记录数据被修改后的样子
redo 日志文件是 InnoDB 特有的他是存储引擎级别的
Redo Log用于实现事务的持久性用于在数据库异常崩溃时恢复数据的一致性。MySQL通过Undo Log和Redo Log的配合确保了数据库的事务安全性和持久性。
该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo log file前者是在内存中后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中用于在刷新脏页到磁盘发生错误时供数据恢复用。
除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中之外其他的操作是在内存中完成的内存中的数据的特点就是断电丢失。如果此时 MySQL 所在的服务器宕机了那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo log 日志文件就需要来大显神通了
redo 就是准备去做、将要去做的意思redo log 记录的是将要做的一些操作。例如此时将要做的是 update students set stuName‘小强’ where id1; 那么这条操作就会被记录到 redo log buffer 中redo log buffer 是 MySQL 为了提高效率所以将这些操作都先放在内存中去完成 这时候假设服务器宕机了那么缓存中的数据还是丢失了。那能不能不要放在内存中直接保存到磁盘呢很显然不行在内存中操作目的是为了提高效率。如果此时 MySQL 真的宕机了那么没关系的因为 MySQL 会认为本次事务是失败的会利用 undo log 的日志信息进行回滚并不会有任何的影响。 到了这里SQL语句也更新好了那么需要将更新的值提交了也就是需要提交本次的事务只要事务成功提交了才会将最后的变更保存到数据库。在提交事务前会将 redo Log Buffer 中的数据写入到磁盘文件 redo log 中进行持久化而刷入磁盘的时机则是根据 innodb_flush_log_at_trx_commit 参数来决定的 0表示每次事务提交时都只是把redo log留在redo log buffer中数据库宕机有可能会丢失数据。 1默认值每次事务提交时都将redo log直接持久化到磁盘数据最安全不会因为数据库宕机丢失数据但是效率稍微差一点线上系统推荐设置 2每次事务提交时都只把redo log写到操作系统的缓存page cache里这种情况如果数据库宕机是不会丢失数据的os cache里还有数据,但是如果操作系统宕机了那么page cache里的数据还没来得及写入磁盘文件就有可能会丢失数据。 InnoDB有一个后台线程每隔1秒就会把redo log buffer中的日志调用操作系统函数write写到文件系统page cache然后调用操作系统函数fsync持久化到磁盘文件中。
关于这个参数的设定需要根据具体的项目以及业务场景来定如果设置合理那么可以带来很大的性能提升。 redo log Buffer 刷入磁盘后就完成了数据的持久化了就算数据库宕机了我们也可以再下次重启的时候将 redo log 日志文件的内容恢复到 Buffer Pool 中。
前面提到如果每次修改都直接以落盘的形式处理那么必定有大量的随机IO如果使用机械磁盘更会存在大量的disk seek时间数据库性能极低那为什么写入 redo log 就能提高性能呢这是因为写 redo log 至磁盘属于磁盘顺序写只需 append log 到下一位置所以 redo log 的落盘速度是非常快的 到此SQL语句的执行就如下 1.准备SQL语句 2.MySQL会先在Buffer pool(缓冲池)中进行查找需要的数据如果没有那么就从磁盘中取出然后放到Buffer pool中进行操作 3.在加载到Buffer pool的同时会将这条数据的原始记录加载到undo日志文件中。 4.在Buffer Pool中完成数据操作 5.更新后的数据会记录在Redo Log Buffer中然后根据 innodb_flush_log_at_trx_commit 参数来决定什么时候将数据刷入磁盘文件redo log进行持久化 6.MySQL重启时会将redo log恢复到缓冲池中。 4、bin log日志文件记录整个操作过程 前言bin log 和 redo log 有些相似两者的主要区别有 1redo log是 InnoDB 存储引擎特有的日志文件而 bin log属于是 MySQL 级别的日志 2redo log 适用于崩溃恢复bin log 适用于主从复制和数据恢复 3redo log 记录的东西是偏向于物理性质的如“对什么数据做了什么修改”。bin log是偏向于逻辑性质的类似于“对 students 表中的 id 为 1 的记录租了更新操作” 。 bin log文件是如何刷入磁盘的 bin log的刷盘策略可以通过 sync_bin log 来修改默认为0表示先写入 os cache也就是说在提交事务的时候数据不会直接到磁盘中这样如果宕机 bin log 数据仍然会丢失。所以建议将sync_bin log设置为 1表示直接将数据写入到磁盘文件中。
既然 bin log 也是日志文件那它是在什么时候记录数据的呢其实 MySQL 在提交事务时不仅会将 redo log buffer 中的数据写入到 redo log 文件中也会将本次修改的数据记录到 bin log 文件中同时会将本次修改的 bin log 文件名和修改的内容在 bin log 中的位置记录到 redo log 中最后还会在 redo log 最后写入 commit 标记这样就表示本次事务被成功的提交了 如果在数据在写入 bin log文件时刚写完数据库宕机了数据会丢失吗首先可以确定的是只要 redo log 最后没有 commit 标记说明本次的事务一定是失败的。但是数据是没有丢失的因为已经被记录到 redo log 的磁盘文件了在 MySQL 重启的时候就会将 redo log 中的数据恢复加载到Buffer Pool中 到目前为止一个更新操作基本介绍得差不多但是你有没有感觉少了哪件事情还没有做是不是你也发现这个时候被更新记录仅仅是在内存中执行的哪怕是宕机又恢复了也仅仅是将更新后的记录加载到 Buffer Pool 中这个时候 MySQL 数据库中的这条记录依旧是旧值也就是说内存中的数据在我们看来依旧是脏数据那这个时候怎么办呢
其实 MySQL 会有一个后台线程它会在某个时机将我们 Buffer Pool 中的脏数据刷到 MySQL 数据库中这样就将内存和数据库的数据保持统一了。刷新脏页的时机如下
1redo log 写满时停止所有更新操作将 checkpoint 向前推进推进那部分日志的脏页更新到磁盘
2需要将一部分数据页淘汰如果是干净页直接淘汰就行了脏页的话需要全部同步到磁盘
3mysql自认为空闲时
4mysql正常关闭之前 关于图中的OS cache是操作系统中内存中的一块区域为了提升应用的写入磁盘的性能解决内存和磁盘交互的性能瓶颈。很多应用如kafka、MQ等写入硬盘的操作那么就把os cache当做硬盘来写入提升性能。 总结 1首先 MySQL 执行器根据执行计划调用存储引擎的API查询数据 2存储引擎先从缓存池 buffer pool 中查询数据如果没有就会去磁盘中查询如果查询到了就将其放到缓存池中 3在数据加载到 Buffer Pool 的同时会将这条数据的原始记录保存到 undo log 日志文件中 4innodb 会在 Buffer Pool 中执行更新操作 5更新后的数据会记录在 redo log buffer 中 6提交事务在提交的同时会做以下三件事 7第一件事将 redo log buffer 中的数据刷入到redo log文件中 8第二件事将本次操作记录写入到 bin log 文件中 9第三件事将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中同时在 redo log 最后添加 commit 标记 10使用一个后台线程它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中这样就将内存和数据库的数据保持统一了
