对于初学者来说通常不关注 MySQL 存储引擎,但是 MySQL 提供了多种存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。

阅读文章的过程中如果有任何疑问,欢迎添加笔者为好友,拉您进微信交流群,一起交流技术,一起打造高质量的职场技术交流圈子,抱团取暖,共同进步。
七日书摘官方群.jpg

MySQL 存储引擎简介

MySQL 中的数据用各种不同的技术存储在文件(或者内存)中。这些技术都使用不同的存储机制、索引技巧、锁水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体性能。 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

InnoDB

InnoDB 是一个健壮的事务型存储引擎,已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。从 5.5.5 版本开始 InnoDB 作为 MySQL 的默认存储引擎,在 InnoDB 中引入了行级锁定和外键约束。

在以下场合下,使用 InnoDB 是理想的选择:

  • 更新密集的表:InnoDB 存储引擎特别适合处理多重并发的更新请求。
  • 事务:InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
  • 自动灾难恢复:与其它存储引擎不同,InnoDB 表能够自动从灾难中恢复。
  • 外键约束:MySQL 支持外键约束的存储引擎只有 InnoDB。
  • 支持自动增加列 AUTO_INCREMENT 属性。

通常来说,如果需要事务支持,并且有较高的并发读取频率,InnoDB 是不错的选择。

MyISAM

MyISAM 表是独立于操作系统的,这说明可以轻松地将其从Windows 服务器复制到 Linux 服务器;每当我们建立一个 MyISAM 引擎的表时,就会在本地磁盘上建立三个文件,文件名就是表名。例如,我建立了一个 MyISAM 引擎的 tb_Demo 表,那么就会生成以下三个文件:

  • tb_demo.frm,存储表定义。
  • tb_demo.MYD,存储数据。
  • tb_demo.MYI,存储索引。

MyISAM 表不支持事务,这就意味着有事务处理需求的表,不能使用 MyISAM 存储引擎。MyISAM 存储引擎适合以下几种情况:

  • SELECT 密集型的表:MyISAM 存储引擎在筛选大量数据时非常迅速,这是它最突出的优点。
  • INSERT 密集型的表:MyISAM 的并发插入特性允许同时 SELECT 和 INSERT 数据。例如:MyISAM 存储引擎很适合管理邮件或 Web 服务器日志数据。

MRG_MYISAM

MRG_MyISAM 存储引擎是一组 MyISAM 表的组合,老版本叫 MERGE,其实是一回事儿,这些 MyISAM 表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge 表就是几个相同 MyISAM 表的聚合器;Merge 表中并没有数据,对 Merge 类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的 MyISAM 表进行操作。

Merge 存储引擎的使用场景。对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用 12 个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有 12 个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除 Merge 表,而不影响原来的数据,删除 Merge 表只是删除 Merge 表的定义,对内部的表没有任何影响。

  • ENGINE=MERGE,指明使用MERGE引擎,其实是跟MRG_MyISAM一回事儿,也是对的,在MySQL 5.7已经看不到MERGE了。
  • UNION=(t1, t2),指明了MERGE表中挂接了些哪表,可以通过alter table的方式修改UNION的值,以实现增删MERGE表子表的功能。比如:
alter table tb_merge engine=merge union(tb_log1) insert_method=last;
  • INSERT_METHOD=LAST,INSERT_METHOD指明插入方式,取值可以是:0 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。
  • MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型,索引也必须按照同样的顺序和同样的方式定义。

MEMORY

使用 MySQL Memory 存储引擎的出发点是速度。为得到最快的响应时间,采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能,但当 mysqld 守护进程崩溃时,所有的 Memory 数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在 Memory 数据表里的数据使用的是长度不变的格式,这意味着不能使用 BLOB 和 TEXT 这样的长度可变的数据类型,VARCHAR 是一种长度可变的类型,但因为它在 MySQL 内部当做长度固定不变的 CHAR 类型,所以可以使用。

