MySQL 的存储引擎

数据库存储引擎storage engine,又称数据表处理器，它是数据库底层软件的组织。数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。

数据表在硬盘上的存储方式

在文件系统中，MySQL将每个数据库 (也称schema)保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个与表同名的.frm文件保存表的定义。

MySQL使用文件系统的目录来保存数据库和表的定义，大小写敏感性与具体的操作系统的文件系统相关。在Windows中，大小写不敏感；而在Uinux/Linux中则是大小写敏感。

不同的储存引擎保存数据和索引的方式是不同的，但表的定义在MySQL服务器层是统一处理的。

查看有哪些存储引擎可用

mysql> SHOW ENGINES\G;

show_engine.jpg

查询默认存储引擎

mysql> SHOW VARIABLES LIKE 'default_storage_engine%';

show_default_engine.jpg

查询数据表的相关信息

mysql> SHOW TABLE STATUS LIKE 'user'\G;

show_table_status.jpg

下面简单介绍每一行的含义：

Name: 表名。

Engine：表的存储引擎类型。

Row_format：行的格式。可选值有 Dynamic、Fixed或者Compressed。

• Dynamic: 行的长度可变，一般包含可变长度的字段，如VARCHAR或者BLOB。
• Fixed:行的长度是固定，只包含固定长度的列，如CHAR、INTEGER。
• Compressed：行存在压缩表中。

Rows：表中的行数。

Avg_row_length：平均每行包含的字节数。

Data_length：整个表的大小(单位：字节)。

Max_data_length: 表可以容纳的最大数据量

Index_length：索引的大小(单位：字节)。

Data_free: 对于MyISAM表，表示已经分配但目前没有使用的空间。这部分空间包括之前删除的行，以及后续可 以被INSERT利用到的空间。

Auto_increment：下一个Auto_increment的值。

Create_time： 表的创建时间。

Update_time：表数据的最后修改时间。

Check_time： 使用 CHECK TABLE 或myisamchk工具最后一次检查表的时间。

Collation： 表的默认字符集和字符排序规则。

Checksum: 如果启用，则对整个表的内容计算时的校验和。

Create_options：指表创建时的其他所有选项。

Comment：包含了其他额外信息，对于MyISAM引擎，保存的是表创建时带的注释；如果表使用的是innoDB引 擎 ，则保存的是表空间的剩余空间信息；如果是一个视图，则包含了“VIEW”的文本字样。

InnoDB存储引擎

InnoDB是MySQL的默认事务引擎，也是目前最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期事务（大部分情况能正常提交，很少被回滚）。同时，InnoDB的性能和崩溃自动恢复特性使得它在非事务型储存的需求中也被广泛使用。若非有特别原因需要使用其他的储存引擎，建议优先考虑InnoDB引擎。

概述

InnoDB的数据存储在表空间(tablespace)中，表空间是由一系列的数据文件构成，类似一个虚拟的文件系统，它存储和管理所有InnoDB数据表内容。

InnoDB 采用MVCC支持高并发，且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ,且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

InnoDB表基于聚蔟索引建立的，其索引结构和MySQL的其他存储引擎有很大不同，聚蔟索引对主键查询有很高的性能。它的二级索引必须包含主键列，所以主键很大的话，其他索引也会很大。因此若表上的索引比较多，主键应当尽可能小。

四种隔离级别说明

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（SERIALIZABLE）	不可能	不可能	不可能

• 脏读 :一个事务读取到另一事务未提交的更新数据。

• 不可重复读 : 在同一事务中,多次读取同一数据返回的结果有所不同, 换句话说, 后续读取可以读到另一事务已提交的更新数据. 相反, “可重复读”在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。。

• 幻读 :一个事务读到另一个事务已提交的insert数据。

提供的功能

• 支持提交和回滚操作，还可以创建保存点实现部分回滚。

• 在系统奔溃后可自动恢复。

• 外键和引用完整性支持，包括递归式删除和更新。

• 数据行级别的锁定和多版本共存，使得InnoDB数据表在同时检索和更新操作的复杂查询中表现出非常好的并发性能。

• 默认情况下，InnoDB储存引擎会把数据表存储在一个共享的表空间里，表空间可由多个文件构成，类似一个虚拟的文件系统，它存储和管理所有InnoDB数据表内容。

MyISAM 存储引擎

MyISAM是MySQL 5.1 以及之前版本的默认存储引擎，它提供了全文检索、压缩、空间压缩函数等特性。MyISAM不支持事务和行级锁，崩溃后无法安全恢复。如果对于只读数据，或者表比较小，可以忍受修复操作，也可以考虑存储引擎选用MyISAM。

存储

MyISAM将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

MyISAM表可以包含动态或者静态(长度固定)行。

