MySQL（九）之MySQL存储引擎

378

　　一、MVCC
　　1、简介
　　Multi-Version Concurrency Control 多版本并发控制，大多数的MySQL事务型存储引擎，如InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制。事实上，他们都和另外一种用来增加并发性的被称为“多版本并发控制（MVCC）”的机制来一起使用。MVCC不只使用在MySQL中，Oracle、PostgreSQL，以及其他一些数据库系统也同样使用它。 你可将MVCC看成行级别锁的一种妥协，它在许多情况下避免了使用锁，同时可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。 MVCC会保存某个时间点上的数据快照。这意味着事务可以看到一个一致的数据视图，不管他们需要跑多久。这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的。各个存储引擎对于MVCC的实现各不相同。这些不同中的一些包括乐观和悲观并发控制。
　　2、InnoDB引擎实现原理
　　InnoDB：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。
　　3、使用其的优势
　　使用MVCC多版本并发控制比锁定模型的主要优点是在MVCC里， 对检索（读）数据的锁要求与写数据的锁要求不冲突， 所以读不会阻塞写，而写也从不阻塞读。 在数据库里也有表和行级别的锁定机制， 用于给那些无法轻松接受 MVCC 行为的应用。 不过，恰当地使用 MVCC 总会提供比锁更好地性能。
　　二、MySQL存储引擎简介
　　存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。 因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。 在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
　　1、查看系统引擎
　　查看当前系统锁支持的引擎：
MariaDB [hellodb]> SHOW ENGINES\G;　　查看当前所在库的所有表的信息，包括存储引擎：
MariaDB [hellodb]> SHOW TABLE status\G;　　也可以查看指定表的所使用的引擎：
　　语法：SHOW TABLE STATUS [{FROM | IN} db_name]  [LIKE 'pattern' | WHERE expr]
　　说明：虽然我们既可以使用LIKE或者WHERE来选择表，但是还是建议使用WHERE来选择，毕竟是精确匹配。
　　2、如何修改默认存储引擎
　　查看当前系统默认的存储引擎：
MariaDB [hellodb]> SHOW VARIABLES LIKE 'default_storage_engine';　　
+------------------------+--------+
　　
| Variable_name          | Value  |
　　
+------------------------+--------+
　　
| default_storage_engine | InnoDB |
　　
+------------------------+--------+
　　
1 row in set (0.00 sec)
　　若想修改默认存储引擎，然后查出当前系统所能支持的引擎，然后根据实际需要修改。
　　3、关于表的属性信息说明
　　使用举例：
MariaDB [hellodb]> SHOW TABLE STATUS FROM hellodb WHERE Name='Students'\G;　　
*************************** 1. row ***************************
　　
           Name: students
　　
         Engine: MyISAM
　　
        Version: 10
　　
     Row_format: Dynamic
　　
           Rows: 27
　　
Avg_row_length: 25
　　
    Data_length: 680
　　
Max_data_length: 281474976710655
　　
   Index_length: 2048
　　
      Data_free: 0
　　
Auto_increment: 28
　　
    Create_time: 2015-08-29 21:54:14
　　
    Update_time: 2015-08-29 23:05:55
　　
     Check_time: NULL
　　
      Collation: utf8_general_ci
　　
       Checksum: NULL
　　
Create_options:
　　
        Comment:
　　
1 row in set (0.00 sec)
　　下面关于各行的信息说明：

• 　　Name： 表名；
  
• 　　Engine ：存储引擎；
  
• 　　Version：版本；
  
• 　　Row_format：行格式。
  

　　{DEFAULT|DYNAMIC|FIXED|COMMPRESSED|REDUNDANT|COMPACT}

• 　　Rows：表中的行数，在MyISAM存储引擎中是准确值，在InnoDB存储引擎中是估计值；
  
• 　　Avg_row_length：平均每行包含的字节数；
  
• 　　Data_length：表中数据总体大小，单位为字节；
  
• 　　Max_data_length：表能够占用的最大空间，单位为字节，0表示么有上限；
  
• 　　Index_length：索引的大小，单位为字节；
  
