存储引擎的架构：

存储引擎是MySQL数据库底层软件组件负责执行数据的存储和检索操作是MySQL区别于其他数据库的核心特性之一。MySQL采用插件式存储引擎架构不同存储引擎提供不同的存储机制、索引技术、锁定水平等功能用户可以根据业务需求灵活选择。通俗点讲存储引擎决定了数据在磁盘上的存储方式和访问方式不同的存储引擎实现了不同的存储和检索算法存储引擎就是数据库服务中的文件系统用户可以根据应用的需要选择存储和索引数据。存储引擎的架构-------------------------| 连接/会话层 |-------------------------| SQL解析/优化层 |-------------------------| 存储引擎接口层 |-------------------------| InnoDB | MyISAM | Memory | ... |-------------------------上层SQL层与存储引擎层通过标准接口交互上层不需要关心存储引擎的具体实现不同存储引擎之间相互独立各自实现自己的特性支持同一数据库中不同表使用不同存储引擎存储引擎的简单运维操作查看数据库中所有引擎种类mysql show engines;-----------------------------------------------------------------------------------------------------------------------------| Engine | Support | Comment | Transactions | XA | Savepoints |-----------------------------------------------------------------------------------------------------------------------------| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO |-----------------------------------------------------------------------------------------------------------------------------9 rows in set (0.00 sec)查看数据库默认引擎mysql select default_storage_engine;--------------------------| default_storage_engine |--------------------------| InnoDB |--------------------------1 row in set (0.00 sec)查看某张表的存储引擎show create table 表名;如果你想修改存储引擎全局修改# 修改配置文件然后重启mysql即可vim /etc/my.cnf# 修改下面的配置[mysqld]default_storage_engineInnoDB针对某一张表修改#创建表时设置create table xxx (id int) engineinnodb charsetutf8mb4;#创建表之后设置alter table xxx enginemyisam;alter table world.xxx engineinnodb;回到顶部存储引擎分类目前mysql有很多种存储引擎但是99%的场景直接选择InnoDB现在InnoDB已经非常成熟性能比MyISAM更高功能更全除非有非常明确的理由否则都用InnoDB。但是为了学习我们在这里还是简单说一下所有的存储引擎InnoDB这是mysql5.5版本中默认的存储引擎其核心特性如下事务支持完全支持ACID特性支持4种事务隔离级别是需要事务支持的业务首选行级锁定粒度更小的行锁并发性能远高于表锁适合高并发读写场景MVCC多版本并发控制读写不阻塞大幅提升高并发场景下的性能外键约束唯一支持外键的存储引擎保证数据的一致性和完整性崩溃恢复通过redo log和undo log实现 crash-safe异常重启后数据不会丢失聚簇索引数据和主键索引存储在一起主键查询性能极高支持热备份可以在服务运行时进行备份不影响业务其优缺点优点缺点事务支持数据安全性高空间占用较高存储成本略高行级锁并发性能好不支持全文索引MySQL 5.6开始支持但功能较弱崩溃恢复能力强批量插入性能略低于MyISAM支持外键、MVCC维护成本略高其适用场景绝大多数OLTP在线事务处理场景需要事务支持、数据一致性要求高的业务高并发读写的场景电商、金融、社交等对数据可靠性要求高的核心业务MyISAM这是MySQL 5.5之前默认引擎现在版本已经很新了使用这个存储引擎的公司应该已经很少了。核心特性如下表级锁定读共享、写独占写操作会锁全表并发写入性能差全文索引原生支持全文索引适合全文搜索场景压缩存储支持压缩表占用空间小适合只读数据查询性能高在数据量不大、读写比很高的场景下查询性能优于InnoDB不支持事务、不支持行锁和外键、崩溃后无法安全恢复可能丢失数据优缺点如下优点缺点占用空间小存储成本低不支持事务数据安全性差只读场景下性能极高表级锁并发写入性能差全文索引支持崩溃后数据易丢失维护简单资源消耗低不支持外键、行锁其适用场景非核心的只读或者读多写极少的场景数据可以通过其他方式恢复的业务比如统计报表、日志归档需要全文搜索的场景现在更推荐用Elasticsearch替代对性能要求极高、数据可靠性要求低的非核心场景MemoryHEAP这是一个将数据存储在内存中的引擎现在基本都是使用redis做缓存所以这个存储引擎使用极低了解即可其核心特性数据存储在内存中访问速度极快响应时间微秒级默认哈希索引等值查询性能极高表级锁定并发写入性能差服务重启后数据全部丢失不支持TEXT、BLOB等大字段varchar最大长度65535内存资源有限不适合存储大量数据适用场景临时数据存储比如 session 数据、一次性统计数据缓存场景热点数据缓存替代Redis的轻量级方案高频读、低频写的小数据量场景Archive这款引擎适合存储海量的归档数据例如日志数据等但是这种类型的数据基本都是用elasticsearch存储所以实际工作中这款引擎使用率也极低了解即可。核心特性高压缩比存储采用zlib压缩空间占用仅为InnoDB的1/5~1/10高写入性能支持批量插入写入性能极高仅支持INSERT和SELECT操作不支持UPDATE、DELETE不支持索引除了自增ID索引不支持事务适用场景日志数据归档、历史数据冷存储不需要修改的海量数据存储场景写入后只做批量查询的场景CSV这款存储引擎适合小数据量适合将外部CSV格式数据到MySQL现在一般不使用这款引擎了解即可。核心特性如下数据以CSV文本格式存储可以直接用Excel等工具打开编辑支持导入导出CSV格式数据不支持索引不支持NULL值不支持事务适用场景数据交换、导入导出中间格式快速导入外部CSV格式数据到MySQL简单的小数据量存储场景Blackhole这款引擎简称黑洞引擎正如名字一样写入的数据会被吞掉丢弃不实际存储读取数据永远返回空值但是binlog日志会正常记录写入的SQL一般作为主从复制的中继节点很少使用了解即可。适用场景作为主从复制的中继节点过滤不需要同步的数据性能测试时的压测目标验证写入性能测试二进制日志功能MergeMRG_MyISAM这款引擎会把多个结构相同的MyISAM表合并成一个逻辑表透明查询和写入不需要关心底层分表仅支持MyISAM表功能十分有限不支持事务适用场景简单的分表场景合并多个历史数据表日志分表后的统一查询场景Federated这款引擎可以访问远程MySQL数据库中的表本地不存储数据所有操作转发到远程节点因为网络开销较大所以性能较低稳定性一般很多特性不支持适用场景跨库访问、数据联邦查询场景临时的跨实例数据访问需求NDB Cluster这款引擎主要用于电信、金融等需要极高可用性的核心场景但是在此类场景下一般不使用mysql所以这个引擎使用极低知道即可。回到顶部核心存储引擎对比InnoDB vs MyISAM此问题一般都是面试的时候会问在实际工作中一般都是无脑选择InnoDB当然一些极特殊情况例外。对比维度InnoDBMyISAM事务支持✅ 支持ACID❌ 不支持锁机制✅ 行级锁、gap锁、next-key lock❌ 仅表级锁MVCC✅ 支持❌ 不支持外键✅ 支持❌ 不支持索引结构✅ 聚簇索引❌ 非聚簇索引崩溃恢复✅ 支持crash-safe❌ 易丢失数据全文索引⚠️ 5.6支持功能弱✅ 原生支持功能强存储空间较高支持压缩较低压缩比更高内存占用较高较低批量写入性能一般更高并发性能极高支持高并发读写低写操作阻塞所有操作适用场景绝大多数OLTP核心场景只读、非核心场景回到顶部InnoDB详解磁盘结构组成在磁盘存储结构中会使用表空间模式进行数据信息的管理经常提到的段区页概念也是属于表空间的逻辑结构。表空间的概念源于Oracle数据库最初的目的是为了能够更好的做存储的扩容因此数据库的表空间技术类似磁盘管理的lvm技术共享表空间共享表空间是ibdata1这个文件作用MySQL8.0.20之前存储Double write buffer信息、changer buffer信息MySQL8.0.20之后只存储changer buffer信息Double write buffer信息被独立出来了MySQL5.7版本存储全局数据字典信息、undo回滚日志、Double write buffer信息、changer buffer以及系统数据共享表空间的运维操作命令查看共享表空间#查看共享表空间mysql select innodb_data_file_path;-------------------------| innodb_data_file_path |-------------------------| ibdata1:12M:autoextend |-------------------------1 row in set (0.00 sec)#查看共享表空间默认扩展大小默认每次扩展64Mmysql select innodb_autoextend_increment;-------------------------------| innodb_autoextend_increment |-------------------------------| 64 |-------------------------------1 row in set (0.00 sec)共享表空间的扩容操作# 编写数据库配置文件信息vim /etc/my.cnf[mysqld]innodb_data_file_pathibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend-- 需要注意的是ibdata1文件大小必须和实际数据库要存储的数据相匹配否则会出现如下报错信息[ERROR] [MY-012264] [InnoDB] The innodb_system data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the .cnf file!-- 表示ibdate1指定大小超过了原有ibdata1实际的大小尺寸共享表空间的初始设置方式# 初始化配置文件rootmaster:~# vim /etc/my.cnf[mysqld]innodb_data_file_pathibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend# 模拟初始化操作命令rootmaster:~# mysqld --initialize-insecure --usermysql --basedir/usr/local/mysql --datadir/data/3306/data# 模拟初始化重启服务rootmaster:~# /etc/init.d/mysqld start生产环境下共享表空间容量推荐MySQL版本共享表空间数量初始推荐大小额外配置5.72~3 个512M / 1G最后一个开启自动扩展8.01 个512M / 1G无需多文件默认管理独立表空间独立表空间是以ibd结尾的文件例如其作用主要是存储表中的数据信息索引信息 表结构 表中行数据。独立表空间操作#表示每个表就是一个独立文件进行数据信息的独立存储不建议进行修改如果改为0就是所有数据统一存储在共享表空间select innodb_file_per_table;---------------------------------| innodb_file_per_table |---------------------------------| 1 |---------------------------------#设置为0表示利用共享表空间存储用户数据 1表示利用独立表空间存储用户数据set global innodb_file_per_table0利用独立表空间进行数据快速迁移源端 3306/test/t100w -- 目标端 3307/test/t100w# 步骤一锁定源端t100w表# 给t100w表加写数据锁mysql lock tables test.t100w write;# 获取创建表结构数据信息mysql show create table test.t100w;CREATE TABLE t100w (id int DEFAULT NULL,num int DEFAULT NULL,k1 char(2) DEFAULT NULL,k2 char(4) DEFAULT NULL,dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,KEY idx (k1,k2,num)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |#步骤二目标端创建oldboy库和t100w空表# 创建新的数据库mysql create database test;# 创建新的数据表mysql CREATE TABLE t100w (id int DEFAULT NULL,num int DEFAULT NULL,k1 char(2) DEFAULT NULL,k2 char(4) DEFAULT NULL,dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,KEY idx (k1,k2,num)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci#步骤三删除目标端空的表空间文件# 删除t100w表的ibd数据文件信息但是保留t100w的frmibdata1中关于t100w的系统数据mysql alter table test.t100w discard tablespace;#操作步骤四拷贝源端ibd文件到目标端目录并设置权限rootmaster: ~# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/rootmaster: ~# chown -R mysql.mysql /data/*#操作步骤五导入表空间# 在目标端加载识别迁移过来的数据文件信息mysql alter table test.t100w import tablespace;# 查看数据表中是否有迁移过来的数据信息mysql select count(*) from t100w;----------| count(*) |----------| 1000000 |----------1 row in set (0.04 sec)#步骤六解锁源端数据库mysql unlock tables;undo表空间在数据库5.7版本中默认存储在共享表空间中ibdata在数据库8.0版本后默认就是独立存储了(undo_001-undo_002)undo表空间的作用在数据库5.7版本中默认存储在共享表空间中ibdata在数据库8.0版本后默认就是独立存储了(undo_001-undo_002)在实际生产环境中建议在5.7版本之后都将undo表空间进行独立文件存储。查看临时表空间相关命令# 设置undo表空间文件数量默认2个文件mysql select innodb_undo_tablespaces;---------------------------| innodb_undo_tablespaces |---------------------------| 2 |---------------------------1 row in set (0.00 sec)# 设置undo表空间文件大小字节mysql select innodb_max_undo_log_size;----------------------------| innodb_max_undo_log_size |----------------------------| 1073741824 |----------------------------1 row in set (0.00 sec)# 设置undo表空间回收机制默认开启mysql select innodb_undo_log_truncate;----------------------------| innodb_undo_log_truncate |----------------------------| 1 |----------------------------1 row in set (0.00 sec)# 设置undo表空间信息检测次数 128次mysql select innodb_purge_rseg_truncate_frequency;----------------------------------------| innodb_purge_rseg_truncate_frequency |----------------------------------------| 128 |----------------------------------------1 row in set (0.00 sec)修改undo表空间配置# 关闭数据库服务程序清理数据库服务数据目录rootmaster:~# vim /etc/my.cnf[mysqld]innodb_undo_tablespaces3innodb_max_undo_log_size128Minnodb_undo_log_truncateONinnodb_purge_rseg_truncate_frequency32临时表空间临时表空间可以存储在内存和磁盘上主要用于存储临时的表信息主要是在使用group byorder byhavingunion all子查询等情况都会使用临时表重新加载临时数据时可以从临时表空间文件中直接读取顺序IO读取数据临时表空间查看操作命令临时表空间默认初始化大小12M不够用会自动扩容每次扩容64Mmysql select innodb_temp_data_file_path;------------------------------| innodb_temp_data_file_path |------------------------------| ibtmp1:12M:autoextend |------------------------------1 row in set (0.00 sec)mysql select innodb_autoextend_increment;-------------------------------| innodb_autoextend_increment |-------------------------------| 64 |-------------------------------1 row in set (0.00 sec)建议初始化时设定好临时表空间mysql5.7版本建议设置2~3个临时表空间大小建议512M或1G最后一个定制为自动扩展mysql8.0版本建议设置1个即可大小建议512M或者1G。vim /etc/my.cnf[mysqld]innodb_temp_data_file_pathibtmp1:12M;ibtmp2:120M:autoextend:max:500Mredo事物日志redo log属于事务重做日志文件主要用于记录内存数据页的变化记录在内存中对数据页的操作信息都会以日志文件方式记录查看redo事物日志mysql show variables like %innodb_log_file%;-------------------------------------| Variable_name | Value |-------------------------------------| innodb_log_file_size | 50331648 || innodb_log_files_in_group | 2 |-------------------------------------2 rows in set (0.01 sec)在实际生产中文件大小建议512M-1G应用组数设置2-4# 编写数据库配置文件信息vim /etc/my.cnf[mysqld]innodb_log_file_size100Minnodb_log_files_in_group3ib_buffer_pool预热文件ib_buffer_pool预热文件可用于缓冲和缓存可以存储‘热’数据页减少物理IO性能损耗。内存结构组成其内存架构主要由缓冲池和日志缓冲组成其结构如下-------------------------| Buffer Pool缓冲池 || - 数据页缓存 || - 索引页缓存 || - 自适应哈希索引 || - 插入缓冲 || - 锁信息、数据字典等 |-------------------------| Log Buffer日志缓冲 |-------------------------Buffer PoolBuffer Pool是InnoDB最重要的内存组件默认占用物理内存的50%~70%缓存热点数据和索引减少磁盘IO。采用LRU算法管理缓存页。是MySQL中最大的、最重要的内存区域。查看buffer poolmysql select innodb_buffer_pool_size;---------------------------| innodb_buffer_pool_size |---------------------------| 1073741824 |---------------------------1 row in set (0.00 sec)设置buffer pool# buffer pool默认内存空间大小为128M生产建议大小可以设置为物理内存总量的50%~80%方式一set global innodb_buffer_pool_size268435456;方式二vim /etc/my.cnf[mysqld]innodb_buffer_pool_size256MLog Buffer缓存redo log日志定期刷入磁盘减少日志写入IO。Log Buffer建议设置64M查看log buffermysql select innodb_log_buffer_size;--------------------------| innodb_log_buffer_size |--------------------------| 16777216 |--------------------------1 row in set (0.00 sec)修改log buffer方式一set global innodb_log_buffer_size33554432;方式二vim /etc/my.cnf[mysqld]innodb_log_buffer_size32M回到顶部InnoDB性能优化缓冲池配置innodb_buffer_pool_size设置为物理内存的50%~70%越大越好日志配置innodb_log_file_size设置为1GB~4GBinnodb_log_buffer_size设置为64MB刷新策略innodb_flush_log_at_trx_commit1最高安全性或2最高性能最多丢失1秒数据IO优化使用SSD存储innodb_io_capacity设置为磁盘的IOPS能力索引优化合理设计索引避免回表使用覆盖索引本文来自博客园作者huangSir-devops转载请注明原文链接MySQL中的存储引擎及InnoDB简单介绍 - huangSir-devops - 博客园微信Vac6666666欢迎交流分类: 数据库 / MySQL免责声明本内容来自平台创作者博客园系信息发布平台仅提供信息存储空间服务。好文要顶 关注我 收藏该文 微信分享huangSir-devops粉丝 - 43 关注 - 1加关注00« 上一篇 ElasticSearch中的索引模板详解» 下一篇 mysql备份恢复详解posted 2026-05-06 11:24 huangSir-devops 阅读(213) 评论(0) 收藏 举报