长沙网站建设要多少钱,泰安网约车平台有哪些,房子装修设计网,大兴网站建设一条龙在MySQL中的引擎一文中说了#xff0c;我们在几乎所有的情况下其实用的都是InnoDB引擎#xff0c;这里我们就重点再看一下这个引擎#xff0c;包括他的存储结构#xff0c;线程模型和数据文件。我们可以通过show engine innodb status \G;(\G只是表示输出结果纵向表格输出)…在MySQL中的引擎一文中说了我们在几乎所有的情况下其实用的都是InnoDB引擎这里我们就重点再看一下这个引擎包括他的存储结构线程模型和数据文件。我们可以通过show engine innodb status \G;(\G只是表示输出结果纵向表格输出)命令可以查看innodb引擎的的一些基本信息如下图image.pngimage.png上图中可以看到有线程、事务、文件IO缓冲区内存日志等等一些信息我们把这些信息分为存储结构、线程模型和数据文件三类以下分别说明一下。存储结构InnoDB的存储引擎其结构从大的划分来讲分为内存区域和磁盘区域两部分image.pngInnoDB的内存区域内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件这里分别看一下1. Buffer Pool(缓冲池简称BP)BP以Page页为单位默认大小16KBP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存以后使用可以减少磁盘IO操作提升效率。以下我们看一下BP的页管理内存的淘汰算法LRU以及缓冲池的一些配置参数1)Page管理机制Page根据状态可以分为三种类型free page 空闲page未被使用clean page被使用page数据没有被修改过dirty page脏页被使用page数据被修改过页中数据和磁盘的数据产生了不一致针对上述三种page类型InnoDB通过三种链表结构来维护和管理free list 表示空闲缓冲区管理free pageflush list表示需要刷新到磁盘的缓冲区管理dirty page内部page按修改时间排序。脏页数据既存在于flush链表也在LRU链表中但是两种互不影响LRU链表负责管理page的可用性和释放而flush链表负责管理脏页的刷盘操作。lru list表示正在使用的缓冲区管理clean page和dirty page缓冲区以midpoint为基点前面链表称为new列表区存放经常访问的数据占63%后面的链表称为old列表区存放使用较少数据占37%。2)改进型LRU算法维护普通LRU末尾淘汰法新数据从链表头部加入释放空间时从末尾淘汰。改进型LRU链表分为new和old两个部分加入元素时并不是从表头插入而是从中间midpoint位置插入如果数据很快被访问那么page就会向new列表头部移动如果数据没有被访问会逐步向old尾部移动等待淘汰。每当有新的page数据读取到buffer pool时InnoDb引擎会判断是否有空闲页是否足够如果有就将free page从free list列表删除放入到LRU列表中。没有空闲页就会根据LRU算法淘汰LRU链表默认的页将内存空间释放分配给新的页。3)Buffer Pool配置参数show variables like %innodb_page_size%; //查看page页大小show variables like %innodb_old%; //查看lru list中old列表参数show variables like %innodb_buffer%; //查看buffer pool参数建议将innodb_buffer_pool_size设置为总内存大小的60%-80%innodb_buffer_pool_instances可以设置为多个这样可以避免缓存争夺。2. Change Buffer(写缓冲区简称CB)在进行DML操作时如果BP没有相应的Page数据并不会立刻将磁盘加载到缓冲池而是在CB记录缓冲变更等未来数据被读取时再将数据合并恢复到BP中。ChangeBuffer占用BufferPool空间默认占25%最大允许占50%可以通过参数innodb_change_buffer_max_size设置具体调多大要根据读写业务量。当更新一条记录时如果该记录在BufferPool存在就直接在BufferPool修改也就是一次内存操作。如果该记录在BufferPool不存在(没有命中)会直接在ChangeBuffer进行一次内存操作不用再去磁盘查询数据避免一次磁盘IO。当下次查询记录时会先进行磁盘读取然后再从ChangeBuffer中读取信息合并最终载入BufferPool中。这里要注意写缓冲区只适用于非唯一普通索引页。如果在索引设置唯一性在进行修改时InnoDB必须要做唯一性校验因此必须查询磁盘这样就要做一次IO操作。会直接将记录查询到BufferPool中然后在缓冲池修改不会在ChangeBuffer操作。3. Adaptive Hash Index(自适应哈希索引)用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找如果观察到建立哈希索引可以带来速度的提升则建立哈希索引所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。4. Log Buffer(日志缓冲区)日志缓冲区用来保存要写入磁盘上log文件(Redo/Undo)的数据日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘当遇到BLOB或多行更新的大事务操作时增加日志缓冲区可以节省磁盘I/O。LogBuffer主要是用于记录InnoDB引擎日志在DML操作时会产生Redo和Undo日志。LogBuffer空间满了会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大减少磁盘IO频率。innodb_flush_log_at_trx_commit参数控制日志刷新行为默认为10 每隔1秒写日志文件和刷盘操作(写日志文件LogBuffer--OS cache刷盘OScache--磁盘文件)最多丢失1秒数据1事务提交立刻写日志文件和刷盘数据不丢失但是会频繁IO操作2事务提交立刻写日志文件每隔1秒钟进行刷盘操作InnoDB的磁盘区域InnoDB磁盘主要包含TablespacesInnoDB Data DictionaryDoublewrite Buffer、Redo Log和Undo Logs这里我们看一下前面三个区域后面两个我们在MySQL日志的学习中再讨论。1. 表空间(Tablespaces)表空间用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间等多种类型1.1 系统表空间(The System Tablespace)包含InnoDB数据字典Doublewrite BufferChange BufferUndo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数innodb_data_file_path控制默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)。1.2 独立表空间(File-Per-Table Tablespaces)独立表空间是一个单表表空间默认是开启的该表创建于自己的数据文件中而非创建于系统表空间中。当innodb_file_per_table选项开启时表将被创建于表空间中。否则innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表该文件默认被创建于数据库目录中。表空间的表文件支持动态(dynamic)和压缩(commpressed)行格式。1.3通用表空间(General Tablespaces)通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间其可以容纳多张表且其支持所有的行格式。CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd EngineInnoDB; //创建表空间ts1CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间1.3 撤销表空间(Undo Tablespaces)撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区从5.7开始将Undo从System Tablespace分离了出来。InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制默认为0。参数值为0表示使用系统表空间ibdata1;大于0表示使用undo表空间undo_001、undo_002等。1.4 临时表空间(Temporary Tablespaces)CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd EngineInnoDB; //创建表空间ts1CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户临时表的回滚段(rollback segments )。mysql服务器正常关闭或异常终止时临时表空间将被移除每次启动时会被重新创建。2. 数据字典(InnoDB Data Dictionary)InnoDB数据字典由内部系统表组成这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因数据字典元数据在一定程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠。3. 双写缓冲区(Doublewrite Buffer)双写缓冲区位于系统表空间是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前会先将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份。如果要禁用Doublewrite 缓冲区可以将innodb_doublewrite设置为0。在大多数情况下默认情况下启用双写缓冲区使用Doublewrite 缓冲区时建议将innodb_flush_method设置为O_DIRECT。这里的MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。他有三个值fdatasync(默认)O_DSYNCO_DIRECT。设置O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据也不要用预读直接从Innodb Buffer写到磁盘文件。默认的fdatasync意思是先写入操作系统缓存然后再调用fsync()函数去异步刷数据文件与redo log的缓存信息。新版本存储结构的改变MySQL在5.7和后续的8.0版本中的存储结构做了一定改变如下图所示image.pngMySQL 5.7 版本1)将 Undo日志表空间从共享表空间 ibdata 文件中分离出来可以在安装 MySQL 时由用户自行指定文件大小和数量。2)增加了 temporary 临时表空间里面存储着临时表或临时查询结果集的数据。3)Buffer Pool 大小可以动态修改无需重启数据库实例。MySQL 8.0 版本1)将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一致才行8.0版本就不需要了。2)emporary 临时表空间也可以配置多个物理文件而且均为 InnoDB 存储引擎并能创建索引这样加快了处理的速度。3)用户可以像 Oracle 数据库那样设置一些表空间每个表空间对应多个物理文件每个表空间可以给多个表使用但一个表只能存储在一个表空间中。4)将Doublewrite Buffer从共享表空间ibdata中也分离出来了。线程模型InnoDB的线程模型分为IO Thread、Purge Thread、Page Thread和Master Thread如下图所示:image.pngIO Thread在InnoDB中使用了大量的AIO(Async IO)来做读写处理这样可以极大提高数据库的性能。在InnoDB1.0版本之前共有4个IO Thread分别是writereadinsert buffer和log thread后来版本将read thread和write thread分别增大到了4个一共有10个了。read thread 负责读取操作将数据从磁盘加载到缓存page页。4个write thread负责写操作将缓存脏页刷新到磁盘。4个log thread负责将日志缓冲区内容刷新到磁盘。1个insert buffer thread 负责将写缓冲内容刷新到磁盘。1个Purge Thread事务提交之后其使用的undo日志将不再需要因此需要Purge Thread回收已经分配的undo页。show variables like %innodb_purge_threads%;Page Cleaner Thread作用是将脏数据刷新到磁盘脏数据刷盘后相应的redo log也就可以覆盖即可以同步数据又能达到redo log循环使用的目的。会调用write thread线程处理。show variables like %innodb_page_cleaners%;Master ThreadMaster thread是InnoDB的主线程负责调度其他各线程优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 保证数据的一致性。这些工作包含脏页的刷新(page cleaner thread)、undo页回收(purge thread)、redo日志刷新(log thread)、合并写缓冲等。在其内部有两个主去处理分别是每隔1秒和10秒处理。每1秒的操作刷新日志缓冲区刷到磁盘合并写缓冲区数据根据IO读写压力来决定是否操作刷新脏页数据到磁盘根据脏页比例达到75%才操作(innodb_max_dirty_pages_pctinnodb_io_capacity)每10秒的操作刷新脏页数据到磁盘合并写缓冲区数据刷新日志缓冲区删除无用的undo页数据文件InnoDB数据文件存储结构分为一个ibd数据文件--Segment(段)--Extent(区)--Page(页)--Row(行)image.png1. Tablesapce(表空间)表空间用于存储多个ibd数据文件用于存储表的记录和索引。一个文件包含多个段。2. Segment(段)段用于管理多个Extent分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)。一个表至少会有两个segment一个管理数据一个管理索引。每多创建一个索引会多两个segment。3. Extent(区)一个区固定包含64个连续的页大小为1M。当表空间不足需要分配新的页资源不会一页一页分直接分配一个区。4 Page(页)页用于存储多个Row行记录大小为16K。包含很多种页类型比如数据页undo页系统页事务数据页大的BLOB对象页。Page是文件最基本的单位无论何种类型的page都是由page headerpage trailer和page body组成。如下图所示image.png5. Row(行)行包含了记录的字段值事务ID(Trx id)、滚动指针(Roll pointer)、字段指针(Fieldpointers)等信息。InnoDB文件存储格式我们通过show table satus \G;命令查看InnoDB的文件状态(示例的数据库employee表)image.png一般情况下如果row_format为REDUNDANT、COMPACT文件格式为Antelope如果row_format为DYNAMIC和COMPRESSED文件格式为Barracuda。通过 information_schema 查看指定表的文件格式select * from information_schema.innodb_sys_tables;File文件格式(File-Format)在早期的InnoDB版本中文件格式只有一种随着InnoDB引擎的发展出现了新文件格式用于支持新的功能。目前InnoDB只支持两种文件格式Antelope 和 Barracuda。Antelope: 先前未命名的最原始的InnoDB文件格式它支持两种行格式COMPACT和REDUNDANTMySQL 5.6及其以前版本默认格式为Antelope。Barracuda: 新的文件格式。它支持InnoDB的所有行格式包括新的行格式COMPRESSED和 DYNAMIC。通过innodb_file_format 配置参数可以设置InnoDB文件格式之前默认值为Antelope5.7版本开始改为Barracuda。Row行格式(Row_format)表的行格式决定了它的行是如何物理存储的这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行查询和索引查找可以更快地工作缓冲池中所需的内存更少写入更新时所需的I/O更少。InnoDB存储引擎支持四种行格式REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。image.pngDYNAMIC和COMPRESSED新格式引入的功能有数据压缩、增强型长列数据的页外存储和大索引前缀。每个表的数据分成若干页来存储每个页中采用B树结构存储。如果某些字段信息过长无法存储在B树节点中这时候会被单独分配空间此时被称为溢出页该字段被称为页外列。1. REDUNDANT 行格式使用REDUNDANT行格式表会将变长列值的前768字节存储在B树节点的索引记录中其余的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段以便能够在页外存储。2. COMPACT 行格式与REDUNDANT行格式相比COMPACT行格式减少了约20%的行存储空间但代价是增加了某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制那么COMPACT格式可能更快。如果系统负载受到CPU速度的限制那么COMPACT格式可能会慢一些。3. DYNAMIC 行格式使用DYNAMIC行格式InnoDB会将表中长可变长度的列值完全存储在页外而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀最多可以为3072字节可通过innodb_large_prefix参数控制。4. COMPRESSED 行格式COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能但增加了对表和索引数据压缩的支持。在创建表和索引时文件格式都被用于每个InnoDB表数据文件(其名称与*.ibd匹配)。修改文件格式的方法是重新创建表及其索引最简单方法是对要修改的每个表使用以下命令ALTER TABLE 表名 ROW_FORMAT格式类型;