• 　　Data_free：对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间；
  
• 　　Auto_increment：下一个AUTO_INCREMENT的值；
  
• 　　Create_time：表的创建时间；
  
• 　　Update_time： 表数据的最后一次修改时间；
  
• 　　Check_time： 使用CHECK TABLE或myisamchk最近一次检查表的时间；
  
• 　　Collation：排序规则；
  
• 　　Checksum：如果启动，则为表的checksum；
  
• 　　Create_options：创建表时指定使用的其他选项；
  
• 　　Comment：表的注释信息；
  

　　三、MyISAM和InnoDB表的存储结构
　　1、MyISAM表，每表有三个文件，都位于数据库目录中
　　tb_name.frm： 表结构定义
　　tb_name.MYD： 数据文件
　　tb_name.MYI： 索引文件
　　2、InnoDB表，有两种存储方式

• 　　默认：每表有一个独立文件和一个多表共享的文件；
  

　　tb_name.frm： 表结构的定义，位于数据库目录中；
　　ibdata#： 共享的表空间文件，默认位于数据目录(datadir指向的目录)中。

• 　　独立的表空间：
  

　　tb_name.frm： 每表有一个表结构文件；
　　tb_name.ibd： 一个独有的表空间文件。
　　表空间：table space，由InnoDB管理的特有格式数据文件，内部可同时存储数据和索引 。
　　四、各存储引擎的特性
　　1、InnoDB
　　InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。下面列举几个其主要功能：

• 　　事务
  
• 　　外键
  
• 　　MVCC
  
• 　　聚簇索引
  
• 　　行级锁
  
• 　　支持辅助索引 ：聚簇索引之外的其它索引，通常称为辅助索引
  
• 　　支持自适应hash索引
  
• 　　支持热备份
  

　　2、MyISAM
　　MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。 每个MyISAM在磁盘上存储成三个文件。每一个文件的名字均以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为·MYD (MYData)。
　　下面列举几个其主要功能：

• 　　全文索引
  
• 　　压缩：用于实现数据仓库，能节约存储空间并提升性能
  
• 　　空间索引
  
• 　　表级锁
  
• 　　延迟更新索引
  

　　不支持功能：

• 　　不支持事务、外键和行级锁
  
• 　　崩溃后无法安全恢复数据
  

　　适用场景：
　　只读数据、较小的表、能够容忍崩溃后的修改操作和数据丢失
　　3、ARCHIVE
　　其主要功能：

• 　　仅支持INSERT和SELECT，支持很好压缩功能；
  
• 　　适用于存储日志信息，或其它按时间序列实现的数据采集类的应用；
  

　　不支持功能：

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

　　4、CSV
　　其主要功能：

• 　　将数据存储为CSV格式；
  
• 　　仅适用于数据交换场景；
  

　　不支持功能：

• 　　不支持索引；
  

　　5、BLACKHOLE
　　其主要功能：

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

　　6、MEMORY
　　其主要功能：

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

　　不支持功能：

• 　　不支持BLOB和TEXT数据类型；
  

　　7、MRG_MYISAM
　　其主要功能：

• 　　MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表；
  

　　8、NDB
　　其主要功能：

• 　　MySQL CLUSTER中专用的存储引擎
  

　　9、第三方的存储引擎
　　OLTP（在线事务出理）：

• 　　XtraDB：增强的InnoDB，由Percona提供；
  

• 　　PBXT： MariaDB自带此存储引擎
  
  
  
  • 　　支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持；
    
  • 　　支持事务、MVCC
    
  
  
• 　　TokuDB： 使用Fractal Trees索引，适用存储大数据，拥有很压缩比；已经被引入MariaDB；
  

　　列式存储引擎：

• 　　Infobright： 目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计；
  
• 　　InfiniDB
  
• 　　MonetDB
  
• 　　LucidDB
  

　　10、开源社区存储引擎

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

　　11、如何选择存储引擎

• 　　是否需要事务
  
• 　　备份的类型的支持
  
• 　　崩溃后的恢复
  
• 　　特有的特性
  

　　几个常用存储引擎的特点