MySQL存储引擎如何理解

2024-07-24 12:33:20

字体：大中小

来源：转载

供稿：网友

　　这篇文章主要讲解了“MySQL存储引擎怎么理解”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL存储引擎怎么理解”吧！

　　今天发现了一个神奇的参数：-site:xxxx.net

　　一、存储引擎的选择（表类型）

　　1、存储引擎的介绍

　　与到多数关系型数据库的区别在于MySQL有一个存储引擎的概念，针对不同的存储需求可以选择最合适的存储引擎。MySQL中的插件式的存储引擎是其一大特色，用户可以根据应用的需求选择如何存储、是否索引，是否使用事务。嘿嘿，你也可以根据业务环境去适配最适合自己业务的存储引擎。

　　Oracle从中嗅到了商机，收购了MySQL，从此有了企业版（商业支持）。社区版依旧可以免费下载。另一大魅力也是因为开源，社区高度活跃，人人都可贡献。接下来介绍几种使用比较多的存储引擎，存储引擎并无优劣之分，有的只是谁更适合对应的生产业务环境。

　　MySQL5.0中支持的存储引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之后的默认存储引擎）、PERFORMANCE_SCHEMA（非常规存储数据引擎）。下面给出MySQL与MariaDB支持的存储器引擎的对比，可以看出MariaDB新增了Aria引擎：

　　作用描述：

　　Engine：引擎名称（描述）；

　　Support：当前版本数据库是否支持该存储引擎，YES：支持、NO：不支持；Supports transactions, row-level locking, and foreign keys，个人字面上翻译这段话：支持事务、行级别锁和外键；

　　Comment：对该存储引擎的详情描述，比如描述该引擎否支持事务和外键；

　　Transactions：对该存储引擎是否支持事务的描述，YES：支持、NO：不支持；

　　XA：是否满足XA规范。XA规范是开放群组关于分布式事务处理(DTP)的规范。YES：支持、NO：不支持；

　　Savepoints：字面意思是保存点，对事物控制是否支持，YES：支持、NO：不支持。

　　小声哔哔，如果你能阅读明白官方的一些英文文档，这将有助于你对MySQL存储引擎的进一步理解，养成阅读源码或者文档的能力。

　　顺带的提一下MySQL的妹妹MariaDB。在MySQL的复刻版本MariaDB中10.2之前使用的自带的新引擎Aria，在MariaDB10.2之后使用的默认存储引擎也是InnoDB，足以看出InnoDB存储引擎的优秀之处。MariaDB的API和协议兼容MySQL，另外又添加了一些功能，以支持本地的非阻塞操作和进度报告。这意味着，所有使用MySQL的连接器、程序库和应用程序也将可以在MariaDB下工作。在此基础上，由于担心甲骨文MySQL的一个更加封闭的软件项目，Fedora等Linux发行版已经在最新版本中以MariaDB取代MySQL，维基媒体基金会的服务器同样也使用MariaDB取代了MySQL。

　　主要需要了解的几种存储引擎：

　　MyISAM

　　InnoDB

　　MEMORY

　　MERGE

　　下面将着重介绍我最近看书认识的几种常用的存储引擎，对比各个存储引擎之间的区别，帮助我们理解不同存储引擎的使用方式。更多详情可以参考MySQL的官方文档。

　　2、部分存储引擎的特性

　　存储引擎/支持特性存储限制事务安全锁机制 B树索引哈希索引全文索引集群索引数据缓存索引缓存数据可压缩空间使用内存使用批量插入速度外键支持

　　InnoDB存储引擎在MySQL5.6版本开始支持全文索引。在MySQL5.7推出了虚拟列，MySQL8.0新特性加入了函数索引支持。

　　2.1、MyISAM存储引擎

　　MyISAM是MySQL5.5之前默认的存储引擎。MyISAM不支持事务、不支持外键。优势在于访问速度快，对事务完整性没有特殊要求或者以select和insert为主的应用基本上可以使用MyISAM作为存储引擎创建表。我们先弄个例子出来演示，事先准备了一张数据千万级别的表，看看这个存储引擎的特性：

　　我已经创建好了数据库为test，在test中分别创建了两张表test和tolove。test表在创建的时候指定默认存储引擎为MyISAM，tolove表指定存储引擎为InnoDB。

　　使用MyISAM存储引擎创建的表tolove，查询存储有1kw数据的表tolove。

