mysql引擎笔记整理

千家信息网最后更新 2025年01月23日mysql引擎笔记整理查看当前的mysql数据库都支持什么样的存储引擎：
mysql> show engines;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 170700
Current database: *** NONE ***

注：
1、support的状态有default，yes，no，disabled。no表示该引擎不可用，disabled表示支持该引擎，但是当前被禁用。
2、Transactions表示是否支持事务。
3、XA表示是否支持分布式事务。
4、Savepoints表示是否支持保存点。

mysql中自带的也比较常见的存储引擎有：MyISAM，Innodb，NDB Cluster，Memory，Archive，Merge，Federated等。其中，最著名和广泛的就是MyISAM，Innodb。

1) MEMORY引擎
MEMORY存储引擎的表只拥有一个独立的磁盘文件，扩展名为.frm，用来存储表结构的定义，不包含数据。
MEMORY引擎表的数据保存在内存中，mysql服务端需要单独为其分配内存区域。分配给它的内存在正常状态下不会释放，会一直被持有。除非整个MEMORY表被删除或重建才会释放相关内存。
如果仅删除MEMORY引擎表中的数据，释放的内存会被该表新插入的数据所使用，无法被其他对象或者线程使用。
当mysql服务关闭时，MEMORY引擎表中的数据都会丢失，表结构会予以保留。
MEMORY引擎表占用的最大内存由参数max_heap_table_size系统变量来决定。该值默认识16M。可以为不同的MEMORY引擎表设置不同的最大内存。但是mysql服务重启后，所有的MEMORY引擎表的最大内存又会被系统变量的值覆盖掉。
---------设置最大内存为1M；----------
set max_heap_table_size=1024*1024;
create tabele test1(user varchar(10)) engine=memory;
---------设置最大内存为2M；----------
set max_heap_table_size=1024*1024*2;
create tabele test1(user varchar(10)) engine=memory;
除此之外，还可以在创建MEMORY引擎表时指定max_rows项的值来限制MEMORY引擎表所使用的内存。

2）csv存储引擎
csv存储引擎是基于csv格式文件存储数据的。
csv存储引擎表的所有列必须强制指定NOT NULL，而且csv存储引擎表不支持索引，不支持分区。
csv存储引擎表包含一个表的结构定义文件，扩展名为.frm，以及一个扩展名为.CSV的数据文件，还有一个同名的元信息文件，扩展名为.CSM，用来保存表状态和表中保存的数据量。
.CSV的数据文件就是CSV格式的平面文本文件，可以用excel打开。
如果.CSV文件中的内容被损坏，可以用CHECK TABLE或者REPAIR TABLE命令来进行检查和恢复。
CHECK TABLE命令用于检查文件中的每一行是否合法，如果又发现不合法的行，就抛出异常。
REPAIR TABLE命令用来修复文件，将文件中损坏的数据清除掉。注意它会将出现损坏行之后的所有记录行都清掉，而不管后面的记录行是否有合法的数据。

3）ARCHIVE存储引擎
基于ARCHIVE存储引擎的对象，能够将大量数据压缩存储，它使用了zlib无损数据压缩算法，并且还可以使用OPTIMIZE TABLE分析表使其打包成更小的格式。
ARCHIVE存储引擎的特点就是插入效率高，数据保存时占空间小。
ARCHIVE存储引擎目前仅能支持insert和select语句，不支持update，delete，replace语句。支持order by操作，BLOB列等常规列，可以使用行级锁定，但不支持索引。
ARCHIVE存储引擎拥有.frm结构定义文件，以及扩展名为.arz的数据文件。在执行优化操作时可能还会出现一个扩展名为.arn的文件

4）BLACKHOLE引擎
BLACKHOLE引擎虽然可以像其他引擎一样接受数据，但是数据并不会保存。BLACKHOLE引擎表中永远为空。但是，该引擎表支持各种索引。
尽管BLACKHOLE引擎表中不存放任何数据，但是如果启用了binlog，那么执行的sql语句实际上是会被记录的。
BLACKHOLE引擎表只有一个.frm格式的文件，用于保存表定义。

