qidao123.com ToB IT社区-企服评测·应用市场

标题: mysql基础架构 [打印本页]
作者: 嚴華    时间: 2024-10-23 13:40
标题: mysql基础架构
Mysql体系架构


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存储结构


内存结构

Buffer Pool:

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

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

针对上述三种page类型，通过三种链表结构来维护和管理

改进型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

磁盘结构

体系表空间（System Tablespace）

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

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指定一个值时，需要在尾部添加一个斜杠。
存储架构的演变


将undo logs与undo tablespace合并;doublewrite buffer双写缓存抽离出去;
后台线程


IO Thread

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

Purge Thread

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

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

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

InnoDB文件存储结构


InnoDB数据文件存储，分为ibd数据文件–>Segment(段)–>Extent(区)–>age(页)–>Row(行)



体系文件层

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


配置文件

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


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的机制

工作原理

Redo Log是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的IDB文件中，在重启mysql服务的时间，根据Redo Log进行重做，从而到达事务的未入磁盘数据进行持久化这一特性

在更新数据的时间，会将原始数据写入到Undo Buffer内里，将修改后的数据写入到Redo Buffer中。在更新的时间出现问题，可以根据Redo log来进行重做，将表里的数据更新成最新的。
redo log的写入机制


如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条纪录，然后再更新，整个过程 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 配置

MTR=小事务=一个事务单位。

建议配置成2，Mysql挂了也是没有数据丧失的，只有整个服务器挂了才会丧失1秒的事务提交数据。
重启重启innodb 时，首先会检查磁盘中数据页的 LSN(日记序列号) ，如果数据页的LSN 小于日记中的 LSN ，则会从 checkpoint 开始规复。还有一种环境，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度凌驾了日记页的刷盘进度，此时会出现数据页中纪录的 LSN 大于日记中的 LSN，这时超出日记进度的部门将不会重做，因为这本身就表现已经做过的事情，无需再重做。
Bin Log(二进制日记=归档日记)

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

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

利用场景

bin log文件结结构

bin log 文件纪录的是对数据库的各种操作，用来表现各种操作的数据结构是log event。差别的修改操尴尬刁难应差别的logevent。好比常用的log eventuery event 、row event 、xid event等。binlog文件的内容就是各种log event的聚集
binlog文件中的log event结构如下:

日记纪录机制

写入机制


各种日记的比较区别

二阶段提交

执行器和 InnoDB 引擎在执行update 语句时的内部流程

在出现异常的环境下binlog 和 redo log先写和后写的区别假设当前存在一个ID=2,字段c=0，将c修改为1

redo log和undo log

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

一个事务在执行到一半的时间实例崩溃了，在规复的时间先规复redo，再根据redo宕机前没有提交的事务来决定是否回滚undo。
redo log 和binlog的区别




免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 qidao123.com ToB IT社区-企服评测·应用市场 (https://dis.qidao123.com/) Powered by Discuz! X3.5