MySQL的存储引擎

傷城~ 2022-05-18 08:14 264阅读 0赞

查看当前MySQL数据库支持的引擎：show engines;

mysql引擎特点：插件式，可混用，可定制

常见mysql引擎介绍：

1、MEMORY

内存表，只有一个.frm文件，用于存储表结构；只有定义，绝无数据；

memory引擎的表数据常驻内存，删除记录后内存可被该表重用，但不能被其他对象或线程使用；

表数据重启mysql后丢失，

查看表状态：show table status like 't\_mem%';

2、CSV Comma Separated Values 逗号分隔

所有列均必须强制指定NOT NULL、不支持索引、不支持分区

.frm 表结构

.CSM 表状态及数据量

.CSV 表数据 可直接被编辑（不建议）；

内容损坏可使用CHECK TABLE或REPAIR TABLE检查和修复；

修复时只要发现有损坏的行，那之后的数据会全部丢失，不管这中间是否有合法数据

类似于Oracle的外部表

3、ARCHIVE 只管存，不管删

适用于将大量数据压缩存储，插入的列会被zlib压缩，压缩比例可达到8:1（与MyISAM相比）

仅支持insert和select，支持order by，支持行级锁，不支持索引，插入效率高

.frm .arz(数据文件) .arn(优化时)

4、BLACKHOLE 只管入，不管存

只有.frm文件；

如果启用了binlog，执行的SQL会被记录，可应用于主从复制；

结合replicate-do-\*或replicate-ignore-\*规则，可实现日志过滤，在slave端产生子集数据

5、MERGE

将一组MyISAM表聚合在一起，使用时就像一张表

基表要求：相同的列和索引信息，甚至列的定义和顺序、索引的顺序都必须完全一样

.frm

.mgr 不保存数据，保存数据的来源地(基表的列表)

支持select、update、delete，insert需在创建表时通过insert\_method指定插入到哪张表

6、FEDERATED

跨MySQL实例访问数据，类似于Oracle的dblink，MySQL默认安装不支持

只有.frm，定义表结构和目标端的连接信息，本地不保存数据

7、MyISAM

.frm

.MYD 数据文件

.MYI 索引文件

单表列长度之和不能超过65532B

缺点：表级锁、不支持事务

优点：快，查询快，写入快！

存储格式：定长(fixed 静态)、动态(dynamic)、压缩(compressed)

8、InnoDB 5.5版本后默认引擎

8.1 遵循ACID模型，支持事务，支持行级锁，拥有服务器故障恢复能力，有独立的缓冲池

所有表都要创建主键，最好选择常作为查询条件的列

join多表关联查询时，最好在join列创建索引，且该列最好有相同的数据类型和定义

8.2

.frm 表定义

系统表空间 数据及索引默认存储文件，支持多个数据文件

缺点：数据文件扩展后无法回缩，即使表被删除，释放的空间仅供innodb使用

回收办法：导出数据，重建数据文件，重启mysql，导入数据

多重表空间 multiple tablespaces 5.6版本后默认启用，之前版本可启用，后续操作生效

.ibd 相当于MyISAM的.MYI+.MYD

各表对象的数据独立存储至不同的文件

支持compressed row format

truncate/drop表时，空间释放回系统层面

空间自动扩展

启用参数：show variables like 'innodb\_file\_per\_table' 1(ON) 0(OFF)

不能直接移动表空间文件，因为表结构、所属数据库等信息存在于系统表空间

同一MySQL实例下不同数据库间移动表可使用rename table A.a to B.a;

日志文件 redolog(innodb专用) binlog(mysql通用日志)

8.3 关闭模式：0、1、2 innodb\_fast\_shutdown

0 等到会话关闭、所有事务结束、缓存区数据刷新至磁盘

相当于oracle的 shutdown immediate

1 快速关闭 默认 关闭会话 中止连接 已提交数据保存至数据文件，未提交事务则回滚

相当于oracle的shutdown normal

2 忽略当前操作，直接关闭，启动时innodb需要执行故障恢复