MyISAM表的存储记录数受限于可用的磁盘空间或者操作系统中单个文件的最大尺寸。

特性

加锁与并发

MyISAM对整表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时会对表加排他锁。若表有读取查询时，可以往表中插入新的记录，称之为并发插入。

修复

MySQL可手工或者自动执行检查和修复的操作。执行表的修复可能会导致一些数据的丢失，而且过程非常 慢。

可通过CHECK TABLE mytable检查表的错误，如果有错误可通过REPAIR TABLE mytable命令修复。如果MySQL服务器已经关闭，可以使用myisamchk命令进行检查和修复的操作。

索引特性

支持全文检索，这是一种基于分词创建的索引，可以支持复杂的查询。

延迟更新索引键

创建MyISAM表时若指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引写入磁盘，而是写到内存中的键缓冲区，在清理缓冲区或者关闭表的时候才会把对应的索引块写入到磁盘。此方式极大提升了写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键可以在全局设置，也可以为单表设置。

MyISAM压缩表

MyISAM压缩表适合那些创建并导入数据后，不再进行修改操作的数据表。可以使用myisampack对MyISAM表进行压缩，压缩后不能修改，除非先将表解压后修改数据，然后再压缩。压缩后，减少了磁盘空间占用，因此减少了磁盘I/O，从而提升查询性能。压缩表支持可读索引。

MyISAM 性能

MyISAM最典型的性能问题是表锁的问题,如果发现所有查询都长期处于“Locked”状态，那么就表锁的问题。

---

MySQL内建的其他存储引擎简介

Archive 引擎

Archive只支持INSERT和SELECT操作，它会缓存所有的写并对插入的行进行行压缩，因此它比MyISAM表的磁盘I/O 更少。Archive引擎每次SELECT查询需要执行全表扫描，它适合日志和数据采集类应用，因为这类应用做数据分析时候需要全表扫描。同时也适合需要更快INSERT操作的场合下使用。

Archive支持行级锁和专用缓冲区，可以实现高并发插入。在一个查询开始直到返回表中存在的所有行数之前，它会阻止其他SELECT执行，以实现读一致性。Archive在操作批量插入时，在完成前对读操作是不可见，此机制模仿了事务和MVCC的一些特性，但是它不是一个事务型引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以它适合用于复制数据到备库，或简单地记录到日志。

CSV引擎

CSV引擎可以将CSV文件作为MySQL的表来处理，但它不支持索引。CSV引擎可以在数据库运行适合拷入或拷出文件，可以把Excel等电子表格中的数据存储为CSV文件，然后复制到M一SQL数据目录下，就能在MySQL中打开。

Federated存储引擎

Federated引擎是访问其他MySQL服务器饿一个代理，它创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。

MERGE储存引擎

• 该引擎提供了把多个MyISAM数据表合并为一个虚拟数据表的手段，查询一个MEGRE数据表相当于查询所有成员数据表，这种做法的好处可以绕开文件系统对各个MyISAM数据表的最大长长大限制。
• 构成MERGE数据表的所有数据表必须具有同样的结构。
• 该引擎适用于日志或数据仓库类应用。

Memory储存引擎

该引擎把数据表保存在内存中，数据表具有固定不变的数据行，因此它的检索非常快，但是服务器断电时，表的内容也随之消失。如果需要快速访问数据，而且这些数据不会被修改，重启后丢失也没关系，可以考虑使用Memory存储引擎。

特点：

• 使用散列索引，因此进行“相等比较”速度非常快，但是进行“范围比较”速度比较慢。散列索引适合使用“=”和“<=>”，不适合使用“<”和“>”，同样散列索引也不适合用在ORDER BY字句里。
• 该引擎的数据表的数据行里使用长度固定不变的格式，以此加快处理速度。不能使用BLOB和TEXT这样长度可变的数据类型。VARCHAR是一种长度可变的类型，但是在MySQL内部被当作一种固定不变的CHAR类型。

适合使用的场景：

• 用于查找（lookup）或者映射（mapping）表。
• 用于缓存周期性的聚合数据的结果。
• 用于保存数据分析中产生的中间数据。

Falcon储存引擎

• 支持提交和回滚的操作。
• 在系统奔溃后可自动恢复。
• 灵活的锁定级别和多版本共存，在同时检索和更新操作的复杂查询中表现出非常好的并发性能。
• 在储存时对数据行进行压缩，在检索时对数据行进行解压缩以节省空间。
• 日常管理和维护开销低。

NDB储存引擎

该引擎是MySQL的集群储存引擎。

命令总结

查看有哪些存储引擎可用

mysql> SHOW ENGINES\G;

查询默认存储引擎

mysql> SHOW VARIABLES LIKE 'default_storage_engine%';

查询数据表的相关信息

mysql> SHOW TABLE STATUS LIKE 'user'\G;