嚴華 发表于 2024-10-23 13:40:20

mysql基础架构

Mysql体系架构

https://i-blog.csdnimg.cn/direct/9dfb2551863944e487fec764629ad7cb.png
Server层

Server 层包括连接器、查询缓存、剖析器、优化器、执行器等,涵盖 MySQL 的大多数焦点折务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,好比存储过程、触发器、视图等。
连接池

连接器

通过工具大概命令连接Mysql:mysql -h$ip -P$port -u$user -p。如果密码失败会收到"Access denied for user",然后客户端步伐竣事执行。密码正确,一个用户乐成建立连接,即使这时间你用管理员账号修改这个用户的权限,也不会影响当前连接的权限。只有再次建立连接的时间才会利用新的权限。
连接池

每次连接都需要创建连接的话,竣事连接进行销毁的时间,高并发下非常的浪费资源和性能。
当利用连接池之后,每次建立连接的时间,会去处连接池代理建立连接。连接池首先判定是否有空余的连接,如果有就返回,如果没有空余的连接,就去判定链接数是否已经到了最大,如果没有就去新建一个连接,如果已经到了最大链接数,就会让步伐去等待,如果在等待时间有连接的释放大概归还,则可以利用连接,如果没有就返回错误。当线程利用完连接,就会将其归还给连接池,此时判定此连接是否已经凌驾最大引用次数,如果到达则销毁,如果没有则归还给连接池供其他线程利用。
MySQL的默认连接数为100(max_connection),可以根据利用的最大链接数(max_used_connection)来动态调解,理论上最大的链接数为16384(和Redis集群的hash槽一样)。
空闲连接的处置惩罚

连接完成之后,如果没有后续的操作,这个连接就处于空闲状态,可以通过 show processlist 查看当前空闲的连接。长时间不操作,连接器会主动断开,默认8小时。可以通过参数wait_timeout去控制。已被断开的连接,如果客户端再次发起哀求的时间会提示Lost
connection to MySQL server during query。只有重连才可以再次执行哀求。
数据库中长连接是指连接之后,客户端持续哀求利用同一个连接;短连接则是每次执行完很少的反复哀求就断开连接,下次再从新建立一个连接。但是如果全部是用长连接的话,Mysql的内存就涨的特别快。mysql执行过程中临时用的内存是管理在连接对象内里的。只有断开连接的时间才能释放。长期这样操作就会出现OOM。Mysql异常重启。
解决方案1:定期断开长连接。利用一段时间,大概步伐内里判定执行过一个占用内存的大查询后,断开连接,之后要查询再重连。
解决方案2:如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接规复到刚刚创建完时的状态。
查询缓存

select查询语句会以key-value存储在内存中。key是查询语句,value是查询效果。正常来说不建议利用查询缓存,如果表经常更新,就会清空表上的所有查询缓存。需要注意Mysql8.0之后不存在查询缓存这个模块
分析器

mysql首先会进行语法分析。语法分析器根据语法规则判定输入的sql是否满足Mysql的语法。如果不满足会收到You have an error in your SQL syntax的错误提示。一样平常语法错误会提示第一个出现错误的位置,所以你要关注的是紧接“use near”的内容。
在此也会校验表是否存在,字段是否存在等。
优化器

优化器就是表内里如果有多个索引,决定利用那个索引。大概在多表联查的时间,决定各个表的连接顺序。通过优化器之后,那终极的sql执行方案就确定下来了
执行器(sql接口)

会首先判定有没有对这个表的操作权限权限存在的话就根据表的引擎定义去调用引擎提供的接口去操作存储引擎终极把处置惩罚的效果返回给客户端
存储引擎

而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL5.5.5 版本开始成为了默认存储引擎。
InnoDB和MyISAM的区别

事物和外键

MyISAM不支持事务和外键,提供高速的存储和检索,适合大量的select操作
InnoDB支持事务和外键,适合大量的insert和update操作。夸大的是安全和完整性。
锁机制