5）MRG_MYISAM存储引擎
又称为MERGE存储引擎，它是将一组MyISAM表聚合在一起，使用时就像一张表一样，以此来简化查询操作。
MRG_MYISAM存储引擎表，要求基表拥有相同的列和索引信息，并且列的定义和顺序，索引的顺序都必须一模一样。否则是无法聚合到一起的。
MRG_MYISAM存储引擎表本身并不存储数据，只是一个汇总作用，但是它除了支持查询操作外，也支持插入，修改，删除操作。操作方式和普通的MyISAM表一样。
只是插入的时候，需要一些配置，以指明往哪张MyISAM表中插入数据。这个配置就是创建MRG_MYISAM存储引擎表时的INSERT_METHOD选项。
INSERT_METHOD选项有三个值，分别是NO，FIRST，LAST。NO表示不允许插入，是默认值。FIRST是插入第一张表。LAST是插入最后一张表。

6）FEDERATED存储引擎
类似于oracle中的database link。默认mysql安装时不安装FEDERATED存储引擎的，如果需要使用该引擎，在编译安装时，添加参数-DWITH_FEDERATED_STORAGE_ENGINE。并且在启动mysql服务时，
附加参数--FEDERATED。或者修改my.cnf配置文件，加上FEDERATED一行，重启mysql服务。
FEDERATED存储引擎表，只有表结构，其物理数据是来自于远端的mysql服务器。每个FEDERATED存储引擎表都包含了两个元素：一个远端的mysql数据库表，一个本地的数据库表。
FEDERATED存储引擎表在创建时，需要通过CONNECTION选项指定连接信息。CONNECTION选项有两种写法：一种通过字符串，一种是通过CREATE SERVER语句。通过CREATE SERVER语句创建的server信息都会被保存在mysql.servers表中。

7）MyISAM存储引擎
每个MyISAM表对象都是由3个独立的文件组成，扩展名分别是.frm，.MYD，.MYI。.frm文件用于存储表对象的结构，.MYD用于存储表数据，.MYI用于存储表的索引信息。
在MyISAM引擎表中，不管包含多少个CHAR/VARCHAR类型列，这些列的长度加起来也不能超过65535B，单表字符列最大长度不能超过65532B。
MyISAM引擎的主要优点是快，查询快，写入快。但是缺点也比较明显，不支持事务，锁粒度太粗（表级锁），在OLTP场景下不合适。
在存储数据时，MyISAM引擎表支持三种存储格式：FIXED（定长或称静态），DYNAMIC（动态），COMPRESSED（压缩）。
静态格式表就是表中不含变长度的列，比如varchar/varbinary/blob/text等，所定义的每一列保存的均是固定的字节数。或者创建时指定ROW_FORMAT=FIXED。优点：简单，查找数据最快，崩溃后最好恢复。缺点：费空间。
动态格式表就是和静态格式表相反的，或者创建时指定了ROW_FORMAT=DYNAMIC。除了字符串长度小于4的列以外，其他字符列的长度都是动态的。优点：省空间。缺点：查询相对较慢，存储空间碎片会比较多。崩溃后恢复操作比较麻烦。
（myisamchk工具：OPTIMIZE TABLE或者myisamchk -r命令用来消除碎片；myisamchk -ei用来查询表的统计信息；myisamchk -ed命令用来查询表对象的链接数。）
压缩格式表：创建只能用myisampack创建，解压缩用myisamchk命令。压缩表只能读，不能添加或修改记录。

