Mysql的存储引擎介绍-蒲公英云

1.前言

mysql> show table status like 'user'  ;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| user | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |            0 |         0 |              4 | 2018-10-10 16:13:48 | NULL        | NULL       | utf8_general_ci | NULL     |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set

Engine 就是表所对应的存储引擎。

在文件系统中，MySQL会把每个数据库(也叫做架构)保存为数据目录下的一个子目录。当创建一个表时，MySQL会在和表名同名的、以.frm 为后缀的文件中存储表的定义。例如，当创建一个名为MyTable的表时，MySQL将在MyTable.frm中存储相关表定义。因为MySQL使用文件系统来存储数据库名和表定义，大小写敏感性将依赖于具体的平台。在Windows平台上，MySQL的实例(Instance) 名、表名、数据库名都是大小写不敏感的；在Unix类平台上，则是大小写敏感的。每种存储引擎对表数据和索引的存储方式有所不同，但表定义是由MySQL服务器独立处理的。可以通过命令show table status like table来区别。

2.mysql都有哪些引擎

2.1 MyISAM 引擎

作为 MYSQL的默认存储引擎，在性能和可用特征之间， MYISAM提供一种良好的平衡，这些特征包括全文检索(Fu- Text Indexing)、压缩、空间函数(GlS)。 MYISAM不支持事务和行级锁。

存储

一般来说， MYISAM将每个表存储成两个文件:数据文件和索引文件。两个文件的扩展名分别为**.MYD和MYI**。MYISAM的格式是平台通用的，这意味着用户可以在不同架构的服务器上毫无问题地相互拷贝数据文件和索引文件。例如，可以从 Intel架构的服务器上复制文件到 Powerpc或 Sun SPARC架构的服务器。

MYISAM表可以包含动态行( Dynamic Row)和静态行( Static Row，即固定长度行)。MYSQL会根据表定义决

定选用何种行格式。 MYISAM表的可容纳的行总数，一般只受限于数据库服务器的可用磁盘空间大小，以及操作系统允许创建的最大文件大小。

在 MYSQL5.0中，默认配置的含有可变长行定义的 MYISAM表可以支持256TB的数据处理，并使用6字节的指针记录数据。更早的 MYSQL版本默认使用4字节指针，最大可处理4GB数据。所有的 MYSQL版本都支持最大8字节的指针。如果想改变 MYISAM表上的指针大小(调高或调低)，必须在表创建选项 MAX ROWS和AVG_ROR_ LENGTH中，指定相关的值，这些选项代表了用户预计使用的表大小。

CREATE TABLE mytable INTEGER NOT NULL PRIMARY KEY,
 b CHAR (18) NOT NULL
) MAX ROWS = 1000000000 AVG ROW LENGTH = 32

在这个例子中，要求 MYSQL为表数据至少准备32GB的数据存储容量。

MyISAM特性

作为 MYSQL历史最悠久的存储引擎， MYISAM的许多特性已被开发出多年，用于满足用户的切身需要。

加锁与并发

MYISAM对整张表进行加锁，而不是行。读取程序( Reader)在需要读取数据时，在所有表上都可以获得共享锁(读锁)，而写入程序( Writer)可以获得排他锁(写锁)。用户在运行 select 查询时，可以在同张表内插入新行(也称之为并发插入)。这是一个非常重要和有用的特征。

自动修复

MySQL支持对 MYISAM表的自动检查和自动修复。

手工修复

用户可以使用 CHECK TABLE mytable和 REPAIR里 ABLE mytable命令，检査表中的错误，并修复错误。当服务器离线时，也可以使用 myisamchk命令行工具检查和修复表。

索引特性

在 MYISAM表中，用户可以基于BLOB或TEXT类型列的前500个字符，创建相关素引文索引，它可以根据个别单词，为复杂的搜索选项创建相关索引。

延迟更新索引( Delayed Key Writes)

使用表创建选项 DELAY KEY_RITE创建的 MYISAM表，在査询结束后，不会将索引的已改变数据写入磁盘，而是在内存的键缓冲区(In- memory Key Buffer)中缓存索引改变数据。它只会在清理缓冲区，或者关闭表时，才将索引块转储到磁盘。对于数据经常改变，并且使用频繁的表，这种模式大大提高了表的处理性能。不过，如果服务器或系统崩溃，索引将肯定损坏，并需要修复。用户可以使用脚本，如运行 myisamchk工具，在重启服务器前进行修复，也可以用自动恢复( Automatic Recovery)选项进行修复(即使没有选用DELAY KEY 9RIT选项，这些修复上的安全措施也是很有价值的)。延迟更新索引特性可以被全局配置，也可以为个别表单独配置。

2.2 MyISAM Merge引擎

Merge引擎是MyISAM的变种。合并表(Merge Tabie)是指将几个相同的MyISAM表合并为一个虚表(Virtual Table)。它对于MySQL记录日志或数据仓库应用特别有用。

2.3 InnoDB 引擎

InnoDB专为事务处理设计的一款存储引擎，特别是用于处理大量短期(Short-lived)事务，短期事务是指般能正常完成，不需要回滚的事务。InnoDB 仍然是一种最广泛应用的事务性存储引擎。它的性能水平和崩溃后自动恢复特性，也让它在非事务性存储应用中很流行。

InnoDB将所有数据共同存储在一个或几个数据文件中，这种数据文件-般称之为表空间(Tablespace)。表空间本质上是一种“黑盒”(Black box)，在“黑盒”内，InnoDB自我管理-切数据。在MySQL4.I版及更新版本中，InnoDB也支持将每个表和相关索引存储为单独的分离文件

InnoDB使用MVCC机制获取高并发性能，并且实现所有四个标准隔离级。它的默认隔离级为REPEATABLE READ，在这个隔离级上，它使用了间隙锁(Next-key locking)策略防止“幻读”问题的产生:不仅对查询中读取的行进行加锁，而且还对索引结构中的间隙(Gaps) 进行加锁，以防止幻影(Phantom) 插人。

InnoDB表是基于聚簇索引建立的。InnoDB 的索引结构，非常不同于其他大多数MySQL存储引擎，因此，它能提供一种非常快速的主键查找性能。不过，它的辅助索引(Sccondary Index，也就是非主键索引)也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

直至本书写作时，InnoDB还不能根据排序(Sorting) 建立索引，而MyISAM支持。因此，当InnoDB加载数据和创建索引时，要比MyISAM慢很多。任何改变InnoDB表结构的操作，会导致整个表的重建，包括重建所有索引。

在最初设计InnoDB时，大多服务器仍然是低速硬盘、单CPU和有限的内存。如今，支持海量内存、高速硬盘的多核CPU服务器已经越来越便宜了。InnoDB也面临着一些扩展性问题。

InnoDB的开发人员正着手解决这些问题，不过在本书写作时，有些问题仍未完全解决。