一般在以下几种情况下使用 Memory 存储引擎:

  • 目标数据较小,而且被非常频繁地访问。在内存中存放数据,所以会造成内存的使用,可以通过参数max_heap_table_size 控制 Memory 表的大小,设置此参数,就可以限制 Memory 表的最大大小。
  • 如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。
  • 存储在 Memory 表中的数据如果突然丢失,不会对应用服务产生实质的负面影响。
  • Memory 同时支持散列索引和 B 树索引。B 树索引的优于散列索引的是,可以使用部分查询和通配查询,也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了,因此散列索引值适合使用在=和<>的操作符中,不适合在<或>操作符中,也同样不适合用在 order by 子句中。

CSV

CSV 存储引擎是基于 CSV 格式文件存储数据。

  • CSV 存储引擎因为自身文件格式的原因,所有列必须强制指定 NOT NULL 。
  • CSV 引擎也不支持索引,不支持分区。
  • CSV 存储引擎也会包含一个存储表结构的 .frm 文件,还会创建一个 .csv 存储数据的文件,还会创建一个同名的元信息文件,该文件的扩展名为 .CSM ,用来保存表的状态及表中保存的数据量。
  • 每个数据行占用一个文本行。

因为 csv 文件本身就可以被 Office 等软件直接编辑,保不齐就有不按规则出牌的情况,如果出现 csv 文件中的内容损坏了的情况,也可以使用 CHECK TABLE 或者 REPAIR TABLE 命令检查和修复

ARCHIVE

Archive 是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5 版以前,Archive 是不支持索引,但是在MySQL 5.5 以后的版本中就开始支持索引了。Archive 拥有很好的压缩机制,它使用 zlib 压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。

BLACKHOLE

黑洞存储引擎,所有插入的数据并不会保存,BLACKHOLE 引擎表永远保持为空,写入的任何数据都会消失。

PERFORMANCE_SCHEMA

主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为 PERFORMANCE_SCHEMA 的表,一般用于记录 binlog 做复制的中继。在这里有官方的一些介绍MySQL Performance Schema

FEDERATED

主要用于访问其它远程 MySQL 服务器一个代理,它通过创建一个到远程 MySQL 服务器的客户端连接,并将查询传输到远程服务器执行,而后完成数据存取;在 MariaDB 的上实现是 FederatedX

其他

这里列举一些其它数据库提供的存储引擎,OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE。提供的名字仅供参考。

常用引擎对比

不同存储引起都有各自的特点,为适应不同的需求,需要选择不同的存储引擎,所以首先考虑这些存储引擎各自的功能和兼容。

特性InnoDBMyISAMMEMORYARCHIVE
存储限制(Storage limits)64TBNoYESNo
支持事物(Transactions)YesNoNoNo
锁机制(Locking granularity)行锁表锁表锁行锁
B树索引(B-tree indexes)YesYesYesNo
T树索引(T-tree indexes)NoNoNoNo
哈希索引(Hash indexes)YesNoYesNo
全文索引(Full-text indexes)YesYesNoNo
集群索引(Clustered indexes)YesNoNoNo
数据缓存(Data caches)YesNoN/ANo
索引缓存(Index caches)YesYesN/ANo
数据可压缩(Compressed data)YesYesNoYes
加密传输(Encrypted data[1])YesYesYesYes
集群数据库支持(Cluster databases support)NoNoNoNo
复制支持(Replication support[2])YesNoNoYes
外键支持(Foreign key support)YesNoNoNo
存储空间消耗(Storage Cost)N/A非常低
内存消耗(Memory Cost)N/A
数据字典更新(Update statistics for data dictionary)YesYesYesYes
备份/时间点恢复(backup/point-in-time recovery[3])YesYesYesYes
多版本并发控制(Multi-Version Concurrency Control/MVCC)YesNoNoNo
批量数据写入效率(Bulk insert speed)非常快
地理信息数据类型(Geospatial datatype support)YesYesNoYes
地理信息索引(Geospatial indexing support[4])YesYesNoYes
  1. 在服务器中实现(通过加密功能)。在其他表空间加密数据在 MySQL 5.7 或更高版本兼容。
  2. 在服务中实现的,而不是在存储引擎中实现的。
  3. 在服务中实现的,而不是在存储引擎中实现的。
  4. 地理位置索引,InnoDB 支持可 MySQL 5.7.5 或更高版本兼容