8）InnoDB存储引擎
InnoDB拥有自己独立的缓存池，对应参数是innodb_buffer_pool_size系统变量，类似于oracle数据库中的SGA_TARGET，常用数据包括索引都在缓存中。
锁粒度为行级，提供一致性读。支持事务。支持外键约束。支持数据加密。支持查询、数据、索引缓存。支持备份/恢复到时间点。
InnoDB存储引擎表必须创建主键，一般选择将经常需要查询的列作为主键，如果没有合适的列，就将主键创造到自动增长的列。
InnoDB能够处理多会话并发读写同一个对象。
InnoDB有两种表空间方式：系统表空间和多重表空间。
InnoDB系统表空间对应哪些物理文件，是由系统变量innodb_data_file_path来设置的：
mysql> show variables like "%innodb_data%";
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+
2 rows in set (0.00 sec)
默认情况下，InnoDB数据文件时是存放在mysql的data目录中，如果想变更目录，可以通过系统变量innodb_data_home_dir来设置。
启动innodb_file_per_table选项，以启用多重表空间，使表中数据和索引保存在单独的文件中，而不是保存在系统表空间中。
设置为多重表空间时，truncate存储引擎为InnoDB的表速度很快，并且释放的空间能够被操作系统使用。
mysql5.6中，innodb_file_per_table选项默认是为启动的。
mysql> show variables like "%innodb_file%";
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+----------+
4 rows in set (0.00 sec)

InnoDB除了有表空间概念，还有自己专用的日志文件，即REDOLOG日志。
默认情况下，InnoDB引擎会创建两组大小均为5M的日志文件，分别名为ib_logfile0和ib_logfile1。
关于InnoDB引擎日志的系统参数有以下几个：innodb_log_group_home_dir redo log的保存路径，默认是在datadir变量指定的路径下
innodb_log_file_size 每一个redo日志的大小，最大不能超过512G
innodb_log_files_in_group 指定日志文件组的数量，默认是两个，最多不超过100个
调整InnoDB引擎日志比较复杂，因为这几个参数均是静态参数，并且调整它们还要依赖参数innodb_fast_shutdown。
innodb_fast_shutdown参数用来控制InnoDB的关闭模式，有三个值：
0 0模式类似于oracle的shutdown normal
1 1模式类似于oracle的shutdown immediate，默认值是1
2 2模式类似于oracle的shutdown abort
InnoDB引擎因为支持事务，所以一定会需要回滚段。关于InnoDB引擎的回滚段，有以下两个参数：innodb_undo_directory 指定UNDO日志的物理文件位置
innodb_undo_tablespaces 指定UNDO表空间的数量，每个undo表空间都是独立的.idb文件
innodb_undo_logs 指定undo表空间中回滚段的数量
undo表空间一旦创建，就无法删除。所以一旦使用了undo表空间，mysql的版本就无法从5.6降下去。

默认情况下，连接到MYSQL服务的客户端处于自动提交模式，即每条DML执行即提交。
如何启动InnoDB引擎的事务支持？有两种方式：
1、禁用事务的自动提交：
将autocommit系统变量置为0或者OFF。以后就由用户来决定是否提交或者回滚事务。
autocommit是会话级变量，只针对当前会话，无法全局生效。
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
2、显式声明事务，通过语句start transaction语句。

mysql库中的表默认均为MYISAM引擎表，不可以更改引擎。
对于InnoDB引擎表而言，用show table status返回表的记录行数，行长度等状态信息并不精确，只是一个估算值。最好用count函数来进行统计。


InnoDB引擎表的逻辑存储结构从小到大分为页(pages)/块，扩展(extend)/区，段(segments)，表空间。
页(pages)/块： 基本单位，默认大小为16K，可以通过参数innodb_page_size变量设置，可选值有4k，8k，16k
扩展(extend)/区：每个区固定1M大小，区由页构成。
段(segments)：InnoDB中，段实际上指的是独立表空间对应的数据文件。
表空间：

InnoDB引擎内部在保存数据到数据库时，会自动生成3个内部列：DB_TRX_IDG标记事务的标识符，DB_ROLL_PRT为回滚标记，DB_ROW_ID为行ID
回滚段中的UNDO日志分为insert UNDO日志和update UNDO日志。
insert UNDO日志：仅在事务回滚时需要，事务提交后即被废弃
update UNDO日志：用于构造一致性读。
回滚段中的UNDO日志记录大小，一般会小于实际插入或者修改的记录大小。

purge线程：delete记录时，记录并不会立即在物理上被删除，只有当InnoDB废弃update UNDO日志后，才会从物理上移除关联的列和索引记录，这种移除操作称之为purge。
控制purge线程延迟可以用过系统变量innodb_max_purge_lag进行设置。