InnoDB支持行级锁,锁定具体的纪录,基于索引来加锁实现。
MyISAM支持的是表级锁,锁定整张表。
索引结构

InnoDB利用的聚集索引(聚簇索引),索引和纪录是在一起存储的,即缓存索引,也缓存纪录。
MyISAM利用的黑白聚集索引,索引和纪录分开。
并发处置惩罚能力

MyISAM利用的是表锁,会导致表操作并发效率很低。读之间并不阻塞,读写阻塞。
InnoDB读写阻塞与隔离级别有关,可以采用多版本控制(MVCC)来支持并发
存储文件

InnoDB对应两个文件,一个是.frm表结构,一个是.ibd数据库。最大支持64TB
MyISAM对应三个文件,一个是.frm表结构,一个MYD数据文件,一个是MYI索引文件。最大是256TB。一个数据是首先从MYI遍历B+树找到数据的磁盘地点,人后去MYD文件找到具体数据。
InnoDB存储结构

https://i-blog.csdnimg.cn/direct/ea63d614dfa64df1bea15d51ef48c2e3.png
内存结构

Buffer Pool:

缓存池,简称BP。以page页为单位,page页默认大小16k。BP底层采用链表数据结构管理Page。在innoDB访问表纪录和索引纪录时会在page页中缓存,以后利用的时间可以减少磁盘io操作,提升效率。
Page管理

page根据状态可以分为三种类型。


[*]free page:空闲的page,未被利用
[*]clean page: 被利用page,数据没有被修改过的
[*]dirty page: 脏页,被利用page,数据被修改过,页中的数据和磁盘的数据产生了不一样。
针对上述三种page类型,通过三种链表结构来维护和管理


[*]free list: 空闲缓冲区,管理free page。
[*]flush list: 需要革新到磁盘的缓冲区,管理dirty page。内部page按照修改时间排序。越早的越先进行刷盘操作。先从尾部开始。脏页在两个内里,但是相互不影响。lru负责管理page的可用性和释放,而flush负责脏页的刷盘。 lru一些镌汰的操作也会出发flush的刷盘操作。
[*]lru list: 正在利用的缓冲区,管理的clean page和dirty page。缓冲区以midpoint为基点。前面的链表成为new列表区,存放经常访问的数据,占63%;背面的链表成为old列表区,存放利用较少的数据,占37%。
改进型LRU算法维护

平常的lru算法:末尾镌汰法,新数据从链表头部加入,释放空间时从末尾镌汰
改进的lru算法:链表分为new和old两个部门,加入元素不是重新部开始的,是从中间midpint位置插入,如果数据很快被访问,那么page就会向new列表头部移动,如果数据没有被访问,会逐步向old尾部移动,等待镌汰。
每当有新的page读取到Buffer Pool时,InnoDB引擎会判定是否有空闲页,是否足够,如果有就将free page从free list删除,放入lru列表中。没有空闲页就会根据lru算法进行镌汰。
Buffer Pool 配置参数

show variables like '%innodb_page_size%' //查看page页大小
show variables like '%innodb_old%' //查看lru list 列表参数
show variables like '%innodb_buffer%' //查看Buffer Pool参数
建议:将innodb_buffer_pool_size设置为总内存的60%-80%,innodb_buffer_pool_instances可以设置为多个,这样制止缓存争取。
Change Buffer

写缓存区,简称CB。在进行DML操作的时间,如果BP没有对应的的page数据,并不会立刻将磁盘页加载到缓冲池,而是在CB纪录缓冲变动,等未来数据被读取的时间,在将数据合并并规复到BP中。
默认占用Buffer Pool空间的25%,最大答应占50%,可以根据实际业务进行调解,参数innodb_change_buffer_max_size;
当更新一条纪录的时间Buffer Pool存在,直接在Buffer Pool修改,一次内存操作;如果Buffer Pool不存在,会直接在Change Buffer进行一次内存操作,不用再去磁盘查询数据,制止了一次磁盘IO(磁盘随机读写操作)。当下次查询纪录的时间,会先进行磁盘的读取,再从Change Buffer中读取信息,然后进行合并,终极载入Buffer Pool。除此之外,当CB内存不足的时间,后台master线程会发起合并操作。
如果根据一个二级索引删除数据,而且这个数据不在BP内里,然后这个索引页也不再内存中,这样CB会缓存这个delete操作,但是BP没有影响,BP中没有这个数据的行,依然获取读取磁盘的。这样才能返回正确的操作行数。
Change Buffer的限制

