Fork me on GitHub
Fork me on GitHub

MySQL存储引擎对比

发表于   |   更新于   |   分类于   |   阅读次数

MySQL存储引擎

存储引擎也通常称作“表类型”。可以在创建表时指定其存储引擎。

mysql> SHOW ENGINES;    #查看支持的存储引擎。
mysql> SHOW TABLES STATUS [LIKE clause] [WHERE clause] #使用WHERE比使用LIKE性能好
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] #如果设定了数据库，就不需要指定[{FROM | IN} db_name]

【示例】：查看某张表的存储引擎

mysql> SHOW TABLE STATUS IN hellodb WHERE Name='classes'\G
*************************** 1. row ***************************
               Name: classes
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 8
     Avg_row_length: 2048
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 9437184
     Auto_increment: 9
        Create_time: 2014-04-08 11:14:52
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.01 sec)
  • Name: 表名
  • Engine: 存储引擎
  • Version: 表的版本
  • Row_format: 行格式。这些行格式是MySQL在其内部存储行数据时所采用的格式。每一种不同格式，其存储空间、开销是各部相同的。对于MyISAM表，常用的通常有DYNAMIC、FIXED或者COMPRESSED。而对于InnoDB存储引擎来讲，还可以额外使用REDUNDANT、COMPACT等类型。对于InnoDB来讲，默认是COMPACT。
    {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  • Rows: 表中的行数。【注意】:对于MyISAM表来讲是精确的，但是对于InnoDB来讲未必精确。因为InnoDB基于多版本并发控制，里面有多种版本的快照，所以有些行你删了里面依然可能有。所以InnoDB表里的行数可能是个估计值。
  • Avg_row_length: 平均每行所包含的字节数。
  • Data_length: 表中数据总体大小，单位是字节。Data_length:=RowsXAvg_row_length。
  • Max_data_length: 表能够占用的最大空间，单位为字节。0表示没有上限。
  • Index_length: 索引的大小，单位为字节。
  • Data_free: 对于MyISAM表，表示已经分配但尚未使用的空间(就是划分给这个表的块的剩余空间)，其中包含此前删除行之后腾出来的空间。对于InnoDB表，就比较复杂了。
  • Auto_increment: 下一个AUTO_INCREMENT的值；
  • Create_time: 表的创建时间；
  • Update_time：表数据的最近一次的修改时间；
  • Check_time：mysql的一些工具，比如使用CHECK TABLE或myisamchk最近一次检测表的时间；
  • Collation: 排序规则；
  • Checksum: 如果启用，则为表的checksum；为空表示没有启用校验功能。但是请注意，每次修改表，这个值也会变动，会产生额外的I/O，性能会有影响，但是对表的一致性和数据完整性提供了额外保证；
  • Create_options: 创建表时指定使用的其它选项，比如SQL_Cache等选项；
  • Comment: 表的注释信息。

存储引擎中的数据和数据文件

InnoDB

两种格式：
1、innodb_file_per_table=OFF，即使用共享表空间。

  • 每张表有一个独有的格式定义文件: tb_name.frm
  • 还有一个默认位于数据目录下共享的表空间文件：ibdata# (一个表空间文件大小到达一定程度后，会启动第二个表空间文件)

2、innodb_file_per_table=ON，即使用独立表空间。
每个表在数据库目录下存储两个文件：

  • tb_name.frm
  • tb_name.ibd

表空间：table space，由InnoDB管理的特有格式数据文件，内部可同时存储数据和索引(因此是聚簇索引)。

MyISAM

每个表都在数据库目录(data目录)下存储三个文件：

  • tb_name.frm #表结构的定义
  • tb_name.MYD #数据
  • tb_name.MYI #索引

如何修改默认存储引擎：通过default_storage_engine服务变量实现。要想永久生效，需要写到配置文件中，重启生效。

各存储引擎的特性

mysql 5.3.8之后mysql就被oracle收购了，原生自带InnoDB存储引擎了。(以前InnoDB是插件形式工作的，但是自从oracle收购InnoDB后做了很大改进，再到后来收购了sun，InnoDB就自然成为原生的了。)

InnoDB

  • 支持事务：有事务日志。为什么要有事务日志啊？因为可以它能够把随机I/O改为顺序I/O。事务日志在数据目录下，至少有两个，而且大小固定，不会增长，而且一定是磁盘上的一段连续存储空间。
  • 支持外键约束：MyISAM是不支持的，因为不支持事务就很难使用外键。
  • MVCC：多版本并发控制。
  • 支持聚簇索引：也叫聚集索引。说白了就是数据和索引存储在一起，通过表空间来实现的。也就意味着创建InnoDB表时必须要有主键。这样的话InnoDB表的查询比MyISAM表的查询多了一步，因为所有非聚集索引(又称为辅助索引)不是指向数据的，而是指向聚簇索引的。什么意思呢？(该节视频31分钟左右开始)。
    聚簇索引之外的其它索引，通常称为辅助索引。聚簇索引只能有一个，辅助索引可以有多个。
  • 行级锁：行级锁之上它在实现MVCC机制时是基于”间隙锁”实现的。在行和行之间加间隙锁来隔离行。
  • 支持辅助索引：有些NoSQL只支持一种索引。
  • 支持自适应hash索引
  • 支持热备份：因此数据库不用离线就可以实现备份了。

【补充】:聚簇索引和辅助索引。
位于磁盘上的数据，我们需要把所有载入内存才可以查询的，太慢了。于是我们把表中数据按我们经常查询的叫搜索键，通常是一个字段，通常我们用于WHERE子句中比较的字段，把它抽取出来做索引就好了，这样可以提高查询速度的。这个索引文件如果是额外存放的，意味着要把这个字段的值复制一份出来，放在另外一个位置，这就是索引文件。但是这索引信息是要被排序的，它把表中的字段拿出来后重新排了下序。排完序以后，一个字段对应的那个行在哪？也就是我们找到索引后，怎么找到对应的整行数据？索引里面有原数据，只有一部分，只有你所抽取的字段，如果你找的正好是那个字段，那就不用去查原来的表了。比如SELECT Age FROM students WHERE Age > 30，那就意味着索引要建立在Age上才行，假如我们在Age上创建了索引，意味着索引文件中已经都有了Age字段的所有数据，正好我们找的就是Age，但是如果我们找的不是Age这个字段，而是Name，那么就需要先找到符合索引条件(Age > 30)的条目，这个条目有指向其在原数据中的行的位置，接着就找到行所在的磁盘块，把这个磁盘块载入内存，对应表中的那个行的所有字段就找到了，从而Name也就找到了。

图中，索引和数据是分开存放的，也就意味着我们找到索引以后，还得做第二次I/O，找到那个真正数据所在磁盘上的位置，然后把数据读进来。
那什么是聚簇索引呢？索引条目和行是挨着存放的，所以你只要能找到索引，就直接能找到这个行了。一个表能有几个聚簇索引呢？索引是排序的，我们按年龄排序和按姓名排序，其结果不一样。由于索引和数据是存放在一起的，那就意味着索引怎么排序，数据就一定是怎么排序的。如果索引按年龄排序存放了，数据也就按年龄排序存放了，很显然不能在拿姓名来排序存放了。所以对于一张表来讲，聚簇索引只能有一个。非聚簇索引可以有多个，因为排序只是索引本身，不是数据。而对于InnoDB表来讲，非聚簇索引的数据指针并不是指向数据的，而是指向聚簇索引。因此查找InnoDB表分两步，先找辅助索引，然后根据辅助索引找到聚簇索引，然后在找到数据。所以选择一个好的聚簇索引能够非常好的提升InnoDB的查询性能的。
因此对于InnoDB表来说必须有一个聚簇索引，而聚簇索引通常用主键来实现，因为主键中的数据是不允许重复的，所以索引中出现重复的可能性就没有了。因此我们通常提供一个主键用于聚簇索引。
当然，无论是聚簇索引还是辅助索引都是B树索引，因为聚簇和辅助只是指明了它的存储位置是否和原数据存储在一块的，但B树指的是索引自己的内部结构，数据结构。你可以把房子盖到市中心，也可以盖到郊区，但房子本身可以是别墅或一室两厅。一个按位置来讲，一个是按数据结构来讲。B树是一种数据结构。

MyISAM

  • 支持全文索引：InnoDB不支持。但是InnoDB不支持，好在可以借助额外的其他工具来完成对InnoDB表的全文索引。因此这对于InnoDB来说不是一个缺陷。比如编译Mroonga存储引擎，这是一个很独特的存储引擎。
  • 支持表压缩：用于实现数据仓库，能节约存储空间并提升性能。只不过压缩以后就不能修改了，只能查询。所以在压缩的时候主要是用来做数据仓库的。
  • 支持空间索引：InnoDB也支持空间索引。要想使用空间索引，得使用空间函数，才能完成里面的很多数据的操作。
  • 支持表级锁：不支持行级锁，所以锁开销比较小。如果读写比例是9:1或8:2，对MyISAM是非常理想的。如果读写差不多比例，那么使用MyISAM其性能就下降太厉害了。所以读写差不多的话，建议使用InnoDB表。

  • 支持延迟更新索引：很多时候索引是为了提升读操作的，那么对写操作会有延迟。好在MyISAM支持延迟更新索引键。创建表有个选项delay_key_write，延迟键更新。这样每当数据更新时，不必要立即更新索引，使得其I/O压力就降低了。

  • 不支持事务、外键和行级锁
    崩溃后无法安全恢复数据(你可能得使用repairtable这样的命令去修复表，而这个修复过程通常是不可控的。不像InnoDB，如果崩溃了，InnoDB存储引擎会自行根据事务日志进行恢复)

  • 适用场景：只读数据、较小的表、能够容忍崩溃后的修改操作时长和数据丢失。

事实上，mysql库中的表都是MyISAM的，因为mysql这个库是数据字典，而数据字典大多数情况下都是一次创建，但是我们对表的读取却是多次的，所以是一种多读少写的场景，而且表也不会特别大。

mysql> SHOW TABLE STATUS IN mysql\G

ARCHIVE

  • 仅支持INSERT和SELECT，支持很好压缩功能；

  • 适用于存储日志信息，或其它按时间序列实现的数据采集类的应用；

  • 不支持事务，不能很好的支持索引；

CSV

  • 将数据存储为CSV格式；不支持索引；仅适用于数据交换场景；
    你可以将excel中表中数据存储为csv格式的，然后在把它导入到mysql中

BLACKHOLE

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

MEMORY

  • 保存数据在内存中，内存表；常用于保存中间数据，如周期性的聚合数据等；也用于实现临时表
  • 仅支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型；

MRG_MYISAM

是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表；
但是现在很多应用场景中存的数据量非常大，而一张表中存的数据过多，查询的时候性能会很差。所以很多时候需要借助一些机制将一个表切割成多张表，更别说合并表了。所以这个引擎很少用。

NDB

是MySQL CLUSTER中专用的存储引擎。是位于内存中完成数据存储和交换的，能够实现分布式、不做任何共享的、容灾的、高可用的mysql集群，但实际上这种集群没人用。
其实早期NDB是爱立信的一个数据库，而后2003年mysql AB公司从爱立信收购了NDB，随后把NDB转换为了存储引擎。

第三方的存储引擎

OLTP类：在线事务处理

  • XtraDB: 增强版的InnoDB，由Percona提供。
    编译安装时，下载XtraDB的源码替换MySQL存储引擎中的InnoDB的源码。然后重命名XtraDB为InnoDB，接着编译安装mysql，就可以取代InnoDB，只不过仍然叫做InnoDB。

  • PBXT: MariaDB自带此存储引擎。
    支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持；
    支持事务、MVCC。

  • TokuDB: 使用Fractal Trees(分形树)索引(这个索引很新，性能很好)，适用存储大数据，拥有很高的压缩比；已经被引入MariaDB。事实上，在mysql被oracle收购后，Apple就立即抛弃了mysql，转向了pgSQL。Apple的领导人很有远见的，迟早有一天要抛弃，还不如早点抛弃。现在Google、Facebook都在纷纷抛弃mysql，转向MariaDB。而Redhat7不在提供mysql，而默认提供MariaDB。可以预见，CentOS、Fedora以后都会转向MariaDB。

列式存储引擎(NoSQL用的多)

Mysql数据是这么存的，所有数据在存储时首先划分成逻辑块的，在每一个逻辑块中存的是数据行，一行一行存，不够存的话，再找一个数据块存。而列式存储是按列存的，先存一个字段。这对于按列查询会很有效，比如找所有用户，找到Name那个字段就可以了。web应用常用的反而是列式查询。

  • Infobright: 目前较有名的列式存储引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计，有商业版和社区版。但是要想使用Infobright存储引擎，需要对mysql做定制。
  • InfiniDB
  • MonetDB
  • LucidDB

开源社区提供的存储引擎

  • Aria：前身为Maria，可理解为增强版的MyISAM(支持崩溃后安全恢复，支持数据缓存。MyISAM只支持索引缓存，不支持数据缓存)
  • Groona：全文索引引擎，Mroonga是基于Groona的二次开发版。
  • OQGraph: 由Open Query研发，支持图结构的存储引擎。
    图是最难处理的数据结构。常用的数据结构有4种：顺序结构(线性结构)、链式结构、树型结构和图。
  • SphinxSE: 为Sphinx全文搜索服务器提供了SQL接口。这个已经被整合进了MariaDB了。
  • Spider: 能将表中的物理数据切分成不同分片，比较高效透明地实现了分片(shared)，还可以存于不同的服务器上，并支持在分片上支持并行查询。就像很多NoSQL的分片。

索引类型

  • 聚簇索引

  • 辅助索引

  • B树(B+树)索引

  • R树索引：空间索引
  • hash索引
  • 全文索引：全文搜索，比如在某帖子里搜索内容。
  • 位图索引：oracle支持，mysql不支持。

如何选择？

  1. 是否需要事务
  2. 备份的类型的支持
  3. 崩溃后的恢复
  4. 特有的特性

一般我们建议默认采用InnoDB，除非你是建立数据仓库时，我们建议使用MyISAM。MySQL5.5.8之前默认的存储引擎是MyISAM，而后其他版本默认是是InnoDB。
如果多个存储引擎满足你的需求，做测试，做性能剖析。

优化没有任何放之四海而皆准的办法，你需要建立对对方良好的精确理解的基础上，并根据自己的需求去做选择。所以不要从网上搜一些文章朝自己的服务器咣当设置，就以为对服务器做优化了。