MySQL存储引擎

最新推荐文章于 2024-09-06 08:32:49 发布

東方既白（积累版）

最新推荐文章于 2024-09-06 08:32:49 发布

阅读量165

点赞数

CC 4.0 BY-SA版权

原文链接：https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter3/3.5.md

一、简单说一下数据库的存储引擎是什么东西？

存储引擎就是数据库对表的数据怎么存储数据，怎么建立索引和查询更新的实现方法。

实际开发中，业务上对数据有不同的要求，

这些数据有的可能偏重于被频繁查询，

有的要求增删速度快，

有的则对事务、索引、外键有特殊规定。

这样一来，就对存储数据的表要求使用不同的数据组织结构，也就是存储引擎。

简而言之，就是业务直接决定了存储引擎。从这里就可以看出，同一个数据库内的多个表，可以使用各自符合业务的存储引擎，即存储引擎的使用级别是数据表，不是库，也不是记录。

比如MySQL中MyISAM引擎不支持事务的概念，多用于数据仓库这样查询多而事务少的情况，速度较快。

MySQL中InnoDB引擎支持事务的概念，多用于web网站后台等实时的中小型事务处理后台。

不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。

MySQL默认配置了许多不同的存储引擎，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

二、MySQL存储引擎的区别与比较

存储引擎是数据库的核心，对于mysql来说，存储引擎是以插件的形式运行的。

虽然mysql支持种类繁多的存储引擎，但是常用的就那么几种。

对于MySQL支持的多种存储引擎，可以通过“SHOW ENGINES”语句来查看。

对于日常工作来说，常用的存储引擎只有三种：默认的InnoDB、MyISAM和MEMORY。

InnoDB

MySQL5.5之后的默认存储引擎，InnoDB是一个健壮的事务型存储引擎，

InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

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

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

应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。

优点：提供了具有提交（Commit）、回滚（Rollback）、崩溃恢复能力的事务安全，支持外键。

缺点：相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引

特点：1、自动增长列

innoDB表的自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列

MyISAM表的自动增长列可以是组合索引的其他列

设置自动增长列：create表时，在字段后加auto_increment

可以通过alter table emp auto_increment=n 来强制设置自动增长列的初始值，默认是1，但是该强制指定的值是保存在内存中的，所以在数据库重启后会失效，需要重新设置

2、外键约束

MySQL的存储引擎中只有innoDB支持外键约束

注意：当某个表被其它表创建了外键参照，那么该表对应的索引和主键禁止被删除

当导入多个表的数据时，如果要忽略表之前导入顺序，或者当执行load data和alter table操作，为了提高处理速度的时候，可以暂时关闭外键约束，命令是

mysql> set foreign_key_checks=0;

执行完之后，再使其为1 ，开启外键。

查看外键信息

show create table 或show table status

3、存储方式

innoDB存储数据和索引有共享表空间存储和独占表空间存储两种方式，通过参数innodb_file_per_table控制，0表示共享空间，也是默认的，1表示独占空间

两种方式的表结构（描述）都保存在.frm文件中

共享表空间：

每一个数据库的所有表的数据、索引都保存在一个文件中，默认在data目录下，名为ibdata1，大小为10M的文件，可以通过参数innodn_data_file_path=/data/ibdata1:2000M来指定存储路径。

优点：（1）可以将表空间分为多个文件放在不同的磁盘上，分布IO，提高性能。innodn_data_file_path=/data/ibdata1:2000M；/db/ibdata2:2000M:autoextend

autoextend表示如果指定的2000M空间用满后，该文件自动增长。

也就是说采用共享空间存储，存储空间的大小不受文件系统下文件大小的限制了，而取决于自身的限制，官方文档显示，表空间的最大限制是64TB。

（2）表数据和表结构放在一起，方便管理

缺点：由于所有的数据和索引都是在一个文件中混合存储，这样的话对一个表做了大量的删除操作后，表空间中会产生大量的空隙

独占表空间存储：

每一张表都有自己独立的表空间，表的结构依然在.frm文件中，还有一个后缀为.ibd的文件，保存了这张表的数据和索引。

优点：每张表都有自己独立的表空间，可实现单表在不同数据库中移动
空间可回收。drop table会自动回收；删除数据后，通过alter table emp engine=innodb也可回收不用的表空间
效率和性能会好一些
缺点：由于每个表的数据都是以一个单独的文件来存放，所以会受到文件系统的大小限制

MyISAM

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

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

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：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 命令检查和修复

BLACKHOLE

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

PERFORMANCE_SCHEMA

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

FEDERATED

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

其他

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

常用引擎对比

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

特性	InnoDB	MyISAM	MEMORY	ARCHIVE
存储限制(Storage limits)	64TB	No	YES	No
支持事务(Transactions)	Yes	No	No	No
锁机制(Locking granularity)	行锁	表锁	表锁	行锁
B树索引(B-tree indexes)	Yes	Yes	Yes	No
T树索引(T-tree indexes)	No	No	No	No
哈希索引(Hash indexes)	Yes	No	Yes	No
全文索引(Full-text indexes)	Yes	Yes	No	No
集群索引(Clustered indexes)	Yes	No	No	No
数据缓存(Data caches)	Yes	No	N/A	No
索引缓存(Index caches)	Yes	Yes	N/A	No
数据可压缩(Compressed data)	Yes	Yes	No	Yes
加密传输(Encrypted data[1])	Yes	Yes	Yes	Yes
集群数据库支持(Cluster databases support)	No	No	No	No
复制支持(Replication support[2])	Yes	No	No	Yes
外键支持(Foreign key support)	Yes	No	No	No
存储空间消耗(Storage Cost)	高	低	N/A	非常低
内存消耗(Memory Cost)	高	低	N/A	低
数据字典更新(Update statistics for data dictionary)	Yes	Yes	Yes	Yes
备份/时间点恢复(backup/point-in-time recovery[3])	Yes	Yes	Yes	Yes
多版本并发控制(Multi-Version Concurrency Control/MVCC)	Yes	No	No	No
批量数据写入效率(Bulk insert speed)	慢	快	快	非常快
地理信息数据类型(Geospatial datatype support)	Yes	Yes	No	Yes
地理信息索引(Geospatial indexing support[4])	Yes	Yes	No	Yes

在服务器中实现（通过加密功能）。在其他表空间加密数据在MySQL 5.7或更高版本兼容。
在服务中实现的，而不是在存储引擎中实现的。
在服务中实现的，而不是在存储引擎中实现的。
地理位置索引，InnoDB支持可mysql5.7.5或更高版本兼容

如何选择合适的存储引擎

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

是否需要支持事务；
是否需要使用热备；
崩溃恢复，能否接受崩溃；
是否需要外键支持；
存储的限制；
对索引和缓存的支持；

三、存储引擎相关的命令使用

查看当前数据库支持的存储引擎

mysql> show engines \G;

查看当前表使用的存储引擎

mysql> show create table emp;

或者mysql> show table status like 'emp' \G;

定义存储引擎

在创建表的时候，在create语句最后加上engine=innodb;

或者用alter table语句修改mysql> alter table emp engine=innodb;

查看存储引擎

使用“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 ENGINES\G;”。

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=CSV

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

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。