网站创建域名,电子政务建设网站图片,网站建设优化学习,wordpress设置文章登录可见Mysql逻辑架构
最上层是服务并不是Mysql所独有的#xff0c;大多数基于网络的客户端/服务器的工具或者服务都有类似的架构#xff0c;比如连接处理#xff0c;授权认证#xff0c;安全等。
第二层是Mysql比较有意思的部分。大多数Mysql的核心服务都在这一层#xff0c;…Mysql逻辑架构
最上层是服务并不是Mysql所独有的大多数基于网络的客户端/服务器的工具或者服务都有类似的架构比如连接处理授权认证安全等。
第二层是Mysql比较有意思的部分。大多数Mysql的核心服务都在这一层包括查询解析分析优化缓存以及所有的内置函数所有跨存储引擎的功能都在这一层实现存储过程视图触发器等。
第三层包括了存储引擎存储引擎负责Mysql中数据的存储和提取。服务器通过API与存储引擎进行通信这些接口屏蔽了不同存储引擎之间的差异。存储引擎不会去解析sql不同存储引擎之间也不会相互通信只是简单地响应上层服务器的请求。
连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程这个连接的查询只会在这个单独的线程中执行该线程只会轮流在某个CPU核心或CPU中运行。服务器只会负责缓存线程因此不需要为每一个新建的连接创建或者销毁线程。
当客户端连接到Mysql服务器时服务器需要对其进行认证如果使用了安全套接字SSL的方式连接还可以使用X.509证书认证。一旦客户端连接成功服务器会继续验证该客户端1是否具有执行特定查询的权限。
优化与执行
Mysql会解析查询并创建内部数据结构然后对其进行各种优化包括重写查询决定表的读取顺序以及选择合适的索引等。用户可以通过关键字提示优化器也可以查询优化器过程explain。
优化器并不关心表使用的什么存储引擎但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息以及表数据的统计信息等。
并发控制
无论何时只要有多个查询需要再同一时刻修改数据都会产生并发控制的问题Mysql会在两个层面进行并发控制服务器层与存储引擎层。
读写锁
读锁是共享的或者说是相互不阻塞的多个客户在同一时刻可以同时读取同一个资源而互不干扰。写锁则是排他的也就是说一个写锁会阻塞其他的写锁和读锁这是处于安全策略的考虑只有这样才能确保在给定的时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源。
锁粒度
一种提高共享资源并发性的方式就是让锁对象更有选择性。尽量只锁定需要修改的部分数据而不是所有的资源。更理想的方式是只对会修改的数据片进行精确的锁定。任何时候在给定的资源上锁定的数据量越少则系统的并发程度越高只要相互之间不发生冲突即可。
问题是加锁也需要消耗资源锁的各种操作包括获得锁检查锁是否已经解除释放锁等都会增加系统的开销。如果系统花费大量的时候来管理锁而不是存储数据那么系统的性能可能会受到影响。
所谓锁策略就是在锁的开销和数据的安全性之间需求平衡这种平衡当然也会影响到性能。一般都是在表上加行级锁并以各种复杂的方式来实现以便在锁较多的情况下尽可能低提供更好的性能。
Mysql提供了多种选择每种Mysql存储引擎都可以实现自己的锁策略和锁粒度。下面介绍两种最重要的锁策略
表锁
表锁是Mysql中最基本的锁策略并且是开销最小的策略。他会锁整张表一个用户对表进行写操作前需要先获取写锁这会阻塞其他用户对该表的所有读写操作。只有没有写锁时其他读取用户才能获取读锁读锁之间是不相互阻塞的。
在特定场景中表锁也可能有良好的性能例如 READ LOCAL 表锁支持某些类型的并发写操作。另外写锁比读锁有更高的优先级因此一个写锁请求可能会被插入到读锁队列的前面。
行级锁
行级锁可以最大程度地支持并发处理同时也带来了最大的锁开销。Mysql在InnoDB中实现了行级锁。行级锁只在存储引擎层实现而Mysql服务器层没有实现服务器层完全不了解存储引擎中的锁实现。
事务
事务就是一组原子性的sql查询或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句那么就执行该组查询。如果其中有任何一个语句因为崩溃或其他原因无法执行那么所有的语句都不会执行。也就是说事务内的语句要么全部执行成功要么全部执行失败。
我们可以先了解一下事务的ACID概念。ACID表示原子性一致性隔离性和持久性。
原子性一个事务必须被视为一个不可分割的最小工作单元整个事务中所有操作要么全部提交成功要么全部失败回滚对于一个事务来说不可能只执行其中的一部分操作这就是原子性操作。
一致性数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性通常来说一个事务所做的修改在最终提交之前对其他事务是不可见的。
持久性一旦事务提交则其所做的修改就会永久保存到数据库中
隔离级别
在SQL标准中定义了四种隔离级别每一种级别都规定了一个事务中所做的修改哪些在事务内和事务间是可见的哪些是不可见的。较低级别的隔离通常可以执行更高的并发系统的开销也更低。
READ UNCOMMITTED(未提交读)事务中的修改即使没有提交对其他事务也是可见的。事务可以读取未提交的数据这也被称为脏读。
READ COMMITTED(已提交读)大多数数据库系统的默认隔离级别都是READ COMMITTED。该级别满足隔离级别的简单定义一个事务开始时只能看见已经提交的事务所做的修改。换句话说就是一个事务从开始到提交之前所做的任何修改对其他事务都不可见。也叫不可重复读。
REPEATABLE READ(可重复读),MYSQL默认隔离级别。该级别解决了脏读的问题。保证了在同一事务中多次读取同样记录的结果是一致的。但是理论上可重复读无法解决幻读的问题。所谓幻读就是说当某个事务在读取某个范围内的记录时另一个事务又在该范围内插入了新的记录当之前的事务再次读取该范围内的记录时会产生幻行。InnoDB等存储引擎通过多版本并发控制解决了幻读的问题。
SERIALIZABLE(可串行化)最高隔离级别通过强制事务串行执行避免了幻读的问题。简单来说就是给每行数据加上锁所以可能会导致大量的超时和锁争用的问题。实际应用中很少用这个隔离级别。
隔离级别脏读可能性不可重复读可能性幻读可能性加锁读READ UNCOMMITTED√√√×READ COMMITTED×√√×REPEATABLE READ××√×SERIALIZABLE×××√
死锁
死锁是指两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源从而导致恶性循环的现象。当多个事务视图以不同的顺序锁定资源时就可能会产生死锁。多个事务同时锁定同一个资源时也会产生死锁。
InnoDB目前解决死锁的方法是将持有最少行级排他锁的事务进行回滚。
事务日志
事务日志可以帮助提高事务的效率使用事务日志存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改行为记录到持久化的事务日志中而不用每次都将修改的数据本身持久化到磁盘。事务日志使用追加的方式因此写日志的操作时磁盘上一小块区域内的顺序IO而不像随机IO需要再磁盘的多个地方移动磁头。事务日志持久化之后内存中被修改的数据在后台可以慢慢的回刷到磁盘。
如果数据的修改已经记录到事务日志并持久化但数据本身还没有写回磁盘此时系统崩溃存储引擎在重启时能够自动修复这部分修改的数据。
Mysql中的事务
自动提交
Mysql默认采用自动提交模式也就是说如果不是显式地开始一个事务则每个查询都被当做一个事务执行提交操作。
在事务中混合使用存储引擎
Mysql服务器层不管理事务事务是由下层的存储引擎实现的。所以在同一个事务中使用多个存储引擎是不可靠的。如果在事务中混合使用了事务型和非事务型的表再正常提交的情况下不会有什么问题但是如果需要回滚的话非事务型的表上的修改就无法撤销从而导致数据不一致。
隐式和显式锁定
InnoDB采用的是两阶段锁定协议。在事务执行过程中随时都可以执行锁定锁只有在执行COMMIT或者ROLLBACK的时候才会释放并且所有的锁是在同一时刻释放。InnoDB会根据隔离级别在需要的时候自动加锁。
Mysql也可以通过特地的语句进行显式的锁定。
SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR UPDATE;多版本并发控制
Mysql的大多数事务型存储引擎实现的都不是简单的行级锁基于提升并发性能的考虑一般都同时实现了多版本并发控制。MVCC是行级锁的一个变种他在很多情况下避免了加锁操作因此开销更低。虽然实现机制有所不同但大都实现了非阻塞的读操作写操作也只锁定必要的行。
MVCC的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间每个事务看到的数据都是一致的。根据事务的开始时间不同每个事务对同一张表同一时刻看到的数据可能是不一样的。
不同的存储引擎MVCC的实现是不同的典型有乐观并发控制和悲观并发控制。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两列一个保存了行的创建时间一个保存行的过期时间。当然存储的并不是实际的时间值而是系统版本号。每开始一个新的事务系统版本号都会自动递增。事务开始时刻的系统版本号作为事务的版本号用来和查询到的每行记录的版本号进行比较。下面我们来看看在 REAPEATABLE READ 隔离级别下。MVCC是如何操作的。
SELECTInnoDB会根据以下两个条件检查每行记录
InnoDB值查找版本早于当前事务版本的数据行这样就可以保证事务读取的行要么是事务开始前已经存在得要么是事务自身修改过的。
行的删除版本要么未定义要么大于当前事务版本号。确保事务读取到的行在事务开始之前未被删除。
只有符合上面两个条件才能返回查询结果。
INSERTInnoDB为新插入的行保存当前系统版本号作为行版本号
DELETEInnoDB为删除的每一行保存当前系统版本号作为行删除标识
UPDATEInnoDB为新插入的行保存当前系统版本号作为行版本号同时保存当前系统版本号到原来的行作为删除标识
保存这两个系统版本号使得大部分读操作都可以不用加锁这样设计使得读取操作很简单性能很好并且也能保证只会读取到符合标准的行。不足之处就是每行记录都需要额外的存储空间。
MVCC只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。因为 READ UNCOMMITTED 总是读取最新的数据行而不是符合当前事务版本的数据行而 SERIALIZABLE 则会对所有读取的行都加锁。
Mysql的存储引擎
在文件系统中Mysql将每个数据库保存为数据目录下的一个子目录。创建表时Mysql会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。可以使用SHOW TABLE STATUS 命令显式表的相关信息。 mysql show table status like user \G
*************************** 1. row ***************************Name: userEngine: InnoDBVersion: 10Row_format: DynamicRows: 9Avg_row_length: 1820Data_length: 16384
Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: 14Create_time: 2023-09-24 17:34:24Update_time: NULLCheck_time: NULLCollation: utf8mb4_general_ciChecksum: NULLCreate_options:Comment:
1 row in set (0.00 sec)下面简单介绍一下每一行的含义。
Name表名
Engine存储引擎再老版本中这列叫Type
Row_format行的格式对于InnoDB的表常见的行格式类型有Compact、Redundant、Dynamic和Compressed。
Compact行格式
Compact行格式是MySQL5.0中引入的其目标是为了更高效的存储数据记录。其存储结构示意图如下
从图中我们可以看出来一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两部分。
记录的额外信息
这部分信息是为了描述这条记录而不是额外添加的一些信息这些额外信息分为三部分分别是变长字段长度列表、NULL值列表和记录头信息
变长字段的长度列表
MySQL支持一些变长的数据类型比如VARCHAR(M)、 VARBINARY(M)、 TEXT类型、BLOB类型这些数据类型修饰列称为变长字段。变长字段中存储多少字节的数据不是固定的所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。也就是变长字段时占用了两部分空间来存储的1真实的数据内容2占用的字节数。
在Compact行格式中把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位从而形成一个变长字段长度列表。
需要注意的是这里面存储的变长长度和字段顺序是相反的。比如两个varchar字段在表结构的顺序是a(10), b(15)那么在变长字段长度列表中存储的长度顺序就是15,10是反过来的。变长字段的长度列表不是一定存在的如果表中没有变长类型的字段或者该记录中所有的变长字段值均为NULL。
NULL值列
表中的某些列可能存储NULL值如果把这些NULL值都放到记录的真实数据中存储则显的比较浪费空间所以Compact行格式把这些值为NULL的列统一管理起来存储到NULL值列表中。
如果表中有字段允许为NULLInnoDB就会开辟一块空间来标识每个字段实际存储的数据是不是NULL如果表中的字段都不允许为NULL则NULL值列表也就不存在了。
每个允许存储NULL的列对应一个二进制位二进制位按照列的逆序排列二进制位表示的意义如下
二进制位的值为1时代表该列的值为NULL。
二进制位的值为0时代表该列的值不为NULL。
记录头信息
记录头信息用于描述该记录的它是由固定的5个字节组成即40个二进制位不同的位代表不同的意思
这些记录头信息中各个属性如下
名称大小(bit)描述预留位11没有使用预留位21没有使用delete_mask1标记该记录是否被删除。0 - 没有被删除1 - 记录被删除那么被删除的记录为什么还在页中存储呢实际上记录并没有从磁盘消失。这些被删除的记录之所以不立即从磁盘上移除是因为移除它们之后其他的记录在磁盘上需要重新排列导致性能消耗。所以只是打一个标记而已所有被删除掉的记录都会组成一个所谓的垃圾链表在这个链表中的记录占用的空间称之为可重用空间之后如果有新纪录插入到表中的话可能把这些被删除的记录占用的存储空间覆盖掉。min_rec_mask1B树的每层非叶子节点中的最小记录都会添加该标记值为1。 0 - 表示不是B树的非叶子节点中的最小记录n_owned4表示当前记录拥有的记录数 。页目录中每个组最后一条记录的头信息中会存储该组一共有多少条记录作为n_owned字段heap_no13表示当前记录在本页中的位置信息heap_no是没有值为0和1的。这是因为MySQL会自动给每个页里加两个记录这两个记录并不是我们自己插入的所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1也就是说它们的位置最靠前。record_type3表示当前记录的类型0 - 普通记录 1- B树非叶子节点记录2 - 最小记录3 - 最大记录next_record16表示下一条记录的相对位置也就是从当前记录的真实数据到下一条记录的真实数据的地址偏移量。
记录的数据内容
记录的真实数据除了自定义的列的数据以外MySQL还会为每条记录默认的添加一些列也称为隐藏列具体的列如下
列名是否必须占用空间描述DB_ROW_ID否6字节行ID唯一标识一条记录DB_TRX_ID是6字节事务IDDB_ROLL_PTR是7字节回滚指针
当用户未指定数据表的主键时MySQL会选择非NULL的Unique列作为主键而如果非NULL的Unique列也没有这个时候MySQL就会向数据表添加DB_ROW_ID字段用来作为主键。记录的数据内容不包括字段值为NULL的数据内容。
Redundant行格式
redundant行格式是MySQL5.0之前的一种旧的格式其结构与compact行格式大体还是比较相似的。
Dynamic 和 Compressed行格式
在MySQL8.0中默认行格式就是Dynamic。Dynamic、Compressed行格式和Compact行格式挺像只不过在处理行溢出数据时有分歧
Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。在数据页中只存放20个字节的指针溢出页的地址实际的数据都存放在Off Page溢出页中。
Compact 和 Redundant两种格式会在记录的真实数据处存储一部分数据存放768个前缀字节。
Compressed行记录格式的另一个功能就是存储在其中的行数据会以zlib的算法进行压缩因此对于blob、text、varchar这类大长度类型的数据能够进行非常有效的存储。
Rows表中的行数InnoDB引擎该值是估计值MyISAM引擎是精确值
Avg_row_length平均每行包含的字节数
Data_length表数据的大小
Max_data_length表数据的最大容量该值和存储引擎有关
Index_length索引的大小
Data_free对于MyISAM表表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行以及后续可以被INSERT利用的空间
Auto_increment下一个 AUTO_INCREMENT 的值
Create_time表的创建时间
Update_time表数据的最后修改时间
Check_time使用 CHECK TABLE 命令或者 myisamchk 工具最后一次检查表的时间
Collation表的默认字符集和字符列排序规则
Checksum如果启用保存的是整个表的实时校验和
Create_options创建表时指定的其他选项
Comment表注释
InnoDB存储引擎
InnoDB采用MVCC来支持高并发并且实现了四个标准的隔离级别。其默认级别是 REAPEATABLE READ并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅锁定查询涉及的行还会对索引中的间隙进行锁定以防止幻行的插入。
InnoDB表是基于聚簇索引建立的。InnoDB内部做了很多优化包括从磁盘读取数据时采用的可预测性预读能够自动在内存中创建hash索引以加速读操作的自适应哈希索引。
MyISAM存储引擎
在Mysql5.1及之前的版本MyISAM是默认的存储引擎。MyISAM提供了大量的特性包括全文索引、压缩、空间函数等但MyISAM不支持事务和行级锁而且崩溃后无法完全恢复。
存储
MyISAM会将表存储在两个文件中数据文件和索引文件分别以 .myd 和 .myi 为扩展名。Mysql会根据表的定义来决定采用哪种行格式。
特性
加锁与并发
MyISAM对整张表加锁而不是针对行。读取时会对需要读到的所有表加共享锁写入时则对表加排他锁。但是在表有读取操作的时候也可以进行插入操作。
修复
对于MyISAM表Mysql可以手工或者自动执行检查和修复操作。执行表的修复可能会导致一些数据的丢失而且修复操作非常慢。
索引特性
对于MyISAM表即使是BLOB和TEXT等字段也可以基于前500个字符创建索引。MyISAM也支持全文索引
延迟更新索引键
创建MyISAM表的时候如果指定了DELAY_KEY_WRITE 选项在每次修改执行完成时不会立刻将修改的索引数据写入磁盘。而是会写到内存中的键缓冲区只有在清理键缓冲区或者关闭表时才会将对应的索引快写入磁盘。这种方式提高了写入性能但是在服务器崩溃的时候会造成索引损坏需要执行修复操作。
压缩表
如果表在创建并导入数据以后不会再进行修改操作那么这样的表适合采用MyISAM压缩表。
可以使用myisampack对MyISAM表进行压缩。压缩表是不能进行修改的极大的减少磁盘空间占用因此也减少磁盘IO从而提升查询性能。
