MySQL 存储引擎对比

一.MySQL 的存储引擎

　　大家应该知道 MySQL 的存储引擎应该是表级别的概念，因为我们无法再创建 database 时指定存储引擎，而是只能在创建表的时候可以明确指定使用哪种存储引擎。因此存储引擎也通常被称作 “表类型”。也就是说，存储引擎是负责跟文件系统真正数据打交道的工具，它却决定了表中是如何存储数据的，不用存储引擎他们指出的工作特性是各不相同的。所以，我们在工作时应该选择时候适合自己的存储引擎。

1>. 查看 MySQL 支持的所有存储引擎

 1 mysql> show engines;
 2 +------------+---------+------------------------------------------------------------+--------------+------+------------+
 3 | Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
 4 +------------+---------+------------------------------------------------------------+--------------+------+------------+
 5 | MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
 6 | CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
 7 | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
 8 | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
 9 | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
10 +------------+---------+------------------------------------------------------------+--------------+------+------------+
11 5 rows in set (0.00 sec)
12 
13 mysql> 

				

					
				
				

					
				
				

					
				
				

					
				
			

2>. 查看某张表的存储引擎

　　使用格式： SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

 1 mysql> show table status in yinzhengjie like 'students'\G        #我们可以用like来过滤
 2 *************************** 1. row ***************************
 3            Name: students                        -------->表名
 4          Engine: InnoDB                        ------->存储引擎
 5         Version: 10                            -------->版本
 6      Row_format: Compact                    -------->行格式
 7            Rows: 11                            -------->现存表中的行数，对于InnoDB的存储引擎的话这个数字可能只是一个估计值，因为innodb支持事物，其中还保存着删除的行数。
 8  Avg_row_length: 1489                        --------->平均每行所包含的字节数
 9     Data_length: 16384                        --------->表中数据总体大小，单位是字节
10 Max_data_length: 0                            --------->表能够占用的最大空间，单位为字节。注意，“0”则表示没有上线哟！
11    Index_length: 16384                        --------->索引的大小，单位也是字节
12       Data_free: 4194304                        ---------->对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间。
13  Auto_increment: 13                        ----------->下一个AUTO_INCREMENT的值
14     Create_time: 2017-11-20 03:57:26            ----------->表的创建时间
15     Update_time: NULL                        ----------->表数据的最后一次修改时间
16      Check_time: NULL                        ------------>使用CHECK TABLE或myisamchk最近一次检查表的时间
17       Collation: utf8_general_ci                    ------------->排序规则
18        Checksum: NULL                        ------------->如果启动，则为表的checksum
19  Create_options:                             ------------->创建表时指定使用的其他选项
20         Comment:                             ------------->表的注释信息
21 1 row in set (0.00 sec)
22 
23 mysql> show table status in yinzhengjie where name='students'\G        #当然用where查询的性能会更好哟！
24 *************************** 1. row ***************************
25            Name: students
26          Engine: InnoDB
27         Version: 10
28      Row_format: Compact
29            Rows: 11
30  Avg_row_length: 1489
31     Data_length: 16384
32 Max_data_length: 0
33    Index_length: 16384
34       Data_free: 4194304
35  Auto_increment: 13
36     Create_time: 2017-11-20 03:57:26
37     Update_time: NULL
38      Check_time: NULL
39       Collation: utf8_general_ci
40        Checksum: NULL
41  Create_options: 
42         Comment: 
43 1 row in set (0.00 sec)
44 
45 mysql> 

				

					
				
				

					
				
				

					
				
				

					
				
			

3>. 行格式

　　MySQL的行格式有：
　　　　{DEFAULT|DYNAMIC|FIXED|COMMPRESSED|REDUNDANT|COMPACT}，
　　　　
　　其中DYNAMIC|FIXED|COMMPRESSED是MyISAM常用的的，而InnoDB即可以使用MyISAM常用的类型还可以使用REDUNDANT|COMPACT等等。 

				

					
				
				

					
				
				

					
				
				

					
				
			

二.MyISAM 和 InnoDB 的存储引擎对比

1>. 什么是表空间（table space）

　　它是由InnoDB管理的特有格式数据文件，内部可同时存储数据和索引。

				

					
				
				

					
				
				

					
				
				

					
				
			

2>.MyISAM 数据存储结果

　　MySQL的每个表都在数据库目录下存储三个文件，名称如下：
　　　　a>.tb_name.frm #存储表结构
　　　　b>.tb_name.MYD #存储数据
　　　　c>.tb_name.MYI #存储索引

				

					
				
				

					
				
				

					
				
				

					
				
			

3>.InnoDB

　　Innodb有两种格式：
　　　　a>.innodb_file_per_table=OFF,即是用共享表空间
　　　　　　每个表一个独有的格式定义文件：tb_name.frm
　　　　　　还有一个默认位于数据目录下的共享的表空间文件：ibdata#（它是会自动增长的，比如ibdata1,ibdata2,...）
　　　　b>.innodb_file_per_table=ON，即是用独立表空间
　　　　　　每个表在数据库目录下存储两个文件
　　　　　　　　tb_name.frm #存储表结构
　　　　　　　　tb_name.ibd #存储数据

				

					
				
				

					
				
				

					
				
				

					
				
			

4>. 修改默认的存储引擎

　　通过default_storage_engine服务变量实现。可以用mysql> show variables like '%default%'来查看相应的信息。

				

					
				
				

					
				
				

					
				
				

					
				
			

