MySQL性能优化（二）——MySQL存储引擎

概念

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

说了一堆，总结一下：

• 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。
• 存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
  另外，在Oracle，Sql Server等数据库只有一种存储引擎。而MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL存储引擎分类

不同版本的MySQL，所支持的存储引擎不尽相同。首先让我们来看一下MySQL提供了哪些存储引擎，以我安装的5.7版本的MySQL为例：

但当前数据库，不支持FEDERATED，默认的存储引擎为InnoDB。每一种存储引擎，有不同特性，也有其各自的适用场景，下面将一一道来。

MyISAM

特性

• MySQL5.5之前默认的存储引擎；
• 不支持事务；
• 并发性（高并发读）；
• 锁级别-表级锁；
• 支持数据文件（索引文件、数据文件）的压缩。

文件结构

通过如下语句，我们创建一个MyISAM存储引擎的表testmysam：

CREATE TABLE testmysam (
    `id` VARCHAR (255) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8

创建成功后，找到我们存放表数据的目录：

进入/var/lib/mysql，找到testmysam表相关的数据文件：

可以看到，保存MyISAM存储引擎数据表的文件有.frm、.MYD、.MYI三种格式的文件。

• .frm文件是任何存储引擎都会有的文件，用来存储表结构；
• .MYD是数据文件，存储表数据；
• .MYI是索引文件，存储表索引。

因为数据和索引是分开储存的，因此MyISAM的索引是非聚集索引（如果是存放在一个文件中，则为聚集索引）。

适用场景

• 非事务型应用（数据仓库，报表，日志数据）；
• 只读类应用；
• 空间类应用（空间函数，坐标）。

Innodb

特性

• MySql 5.5以及以后版本默认存储引擎；
• 事务性存储引擎；

• 完全支持事务得ACID特性；
• Redo Log 和 Undo Log；

• Innodb支持行级锁（并发程度更高）；
• 支持系统表空间和独立表空间。

• 独立表空间、系统表空间

  在表存储引擎为Innodb时，MySQL增加了一个系统表空间和独立表空间的概念。

  在MySQL5.6以前默认为系统表空间，5.6（包含）之后默认使用独立表空间。

  MySQL提供了一个innodb_file_per_table参数，来控制使用独立表空间还是系统表空间。

  

  如图，当innodb_file_per_table值为ON时，表示开启独立表空间。为OFF表示使用系统表空间。

  如果要关闭或开启独立表空间，可以使用如下语句控制：

  set global innodb_file_per_table= on / off;
  

  • 系统表空间

  多个Innodb的表数据和索引保存在同一个文件中，这个文件被抽象出的概念，称作系统表空间，也叫共享表空间。

  例如，新建一个使用Innodb的表s4，并将独立表空间关闭，启用系统表空间：

  CREATE TABLE s4 (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(10) DEFAULT NULL,
    addr varchar(20) DEFAULT NULL,
  

  在文件系统中，找到s4的表数据文件目录：

  

  会发现，在测试数据库的文件存储目录下，只有一个.frm文件，也就是只保存了数据表结构的定义，那么数据保存在哪儿呢？

  我们cd进入到data的主目录下：

  

  会找到一个idbdata* 的文件，这便是我们使用系统表空间时，Innodb表所共用的数据文件了。

  • 独立表空间 每一个Innodb表数据和索引，被单独的存放在一个独立的文件中，该文件被抽象的称为独立表空间。

  同样我们创建一个Innodb表s4，打开数据库文件存储目录：

  

  可以看到，在数据库目录下，不仅有s4.frm文件，还有一个s4.ibd文件，这便是单独用于存放s4表数据和表索引的文件。

  • 系统表空间、独立表空间区别
  1. 系统表空间无法简单的收缩文件大小，即不支持压缩；
  2. 独立表空间可以通过optimize table收缩系统文件；
  3. 系统表空间会产生IO瓶颈；
  4. 独立表空间可以同时向多个文件刷新数据。

  因此，当我们使用InnoDB时，最好使用独立表空间。

  Innodb使用场景

  • Innodb适合于大多数OLTP（联机事务处理）应用，也就是需要使用事务的场景。

  MyISAM与Innodb对比

  

  CSV

  特性

  • 数据以文本方式存储在文件；
  • 以csv格式进行数据存储；
  • 所有列都不能为null的（可自行试验），且不支持自增；
  • 不支持索引（不适合大表，不适合在线处理）；
  • 可以对数据文件直接编辑（保存文本文件内容），修改完毕后，需要执行flush tables指令。

  文件结构

  同样，我们创建一个CSV存储引擎表：

  CREATE TABLE mycsv (
    id int(11) NOT NULL,
    c1 varchar(10) NOT NULL,
    c2 char(10) NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8
  

  在MySQL数据目录下，找到mycsv的数据文件：

  

  如图，csv表的数据，由.CSM、.CSV、.frm三个文件组成，.frm与其它存储引擎一样，是存储表结构的文件，说一下另外两个的作用：

  • .CSM

  .CSM文件存储表的元数据，如表状态和数据量。

  • .CSV

  .CSV文件存储表数据内容。

  使用场景

  由于CSV存储数据采用.CSV格式文件存储，该种格式文件可以直接通过Excel等第三方软件打开，且无需转换，因此比较适合用于存储一些报表类的数据，如财务报表。

  Archive

  Archive是一种使用相对较少，使用场景相对受限的一种存储引擎。

  特性

  • 只支持insert和select操作；
  • 只允许在自增ID列上加索引；
  • 以zlib对表数据进行压缩，磁盘I/O更少；
  • 数据存储在ARZ为后缀的文件中。

  Archive表的数据文件以.ARZ格式的文件存储，该类型的文件，是经过压缩存储的，因此读写Archive表的数据，会占用更小的磁盘I/O。

  

  使用场景

  • 日志和数据采集应用。

  由于Archive只支持写和读操作，并且拥有低I/O的特性，因此比较适合用于归档日志的存储，因为在生产环境中，我们通常只需要存储日志，在出现问题的时候，查询日志，或者读取日志，进行一些分析操作，同时这类辅助操作，我们希望尽可能小的占用影响系统性能的资源。数据采集也同样如此。

  Memory

  特性

  • 所有数据保存在内存中；
  • 支持HASH索引和BTree索引；
  • 所有字段都是固定长度 varchar(10) = char(10)；
  • 不支持Blog和Text等大字段；
  • Memory存储引擎使用表级锁；
  • 最大大小由max_heap_table_size参数决定；
  • memory数据易丢失。

  Memory虽然在日常开发中，使用场景很少，但是对MySQL自身来说，却是使用的比较多的一个存储引擎，这里就不做过多讲解，如果感兴趣，可自行了解一下。

  Memory的数据，是保存在内存中的，在文件系统中，一张Memory表，只有一个保存表定义的.frm文件。

  

  由于数据存储在内存中，因此Memory表的数据，在重启MySQL之后，数据会消失。Memory还有一个值得关注的特性便是字段长度固定。如果我们声明两个字段，一个是varchar(10)，一个是char(10)，那么两个字段最终都会以char(10)来保存，同时，即便我们存储一个变量，只占两个字符，但是在内存中，该变量任然占用10个字符的空间。

  Memory VS 临时表

  Memory存储表和临时表都是存储于内存中，但是二者是有区别的，Memory表的数据，存储在内存中，只要数据库不重启，数据是一直存在的，在这期间，我们可以任意访问。而临时表的数据，虽然也是存储在内存中，但是该数据的生命周期是会话级的，什么意思呢？也就是说我们通过客户端，与MySQL创建了会话，在该会话期间，我们创建一个临时表，此时，我们再打开另外一个会话，去查询我们先创建的临时表，那是查询不到的。

  

  适用场景

  • hash索引用于查找或者是映射表（邮编和地区的对应表）；
  • 用于保存数据分析中产生的中间表；
  • 用于缓存周期性聚合数据的结果表。

  Ferderated

  特性

  • 提供了访问远程MySQL服务器上表的方法；
  • 本地不存储数据，数据全部放到远程服务器上；
  • 本地需要保存表结构和远程服务器的连接信息。

  Ferderated存储引擎，默认是不支持的，如果要开启该存储引擎，需要在MySQL安装目录下的my.ini文件中，加上fedetated=1。重启MySQL即可生效。

  由于Ferderated表数据不存储本地文件系统，而全部存储到远程服务器上，因此在本地系统中，除了要存储Ferdetated表结构信息，还要存储远程服务器信息。

  适用场景

  • 统计分析及手工查询。

  原文地址：http://jbdatatech.com/archives/323

  更多精彩，敬请关注：数聚宝技术团队