　　tips：你可以使用use test，切换到test数据库，就不用像我这样查询tolove表去指定test数据库了哟！

　　MySQL [(none)]> select count(*) from test.tolove;

　　+----------+

　　| count(*) |

　　+----------+

　　| 10000000 |

　　+----------+

　　1 row in set (0.000 sec)

　　再看演示使用InnoDB存储引擎创建的表test，同样为了演示，事先随机生成了1kw条数据。

　　MySQL [(none)]> select count(*) from test.test;

　　+----------+

　　| count(*) |

　　+----------+

　　| 10000000 |

　　+----------+

　　1 row in set (3.080 sec)

　　进行对比同样存储1kw条数据的表，使用MyISAM作为存储引擎查询速度堪称光速1 row in set (0.000 sec)，使用InnoDB存储引擎查询速度稍逊一筹1 row in set (3.080 sec)。

　　MyISAM在磁盘中存储的文件：

　　每个MyISAM在磁盘上存储成3个文件，其文件名和表名都相同，扩展名分别是：

　　.frm：存储表定义；

　　.MYD：MYData，存储数据；

　　.MYI：MYindex，存储索引。

　　MySQL存储引擎怎么理解

　　数据文件和索引文件可以存放在不同的目录，平均分布IO，获得更快的速度，提升性能。需要指定索引文件和数据文件存储的路径，创建表时通过DATA DIRECTORY和INDEX DIRECTORY参数指定，表明不同MyISAM表的索引文件和数据文件可以存放在不同的路径下。当然，需要给予该路径的访问权限。

　　MyISAM损坏处理方式：

　　MyISAM类型的表可能会损坏，原因多种多样。损坏后的表有可能不能被访问，会提示需要修复或者访问后提示返回错误结果。MyISAM类型的表，可以通过提供的修复工具执行CHECK TABLE语句检查MyISAM表的健康程度，使用REPAIR TABLE语句修复一个损坏的表。表损坏可能会导致数据库异常重新启动，需要尽快修复并确定原因好做应对策略。

　　MySQL存储引擎怎么理解

　　使用MyISAM存储引擎的表支持3种不同的存储格式，如下：

　　静态表，固定长度；

　　动态表

　　压缩表

　　静态表是MyISAM存储引擎的默认存储格式，字段长度是定长，记录都是固定长度。优势在于存储迅速、容易缓存、出现故障易恢复；缺点是相对耗存储空间。需要注意的是：如需保存内容后面的空格，默认返回结果会去掉后面的空格。

　　动态表包含变长字段，记录不是固定长度，存储优势：占用空间相对较小、但频繁删除和更新记录会产生碎片。这时，需要定期执行optimize table语句或者myisamchk -r命令来改善性能，出现故障恢复相对较难。

　　压缩表由mysiampack工具创建，占用磁盘空间很小。因为每个记录是被单独压缩，所以访问开始非常小。

　　梳理一下MyISAM存储引擎的要点，如下图1-2-2-1所示：

　　MySQL存储引擎怎么理解

　　顺带安利一波，前段时间发现WPS也能够制作精美的思维导图，并且支持一键导入到doc文件中。普通用户最多可存储150个文件。之前也用过XMind、processon、gitmind等等，现在使用WPS更方便了。

　　2.2、InnoDB存储引擎

　　优点与缺点：InnoDB存储引擎提供了具有提交（commit）、回滚（rollback）和崩溃恢复能力的事务安全。但对比MyISAM存储引擎，InnoDB写的处理效率相对差一些，并且会占用更多的磁盘空间保留数据和索引。下图是我存储了1kw条数据的表，并且使用的是InnoDB存储引擎。student01表同样使用了InnoDB存储引擎，存储数据为100w条。从下图可以看出存储数据索引在.ibd文件中、表结构则存在.frm文件中。

　　MySQL存储引擎怎么理解

　　2.2.1、自动增长列

　　InnoDB表的自动增长列可以手工插入，但插入的值为空或者0，则实际插入的将是自动自动增长后的值。

　　本来想继续使用bols那张表作为演示的，思来想去还是正经一点。为了演示，我又新增了一张表为autoincre_test，表示id设置为主键且自增长，存储引擎选择InnoDB。然后插入了3条数据进行演示。查询当前线程最后插入数据的记录使用值：

　　MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb;

　　Query OK, 0 rows affected (0.018 sec)

　　MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3');

　　Query OK, 3 rows affected (0.007 sec)