首先得要求是二级索引。如果不是二级索引到话,那前面change buffer存在意义又是什么呢?没有啥可优化的地方。那不如不要这个change buffer。因为二级索引,本身在磁盘上,在B树的非叶子结点上。
只实用于非唯一平常索引页。因为:如果在索引设置了唯一性,在进行修改的时间,必须要做唯一性的校验,此时需要读取磁盘的数据到Buffer Pool。
Adaptive Hash Index

自适应哈希索引,用于优化对BP数据的查询。InnoDB存储引擎会监控表索引的查找,如果观察到建立哈希索引可以带来速率的提升,则建立哈希索引,所以称之为自适应。InnoDB存储引擎会主动根据访问的频率和模式来为某些页建立哈希索引。
Log Buffer

日记缓冲区,用来保存要写入磁盘的log文件(Redo/Undo)的数据,日记缓冲区的内容定期革新到磁盘log文件中。日记缓冲区默认大小为16m,缓冲区满时也会主动将其革新到磁盘中,当遇到BLOB或多行更新的大事务操作时,增长日记可以节省磁盘IO。
Log Buffer写入磁盘,不是直接写入磁盘,而是先写入OS Cache(体系缓存),然后OS Cache在写入磁盘。Buffer Pool是可以直接进行刷盘操作的。可以看到上图有个标识O_DIRECT。
show variables like '%innodb_flush_log%' Log Buffer未满的时间,一些刷盘机制。
innodb_flush_log_at_trx_commit参数控制日记革新举动,默认1s


[*]如果为0:每隔1s写入OS Cache。数据会丢失,最多丢失1s的数据。
[*]1:事务提交,立刻写日记文件和刷盘。 数据不丢失,但是轻易频仍的操作
[*]2:事务提交,立刻写日记文件,但是不刷盘。刷盘是每隔1s刷盘操作。
磁盘结构

体系表空间(System Tablespace)

innodb体系表空间包罗innodb数据字典(innodb相关对象的元数据),同时,双写缓冲(doublewrite buffer)、写缓冲(change buffer)和undo日记(undo logs)等也存储于体系表空间中。此外,体系表空间也包罗用户在该表空间创建的表和索引等数据。由于体系表空间可以存储多张表,因此,其为一个共享表空间。体系表空间由一个或多个数据文件组成,默认环境下,其包罗一个叫ibdata1的体系数据文件,位于mysql数据目次(datadir)下。体系表空间数据文件的位置、大小和数目由innodb_data_home_dir和innodb_data_file_path启动选项控制。针对差别场景,具体举比方下:


[*] 参数innodb_data_home_dir未配置,只配置参数innodb_data_file_path:innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend。体系表空间包罗ibdata1和ibdata2两个数据文件,二者均位于datadir目次下。
[*] 参数innodb_data_home_dir和innodb_data_file_path均进行了配置:
innodb_data_home_dir = /data;innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend。体系表空间包罗ibdata1和ibdata2两个数据文件,二者均位于/data目次下。
[*] 参数innodb_data_home_dir位置为空串,只配置参数innodb_data_file_path:
innodb_data_home_dir =;innodb_data_file_path=/d1/ibdata1:1024M;/d2/ibdata2:1024M:autoextend。体系表空间包罗ibdata1和ibdata2两个数据文件,ibdata1位于/d1目次下,ibdata2位于/d2目次下。
doublewrite buffer

undo logs

表文件表空间或独立的表空间(File-Per-Table Tablespaces)

