橙色网站欣赏,wordpress开启用户激活验证失败,做论坛网站需要备案,湘潭网站建设的公司MySQL是一个多存储引擎的数据库管理系统#xff0c;支持多种不同的存储引擎。每种存储引擎都有其独特的特性、优势和适用场景。选择合适的存储引擎对于优化数据库性能、确保数据完整性和满足业务需求至关重要。 注#xff1a;在同一个Mysql的数据库中#xff0c;对于不同的表…MySQL是一个多存储引擎的数据库管理系统支持多种不同的存储引擎。每种存储引擎都有其独特的特性、优势和适用场景。选择合适的存储引擎对于优化数据库性能、确保数据完整性和满足业务需求至关重要。 注在同一个Mysql的数据库中对于不同的表是可以指定不同存储引擎的。
一、MEMORY
概述
内存表Memory引擎将所有数据存储在内存中速度非常快但不具备持久性。一旦服务器重启或断电表中的数据将丢失。表级锁Memory引擎使用表级锁类似于MyISAM但由于其在内存中执行因此性能更高。固定长度记录Memory引擎只支持固定长度的记录不支持TEXT、BLOB等变长类型字段。临时表Memory引擎常用于创建临时表适合短期存储和快速查询。
适用场景
临时数据存储Memory引擎适合用于存储临时数据如会话信息、缓存数据等尤其适用于需要快速查询和插入的场景。中间结果集在复杂的查询中可以将中间结果集存储在Memory表中以提高查询性能。高速缓存Memory引擎可以用作高速缓存存储频繁访问的数据减少对磁盘的读取。
二、MyISAM
概述
早期默认存储引擎在MySQL 5.1及之前的版本中MyISAM是默认的存储引擎。表级锁MyISAM使用表级锁当一个事务对表进行写操作时整个表会被锁定其他事务无法同时进行写操作这可能导致性能瓶颈。不支持事务MyISAM不支持事务也没有回滚功能因此不适合需要事务支持的应用场景。全文索引MyISAM支持全文索引Full-Text Index适用于需要进行全文搜索的场景。压缩表MyISAM支持创建压缩表可以减少磁盘空间占用。
适用场景
读多写少MyISAM的表级锁机制使得它在读多写少的场景下表现较好尤其是当写操作较少且不会频繁发生时。全文搜索MyISAM的全文索引功能使其适合需要进行全文搜索的应用如博客、论坛等。日志记录由于MyISAM的简单性和高效性它适合用于日志记录、统计分析等场景尤其是在不需要事务支持的情况下。
三、InnoDB
1、概述
默认存储引擎从MySQL 5.5版本开始InnoDB成为MySQL的默认存储引擎。事务支持InnoDB支持ACID原子性、一致性、隔离性、持久性事务确保数据的完整性和可靠性。行级锁InnoDB使用行级锁允许多个事务并发访问不同行的数据减少了锁冲突适合高并发读写场景。外键约束InnoDB支持外键约束确保表与表之间的关系完整性。崩溃恢复InnoDB具有自动崩溃恢复功能能够在系统崩溃后快速恢复数据。MVCC多版本并发控制InnoDB实现了MVCC允许读操作不加锁同时保证事务的隔离性提升了并发性能。
适用场景
高并发读写InnoDB的行级锁和MVCC机制使其非常适合高并发的读写操作。事务处理InnoDB是唯一支持完整事务的存储引擎适用于需要事务支持的应用程序如银行系统、电子商务平台等。数据完整性要求高由于支持外键约束和崩溃恢复InnoDB适合对数据完整性有严格要求的场景。
性能优化:
索引优化InnoDB使用聚簇索引Clustered Index主键索引和数据存储在一起可以提高查询性能。建议为表设置合理的主键。缓冲池InnoDB有一个名为InnoDB Buffer Pool的内存区域用于缓存数据和索引。可以通过调整innodb_buffer_pool_size参数来优化性能。日志文件InnoDB使用重做日志Redo Log和回滚段Undo Log来实现事务的持久性和回滚。可以通过调整innodb_log_file_size和innodb_flush_log_at_trx_commit参数来优化日志性能。
2、InnoDB内部结构
下图是官方文档给出的架构图可以看到InnoDB的架构主要分为两部分一部分是内存结构另一部分是磁盘结构。
1、内存结构
InnoDB内存结构主要分为Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer四个部分。
1、缓冲池Buffer Pool
1、缓冲池概述
缓冲池 Buffer Pool是主内存中的一个区域里面可以缓存磁盘上经常操作的真实数据在执行增删改查操作时先操作缓冲池中的数据若缓冲池没有数据则从磁盘加载并缓存然后再以一定频率刷新到磁盘中从而减少磁盘IO加快处理速度。 Buffer Pool的实现其实是一个链表链表上为访问的页数据。
功能缓冲池是InnoDB的核心内存结构用于缓存数据页和索引页。它显著减少了磁盘I/O操作提升了读写性能。大小缓冲池的大小由配置参数innodb_buffer_pool_size控制通常建议设置为服务器物理内存的70%-80%。分片可以通过innodb_buffer_pool_instances参数将缓冲池划分为多个实例减少锁竞争提升并发性能。基本单位缓冲池以Page页为单位底层采用链表数据结构管理Page。根据状态将Page分为三种类型 free page空闲page即还未被使用的页。clean page被使用page即数据从磁盘加载到内存中的页数据没有被修改过此时内存页数据和磁盘页数据相同。dirty page脏页被使用page即内存中的页数据被修改过此时与磁盘的数据已经不一致了。 在专用服务器上通常将多达80的物理内存分配给缓冲池 。
查看缓冲池大小
show variables like innodb_buffer_pool_size; 运行结果
2、LRU算法
LRULeast Recently Used缓冲池使用LRU算法来管理内存页。当缓冲池满时最久未使用的页将被移出为新的数据页腾出空间。Young和Old列表InnoDB将缓冲池分为两个列表年轻列表Young List和老年列表Old List。年轻列表用于存储最近访问的页老年列表用于存储较早访问的页。这种设计有助于提高缓存命中率。
3、预读机制
预读InnoDB支持预读机制可以在一次I/O操作中读取多个相邻的数据页减少磁盘I/O次数。 预读分为两种模式 线性预读当顺序扫描表时InnoDB会预读后续的页。随机预读当频繁访问某些非连续的页时InnoDB会预读这些页周围的页。
4、脏页刷新
当缓冲池中的页被修改后它们被称为脏页。脏页不会立即写入磁盘而是会在适当的时机进行刷新。
InnoDB使用以下机制来管理脏页刷新
后台刷新InnoDB有一个后台线程定期将脏页刷新到磁盘。这个过程称为后台刷新Background Flush。检查点机制InnoDB使用检查点Checkpoint来确保脏页及时写入磁盘。检查点是指重做日志Redo Log的某个位置所有在此之前修改的页都必须刷新到磁盘。通过这种方式InnoDB可以确保在系统崩溃后能够快速恢复数据。
理解 Mysql的数据会被先保存到内存中之后在保存到磁盘中。数据加载也是先从磁盘加载到内存中在从内存提取返回给客户端。 在内存中的数据即使被修改了也会先将修改后的结果保存到内存中并不会立即刷新到磁盘中。这种内被修改了的页称之为脏页InnoDB会定期将脏页的数据刷新到磁盘中。同时会将已经刷新的脏页的位置标记到Redo Log中称之为检查点。下一次直接从这个检查点之后获取脏页数据在刷新到内存中即可。
5、分片缓冲池实例
为了减少锁争用提升并发性能InnoDB支持分片缓冲池Buffer Pool Instances。你可以通过innodb_buffer_pool_instances参数将缓冲池划分为多个实例。每个实例都有自己的锁和管理机制从而减少了全局锁的争用。
2、更改缓冲区Change Buffer
1、更改缓冲区概述
更改缓冲区Change Buffer是InnoDB用于优化非聚簇索引Secondary IndexDML语句操作的一种机制。在执行DML语句时如比如INSERT、UPDATE、DELETE如果这些数据Page没有在Buffer Pool中不会直接操作磁盘而会将数据变更信息暂存到更改缓冲区Change Buffer中在未来数据被读取时再将数据合并恢复到Buffer Pool中最终合并后的数据在刷新到磁盘中。
Change Buffer的意义在于不用每一次DML后都直接操作磁盘造成大量的磁盘I/O。有了 Change Buffer之后我们可以在缓冲池中进行合并处理之后对批量数据进行一次I/O减少磁盘I/O提升写性能尤其是在高并发场景下。
2、Change Buffer和聚簇索引非聚簇索引的关系理解
更改缓冲区Change Buffer主要用于优化非聚簇索引的插入、更新和删除操作。
示例 假设你有一个 users 表如下
CREATE TABLE users (id INT PRIMARY KEY, -- 聚簇索引主键name VARCHAR(50),age INT,INDEX idx_name (name) -- 非聚簇索引
);1、新增数据 当你向表中插入一条新记录时InnoDB 会执行以下操作 聚簇索引主键的插入新记录会根据主键的顺序插入到聚簇索引中并且该操作会立即写入磁盘以确保数据的一致性和完整性。 非聚簇索引的插入如果表中也有非聚簇索引例如基于name列的索引InnoDB还需要将新记录的name值插入到相应的非聚簇索引中。这个插入操作不会立即写入磁盘而是会先进入更改缓冲区直到需要访问该索引页时再合并到磁盘上。
sql
INSERT INTO users (id, name, age) VALUES (1, Alice, 30);解释
聚簇索引id 1的记录会被插入到聚簇索引中并且该操作会立即写入磁盘。非聚簇索引name Alice’的记录会被插入到idx_name索引中。这个插入操作不会立即写入磁盘而是会先进入更改缓冲区直到需要访问该索引页时再合并到磁盘上。
简单理解 新增数据有3个部分第一部分是聚簇索引立即写入到磁盘因为它决定了数据在磁盘的位置。第二步会立即将数据写入到该聚簇索引在磁盘的位置中。第三步对非聚簇索引会暂存到Change Buffer中以减少磁盘I/O。
2、更新数据 当你更新一条现有记录时InnoDB会执行以下操作 聚簇索引主键的更新如果更新的是主键聚簇索引InnoDB会立即修改聚簇索引中的相应记录并将该修改立即写入磁盘以确保数据的一致性和完整性。 非聚簇索引的更新如果更新的是非聚簇索引列例如name列InnoDB会将旧值和新值以及非聚簇索引的更新信息保存到更改缓冲区中。这些信息会在适当的时机如索引页被读取时或后台刷新时合并到磁盘上的索引页中。
sql
UPDATE users SET name Bob WHERE id 1;解释
聚簇索引id 1的记录中主键id没有发生变化因此不需要修改聚簇索引。非聚簇索引name列的值从’Alice’变为’Bob’InnoDB会将旧值’Alice’和新值’Bob’以及非聚簇索引的更新信息暂存到更改缓冲区中直到需要访问该索引页时再合并到磁盘上。
简单理解 更新数据时如果修改是聚簇索引那么数据的物理存储位置就会发生了改变所以要第一时间写入到磁盘中保证数据的完整性。如果更新的只是非聚簇索引那么数据的物理位置就没有发生改变。可以先将更新的数据信息和非聚簇索引信息暂存到更改缓冲区中以减少磁盘I/O频率等待合适时机在合并到磁盘中。
3、删除操作 当你删除一条记录时InnoDB会执行以下操作 聚簇索引主键的删除InnoDB会立即从聚簇索引中删除该记录并将该删除操作立即写入磁盘以确保数据的一致性和完整性。 非聚簇索引的删除如果表中有非聚簇索引例如基于name列的索引InnoDB还需要从非聚簇索引中删除该记录的name值。这个删除操作不会立即写入磁盘而是会先进入更改缓冲区直到需要访问该索引页时再合并到磁盘上。
sql
DELETE FROM users WHERE id 1; // 主键方式删除
DELETE FROM users WHERE name Alice; // 非聚簇索引方式删除解释
聚簇索引id 1的记录会被从聚簇索引中删除并且该操作会立即写入磁盘。非聚簇索引name Alice’的记录会被从idx_name索引中删除。这个删除操作不会立即写入磁盘而是会先进入更改缓冲区直到需要访问该索引页时再合并到磁盘上。
简单理解 如果删除的是聚簇索引聚簇索引信息和物理数据位置的数据都会被立即删除以保证数据的完整性。但是如果这行数据还有非聚簇索引这个非聚簇索引的删除信息会暂存到更改缓冲区中以减少磁盘I/O频率等待合适时机在合并到磁盘中。
3、更新缓冲区工作原理 修改缓存当对非聚簇索引进行插入、更新或删除操作时InnoDB不会立即修改磁盘上的索引页而是将这些修改暂存在更改缓冲区中。 合并操作当InnoDB需要读取某个索引页时它会检查更改缓冲区中是否有对该页的未应用的修改。如果有InnoDB会将这些修改与索引页合并然后将结果写入磁盘。这个过程称为合并Merge。 后台刷新即使没有读取操作InnoDB也会定期将更改缓冲区中的修改合并到磁盘上的索引页中。这个过程由后台线程执行称为后台刷新Background Flush。 崩溃恢复更改缓冲区中的修改并不是持久化的因此在系统崩溃后这些修改可能会丢失。为了确保数据的一致性InnoDB会在崩溃恢复时重新应用重做日志Redo Log中的相关记录。
4、更新缓冲区的优势 减少磁盘I/O通过将对非聚簇索引的修改暂存到内存中InnoDB可以减少频繁的磁盘写入操作尤其是当索引页不在缓冲池中时。这可以显著提升写性能特别是在高并发场景下。 批量合并更改缓冲区允许InnoDB将多个小的修改合并为一次大的写操作减少了磁盘I/O次数。例如多个插入操作可以合并为一次批量插入多个删除操作可以合并为一次批量删除。 提高写密集型应用的性能对于写密集型应用特别是那些频繁插入或更新非聚簇索引的应用更改缓冲区可以显著减少磁盘I/O提升整体性能。 优化冷索引对于那些不经常被查询的非聚簇索引更改缓冲区可以推迟写入操作直到索引页被读取时再进行合并。这可以减少不必要的磁盘写入提升性能。
5、更改缓冲区的限制 仅适用于非聚簇索引更改缓冲区仅对非聚簇索引有效对聚簇索引即主键索引无效。因此更改缓冲区不能优化对主键的插入、更新或删除操作。 不适用于唯一索引更改缓冲区不支持唯一索引Unique Index。对于唯一索引InnoDB 必须立即检查是否存在重复值因此无法将修改缓存到更改缓冲区中。 内存占用更改缓冲区占用缓冲池的一部分内存。如果更改缓冲区过大可能会影响其他数据和索引页的缓存导致缓冲池命中率下降。因此需要合理配置更改缓冲区的大小。 崩溃恢复更改缓冲区中的修改不是持久化的因此在系统崩溃后这些修改可能会丢失。为了确保数据的一致性InnoDB会在崩溃恢复时重新应用重做日志Redo Log中的相关记录。
6、相关配置参数
InnoDB提供了几个配置参数来控制更改缓冲区的行为和性能。以下是常用的配置参数及其作用 1、innodb_change_buffer_max_size
功能该参数控制更改缓冲区的最大大小占缓冲池的比例。该参数的值范围为0到50默认值为25表示更改缓冲区最多可以占用缓冲池的25%。推荐值如果你的应用程序有大量对非聚簇索引的插入、更新或删除操作可以适当增加该参数的值。但要注意过大的更改缓冲区可能会影响其他数据和索引页的缓存导致缓冲池命中率下降。
2、innodb_change_buffering 功能该参数控制哪些类型的索引操作可以使用更改缓冲区。可选值包括 all所有类型的索引操作都可以使用更改缓冲区默认值。inserts只有插入操作可以使用更改缓冲区。deletes只有删除操作可以使用更改缓冲区。changes更新操作可以使用更改缓冲区。none禁用更改缓冲区所有索引操作都会立即写入磁盘。 推荐值默认值all适用于大多数场景。如果你的应用主要进行插入操作可以设置为inserts以减少不必要的删除操作进入更改缓冲区。如果你的应用对索引的实时性要求较高可以考虑设置为none以确保所有修改立即写入磁盘。
3、innodb_buffer_pool_instances
功能该参数控制缓冲池的分片数量。更改缓冲区也受此参数影响每个分片都有自己的更改缓冲区。通过增加分片数量可以减少锁争用提升并发性能。推荐值建议根据服务器的CPU核心数设置通常设置为8或16。对于多核CPU的服务器增加分片数量可以显著提升并发性能。
7、更改缓冲区的监控与优化
为了更好地管理和优化更改缓冲区InnoDB提供了一些监控工具和性能指标。
1、performance_schema 从MySQL 5.7开始performance_schema提供了更详细的监控信息。你可以查询 performance_schema.table_io_waits_summary_by_index_usage表查看各个索引的I/O等待情况从而评估更改缓冲区的效果。
sql
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL;运行结果
2、INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS 该表提供了缓冲池的统计信息包括更改缓冲区的使用情况。你可以查询该表查看更改缓冲区的命中率、合并次数等指标。
sql
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;运行结果
3、Adaptive Hash Index
自适应hash索引用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引但是给我们提供了一个功能就是这个自适应hash索引。
hash索引在进行等值匹配时一般性能是要高于B树的因为hash索引一般只需要一次I/O即可而B树可能需要几次匹配所以hash索引的效率要高但是hash索引又不适合做范围查询、模糊匹配等。
InnoDB存储引擎会监控表上各索引页的查询如果观察到在特定的条件下hash索引可以提升速度则建立hash索引称之为自适应hash索引。
自适应哈希索引无需人工干预是InnoDB根据情况自动完成的。
示例
show VARIABLES like %adaptive_hash_index%;运行结果
4、Log Buffer
Log Buffer日志缓冲区用来保存要写入到磁盘中的log日志数据的缓存部分如redo log 、undo log等默认大小为16MB日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务增加日志缓冲区的大小可以节省磁盘 I/O。
查看日志缓冲区大小
show VARIABLES like %innodb_log_buffer_size%;运行结果
查看日志刷新到磁盘的时机
show VARIABLES like %innodb_flush_log_at_trx_commit%;解释 1: 日志在每次事务提交时写入并刷新到磁盘默认值。 0: 每秒将日志写入并刷新到磁盘一次。 2: 日志在每次事务提交后写入并每秒刷新到磁盘一次。 运行结果
2、磁盘结构
InnoDB磁盘结构主要包含表、索引、表空间、Doublwrite Buffer、Redo Log和Undo Log几个部分。
1、表空间Tablespace
表空间是InnoDB存储数据和索引的基本单位。每个表空间由一个或多个文件组成用于存储表的数据页和索引页。根据配置表空间可以分为以下几类
1、系统表空间System Tablespace
针对Double write buffer和Change buffer的一块物理存储区域。如果表建立在系统表空间内那么也会包含表和索引的数据。 它是InnoDB的全局共享表空间系统表空间通常位于ibdata1文件中。
功能
数据字典存储所有表的元数据包括表结构、索引信息等。回滚段存储回滚段Undo Log用于实现事务的回滚和多版本并发控制MVCC。双写缓冲区存储双写缓冲区Doublewrite Buffer用于防止页面损坏。插入缓冲区存储插入缓冲区Insert Buffer用于加速非聚簇索引的插入操作。
2、独立表空间File-Per-Table Tablespace
过去InnoDB都是把表数据存储在系统表空间内这种方式适用于专门用于数据库处理的机器而File-Per-Table Tablespace允许每个表的数据都可以存储在自己的表空间数据文件里(.ibd文件)。 这是通过配置参数innodb_file_per_table ON实现的。启用后每个表的数据和索引都会存储在单独的.ibd文件中。在较新版本的Mysql中都是默认开启独立标间存储的。
sql
show VARIABLES like %innodb_file_per_table%;运行结果
优点
方便管理可以单独对某个表进行备份、恢复、收缩或删除。减少碎片独立表空间可以减少全局表空间的碎片化问题。支持在线DDL操作某些DDL操作如添加索引可以在不影响其他表的情况下执行。
3、临时表空间Temporary Tablespace
临时表空间用于存储临时表和内部临时表如排序、分组等操作。临时表空间通常位于ibtmp1文件中。
功能
存储临时表的数据和索引。存储内部临时表用于查询优化如排序、分组、连接等。
4、通用表空间General Tablespace
通用表空间允许用户自定义创建多个共享表空间可以为特定的表集分配一个独立的表空间文件。可以通过CREATE TABLESPACE语法创建共享表空间。 上面介绍的3种表空间都是系统控制的有自己独立的业务场景需要。如果我们自己想要独立的表空间的话可以在通用表空间中自己创建出来再使用到具体的表上。
优点
灵活管理可以根据需要创建多个通用表空间便于管理和优化。支持大文件通用表空间可以跨越多个文件适用于存储非常大的表。
5、撤销表空间Undo Tablespace
撤销表空间MySQL实例在初始化时会自动创建两个默认的undo表空间初始大小16M用于存储undo log日志。 通过该日志可以撤销事务对聚簇索引数据的最新修改即我们常说的数据回滚。
2、双写缓冲区Doublewrite Buffer Files
双写缓冲区Doublewrite Buffer Files是InnoDB用于防止页面损坏的关键机制。InnoDB引擎将数据页从Buffer Pool刷新到磁盘前先将数据页写入双写缓冲区文件中便于系统异常时恢复数据。 双写缓冲区存储在系统表空间中包含128个16KB的页。
工作原理
InnoDB将要写入的数据页分成16KB的块。这些块首先被写入双写缓冲区。然后InnoDB将这些块写入实际的数据表空间文件中。如果系统崩溃InnoDB可以根据双写缓冲区中的数据页快速恢复损坏的页。
功能
防止页面损坏在将数据页写入磁盘之前InnoDB会先将数据页的副本写入双写缓冲区。如果系统崩溃InnoDB可以从双写缓冲区中恢复损坏的数据页。简化恢复过程在崩溃恢复时InnoDB只需要检查双写缓冲区中的页而不需要逐个检查所有数据页从而加快恢复速度。
3、Redo Log
重做日志Redo log是InnoDB用于确保事务持久性和崩溃恢复的关键组件。
该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo log,前者是在内存中后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于发生错误时, 进行数据恢复使用。
重做日志文件通常位于ib_logfile0和ib_logfile1中文件大小由innodb_log_file_size参数控制。
功能
记录所有对数据页的修改操作确保事务的持久性。在系统崩溃后InnoDB可以根据重做日志重新应用未完成的事务恢复数据的一致性。重做日志采用循环写入的方式当一个日志文件写满后InnoDB会切换到下一个日志文件。
3、InnoDB存储结构
InnoDB数据存储结构示例图
1、表空间Tablespace
表空间是InnoDB存储数据和索引的基本单位。一个mysql实例可以对应多个表空间每个表空间由一个或多个文件组成用于存储表的数据页和索引页。
表空间可以分为5类 系统表空间System Tablespace即ibdata1文件。 独立表空间File-Per-Table Tablespace即每一个.ibd文件。 临时表空间Temporary Tablespace常位于ibtmp1文件中。 撤销表空间Undo Tablespace用于存储undo log日志名称为undo_001类似的文件。 通用表空间General Tablespace用于自定义创建的表空间。
2、段Segment
段Segment是表空间中的逻辑分区用于管理不同类型的对象。InnoDB中的段主要用于管理数据段和索引段。 简单说一个表空间是由多个逻辑段组成的。
按照功能类型划分段的种类分为如下3种 1、数据段Data Segment 数据段用于存储表的实际数据行。每个表都有一个数据段负责管理数据页的分配和回收。
特点
按需分配数据段会根据需要动态分配新的数据页。支持扩展当表的数据量增加时数据段可以自动扩展分配更多的数据页。
2、索引段Index Segment 索引段用于存储表的索引信息。每个索引包括聚簇索引和非聚簇索引都有一个独立的索引段负责管理索引页的分配和回收。
特点
按需分配索引段会根据需要动态分配新的索引页。支持扩展当索引的大小增加时索引段可以自动扩展分配更多的索引页。
3、回滚段Undo Segment 回滚段用于存储回滚日志Undo Log记录了事务的旧版本数据。回滚段是InnoDB实现事务的回滚和多版本并发控制MVCC的关键组件。
特点
按需分配回滚段会根据需要动态分配新的回滚页。支持扩展当事务的回滚日志增多时回滚段可以自动扩展分配更多的回滚页。
3、区Extent
区是InnoDB中的一个物理存储单元每个区由64个连续的页组成。区是InnoDB分配和回收存储空间的基本单位。 每个区包含64个页默认情况下每个页的大小为16KB因此每个区的大小为1MB64 * 16KB。
功能
分配存储空间InnoDB通过分配区来为数据段、索引段和回滚段分配存储空间。回收存储空间当数据或索引被删除时InnoDB会将空闲的区标记为可回收并在适当的时候将其回收。
4、页Page
页是InnoDB中最小的I/O单位所有的数据和索引都存储在页中。页是InnoDB进行读取、写入和缓存的基本单位。 InnoDB的默认页大小为16KB可以通过innodb_page_size参数调整为4KB或8KB。
影响 页大小的选择会影响I/O性能和内存使用。较大的页可以减少磁盘I/O次数但会增加内存占用较小的页可以提高内存利用率但可能会增加 I/O 次数。
页类型 InnoDB中有多种类型的页每种页用于存储不同类型的数据
数据页Data Page存储表的实际数据行。索引页Index Page存储索引信息。undo 页存储回滚段数据。系统页存储元数据和其他控制信息。自由页Free Page未使用的空闲页等待分配。插入缓冲区页Insert Buffer Page用于缓存对非聚簇索引的插入操作。更改缓冲区页Change Buffer Page用于缓存对非聚簇索引的插入、更新和删除操作。
页结构 每个页都有固定的结构主要包括以下几个部分
页头Page Header包含页的元数据如页号、页类型、自由空间指针等。用户数据User Data存储实际的数据或索引信息。页尾Page Trailer包含校验和和LSN日志序列号用于检测页面是否损坏。
5、行Row
行RowInnoDB 存储引擎数据是按行进行存放的。在行中默认有两个隐藏字段 Trx_id每次对某条记录进行改动时都会把对应的事务id赋值给trx_id隐藏列。 Roll_pointer每次对某条引记录进行改动时都会把旧的版本写入到undo日志中然后这个隐藏列就相当于一个回滚指针可以通过它来找到该记录修改前的信息。
4、InnoDB日志管理
InnoDB的日志管理是其事务处理和崩溃恢复机制的核心组成部分。通过日志InnoDB能够确保数据的持久性和一致性即使在系统崩溃的情况下也能快速恢复到一致的状态。InnoDB主要使用两种类型的日志重做日志Redo Log和回滚段Undo Log。此外MySQL还提供了二进制日志Binary Log用于主从复制和备份恢复。
1、重做日志Redo Log
1、概述
重做日志是InnoDB用于确保事务持久性的关键组件。它记录了所有对数据页的物理修改操作确保在系统崩溃后可以重新应用这些修改恢复数据的一致性。
位置重做日志文件通常位于ib_logfile0和ib_logfile1中。大小每个重做日志文件的大小由innodb_log_file_size参数控制默认值为48MB。数量可以通过innodb_log_files_in_group参数指定重做日志文件的数量默认为2。
2、工作原理
1、记录修改每当对数据页进行修改时InnoDB会先将修改操作记录到重做日志中。重做日志记录的是物理修改而不是逻辑操作。即记录了页的相关变化而不是具体的SQL语句。
2、循环写入重做日志采用循环写入的方式。当一个日志文件写满后InnoDB会切换到下一个日志文件继续写入。所有日志文件写满后InnoDB会回到第一个日志文件覆盖旧的日志记录。
3、检查点Checkpoint为了防止重做日志被无限期地循环覆盖InnoDB使用检查点机制。检查点是指重做日志的某个位置所有在此之前修改的数据页都必须已经刷新到磁盘。通过这种方式InnoDB可以确保在系统崩溃后能够从检查点开始恢复数据。
4、崩溃恢复在系统崩溃后InnoDB会在启动时读取重做日志重新应用未完成的事务恢复数据的一致性。这个过程称为前滚恢复Roll Forward Recovery。
3、配置参数
innodb_log_file_size
功能设置每个重做日志文件的大小。推荐值建议根据工作负载调整。对于高并发写入场景可以适当增大该值以减少日志切换的频率。默认值为48MB。
innodb_log_files_in_group
功能设置重做日志文件的数量。推荐值默认为2通常不需要调整。如果需要更大的日志空间建议增加单个日志文件的大小而不是增加日志文件的数量。
innodb_flush_log_at_trx_commit
功能控制事务提交时是否立即刷新重做日志到磁盘。取值 0不刷新重做日志性能最好但安全性最低。如果系统崩溃可能会丢失最近的事务。1默认每次事务提交时都刷新重做日志到磁盘确保数据的安全性但性能稍差。2每次事务提交时将重做日志写入操作系统缓存但不立即刷新到磁盘。适合追求性能的场景但在系统崩溃时可能会丢失最近的事务。
innodb_log_buffer_size
功能设置重做日志缓冲区的大小。重做日志缓冲区用于暂存尚未写入磁盘的重做日志记录。推荐值默认为8MB可以根据工作负载适当调整。较大的缓冲区可以减少磁盘I/O次数提升写性能。
4、优化建议 增大innodb_log_file_size对于高并发写入场景建议增大重做日志文件的大小以减少日志切换的频率。较大的日志文件可以容纳更多的事务减少磁盘I/O次数。 调整innodb_flush_log_at_trx_commit根据应用场景选择合适的值。如果你的应用对数据安全要求较高建议保持默认值1如果你的应用对性能要求较高且可以容忍少量数据丢失可以选择2或0。 使用O_DIRECT刷新方法通过innodb_flush_method O_DIRECT配置绕过操作系统的缓存直接将重做日志写入磁盘减少双重缓存问题提升性能。
2、回滚日志Undo Log
1、概述
回滚日志是InnoDB用于实现事务的回滚和多版本并发控制MVCC的关键组件。它记录了事务的旧版本数据确保未提交的事务不会影响数据库的状态并支持多个事务同时读取不同的数据版本。
位置回滚段存储在系统表空间中通常是ibdata1文件的一部分。类型插入回滚段用于记录插入操作的旧版本数据。更新回滚段用于记录更新操作的旧版本数据。
2、工作原理
1、记录旧版本数据每当对数据进行插入、更新或删除操作时InnoDB会将旧版本数据记录到回滚日志中。这些旧版本数据用于实现事务的回滚和多版本并发控制MVCC。
2、事务回滚如果事务未提交或发生错误InnoDB可以根据回滚段中的旧版本数据将数据恢复到事务开始时的状态。
3、多版本并发控制MVCCInnoDB使用回滚段来支持多版本并发控制。当多个事务同时读取同一行数据时InnoDB会根据事务的隔离级别返回合适的数据版本。例如在读已提交Read Committed隔离级别下事务只能看到已经提交的数据而在可重复读Repeatable Read隔离级别下事务在整个生命周期内都能看到相同的数据版本。
4、清理旧版本数据当事务提交后回滚段中的旧版本数据不再需要InnoDB会定期清理这些数据释放空间。
3、配置参数
innodb_undo_tablespaces
功能设置独立的回滚表空间的数量。启用该参数后回滚段将存储在独立的.ibd文件中而不是系统表空间中。推荐值默认为0表示回滚段存储在系统表空间中。如果你有大量长时间运行的事务建议启用独立的回滚表空间以减少系统表空间的碎片化问题。
innodb_undo_log_truncate
功能控制是否定期截断回滚段。启用该参数后InnoDB会定期清理不再需要的回滚段释放空间。推荐值默认为OFF建议在生产环境中启用该参数以减少回滚段的占用空间。
innodb_max_undo_log_size
功能设置回滚段的最大大小。当回滚段超过该大小时InnoDB会自动截断回滚段释放空间。推荐值默认为1GB可以根据工作负载适当调整。
4、优化建议 启用独立回滚表空间如果你有大量长时间运行的事务建议启用独立的回滚表空间以减少系统表空间的碎片化问题。独立的回滚表空间可以更好地管理和优化回滚段的存储。 定期截断回滚段启用innodb_undo_log_truncate参数定期清理不再需要的回滚段释放空间。这有助于减少回滚段的占用空间提升性能。 调整回滚段大小根据工作负载调整innodb_max_undo_log_size参数确保回滚段不会占用过多的空间。对于大事务或长事务较多的场景可以适当增大该值。
3、二进制日志Binary Log
1、概述
二进制日志是MySQL用于主从复制和备份恢复的关键组件。它记录了所有对数据库的修改操作包括DDL数据定义语言和DML数据操作语言语句。
位置二进制日志文件通常位于mysql-bin.000001等文件中。格式二进制日志记录的是逻辑操作而不是物理修改。每个日志文件包含一系列事件Event每个事件代表一个修改操作。
2、工作原理
1、记录修改每当对数据库进行修改操作时MySQL会将该操作记录到二进制日志中。二进制日志记录的是逻辑操作例如INSERT、UPDATE、DELETE等语句。
2、主从复制在主从复制架构中主库会将二进制日志发送给从库从库会根据这些日志重新执行相同的修改操作确保主从库的数据一致。
3、备份恢复二进制日志可以用于备份和恢复。通过二进制日志可以在全量备份的基础上恢复到特定的时间点确保数据的完整性。
3、配置参数
log_bin
功能启用或禁用二进制日志。推荐值如果你需要主从复制或备份恢复功能建议启用二进制日志。默认情况下二进制日志是禁用的。
binlog_format
功能设置二进制日志的格式。取值 STATEMENT记录SQL语句本身适用于简单的查询但可能无法正确处理某些复杂的操作。ROW记录每一行的变化适用于复杂查询和事务但日志文件较大。MIXED根据情况自动选择STATEMENT或ROW模式适用于大多数场景。
expire_logs_days
功能设置二进制日志的保留天数。超过该天数的日志文件将被自动删除。推荐值根据备份策略和磁盘空间调整。建议设置合理的保留时间避免日志文件占用过多空间。
sync_binlog
功能控制是否每次写入二进制日志后立即同步到磁盘。取值 0不立即同步性能最好但安全性最低。如果系统崩溃可能会丢失最近的日志记录。1默认每次写入后立即同步到磁盘确保数据的安全性但性能稍差。N 1每N次写入后同步一次平衡性能和安全性。
4、优化建议 选择合适的日志格式根据应用场景选择合适的二进制日志格式。对于简单的查询STATEMENT模式性能较好对于复杂查询和事务ROW模式更安全可靠MIXED模式则适用于大多数场景。 合理设置日志保留时间根据备份策略和磁盘空间调整expire_logs_days参数避免日志文件占用过多空间。建议设置合理的保留时间确保在需要时可以恢复到特定的时间点。 启用日志同步根据应用程序对数据安全的要求选择合适的sync_binlog值。如果你的应用对数据安全要求较高建议保持默认值1如果你的应用对性能要求较高且可以容忍少量日志丢失可以选择0或较大的值。
4、日志管理总结
InnoDB的日志管理是其事务处理和崩溃恢复机制的核心组成部分。通过重做日志、回滚段和二进制日志InnoDB能够确保数据的持久性和一致性即使在系统崩溃的情况下也能快速恢复到一致的状态。 重做日志Redo Log用于确保事务的持久性记录对数据页的物理修改操作。通过循环写入和检查点机制InnoDB可以在系统崩溃后快速恢复数据。 回滚段Undo Log用于实现事务的回滚和多版本并发控制MVCC记录事务的旧版本数据。通过独立回滚表空间和定期截断回滚段可以优化回滚段的存储和性能。 二进制日志Binary Log用于主从复制和备份恢复记录对数据库的逻辑修改操作。通过合理的日志格式和保留时间设置可以确保数据的安全性和可恢复性。
5、InnoDB锁机制
InnoDB 是 MySQL 的默认存储引擎提供了强大的事务支持和并发控制机制。为了确保数据的一致性和并发性InnoDB 使用了多种锁机制来管理对数据的访问。理解 InnoDB 的锁机制对于优化数据库性能、避免死锁以及确保事务的隔离性至关重要。
InnoDB的锁机制主要包括以下几种
行级锁Row-Level Locking表级锁Table-Level Locking意向锁Intention Locks间隙锁Gap Locks临键锁Next-Key Locks记录锁Record Locks自动增长锁Auto-Increment Locks元数据锁Metadata Locks
1、行级锁Row-Level Locking
行级锁是InnoDB最常用的锁类型它只锁定被操作的特定行而不是整个表。行级锁可以提高并发性允许多个事务同时对不同行进行操作而不会相互阻塞。
优点行级锁的粒度较小能够有效减少锁冲突提升并发性能。缺点行级锁需要更多的内存和CPU资源来管理和维护锁信息尤其是在高并发场景下可能会导致性能下降。
行级锁分类
记录锁Record Locks锁定单个索引记录。例如在SELECT … FOR UPDATE或UPDATE操作中InnoDB会为涉及的每一行加记录锁。间隙锁Gap Locks锁定索引中的一个区间即“间隙”防止其他事务在这个区间内插入新行。间隙锁主要用于可重复读Repeatable Read隔离级别下的防幻读Phantom Read问题。临键锁Next-Key Locks结合了记录锁和间隙锁锁定一个范围内的所有行和间隙。临键锁用于防止其他事务在该范围内插入新行或修改现有行。这是InnoDB在可重复读隔离级别下的默认锁机制。
应用场景
SELECT … FOR UPDATE在读取数据时加写锁防止其他事务修改或删除这些行。SELECT … LOCK IN SHARE MODE在读取数据时加读锁允许其他事务读取相同的数据但不允许修改或删除。INSERT、UPDATE、DELETE在修改数据时自动加写锁确保同一行不会被多个事务同时修改。
2、表级锁Table-Level Locking
表级锁是锁定整个表的锁阻止其他事务对该表进行任何操作。表级锁的粒度较大适用于某些特殊场景但在大多数情况下行级锁更为常用。
优点表级锁的实现简单管理成本低适合全表扫描或批量操作。缺点表级锁会严重限制并发性可能导致大量事务阻塞影响性能。
工作原理
LOCK TABLES显式地对表加锁。可以通过READ或WRITE锁定表分别表示只读锁和写锁。UNLOCK TABLES释放显式加的表锁。
应用场景
批量导入数据在导入大量数据时可以使用表级锁来防止其他事务对该表进行操作确保数据一致性。全表扫描在执行全表扫描时可以使用表级锁来防止其他事务修改表中的数据。
3、意向锁Intention Locks
意向锁是一种解决行锁和表锁冲突的机制。其作用为表级别的锁用于表明事务打算在表的某个范围内加行级锁。意向锁本身并不锁定任何行而是作为一种信号告诉其他事务当前事务可能会影响哪些行。 可以理解为意向锁实际上是一种解决锁冲突的机制主要用于解决行锁和表级锁之间的兼容问题并不是实质意义上的锁。
意向共享锁Intention Shared Lock, IS表明事务打算在表的某些行上加共享锁读锁。意向排他锁Intention Exclusive Lock, IX表明事务打算在表的某些行上加排他锁写锁。
工作原理
IS锁当事务打算在表的某些行上加共享锁时会先在表上加IS锁。IS锁与其他S锁兼容但与X锁不兼容。IX锁当事务打算在表的某些行上加排他锁时会先在表上加IX锁。IX锁与其他X锁不兼容但与S锁兼容。 注意下 意向锁主要是解决行锁和表锁之间的兼容问题。当一张表具有IX时意向锁会阻止其他事务再次获取该表的X锁而不是阻止其他事务获取表中某一行的X锁。即对于同一个表行锁和其他事务行锁还是可以共存的只要行不一致就可以。但是行锁和表锁是不能共存的。
应用场景
并发控制意向锁用于防止多个事务同时对同一张表的不同行加锁时发生冲突。通过意向锁InnoDB可以提前检测到潜在的锁冲突避免不必要的等待和死锁。
4、间隙锁Gap Locks
间隙锁是锁定索引中的一个区间即“间隙”防止其他事务在这个区间内插入新行。间隙锁主要用于可重复读Repeatable Read隔离级别下的防幻读Phantom Read问题。 简单理解目标行索引的前后相邻索引区间的行都会被锁住即锁住了周围的多行数据而不仅仅只是锁住目标那一行的数据。
幻读问题 在一个事务中两次查询返回的结果集行数不同因为另一个事务在这两次查询之间插入了新行。间隙锁可以防止这种情况发生。
工作原理
锁定区间间隙锁锁定的是索引中的一个区间而不是具体的行。例如如果索引中有两个相邻的值1和3间隙锁可以锁定(1, 3)这个区间防止其他事务在此区间内插入新行。与记录锁结合间隙锁通常与记录锁结合使用形成临键锁Next-Key Lock锁定一个范围内的所有行和间隙。
应用场景
防幻读在可重复读隔离级别下间隙锁用于防止其他事务在查询结果集中插入新行确保事务在整个生命周期内看到相同的数据版本。唯一约束在插入新行时InnoDB会自动加间隙锁确保唯一约束不会被违反。
5、临键锁Next-Key Locks
临键锁是结合了记录锁和间隙锁的锁类型锁定一个范围内的所有行和间隙。临键锁是 InnoDB在可重复读隔离级别下的默认锁机制用于防止其他事务在该范围内插入新行或修改现有行。
范围锁定临键锁不仅锁定具体的行还锁定行之间的间隙防止其他事务在该范围内插入新行。
工作原理
锁定范围临键锁锁定的是一个范围包括索引中的具体行和行之间的间隙。例如如果索引中有两个相邻的值1和3临键锁可以锁定 [1, 3] 这个范围防止其他事务在此范围内插入新行或修改现有行。防幻读临键锁可以有效防止幻读问题确保事务在整个生命周期内看到相同的数据版本。
应用场景
可重复读隔离级别在可重复读隔离级别下临键锁是默认的锁机制用于防止其他事务在查询结果集中插入新行或修改现有行。唯一约束在插入新行时InnoDB会自动加临键锁确保唯一约束不会被违反。
6、记录锁Record Locks
记录锁是锁定单个索引记录的锁防止其他事务修改或删除该记录。记录锁是最常见的行级锁类型。
锁定单行记录锁只锁定具体的行而不锁定行之间的间隙。
工作原理
SELECT … FOR UPDATE在读取数据时加写锁防止其他事务修改或删除这些行。SELECT … LOCK IN SHARE MODE在读取数据时加读锁允许其他事务读取相同的数据但不允许修改或删除。INSERT、UPDATE、DELETE在修改数据时自动加写锁确保同一行不会被多个事务同时修改。
应用场景
更新操作在修改数据时InnoDB会自动加记录锁确保同一行不会被多个事务同时修改。读取操作在读取数据时可以根据需要显式加记录锁确保数据的一致性。
7、自动增长锁Auto-Increment Locks
自动增长锁用于管理AUTO_INCREMENT列的值分配确保多个事务不会生成重复的自增值。
传统模式在传统模式下InnoDB会对整个表加表级锁确保只有一个事务可以分配AUTO_INCREMENT值。这种方式虽然简单但会严重限制并发性。互斥锁模式从MySQL 5.1开始InnoDB引入了互斥锁模式允许多个事务并发分配AUTO_INCREMENT值而不需要对整个表加锁。
配置参数 innodb_autoinc_lock_mode
0传统模式对整个表加表级锁确保只有一个事务可以分配AUTO_INCREMENT值。适用于需要严格顺序的场景但并发性较差。1连续模式允许多个事务并发分配AUTO_INCREMENT值但可能会出现跳号现象。适用于大多数场景推荐使用。2交错模式允许多个事务并发分配AUTO_INCREMENT值且不会出现跳号现象。适用于高并发场景但可能会导致自增值不连续。
应用场景
高并发插入在高并发插入场景下建议使用innodb_autoinc_lock_mode 1或2以提高并发性能。严格顺序要求如果应用对AUTO_INCREMENT值的顺序有严格要求建议使用innodb_autoinc_lock_mode 0但要注意并发性较低。
8、元数据锁Metadata Locks
元数据锁用于管理对表结构的修改操作确保在事务执行期间表结构不会发生变化。元数据锁可以防止多个事务同时对同一张表进行DDL操作或者在事务执行期间对表结构进行修改。
表结构保护元数据锁确保在事务执行期间表结构不会被修改防止数据不一致问题。DDL操作保护元数据锁可以防止多个事务同时对同一张表进行DDL操作确保表结构的一致性。
工作原理
读锁当事务读取表时InnoDB会为该表加读元数据锁允许其他事务读取相同的数据但不允许修改表结构。写锁当事务修改表结构时InnoDB会为该表加写元数据锁阻止其他事务对该表进行任何读写操作直到DDL操作完成。
应用场景
DDL操作在执行DDL操作如ALTER TABLE时InnoDB会自动加写元数据锁确保表结构的一致性。事务执行期间在事务执行期间InnoDB会为涉及的表加读元数据锁防止其他事务修改表结构确保数据的一致性。
9、死锁检测与处理
1、死锁检测
InnoDB自动检测死锁并选择回滚其中一个事务来解决死锁问题。InnoDB会定期检查是否存在循环等待的情况来发现死锁。
死锁检测频率InnoDB会在每次等待锁时检查是否存在死锁。如果检测到死锁InnoDB会选择回滚代价最小的事务。回滚策略InnoDB会根据事务的大小、已修改的数据量等因素选择回滚代价最小的事务以减少对系统的影响。
2、死锁预防
虽然InnoDB提供了自动的死锁检测机制但实际过程中还是建议避免频繁的死锁发生。
建议采取以下措施
尽量减少事务的持有时间尽量缩短事务的持续时间减少锁的持有时间降低死锁发生的概率。按固定顺序加锁在多个事务中尽量按照相同的顺序加锁避免交叉加锁导致的死锁。使用合适的隔离级别根据应用场景选择合适的隔离级别避免不必要的锁竞争。例如在不需要严格一致性的场景下可以选择读已提交Read Committed隔离级别减少锁的使用。
10、锁机制总结
InnoDB的锁机制是其事务处理和并发控制的核心组成部分。通过行级锁、表级锁、意向锁、间隙锁、临键锁等多种锁类型InnoDB能够有效地管理对数据的访问确保数据的一致性和并发性。
行级锁提供细粒度的锁控制允许多个事务同时对不同行进行操作提升并发性能。表级锁适用于全表扫描或批量操作但在大多数情况下行级锁更为常用。意向锁用于表明事务打算在表的某些行上加锁帮助提前检测潜在的锁冲突。间隙锁和临键锁用于防止幻读问题确保事务在整个生命周期内看到相同的数据版本。自动增长锁用于管理 AUTO_INCREMENT 列的值分配确保多个事务不会生成重复的自增值。元数据锁用于管理对表结构的修改操作确保在事务执行期间表结构不会发生变化。
6、InnoDB事务与并发控制
InnoDB是MySQL的默认存储引擎提供了强大的事务支持和并发控制机制。事务确保了数据操作的原子性、一致性、隔离性和持久性ACID而并发控制则允许多个事务同时对数据库进行操作而不影响数据的一致性。
1、事务的基本概念
1、事务的ACID特性
事务是数据库中一组逻辑操作的集合。
它具有以下四个重要特性
原子性Atomicity事务中的所有操作要么全部成功要么全部失败。如果事务中的任何一个操作失败整个事务都会被回滚确保数据库状态的一致性。一致性Consistency事务执行前后数据库必须保持一致的状态。事务不能破坏数据库的完整性约束如外键、唯一性等。隔离性Isolation多个事务并发执行时每个事务都应独立运行互不干扰。事务的隔离性由隔离级别决定。持久性Durability一旦事务提交其对数据库的修改将永久保存即使系统崩溃也不会丢失。
2、事务的生命周期
一个典型的事务生命周期包括以下几个阶段
开始事务使用START TRANSACTION或BEGIN命令显式开始一个事务。执行操作在事务中执行一系列SQL操作如INSERT、UPDATE、DELETE等。提交事务使用COMMIT命令提交事务将所有修改永久保存到数据库中。回滚事务如果事务执行过程中发生错误可以使用ROLLBACK命令回滚事务撤销所有修改。
2、事务的隔离级别
事务的隔离级别决定了多个事务并发执行时的可见性和行为。InnoDB支持四种标准的隔离级别分别是
读未提交Read Uncommitted读已提交Read Committed可重复读Repeatable Read串行化Serializable
1、读未提交Read Uncommitted
定义允许事务读取其他事务尚未提交的数据即“脏读”。这是最低的隔离级别可能会导致数据不一致问题。优点并发性能最高因为没有锁等待。缺点可能会读取到未提交的脏数据导致数据不一致。适用场景适用于对数据一致性要求不高的场景例如统计分析或报表生成。
2、读已提交Read Committed
定义事务只能读取已经提交的数据无法读取未提交的数据。这是大多数数据库系统的默认隔离级别。优点避免了脏读问题确保事务只能看到已提交的数据。缺点可能会出现不可重复读Non-repeatable Read和幻读Phantom Read问题。适用场景适用于大多数应用场景尤其是对数据一致性有一定要求但不需要严格隔离的场景。
3、可重复读Repeatable Read
定义事务在整个生命周期内看到的数据版本是一致的不会受到其他事务的影响。这是InnoDB的默认隔离级别。优点避免了脏读、不可重复读和幻读问题确保事务在整个生命周期内看到相同的数据版本。缺点可能会导致更多的锁竞争尤其是在高并发场景下。适用场景适用于对数据一致性要求较高的场景例如金融交易、库存管理等。
4、串行化Serializable
定义事务以串行的方式执行完全避免了并发问题。这是最高的隔离级别确保事务之间不会相互影响。优点提供最严格的隔离性确保数据的一致性。缺点并发性能最低因为事务需要排队执行可能会导致大量锁等待。适用场景适用于对数据一致性要求极高的场景例如银行转账、证券交易等。
5、隔离级别的选择
innodb_default_isolation_level可以通过该参数设置InnoDB的默认隔离级别。默认值为REPEATABLE READ。动态设置隔离级别可以在会话级别或事务级别动态设置隔离级别。 示例: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;3、并发控制机制
InnoDB使用多种并发控制机制来确保多个事务能够安全地并发执行主要包括锁机制和多版本并发控制MVCC。
1、锁机制
InnoDB的锁机制用于防止多个事务同时对同一数据进行冲突的操作。锁分为行级锁和表级锁具体包括以下几种类型
记录锁Record Locks锁定单个索引记录防止其他事务修改或删除该记录。间隙锁Gap Locks锁定索引中的一个区间即“间隙”防止其他事务在这个区间内插入新行。临键锁Next-Key Locks结合了记录锁和间隙锁锁定一个范围内的所有行和间隙防止其他事务在该范围内插入新行或修改现有行。InnoDB默认的行级锁意向锁Intention Locks表明事务打算在表的某些行上加锁帮助提前检测潜在的锁冲突。表级锁Table-Level Locks锁定整个表阻止其他事务对该表进行任何操作。自动增长锁Auto-Increment Locks用于管理AUTO_INCREMENT列的值分配确保多个事务不会生成重复的自增值。元数据锁Metadata Locks用于管理对表结构的修改操作确保在事务执行期间表结构不会发生变化。
2、多版本并发控制MVCC
多版本并发控制MVCC是InnoDB实现高并发的核心机制之一。通过MVCCInnoDB可以允许多个事务同时读取和写入数据而不会相互阻塞。MVCC的主要思想是为每个事务提供一个独立的快照视图确保事务在整个生命周期内看到相同的数据版本。
1、MVCC的工作原理 隐藏列InnoDB在每个行中添加了两个隐藏列用于实现MVCC DB_TRX_ID记录最后一次修改该行的事务ID。DB_ROLL_PTR指向回滚段Undo Log的回滚指针用于存储该行的历史版本。 读视图每个事务都有一个读视图记录了事务开始时的系统状态。读视图包含了以下信息 创建读视图时活跃的事务列表。创建读视图时的最大已提交事务ID。 一致性非锁定读对于只读查询如SELECTInnoDB使用一致性非锁定读Consistent Non-Locking Read即读取数据时不加锁。InnoDB会根据读视图判断是否应该返回当前版本的数据还是从回滚段中查找历史版本的数据。 一致性锁定读对于修改操作如SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODEInnoDB会加锁确保数据的一致性。
2、MVCC的隔离级别支持
读未提交Read Uncommitted不使用MVCC允许读取未提交的数据。读已提交Read Committed每个事务都有自己的读视图但每次读取数据时都会重新生成一个新的读视图。因此事务可以看到其他事务提交后的最新数据。可重复读Repeatable Read每个事务在其生命周期内只有一个读视图确保事务在整个生命周期内看到相同的数据版本。串行化Serializable不使用MVCC所有事务以串行的方式执行完全避免了并发问题。
3、MVCC的优势
提高并发性能通过MVCCInnoDB可以允许多个事务同时读取和写入数据而不会相互阻塞从而提高并发性能。减少锁竞争一致性非锁定读Consistent Non-Locking Read允许只读查询不加锁减少了锁的竞争提升了系统的吞吐量。防幻读在可重复读隔离级别下InnoDB使用间隙锁Gap Locks和临键锁Next-Key Locks来防止幻读问题确保事务在整个生命周期内看到相同的数据版本。
4、优化并发性能
为了提高InnoDB的并发性能建议采取以下优化措施 1、减少事务的持锁时间
尽量缩短事务的持续时间事务持有的时间越长锁的竞争就越激烈。尽量将事务的范围缩小到最小避免不必要的操作。批量处理数据如果需要对大量数据进行修改可以考虑分批处理减少单个事务的持有时间。 2、选择合适的隔离级别根据应用场景选择隔离级别不同的隔离级别有不同的锁开销和并发性能。对于大多数应用场景REPEATABLE READ是一个合理的选择但在不需要严格一致性的场景下可以选择READ COMMITTED来减少锁的竞争。避免使用SERIALIZABLE除非绝对必要否则尽量避免使用SERIALIZABLE隔离级别因为它会导致大量的锁等待严重影响并发性能。 3、优化锁粒度使用行级锁InnoDB默认使用行级锁允许多个事务同时对不同行进行操作。相比于表级锁行级锁的粒度更细能够有效减少锁冲突提升并发性能。避免不必要的锁在查询中尽量避免使用FOR UPDATE或LOCK IN SHARE MODE除非确实需要加锁。不必要的锁会增加锁竞争降低并发性能。 4、优化索引设计使用覆盖索引覆盖索引是指查询所需的所有列都在索引中而不需要回表查询。通过使用覆盖索引可以减少I/O操作提升查询性能。避免索引过多过多的索引会增加插入、更新和删除操作的开销降低并发性能。建议根据实际查询需求合理设计索引。 5、优化日志配置增大重做日志文件大小通过增大innodb_log_file_size参数可以减少日志切换的频率提升写性能。调整innodb_flush_log_at_trx_commit根据应用程序对数据安全的要求选择合适的innodb_flush_log_at_trx_commit值。如果你的应用对性能要求较高且可以容忍少量数据丢失可以选择2或0如果你的应用对数据安全要求较高建议保持默认值1。 6、启用并行查询使用并行查询从MySQL 8.0开始InnoDB支持并行查询Parallel Query可以在多个线程中并行执行查询操作提升查询性能。可以通过innodb_parallel_read_threads参数启用并行查询。
5、InnoDB并发控制总结
InnoDB的事务管理和并发控制机制是其高性能和高可靠性的核心组成部分。通过事务的 ACID特性、隔离级别、锁机制和多版本并发控制MVCCInnoDB能够确保多个事务安全地并发执行同时保持数据的一致性和完整性。
事务的ACID特性确保事务的原子性、一致性、隔离性和持久性。隔离级别通过不同的隔离级别平衡并发性能和数据一致性。InnoDB默认使用REPEATABLE READ但在某些场景下可以选择READ COMMITTED来减少锁竞争。锁机制InnoDB使用行级锁、间隙锁、临键锁等多种锁类型确保多个事务能够安全地并发执行。多版本并发控制MVCC通过MVCCInnoDB可以允许多个事务同时读取和写入数据而不会相互阻塞从而提高并发性能。
乘风破浪会有时直挂云帆济沧海
