数据库基础

linux

2023-01-19

Source

Edit

History

undefined

Mysql的存储引擎

一、概述

使用 show engines; 查看支持的引擎，常见的存储引擎

存储引擎	描述
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	置于内存的表
MERGE	用来管理由多个 MyISAM 表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL 集群专用存储引擎

# 查看默认的存储引擎
show variables like 'default_storage_engine%';
# 设置默认的存储引擎
set default_storage_engine = MyISAM/InnoDB;
# 修改数据表的存储引擎
alter table <table_name> engine=<engine_name>;
alter table student engine = MyISAM;
# 在 my.cnf 中 mysqld 配置
default-storage-engine = engine_name

二、InnoDB存储引擎

1. 优势

支持事务安装，重要原因，InnoDB实现了4种隔离级别（Read Uncommited、Read Commited、Repeatable Read和Serializable）
灾难恢复性好。InnoDB通过 commit、rollback、crash-recovery 来保障数据的安全。crash-recovery 是指如果服务器因为硬件或者软件的问题而崩溃，不管当时的数据是怎么的状态，在MySql 之后，InnoDB 都会自动恢复到发生崩溃之前的状态，并回到用户离开的地方
InnoDB使用行级锁
实现了缓冲处理。InnoDB提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据直接从内存中取。相比之下，MyISAM只是缓存了索引
支持外键，InnoDB支持外键约束、检查外键、插入、更新和删除，以确保数据的完整性
性能较高，InnoDB的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的

2. 物理存储

使用InnoDB时，MySql会在数据目录（Data）下创建一个名为 ibdata1 的10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的5MB大小的日志文件。

InnoDB的物理存储结构分为两大部分：数据文件、日志文件

数据文件：

数据文件（表数据和索引数据），InnoDB 的分为共享表空间和独立表空间两种形式

共享表空间：表数据和索引都存放在同一个表空间。默认的表空间文件就是 ibdata1 文件
独立表空间：每个表的数据和索引被存放在一个单独的 .ibd 文件中

查看是否使用独立表空间： show variables like 'innodb_file_per_tables%'; 值为 ON 表示开启独立表文件

1）共享表空间

共享表空间的数据文件可以设置为”固定大小“和”可自动扩展大小“两种形式。

自动扩展形式的文件可以设置文件的最大大小和每次扩展量。在创建自动扩展的数据文件时，最好加上最大尺寸的属性，因为文件系统本身有一定的大小限制，还有方便自身维护。

当共享表空间快要用完的时候，如何增加数据文件呢？

只需要在 innodb_data_file_path 参数后面按照标准格式设置好文件路径和相关属性即可

show variables like 'innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
# 共享表空间的路径、初始化大小、自动扩展策略
# 如何设置共享表空间？在 my.cnf 中的 mysqld 下设置：https://blog.csdn.net/demonson/article/details/79863166
innodb_data_file_path=/db/ibdata1:200M;/dr2/db/ibdata2:2000M:autoextend;
# 另：给共享表空间增加数据文件后，必须要重启数据库系统才能生效

2）独立表空间

独立表空间的命名规范为 table_name.ibd

1 2	show variables like 'innodb_file_per_table'; set global innodb_file_per_table = 0/1;

单独的共享表空间文件只存储该表的数据、索引和缓冲等信息。所以无论使用共享表空间还是独享表空间来存放表，共享表空间都是必须存在的。

日志文件：

默认情况下，InnoDB存储引擎的数据目录下会有两个 ib_logfile0 和 ib_logfile1 的文件，称为”重做日志文件“。InnoDB 通过重做日志将数据库宕机时已经完成但还没有来得及将数据写入磁盘的事务恢复，也能将所有部分完成并已经写入磁盘的未完成事务回滚，并且将数据还原，以此来保证数据的完整性。

每个 InnoDB 存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件。

innodb_log_file_size: 指定每个重做日志的大小
innodb_log_files_in_group: 指定日志文件组中重做日志文件的数量，默认为1
innodb_mirrored_log_groups: 指定日志精选文件组的数量，默认为1
innodb_log_group_home_dir: 指定日志文件组所在路径，默认为 './'

三、MyISAM 存储引擎

MySQL 5.1 及之前版本的默认存储引擎

优点

占用空间小
访问速度快，对事务完整性没有要求或以 select、insert 为主的应用基本上都可以使用这个引擎来创建表
可以配合锁，实现操作系统下的复制备份
支持全文检索（InnoDB也支持）
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能

加锁与并发，MyISAM 对整张表加锁，而不是针对行。读取时加共享锁，写入时加排他锁
索引特性：支持3种类型索引，B-Tree 索引（所有索引数据节点都在叶节点）、R-Tree 索引（用于为存储空间和多维数据的字段做索引）、Full-text 索引（全文索引，存储结构是b-tree，主要为解决用 like 查询时低效问题）

缺点

不支持事务的完整性和并发性
不支持行级锁，使用表级锁，并发性差
主机宕机后，MyISAM表易损坏，灾难恢复性不佳
数据库崩溃后无法安全恢复
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的，可能会引发过多的系统调用，且效率不佳

物理存储

MyISAM 存储引擎的表在数据库中被存储成 3 个物理文件，文件名和表名相同。扩展名为 frm、MYD、MYI。

frm：以 frm 为扩展名的文件存储表的结构，innoDB和MyISAM都有此文件
MYD：以 MYD 为扩展名的文件存储数据，其实是 MYData 的缩写
MYI：以 MYI 为扩展名的文件存储索引，其实是 MYIndex 的缩写。不管表有多少索引，都是存放在同一个 .MYI 文件

MyISAM类型的数据文件和索引文件可以放置在不同的目录，平均分布IO，以此来获得更快的速度

每一个MyISAM 的表数据都存放在后缀名为 .MYD 的文件中，但每个文件的存放格式可能并不完全一样。MyISAM 支持3种不同的数据存放格式。

静态型：MyISAM 存储引擎默认的存储格式，其字段是固定长度，存储非常迅速、容易缓存，出现故障容易恢复。缺点是占用的空间比动态表多。静态型的表的数据在存储的时候会按照列的宽度定义去补足空格，在给应用返回的时候会去掉空格。如果保存的内容后面本来就带有空格，那么在返回的时候也会被去掉。
动态型：包含变长字段，占用空间相对较少，但频繁的更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并出现故障时恢复相对较困难
压缩型：每条记录被单独压缩，占用磁盘空间非常小，可以减少磁盘IO，从而提升查询性能。

四、磁盘存储

MySQL 每一个数据表有存放数据表结构定义的 .frm 文件。不同的存储引擎还有其他存放数据和索引信息的文件

从MySQL 8.0 开始，frm 表结构定义文件被取消，MySQL把表结构信息都写到了系统表空间

1、MyISAM

1
2
3

.frm: 表结构的文件，存放该数据表的结构定义
.MYD: MY DATA 的缩写，数据文件，存放该数据表中各个行的数据
.MYI: MY Index 的缩写，索引文件，存放该数据表的全部索引信息

2. InnoDB

1 2	.frm: 存储表结构 .ibd: 存储表中数据

五、存储引擎对比

特性	MyISAM	InnoDB	MEMORY
存储限制	有	支持	有
事务安全	不支持	支持	不支持
锁机制	表锁	行锁	表锁
B树索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	不支持	不支持
集群索引	不支持	支持	不支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
支持外键	不支持	支持	不支持