表文件表空间是一个单表表空间,该表创建于本身的数据文件中,而非创建于体系表空间中。**当innodb_file_per_table选项开启时,表将被创建于表文件表空间中。否则,innodb将被创建于体系表空间中。**每个表文件表空间由一个.ibd数据文件代表,该文件默认被创建于相应数据库目次中。表文件表空间支持动态(DYNAMIC)和压缩(commpressed)行格式。
通用表空间(General Tablespaces)

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目次外的其他表空间,其可以容纳多张表,且其支持所有的行格式。通过create table tab_name … tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name语法将其添加与通用表空间内。
undo表空间(undo tablespace)

undo表空间由一个或多个包罗undo日记的文件组成。innodb_undo_tablespace配置选项控制undo表空间的数目。undo表空间创建于innodb_undo_directory配置选项确定的位置,该选项典范被用于将undo日记放于差别的存储装备上。如果该选项没有确定任何路径,undo表空间则被默认创建于mysql数据目次(datadir)下。
临时表空间(Temporary Tablespace)

用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。innodb_temp_data_file选项确定临时表空间数据文件的相对路径、名字、大小和属性等。如果该选项未确定任何值,默认环境下,体系将在innodb_data_home_dir确定的目次下创建一个叫ibtmp1的主动扩展的数据文件,该文件将稍大于12m。
mysql服务器正常关闭或异常终止初始化时,临时表空间将被移除,而且,mysql服务器每次启动时会被重新创建。当临时表空间被创建时,其被赋予一个动态产生的空间ID(space ID)。如果不能创建临时表空间,
mysql服务器启动将被拒绝。mysql服务器异常终止的环境下,临时表空间将不被移除。这种环境下,DBA能手工移除临时表空间或重启mysql服务器,重启服务器过程中,将主动移除和重新创建临时表空间。
临时表空间并不能存储于裸装备。
这里既然说到了innodb_data_home_dir,那么,就说说这个选项,该选项确定innodb体系表空间数据文件目次路径的共同部门。innodb_file_per_table开启时,该选项设置并不影响表文件表空间的位置。该选项默认值为mysql数据目次。如果你将该选项设置为空串,那么,你可以为innodb_data_file_path设置一个绝对路径值。此外,当为innodb_data_home_dir指定一个值时,需要在尾部添加一个斜杠。
存储架构的演变

https://i-blog.csdnimg.cn/direct/3aa1a3d80b6145218f0556beb60ae0c6.png
将undo logs与undo tablespace合并;doublewrite buffer双写缓存抽离出去;
后台线程

https://i-blog.csdnimg.cn/direct/2dd4e5ad881a4f4a93c92c55f1cd198c.png
IO Thread

在InnoDB中利用了大量的AIO(Async IO)来操作读写,这样可以极大进步数据库的性能。在InnoDB1.0版本之前共有4个IO Thread,分别是write、read、insert buffer、log thread,厥后版本将write、read分别增大到了4个。这样一共就有10个了。


[*]read thread: 负责读取操作,将数据从磁盘加载到缓存page页。
[*]write thread: 负责写操作,将缓存脏页革新到磁盘。
[*]log thread:负责将日记缓冲区革新到磁盘。
[*]insert buffer thread: 将写缓冲的内容革新到磁盘内里
Purge Thread

事务提交之后,其利用的undo日记将不在需要,因此需要Purge Thread接纳已经分配的undo页。
Page Cleaner Thread

作用是将脏数据革新到磁盘,脏数据刷盘后相应redo log也就可以覆盖,既可以同步数据,又能到达redo log 循环利用的目的。会调用write thread线程处置惩罚。
Master Thread

是InnoDB的主线程,负责调度其他个线程,优先级最高。作用是将缓存池中的数据异步革新到磁盘,保证数据的一致性。
内部有两个处置惩罚,分别是每隔1s和10s处置惩罚


[*]1s处置惩罚的操作

