表类型，存储引擎的选择

一、MySql存储引擎概述

1、InnoDB和BDB提供事务安全表

2、MySql5.5以前的版本默认MyISAM，5.5之后改为InnoDB

3、修改默认的存储引擎，可以在参数文件中设置default-table-type

4、查看当前的默认存储引擎：   

show  variables  like  'table_type'

5、查看当前数据库支持的存储引擎：

value为DISABLED的记录表示：支持该存储引擎，但是数据库启动的时候被禁用。

（1）方法一

SHOW  ENGINES  \G

（2）方法二

SHOW  VARIABLES  LIKE  'have%'

6、建表时指定：

CREATE  TABLE ai(

   i bigint(20) NOT NULL AUTO_INCREMENT,

   PRIMARY KEY(i)

)ENGINE=MyISAM DEFAULT CHARSET=gbk

ALTER TABLE ai engine = innodb

二、各种存储存储引擎的特性

1、MyISAM

（1）不支持：事务、外键

（2）优势：访问的速度快，对事务完整性没有要求。以select和insert为主的应用基本都可以使用这个引擎来创建表。

（3）在磁盘上存储成3个文件，文件名和表名相同。

.frm（存储表定义）

.MYD（MYData,存储数据）

.MYI（MYIndex，存储索引）

    数据文件和索引文件可以防止在不同的目录，平均分布IO，获得更快的速度。

    建表时通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件路径需要是绝对路径，并且具有访问权限。

（4）检查MyISAM表的健康：

CHECK TABLE 

修复损坏的MyISAM表：

REPAIR TABLE 

（5）支持3种不同的存储格式

       静态（固定长度）表：默认。字段都是非变长字段。优点：存储迅速，容易缓存，出现故障容易恢复；缺点：占用的空间通常比动态表多。存储时会按列宽带定义补足空格，但在应用访问时并不会得到这些空格，这些空格在返回给应用之前已经去掉。

        动态表：

        压缩表：由myisampack工具创建，每个记录是被单独压缩的，非常小的访问开支。

（6）频繁的更新和删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能。

2、InnoDB

     对比MyISAM的存储引擎，InnoDB写的处理效率差一些，占用更多的空间。

     具有提交、回滚和崩溃恢复能力的事务安全。

（1）自动增长列：插入0或空时，实际插入的是字段增长后的值。

设置自动增长列的初始值，默认是1。该值保存在内存中，如果该值在使用之前数据库重新启动，那么这个强制值就会丢失，就需要在数据库启动以后重新设置。

ALTER TABLE   ***   AUTO_INCREMENT = n 

     

       当前线程最后插入记录使用的值,如果一次插入多条记录，返回的是第一条记录使用的自动增长值。

LAST_INSERT_ID()

      

         InnoDB的自动增长列必须是索引（或组合索引的第一列）

         MyISAM表自动增长列可以是组合索引的其他列，插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

（2）外键约束

唯一支持外键的。

在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

（3）存储方式

     共享表空间存储：

a.表结构保存在.frm文件中，

b.数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

     多表空间存储：

a.表结构保存在.frm文件中，可以是多个文件。

b.数据和索引文件单独保存在.ibd中（分区表每个分区对应单独的.ibd文件，文件名：表名+分区名，可以在创建分区的时候指定每个分区的数据文件的位置，以此将表的IO均匀分布在多个磁盘上）。

c.设置参数innodb_file_per_table并在重新启动服务后才可以生效。

d.数据文件没有大小限制，不需要设置初始化大小，也不需要设置文件的最大限制、扩展大小等参数。

e.可以方便的进行   单表备份和恢复    操作。直接复制.ibd文件不行，因为没有共享表空间的数据字典信息。将备份恢复到数据库中（只能恢复到原来所在的数据库）：

ALTER TABLE tbl_name  DISCARD TABLESPACE;

ALTER TABLE tbl_name    IMPORT TABLESPACE;

表恢复到目标数据库，则需要通过mysqldump和mysqlimport来实现。

f.即使在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和在线重做日志放在这个文件中。

3、MEMORY

（1）用存在于内存中的内容来创建表。

（2）每个MEMORY表只实际对应一个磁盘文件，格式是.frm

（3）访问速度快，默认使用HASH索引，但一旦服务关闭，表中的数据就会丢失掉。

（4）在启动MySQL服务的时候使用--init-file选项，把insert into...select或load data infile这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。

（5）DELETE FROM或TRUNCATE TABLE，或者整个地删除表（DROP TABLE）

（6）每个MEMORY表中可以放置的数据量大小：max_heap_table_size系统变量初始值16MB。MAX_ROWS子句指定表的最大行数。

（7）适用于：内容变化不频繁的代码表、作为统计操作的中间结果表、便于高效的对中间结果进行分析并得到最终的统计结果。更新数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

4、MERGE

（1）是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身并没有数据，可以查询、更新、删除，这些操作实际是对内部的MyISAM表进行的。

       对于MERGE类型表的插入操作，通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使得插入操作被相应的作用在第一或最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

      对MERGE表再磁盘上保留2个文件，文件名以表的名字开始，一个.frm文件存储表定义，另一个.MRG文件包含组合表的信息，包括MERGE表由哪些表组成、插入新的数据时的依据。

      可以通过修改.MRG文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。

（2）MERGE表不能智能的将记录写到对应的表中，而分区表是可以的。通常用MERGE表来透明地对多个表进行查询和更新操作，而对这种按照时间记录的操作日志表则可以透明地进行插入操作。

5、TokuDB

（1）高写性能高压缩

（2）高性能、支持事务处理的MySQL和MariaDB的存储引擎，有高扩展性、高压缩率、高效的写性能，支持大多数在线DDL操作。

（3）特性

（4）场景

三、各存储引擎的选择