查看存储引擎

使用“SHOW VARIABLES LIKE '%storage_engine%';” 命令在 mysql 系统变量搜索磨人设置的存储引擎,输入语句如下:

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------------+---------+
| Variable_name                    | Value   |
|----------------------------------+---------|
| default_storage_engine           | InnoDB  |
| default_tmp_storage_engine       | InnoDB  |
| disabled_storage_engines         |         |
| internal_tmp_disk_storage_engine | InnoDB  |
+----------------------------------+---------+
4 rows in set
Time: 0.005s

使用“SHOW ENGINES;”命令显示安装以后可用的所有的支持的存储引擎和默认引擎,后面带上 G 可以列表输出结果,你可以尝试一下如“SHOW ENGINESG;”。

mysql> SHOW ENGINES;
+--------------------+---------+--------------------------------------+-------------+--------+-----------+
| Engine             | Support | Comment                              | Transactions| XA     | Savepoints|
|--------------------+---------+--------------------------------------+-------------+--------+-----------|
| InnoDB             | DEFAULT | Supports transactions,               | YES         | YES    | YES       |
|                    |         | row-level locking, and foreign keys  |             |        |           |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables| NO          | NO     | NO        |
| MEMORY             | YES     | Hash based, stored in memory, useful | NO          | NO     | NO        |
|                    |         | for temporary tables                 |             |        |           |
| BLACKHOLE          | YES     | /dev/null storage engine (anything   | NO          | NO     | NO        |
|                    |         | you write to it disappears)          |             |        |           |
| MyISAM             | YES     | MyISAM storage engine                | NO          | NO     | NO        |
| CSV                | YES     | CSV storage engine                   | NO          | NO     | NO        |
| ARCHIVE            | YES     | Archive storage engine               | NO          | NO     | NO        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                   | NO          | NO     | NO        |
| FEDERATED          | NO      | Federated MySQL storage engine       | <null>      | <null> | <null>    |
+--------------------+---------+--------------------------------------+-------------+--------+-----------+

由上面命令输出,可见当前系统的默认数据表类型是 InnoDB。当然,我们可以通过修改数据库配置文件中的选项,设定默认表类型。

设置存储引擎

对上面数据库存储引擎有所了解之后,你可以在 my.cnf 配置文件中设置你需要的存储引擎,这个参数放在 [mysqld] 这个字段下面的 default_storage_engine 参数值,例如下面配置的片段

[mysqld]
default_storage_engine=InnoDB

在创建表的时候,对表设置存储引擎,例如:

CREATE TABLE `user` (
  `id`     int(100) unsigned NOT NULL AUTO_INCREMENT,
  `name`   varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

在创建用户表 user 的时候,SQL 语句最后 ENGINE=InnoDB 就是设置这张表存储引擎为 InnoDB。

如何选择合适的存储引擎

提供几个选择标准,然后按照标准,选择对应的存储引擎即可,也可以根据常用引擎对比来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择,一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。

  1. 是否需要支持事务;
  2. 是否需要使用热备;
  3. 崩溃恢复,能否接受崩溃;
  4. 是否需要外键支持;
  5. 存储的限制;
  6. 对索引和缓存的支持;

------完------

推荐阅读:

Java IO 基础教程

Java NIO 简明教程

Java 网络编程

更多学习讨论欢迎关注公众号:
qrcode_for_gh_36ba5cb42130_258.jpg

参考资料:
数据库存储引擎