[*]革新日记缓冲区,刷到磁盘
[*]合并写缓冲区数据,根据IO读写压力来以为是否操作。
[*]革新脏页数据刷到磁盘,根据脏页比例到达75%才操作。show variables like '%innodb_max_dirty_pages_pct%';//可配置比列show variables like '%innodb_io_capacity%'//每次革新多少页

[*]10s的操作

[*]脏页革新到磁盘-无条件
[*]合并写缓冲区-无条件
[*]革新日记缓冲区
[*]删除无用的undo页。

InnoDB文件存储结构

https://i-blog.csdnimg.cn/direct/a971da7740e5417490d3a7027e0c9c9b.png
InnoDB数据文件存储,分为ibd数据文件–>Segment(段)–>Extent(区)–>Page(页)–>Row(行)


[*] Tablesapce:表空间,用于存储多个ibd数据文件,存储表纪录和索引。一个文件包罗多个段
[*] Segment:用于管理多个Extent,分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)。一个表至少有两个段,一个管理数据,一个管理索引。每多建一个索引,会多两个segment(索引是树结构,创建一个索引肯定有叶子节点和非叶子节点)。
[*] Extent:一个区是固定包罗连续的64个页。大小为1M。当表空间不足,需要分配新的也资源,不是一页一页分配,直接分配一个区。
[*] Page:存储多个Row。大小为16K。包罗很多种页类型,常见的数据页,undo页,体系页,事务数据页,大的BLOB对象页。

[*]Page页是文件最根本的单位,无论何种类型的page,都是有page header,page trailer和page body组成

https://i-blog.csdnimg.cn/direct/2a34b47f3b6a45679576d5894478ef4d.png


[*]Row:包罗了字段值,事务ID,滚动指针(Roll pointer),字段指针(Field pointer)。
体系文件层

该层负责将数据库的数据和日记存储在文件体系之上,并完成与存储引擎的交互,是文件的物理存储层。重要包罗日记文件,数据文件,配置文件,pid文件,socket文件等
日记文件



[*]错误日记(Erroe log):默认是开启的。show variables like '%log_error%';
[*]通用查询日记(General query log):纪录一样平常查询语句,show variables like '%general%';
[*]二进制文件(binary log):纪录对Mysql数据库执行的更改操作,而且纪录语句的发生时间,执行时长;但是不纪录select、show等不修改数据库的sql,重要用具数据库的规复和主从复制。show variables like '%log_bin%';//是否开启 show variables like '%binlog%';//参数参看 show binary logs;//查看日记文件
[*]慢查询日记(slow query log):纪录所有执行超时的查询sql,默认10秒show variables like '%show_query%';//是否开启 show variables like '%long_query_time%';//时长
配置文件

Mysql所有的配置信息文件,如my.cnf、my.imi。
数据文件



[*]db.opt文件:纪录这个库默认利用的字符聚集校验规则
[*]frm文件:存储与表相关的元数据信息,包括表结构的定义信息等,每张表都会有一个frm文件
[*]MYD文件:MyISAM存储引擎专用,存放MyISAM表的数据,每张表都会有一个.MYD文件。
[*]MYI文件:MyISAM存储引擎专用,存放MyISAM表的索引相关信息。每个表都有一个对应的文件
[*]ibd文件和IBDATA文件:存放InnoDB的数据文件(包罗索引).InnoDB存储引擎的两种表空间,独享表空间和共享表空间。独享表空间利用.ibd文件来存放数据,且每张表对应一个.ibd文件。共享表空间利用的是.ibdata文件,所有表共同利用一个和多个,可配置化
[*]ibdata1文件:体系表空间和数据文件,存储表元数据,Undo日记等。
[*]ib_logfile0、ib_logfile1:Redo log日记文件。
pid文件

pid文件是mysqlId应用步伐在Unix/Linux环境下的一个进程文件,存放着本身的进程id
socket文件

socket文件也是Unix/Linux环境下才有的,用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接利用Unix socket来连接Mysql
比较重要的日记体系

Undo Log(回滚日记)