相当于oracle的shutdown abort

undo表空间 回滚日志，保存被修改数据的前映像

默认为系统表空间，5.6版本之后可以单独设置表空间，涉及变量：innodb\_undo\_%

必须在数据库创建之前指定，一旦创建不能删除

8.4 关于事务的启用

如果一个库里表采用不同的引擎，因其他引擎不支持事务，这时可能要求禁用事务；

选择不支持事务的对象，还是选择事务，取决于具体需求；

禁用事务的自动提交：set autocommit=off; 会话级参数

全局禁用autocommit：

set global init\_connect='SET autocommit=0'; 重启失效

配置文件\[mysqld\]加入参数：init\_connect='SET autocommit=0' 重启生效

启动时带上参数：-init\_connect='SET autocommit=0' 启动生效

关闭autocommit后就可以实现rollback功能了

其实，如果显示声明事务，则不论自动提交是否开启，事务的提交、回滚均由程序控制。

8.5 引擎的转换

可使用alter table实现引擎的转换，但需要注意：

MySQL系统表仅支持MyISAM；

引擎转换的本质是结构和数据的重建，大表操作时需充分评估作业时间；

加大Innodb缓冲池，调整日志文件大小，可增加转换速度；

评估磁盘空间，Innodb比MyISAM磁盘占用更多，空间不足将自动回滚，耗时更长；

表记录数、空间估值查看：show table status或查询information\_schema.tables

8.6 逻辑存储结构

页、块、page 默认16KB 一条记录(变长列除外)最多只能占半页，即8KB，超出后存到溢出页

扩展、区、extents 1MB

段 segments 独立表空间下，对应数据文件

表空间 tablespaces

一张表的有效空间占用估算：平均记录长度\*记录数\*2，如果超出太多，考虑进行碎片清理

8.7 多版本机制

保存一定数量的记录修改的历史版本，以支持回滚和一致性读，信息保存在回滚段；

回滚段中的undo日志分为insert undo和update undo分别支持回滚和读一致性

多版本下delete操作只是把记录标识为被删除状态，最终由purge线程进行物理删除

8.8 联机修改表结构

5.6版本之前，表对象DDL操作处理流程：

创建一个与原表结构一样的临时表

锁定原表，只读不改

复制原表数据到临时表 耗时可能很长

将原表改名，临时表改为原表，释放锁定，删除原表

5.6版本之后的联机DDL场景

场景分四种参考要素：

就地操作 In-Place 最好为YES

复制表 Copies Tables 最好为NO

同时更新/删除 最好为YES

同时查询 最好为YES

联机DDL的两个子句：

LOCK default none shared exclusive

ALGORITHM default inplace copy

8.9 InnoDB表对象的限制

最大和最小

单表最多1020列

单列索引(包括前缀索引)默认长度不超过767B，通过innodb\_large\_prefix能提高到3072B

3072B的键值长度限制同样适用于符合索引，且与page size有关，此为16KB的

通过innode\_page\_size修改page size后，键值最大长度对应调整

每条记录的最大长度要小于数据页的一般，及默认小于8KB

Innodb日志文件不超过512GB，系统表空间至少10MB，最大64TB（单表最大不超过64TB）

其他

analyze table收集的是索引统计信息，除物理大小外，其他为估算值，所以每次分析结果可能有差异，故有此生成的执行计划也可能不是最优，可通过force index指定索引

show table status显示的也是预估值

windows系统，innodb使用小写名称保存数据库和表名，建议统一为小写

合理选择auto\_increment列的整型，避免超出

级联外键不会触发triggers

MySQL的存储引擎

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【MySQL】MySQL的存储引擎

相关 mysql的存储引擎

相关 php中mysql存储引擎_MySQL存储引擎

相关【MySQL】存储引擎（一）：存储引擎介绍

相关【MySQL】MySQL的存储引擎

相关 MySQL的存储引擎

相关 MySQL的存储引擎

相关 MYSQL存储引擎

相关 MySQL存储引擎

相关 MySQL存储引擎