三.MySQL 各存储引擎的特性

1>.InnoDB 存储引擎

　　a>.支持事务:
　　　　　　有事务日志，这些事物日志文件的大小是一致的哟！）：ib_logfile#（例如：ib_logfile0，ib_logfile1,....）；
　　b>.支持外键约束；
　　c>.支持MVCC(多版本并发控制)；
　　d>.支持聚簇索引：
　　　　　　聚簇索引之外的其他索引，通常称之为辅助索引，一张表聚簇索引只能有一个而辅助索引可以有多个。聚簇索引通常是用主键的方式来实现，（因为主键的数据是不允许重复的）；
　　e>.行级锁：间隙锁；
　　f>.支持使用辅助索引；
　　g>.支持自适应的hash索引；
　　h>.支持热备份，因此数据不需要离线就可以完成备份啦，当然可能要用独立表空间；

				

					
				
				

					
				
				

					
				
				

					
				
			

2>.MyISAM

　　MyISAM的使用场景：　　　　只读数据，表较小，能够忍受崩溃后的修复操作和数据丢失，它具有一下特点：
　　　　　　a>.全文索引
　　　　　　b>.支持表压缩存放：做数据仓库，能节约存储空间并提升性能
　　　　　　c>.支持空间索引
　　　　　　d>.表级锁
　　　　　　e>.延迟更新索引
　　　　　　f>.不支持事务、外键和行级锁
　　　　　　g>.崩溃后无法安全恢复数据
　　MySQL的mysql库使用就是MyISAM存储引擎，我们可以使用“mysql> show table status in mysql\G”进行查看。

				

					
				
				

					
				
				

					
				
				

					
				
			

3>.ARCHIVE（归档）

　　仅支持INSERT和SELECT，支持很好压缩功能。应用于存储日志信息，或其他按照时间序列实现的数据采集类的应用。它不支持事物，不能很好的支持索引。

				

					
				
				

					
				
				

					
				
				

					
				
			

4>.CSV

　　将数据存储为CSV格式，不支持索引，仅使用与数据交换场景。说白了就是可以在多种不同的数据库之间来回导入导出数据库的。也就是说我们可以将office的文档保存为CSV就可以直接向MySQL数据库导入哟。

				

					
				
				

					
				
				

					
				
				

					
				
			

5>.BLACKHOLE

　　又名黑洞存储引擎，没有存储机制，任何发往次引擎的数据都会丢弃，其会记录二进制日志，因此，常用于多级复制架构中作中转服务器。

				

					
				
				

					
				
				

					
				
				

					
				
			

6>.MEMORY

　　保存数据在内存中，它是一个内存表（也就是说重启操作系统之后就会丢失该表的数据哟！）；常用于保存中间数据，如周期性的聚合数据等，也用于实现临时表，支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型。

				

					
				
				

					
				
				

					
				
				

					
				
			

7>.MRG_MYISAM

　　是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表。它是传统的MySQL存储引擎，现在基本上很少有人用了，因为一个表的数据内容本来就很大了，人们更多考虑的是如何将一张表的内容如何拆分开来。

				

					
				
				

					
				
				

					
				
				

					
				
			

8>NDB

　　是MySQL CLUSTER中专用的存储引擎。它是集群用的一种存储引擎，但是这种集群案例用的几乎寥寥无几。

				

					
				
				

					
				
				

					
				
				

					
				
			

四。第三方的存储引擎

1>. 在线存储引（简称，OLPT），常见的有以下三种：

　　XtraDB
　　　　它是增强的InnoDB，有Percona提供。
　　　　编译安装时，下载XtraDB的源码替代MySQL存储引擎中的InnoDB的源码（当然你得把XtraDB的名称改为InnoDB哟）
　　PBXT：
　　　　MariaDB自带此存储引擎
　　　　支持引擎级别的复制、外键约束，对SSD磁盘（固态磁盘）提供适当支持，当然也支持事务和MVCC等等。
　　TokuDB：
　　　　使用 Fractal Trees索引，适用存储大数据，拥有很好的压缩比，已经被引入MariaDB。

				

					
				
				

					
				
				

					
				
				

					
				
			

2>. 按照列式数据存储引擎分为以下几种 4 种：

　　Infobright
　　　　目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计，分为商业版本和社区版本，其他三个都不是特别有名。
　　InfiniDB
　　MonetDB
　　LucidDB

				

					
				
				

					
				
				

					
				
				

					
				
			

3>. 按照开源社区存储引擎分为以下几种：

　　Aria：　　　　前身为Maria，是增强版的MyISAM（支持崩溃后安全恢复，支持数据缓存）
　　Groona：　　　　全文索引引擎
　　Mroonga：　　　　是基于Groona的二次开发版
　　OQGraph：　　　　由open query研发，支持图（网状 ）结构的存储引擎
　　SphinxSE：　　　　为Sphinx全文搜索服务器提供了SQL接口
　　Spider：　　　　能将数据切分成不同的分片，比较高效透明的实现了分片（shared），并支持在分片上支持并行查询

				

					
				
				

					
				
				

					
				
				

					
				
			

五。如何选择合适的存储引擎

　　我们一般从以下几个维度衡量：

　　　　1>. 是否需要事务；

　　　　2>. 备份的类型的支持；

　　　　3>. 崩溃后的恢复；

　　　　4>. 特有的特性；