数据库事务开始之前,会将要修改的纪录放到undo 日记内里,当事务回滚时大概数据库崩溃时,可以利用Undo 日记,打消未提交事务对数据库产生的影响。
Undo Log在事务开始前产生;事务提交时并不会立刻删除Undo Log,InnoDb会将该事务对应的undo Log放入到删除列表中,背面会通事后台线程purge thread进行接纳处置惩罚。
Undo Log是一种逻辑日记,纪录的是变化过程。好比一条 INSERT 语句,对应一条DELETE 的 undo log ,对于每个 UPDATE 语句,对应一条相反的 UPDATE 的 undo log ,这样在发生错误时,就能回滚到事务之前的数据状态。
Undo Log采用段的方式管理和纪录。在InnoDB数据文件中包罗一种rollback segment回滚段,内部包罗1024个undo log segment
作用及原理

实现事务的原子性

Undo log 是为了实现事务的原子性而出现的产物。事务处置惩罚过程中,如果出现了错误大概用户执行了RollBack语句,Mysql可以利用Undo Log中的备份将数据规复到事务开始之前的状态。
实现多版本控制MVCC

Undo Log在Mysql InnoDB存储引擎中用来实现多版本并发控制。事务未提交之前,Undo lOG保存了未提交的版本数据,Undo Log中的数据可以作为快照供其他并发事务进行快照读。
事务A手动开启事务,执行更新操作,首先会把更新命中的数据备份到 Undo lOG中,如果事务回滚,则可以从Undo lOG中找回
事务B手动开启事务,执行查询操作,会读取Undo日记数据返回,进行快照读。
Redo Log(重做日记)



[*]redo log 是 InnoDB 引擎特有的日记,属于存储引擎层面的。有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的纪录都不会丢失,这个能力称为crash-safe。redo log 是物理日记,纪录的是“在某个数据页上做了什么修改”
[*]redo log 随着事务操作的执行,就会天生redo log,在事务提交时会产生redo log写入log buffer,并不是随着事务的提交就立刻写入磁盘文件。而是等事务操作的脏页写入磁盘之后,Redo log的任务完成了,这时间占用的空间就可以重用(被覆盖写)。
Redo Log的机制

工作原理

Redo Log是为了实现事务的持久性而出现的产物。防止在发生故障的时间点,尚有脏页未写入表的IDB文件中,在重启mysql服务的时间,根据Redo Log进行重做,从而到达事务的未入磁盘数据进行持久化这一特性
https://i-blog.csdnimg.cn/direct/e0f8ebcdc3404f79b87b75cfdae9b99f.png
在更新数据的时间,会将原始数据写入到Undo Buffer内里,将修改后的数据写入到Redo Buffer中。在更新的时间出现问题,可以根据Redo log来进行重做,将表里的数据更新成最新的。
redo log的写入机制

https://i-blog.csdnimg.cn/direct/73a028f78192427186e6673a3bb81d3e.png
如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条纪录,然后再更新,整个过程 IO 成本、查找成本都很高。
Mysql利用WAL 技术: Write-Ahead Logging先写日记(redo log buffer),再写磁盘(redo logfile)。
当一条纪录需要更新的时间,会先把纪录写到redo log内里,并更新内存,这个时间更新就算完成了。InnoDB 引擎会在适当的时间,将这个操作纪录更新到磁盘内里。
InnoDB 的 redo log 是固定大小的。好比可以配置为一组 4 个文件,每个文件的大小是 1GB,那么统共就可以纪录 4GB 的操作。重新开始写,写到末尾就又回到开头循环写。
write pos 是当前纪录的位置,一边写一边后移,写到第 3 号文件末尾后就回溯 0 号文件开头。
checkpoint 是当前要擦除的位置,也是往后推移而且循环的,擦除纪录前要把纪录更新到数据文件。
write pos 和 checkpoint 之间还空着的部门,可以用来纪录新的操作。如果 write pos 追上 checkpoint,表现日记文件满了,这时间不能再执行新的更新,得停下来先擦掉一些纪录,把 checkpoint 推进一下。
每个InnoDB存储引擎至少有一个重做日记文件组,每个文件组至少有两个重做日记文件,默认为ib_logfile0,ib_logfile1
Redo Buffer持久化到Redo Log的策略,可通过innodb_flush_log_at_trx_commit 配置https://i-blog.csdnimg.cn/direct/5cd1458353e34f2c8e70031b6bc877c3.png
MTR=小事务=一个事务单位。


