Mysql几种引擎

        MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。如果技术高超，还可以使用MySQL++ API自己做一个引擎。

 

        一、存储引擎种类

        1.ISAM

        ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不 支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把 ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

        2.MyISAM

        MyISAM是 MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供 ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的 MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么 MySQL受到了WEB开发如此青睐的主要原因：在 WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和 INTERNET平台提供商只允许使用 MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

        3.MEMORY

        MEMORY允许只驻留在内存里的临时表格。驻留在内存里让 MEMORY要比 ISAM和 MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，MEMORY也不会浪费大量的空间。MEMORY表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

        4.InnoDB

        InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者 或者两者，那你就要被迫使用后两个引擎中的一个了。

        MySQL官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

        InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

        InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

        InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。 Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的。

        5.MERGE

        MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。

        在水平分表的情况下，merge引擎特别适用，它可以将数据结构相同的多张数据表当做一张表来处理，这种方式类似与分区。

 

        二、使用存储引擎

         1.查看所支持引擎

show engines;

        该命令可以查看数据库支持的存储引擎。以下是新安装 MySQL所支持的存储引擎列表：

        从结果就可以看出InnoDB是本数据库的默认存储引擎。

 

        2.默认存储引擎

        首先我们创建一个学生表（STUDENT），sql如下：

drop table if exists STUDENT;

create table STUDENT
(
   STUDENT_ID           int not null auto_increment comment '学生ID，主键，自增',
   NAME                 varchar(16) not null,
   AGE                  int,
   CLASS                varchar(16) not null,
   primary key (STUDENT_ID)
);

alter table STUDENT comment '学生表';

        在默认情况下我们并没有指定存储引擎，所以创建表后采用数据库默认的存储引擎。所以STUDENT表的默认存储引擎就应该为InnoDB。使用show table status命令可查看表的相关信息。

mysql> show table status \G
*************************** 1. row ***************************
           Name: student      *名称
         Engine: InnoDB       *引擎
        Version: 10           *版本
     Row_format: Dynamic      *行格式
           Rows: 0            *表中行数
 Avg_row_length: 0            *平均每行包括的字节数
    Data_length: 16384        *整个表的数据量(单位：字节)
Max_data_length: 0            *表可以容纳的最大数据量
   Index_length: 0            *索引占用磁盘的空间大小
      Data_free: 0            *对于MyISAM引擎，标识已分配，但现在未使用的空间
 Auto_increment: 1            *下一个 Auto_increment的值
    Create_time: 2016-05-13 15:45:26    *表的创建时间
    Update_time: NULL         *表的最近更新时间
     Check_time: NULL         *使用 check table 或myisamchk工具检查表的最近时间
      Collation: utf8_general_ci        *表的默认字符集和字符排序规则
       Checksum: NULL         *如果启用，则对整个表的内容计算时的校验和
 Create_options:              *指表创建时的其他所有选项
        Comment: 学生表       *注释
1 row in set (0.00 sec)

 

        3.设置引擎

        1）创建表时指定

CREATE TABLE `student` (
  `STUDENT_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID，主键，自增',
  `NAME` varchar(16) NOT NULL,
  `AGE` int(11) DEFAULT NULL,
  `CLASS` varchar(16) NOT NULL,
  PRIMARY KEY (`STUDENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

        2）使用alter命令

alter table `student` engine=MyISAM;

 

 

        三、引擎特点

        1.MyISAM

        MyISAM引擎它不支持事务，也不支持外键，但是是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

        1）文件结构

        每个 MyISAM表在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

        其中：

.frm（存储表定义）
.MYD（MYData，存储数据）
.MYI（MYIndex，存储索引）

        数据文件和索引文件可以放置在不同的目录，平均分配IO，获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件路径需要使用绝对路径。

        2）事务支持

        MyISAM引擎它不支持事务，也不支持外键。

        3）锁

        MyISAM只支持表锁，即插入数据时锁定整张表。

        4）存储格式

        MyISAM的表支持3种不同的存储格式：

        • 静态(固定长度)表

        • 动态表

        • 压缩表

        其中静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。

        动态表包含变长字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁到更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

        压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。 

 

        2.InnoDB

        事务型数据库的首选引擎，支持ACID事务，支持行级锁定。InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与 MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM表不同，比如在 MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。InnoDB默认地被包含在MySQL二进制分发中。

        InnoDB 给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行级锁(locking on row level)，提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的行级锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

        在技术上，InnoDB 是一套放在 MySQL后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

        InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，可也可以每个表使用各自独立的表空间，只需要启用选项 innodb_file_per_table。

        1）文件结构

        每个InnoDB表在磁盘上存储成2个文件，其中文件名和表名都相同，但是扩展名分别为：

        其中：

.frm（存储表定义）
.ibd（独享存储数据）
或
.ibdata（共享存储数据）

        .ibd与 .ibdata文件都是存放 InnoDB数据的文件，之所以用两种文件来存放 InnoDB的数据，是因为 InnoDB的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

        独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件；共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件。

        2）事务支持

        InnoDB引擎它支持事务。

        3）锁

        InnoDB支持行锁，也支持表锁。

        4）存储格式

 

 

3.MERGE

 

4.MEMORY

 

 

        MyISAM适用场景：
                (1)做很多count 的计算；
                (2)插入不频繁，查询非常频繁；
                (3)没有事务。
        InnoDB适用场景：
                (1)可靠性要求比较高，或者要求事务；
                (2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

 

        一般情况下，MySQL会默认提供多种存储引擎，可以通过下面的查看:

                (1)看你的MySQL现在已提供什么存储引擎: mysql> show engines;

                (2)看你的MySQL当前默认的存储引擎: mysql> show variables like '%storage_engine%';

                (3)你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql> show create table 表名;

        所有的性能测试在：Micrisoft window xp sp2 ， Intel(R) Pentinum(R) M processor 1.6oGHz 1G 内存的电脑上测试。

        测试方法：连续提交10个query， 表记录总数：38万 ， 时间单位 (s)

引擎类型	MyISAM	InnoDB	性能相差
count	0.0008357	3.0163	3609
查询主键	0.005708	0.1574	27.57
查询非主键	24.01	80.37	3.348
更新主键	0.008124	0.8183	100.7
更新非主键	0.004141	0.02625	6.338
插入	0.004188	0.3694	88.21

 

 

 

        (1)加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍，同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更新速度减慢为原来的1/30。要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。

        (2)如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。

        (3)InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。

        在我们测试的这个38w的表中，表占用空间的情况如下：

引擎类型	MyISAM	InnoDB
数据	53,924 KB	58,976 KB
索引	13,640 KB	21,072 KB
占用总空间	67,564 KB	80,048 KB

        另外一个176W万记录的表， 表占用空间的情况如下：

引擎类型	MyIsam	InnorDB
数据	56,166 KB	90,736 KB
索引	67,103 KB	88,848 KB
占用总空间	123,269 KB	179,584 KB