　　Records: 3 Duplicates: 0 Warnings: 0

　　MySQL [test]> select * from autoincre_test;

　　+----+------+

　　| id | name |

　　+----+------+

　　| 1 | 1    |

　　| 2 | 2    |

　　| 3 | 3    |

　　+----+------+

　　3 rows in set (0.000 sec)

　　select last_insert_id();

　　MySQL [test]> select last_insert_id();

　　+------------------+

　　| last_insert_id() |

　　+------------------+

　　|                2 |

　　+------------------+

　　1 row in set (0.000 sec)

　　tips：可以通过alter table table_name=n;语句强制设置自动增长列的初始值，默认从1开始，但该强制的默认值是保留在内存中的，如果使用该值之前数据库重新启动，强制默认值则会丢失，就需要重新设置，毕竟使用内存没有加载到磁盘中。

　　通过上面的演示，你会发现插入记录是0或者空时，实际插入的将是自动增长后的值。通过last_insert_id()函数可以查询当前线程最后插入数据的记录使用值。如果一次插入多条记录，则返回的是第一条记录使用的自动增长值，这里就不演示插入多条数据了。记住一点，可以使用last_insert_id()去查询id记录值。

　　对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。但对于MyISAM表，自动增长列可以是组合索引的其它列。这样插入记录后，自动增长列是按照组合索引的前面几列排序后递增的。你可以创建一张表指定MyISAM存储引擎，然后将两列字段组合索引进行测试验证。

　　2.2.2、外键约束

　　在MySQL中，目前支持外键约束的存储引擎只有InnoDB。创建外键的时候，要求父表必须有对应的索引。子表创建外键的时候，也会自动创建对应的索引。下面将通过实例进行讲解。可以从MySQL官网下载示例数据库world和sakila进行参考。

　　city表，FOREIGN KEY (CountryCode) REFERENCES country (Code)

　　country表

　　countrylanguage表，FOREIGN KEY (CountryCode) REFERENCES country (Code)

　　通过MySQL workbench或者Navicat逆向生成物理模型进行参考，更加直观。插一句，在MySQL的官网同样有一个sakila数据库是关于演员电影的，也提供了sakila的ERR物理模型图，这句话做了超链接，可以直接访问。给出我之前逆向生成的world数据库的物理模型：

　　MySQL存储引擎怎么理解

　　在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作包含：

　　restrict

　　cascade

　　set null和no action

　　其中restrict和no action相同，restrict限制在子表有关联记录的情况下父表无法更新；cascade表示在父表更新或删除的时候，级联更新或者删除子表对应记录；set null表示在父表更新或删除的时候，子表的对应字段被set null。选择cascade以及set null时需要谨慎操作，有可能导致数据丢失。

　　在导入多个表的数据时，如果忽略表之前的导入顺序，可以暂时关闭外键检查；同样执行load data和alter table时也可以暂时关闭外键检查加快处理的速度，提升效率。关闭外键检查的命令为：

　　set foreign_key_checks=0;

　　执行完导入数据或者修改表的操作后，通过开启外键检查命令改回来：

　　set foreign_key_checks=1;

　　对于InnoDB类型的表，外键信息可以通过show create table或者show table status查看。比如查找world数据库中的city表：

　　MySQL [sakila]> show table status like 'city'/G

　　关于外键约束就提这么多，没有演示创建以及删除，因为贴太多的SQL语句太占篇幅了。可以到MySQL官网下载world和sakila数据库进行测试。

　　2.2.3、存储方式

　　InnoDB存储表和索引有两种方式：

　　共享表空间存储

　　多表空间存储

　　使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。在开头介绍InnoDB存储引擎时也提到过文件存储位置。

　　使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但每个表的数据和索引单独保存在.ibd文件中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名为表名+分区名。可以在创建分区的时候指定每个分区的数据文件位置，以此来平均分布磁盘的IO，达到缓解磁盘压力的目的。如下是在Windows下使用InnoDB存储了海量数据的文件：

　　MySQL存储引擎怎么理解

　　使用多表空间存储需要设置参数innodb_file_per_table，重启数据库服务器才能生效哟。多表空间的参数生效后，只对新建的表生效。多表空间的数据文件无大小限制，无需设置初始大小，也不需设置文件的最大限制与扩展大小等参数。使用多表空间存储优势在于方便单表备份和恢复操作。虽然不能直接复制.frm和.ibd文件达到目的，但可以使用如下命令操作：