[*]0:每秒提交Redo Buffer -> OS Cache -> flush cache to disk ,可能丢失一秒内的数据,有后台Master线程每隔一秒操作一次
[*]1(默认值):每次事务提交Redo Buffer -> OS Cache -> flush cache to disk ,最安全,性能最差的凡是
[*]2:每次事务提交Redo Buffer -> OS Cache,然后由后台线程每隔1秒执行OS Cache -> flush cache to disk的操作
建议配置成2,Mysql挂了也是没有数据丧失的,只有整个服务器挂了才会丧失1秒的事务提交数据。
重启重启innodb 时,首先会检查磁盘中数据页的 LSN(日记序列号) ,如果数据页的LSN 小于日记中的 LSN ,则会从 checkpoint 开始规复。还有一种环境,在宕机前正处于checkpoint 的刷盘过程,且数据页的刷盘进度凌驾了日记页的刷盘进度,此时会出现数据页中纪录的 LSN 大于日记中的 LSN,这时超出日记进度的部门将不会重做,因为这本身就表现已经做过的事情,无需再重做。
Bin Log(二进制日记=归档日记)

#binlog的配置
log-bin=/xx/xx/xx
#binlog的格式 statement、row、mixed
binlog-format=ROW
#表示没1次执行写入就与磁盘同步,影响性能,为0时,表示事务提交的时候不做刷盘操作,有系统决定
sync-binlog=1
#服务器唯一iD 默认是1
server-id=1
#只保留7天的二进制日志
expire-logs-days=6
BingLog纪录模式

最开始是没有InnoDB存储引擎的。Mysql自带的存储引擎MyISAM 没有 crash-safe 的能力。binlog 是 MySQL 的 Server 层实现的,所有引擎都可以利用。
binlog是纪录所有数据库表结构变动以及数据修改的二进制日记,不会纪录select和show这类操作。binlog是以事件形式纪录,还包罗了语句消耗的时间。
binlog文件名默认为“主机名-binlog-序列号”格式,比方“oak-binlog-000001”,也可以在配置文件执行名称。
文件纪录模式有三种


[*]STATMENT:每一条被修改的数据都会纪录到master的binglog中,slave在复制的时间sql进程会剖析成和原来master端执行过的雷同的sql再次执行一遍,简称语句复制。

[*]优点:日记量少,减少io。
[*]缺点:如果sql利用了可变变量,类似当前时间等,这样到从库内里就会变的不一样了。

[*]ROW:日记中会纪录每一行数据被修改的环境,然后在slave端对雷同的数据进行修改。

[*]优点:清楚的纪录每一行数据的细节,能完全实现主从数据同步和数据的规复
[*]缺点:批量的操作,会产生大量的日记,类似加个字段等。

[*]MIXED:以上两种模式混合利用,一样平常会利用STATMENT模式保存binlog,对于STATMENT模式无法复制的操作利用ROW模式保存binlog,Mysql会根据执行的sql语句选择写入模式。
利用场景


[*]主从复制:在主库中开启binlog,这样主库就可以把binlog传递给从库,从库拿到binlog后实现数据规复到达和主从数据的一致性。
[*]数据规复:通过mysqlbinlog工具来规复数据
bin log文件结结构

bin log 文件纪录的是对数据库的各种操作,用来表现各种操作的数据结构是log event。差别的修改操尴尬刁难应差别的logevent。好比常用的log event:Query event 、row event 、xid event等。binlog文件的内容就是各种log event的聚集
binlog文件中的log event结构如下:
https://i-blog.csdnimg.cn/direct/a0bc951807bf46b3b6e30dcbca924f07.png
日记纪录机制

写入机制



[*]根据纪录模式触发event事件天生log event(事件触发执行机制)
[*]将事物执行过程中产生的log evevt写入缓冲区,每个事务线程都有一个缓冲区
[*]事务在提交阶段会将产生的log evet写入外部的binlog文件中。

[*]log event保存在一个binlog_cache_mngr数据结构中。在该结构中有两个缓冲区,一个是stme_cache用于存放不支持事务的信息;另一个是trx_cache,用于存放支持事务的信息。

[*]binlog是纪录的sql语句的原始逻辑且是追加写,不会覆盖前面的binlog文件日记文件大小根据max_binlog_size控制

[*]差别的事务是按照串行的方式写入到binlog文件中,所以一个事务包罗的log event信息在binlog文件中是连续的,中间不会插入其他事务的log evetn。binlog是引擎插件的上层功能,事务提交第一个就会调用binlog功能接口,然后再调用其他存储引擎的功能接口。因此是先写binlog,然后执行innodb的redolog/undo 脏页革新操作。

各种日记的比较区别

二阶段提交

执行器和 InnoDB 引擎在执行update 语句时的内部流程https://i-blog.csdnimg.cn/direct/da48d0e2c0694ad39bd78c566ba37ade.png

[*]执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果
ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁
盘读入内存,然后再返回。
[*]执行器拿到引擎给的行数据,把这个值加上 1,好比原来是 N,现在就是 N+1,得到
新的一行数据,再调用引擎接口写入这行新数据。
[*]引擎将这行新数据更新到内存中,同时将这个更新操作纪录到 redo log 内里,此时
redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。
[*]执行器天生这个操作的 binlog,并把 binlog 写入磁盘。
[*]执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状
态,更新完成。
在出现异常的环境下binlog 和 redo log先写和后写的区别假设当前存在一个ID=2,字段c=0,将c修改为1


[*] 先写 redo log 后写 binlog:https://i-blog.csdnimg.cn/direct/3d4accbb8dee46f193c6fca72bc8c79a.png
[*] 写完redo log的时间出现异常,此时还未写binlog。体系即使崩溃,仍然可以把数据规复,所以规复这一行,字段c的值是1.但是由于binlog未写入数据,如果需要用binlog来规复数据的话,那这个临时库就会少了一次更新。那此时字段c的值就是0,与原库值差别.
[*] 先写 binlog 后写 redo log:如果写完binlog之后出现异常,未写入redo log,崩溃规复之后这个事务是无效的。所以字段c的值应该是0。但是binlog中已经存在语句,所以是用binlog规复的时间就会多出一条事务出来,字段c的值就变成1.与原库值差别
redo log和undo log

假设有A、B两个数据,值分别为1、2,开启事务分别对其进行修改A → 3,B → 4,在提交,过程如下:


[*]事务开始
[*]纪录A=3到redo log
[*]修改A=3
[*]纪录A=1到undo log
[*]纪录B=4到redo log
[*]修改B=4
[*]纪录B=2到undo log
[*]将redo log写入磁盘
[*]事务提交
一个事务在执行到一半的时间实例崩溃了,在规复的时间先规复redo,再根据redo宕机前没有提交的事务来决定是否回滚undo。
redo log 和binlog的区别



[*]binlog是服务层的功能,redo log是innodb提供的。
[*]redo log是物理日记,纪录数据页更新内容,binlog是逻辑日记,纪录更新过程
[*]redo log日记是循环写,日记空间大小固定,binlog是追加写。
[*]redo log作为服务器异常宕机后事务数据主动规复利用,binlog作用于主从复制和数据规复。binlog没有主动crash-safe功能。
https://i-blog.csdnimg.cn/direct/63317127fa6c44cab04bf8db8764a9b2.gif

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页: [1]
查看完整版本: mysql基础架构