　　alter table table_name discard tablespace;alter table table_name import tablespace;

　　将备份恢复到数据库中，单表备份，只能恢复到原来所在的数据库中，无法恢复到其它数据库中。如过需要将单表恢复至其它目标数据库中，则需要通过mysqldump和mysqlimport来实现。

　　注意：即便多表存储更有优势，但是共享表存储空间依旧是必须的，InnoDB将内部数据字典和在线重做日志存在这个文件中。

　　梳理一下InnoDB存储引擎的要点，

　　MySQL存储引擎怎么理解

　　关于InnoDB存储引擎就介绍到此处了，更多详情可以参考MySQL的官方文档。是不是发现了我只在MyISAM和InnoDB存储引擎做了总结的思维导图。没错，只做了这两个，因为这俩最常用。至于为啥是粉色背景，因为老夫有一颗少女心！

　　2.3、MEMORY存储引擎

　　MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只对应一个磁盘文件，格式是.frm。MEMORY类型的表访问速度极快，存在内存中当然快。这就是Redis为什么这么快？不仅小？还能持久？咱回到正题，MEMORY存在内存中并默认使用hash索引，一旦服务关闭，表中数据会丢失。创建一张名为GIRLS的表指定存储引擎为MEMORY，注意了在UNIX和Linux操作系统下，是对字段和表名大小是写敏感的，关键字不影响。

　　CREATE TABLE GIRLS (

　　 ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL,

　　 CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

　　还记得在介绍存储引擎做的那会张表格吗，有介绍到MEMORY支持B TREE索引。虽然MEMORY默认使用的索引是hash索引，但是你可以手动指定索引类型。例如默认手动指定使用关键字USING HASH：

　　-- 创建索引指定索引类型为hash。create index mem_hash USING HASH on GIRLS(ID);-- 查询索引类型，简化了一下，只展示了部分参数。mysql> SHOW TABLE STATUS LIKE 'GIRLS'/G*************************** 1. row ***************************

　　           Name: GIRLS         Engine: MEMORY

　　        Version: 10

　　     Row_format: Fixed1 row in set (0.00 sec)

　　虽然MEMORY容易丢失数据，但是在启动MySQL服务的时候，我们可以使用**–init-file选项，将insert into … select或者load data infile**这样的语句存放在这个指定的文件中，就可以在服务启动时从持久稳固的数据源装载表。

　　服务器需要提供足够的内存来维持所有在同一时间使用的MEMORY表，当不在需要MEMORY表内容之时，释放被MEMORY表使用的内存。仔细思考一下，如果内存用了不释放那将有多可怕。此时可以执行delete form 或truncate table亦或完整地删除整个表，使用drop table。这里提一点，在Oracle中也同样支持truncate，使用truncate的好处在于不用再去考虑回滚（rollback），效率更高。使用truncate需要在命令模式下使用，其它客户端工具可能不支持。

　　每个MEMORY表中存放的数据量大小，受max_heap_table_size系统变量约束，初始值为16MB，可以根据需求调整。通过max_rows可以指定表的最大行数。

　　MEMORY存储引擎最大特色是快，主要用于内容变化不频繁的代码表，或者是为了做统计提供的中间表，效率更高。使用MEMORY时需谨慎，万一忘了这厮重启数据就没了就尴尬了。所以在使用时，考虑好重启服务器后如何取得数据。

　　关于MEMORY存储引擎就介绍到这里，大部分都是些理论知识，更多的需要自己去实践测试。

　　2.4、MERGE存储引擎

　　MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结果完全相同，MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作，实际上是对内部的MyISAM表进行操作的。对于MERGE类型表的插入操作，通过insert_method子句定义插入的，可以有3个不同的值，使用first或last插入操作对应开始与最后一个表上。如果不定义这个子句，或者定义为NO，表示不能对MERGE表进行操作。

　　对MERGE表进行DROP操作，只是对MERGE的定义进行删除，对内部表没有任何影响。MERGE表上保留两个文件，文件名以表的名字开始，分别为：

　　.frm文件存储表定义；

　　.mrg文件包含组合表的信息，包含表组成、插入数据依据。

　　可以通过修改.mrg文件来修改表，但修改后需要使用flush tables刷新。测试可以先创建两张存储引擎为MyISAM的表，再建一张存储引擎为MERGE存储引擎的表。

（编辑：武林网）

上一篇：MySQL表结构实例解析

下一篇：MySQL数据库基础知识点有哪些