mysql存储引擎

一、【mysql存储引擎概述】
插件式存储引擎是Mysql数据库重要特征之一
5.5之前的默认存储引擎为MyISAM
5.5之后的默认存储引擎为InnoDB
如果要修改默认的存储引擎，可以在参数文件中设置default_table_type;
查询当前数据库支持的存储引擎：SHOW ENGINES \G
可以通过创建表语句设置指定表的存储引擎：
①
create table ai
(id bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

②alter table ai engine = MyISAM;

二、【各种存储引擎的特性】
（1）、MyISAM
①索引：BTREE索引、全文索引
②表锁
③不支持事务，不支持外键，批量插入效率高(高于InNoDB)，访问速度快，适合SELECT，INSERT为主的业务场景
④每个MyISAM表在磁盘上存储3个文件，文件名与表名相同，扩展名
.frm：存储表定义（表结构）
.MYD：MYData，存储数据
.MYI：MYIndex，存储索引
数据文件和索引文件可以放在不同的目录，平均分布IO，获得更快的速度，且不同的MyISAM表的数据文件和索引文件可以放置在不同的目录，
在创建MyISAM表时，通过DATA DIRECTORY 和INDEX DIRECTORY语句指定，文件路径需要绝对路径，且具有访问权限
⑤MyISAM表可能被损坏，通过CHECK TABLE 语句检查表健康，通过REPAIR TABLE语句修复一个损坏的MyISAM表，MyISAM的损坏可能会导致数据库重启
⑥支持三种不同的存储格式：静态表（固定长度）、动态表、压缩表
静态表（FIXED）：默认的存储格式，字段为非变长字段，每个记录都是固定长度，优点存储迅速，容易缓存，出现故障易恢复，缺点为占用的空间比动态表多。存储时，会按照字段定义的长度补足空格，
但是在访问数据时，会自动去除尾部的空格(易误删空格，导致数据不准确)。
动态表（DYNAMIC）：包含变长字段，记录不是固定长度的，占用空间较少，但是频繁的更新与删除会产生碎片，需要定期的执行OPTIMIZE TABLE语句优化，故障恢复相对困难
压缩表（COMPRESSED）：由myisampack工具创建，占据非常小的磁盘空间。每个记录被单独压缩，访问开支小
当你对一个没有BLOB或TEXT类型的表操作时，可以在CREATE TABLE时，通过ROW_FORMAT表选项，强制表的存储格式为FIXED或DYNAMIC，这会导致CHAR和VARCHAR类型字段因FIXED存储格式变为CHAR（因为CHAR类型字段
会自动补全空格），因DYNAMIC存储格式改为VARCHAR。
⑦查询时，按照组合索引前几列排序递增

（2）、InNoDB
①索引：BTREE索引
②行锁
③支持事务（提供了具有提交、回滚、崩溃恢复能力的事务安全）、支持外键、相对于MyISAM存储引擎，InnoDB写的效率差一些，并占用更多的磁盘空间来存储数据和索引
④自动增长列：InnoDB自动增长列可以手工插入，当插入的值为null或0时，实际插入的值为自动增长后的值，可以通过ALTER TABLE 名字 AUTO_INCREMENT = n;来设置自动增长列的初始值，但是初始值存储在
内存中，重启数据库后，需要重新设置；
⑤对于InNoDB而言，自动增长列必需是索引。如果是组合索引，也必须是索引的第一列。
⑥支持外键（Mysql唯一一个支持外键的存储引擎）
⑦InnoDB存储表和索引有2中存储方式：
共享表空间存储：这种方式创建的表的表结构存储在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件
多表空间存储：表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中，如果是个分区表，每个分区对应单独的.ibd文件，文件名是表名+分区名，可以在创建分区的时候，
指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。
⑧要使用多表空间的存储方式，需要设置参数innodb_file_per_table，并重新启动服务才可生效。对于新建的表，按照多表空间的存储方式，已有的表仍然使用共享表空间，如果将多表空间存储方式修改回
共享空间存储格式，则新建的表会在共享空间中创建，但已有的多表空间的表仍然保存原来的访问方式，所以，多表空间参数生效后，只对新建的表生效。
⑨即使在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部的数据词典和在线重做日志放在这个文件中。

（3）、MEMORY
①索引：BTREE索引、HASH索引（默认使用）
②表锁
③MEMORY存储引擎使用存在于内存中的内容来创建表。每个MEMORY表实际只对应一个磁盘文件，格式为.frm。
④可以指定索引类型： create index mem_hash_idx USING HASH on 表名(字段名);
drop index mem_hash_idx on 表名;
⑤在启动MySQL服务时，使用init-file选项，把INSERT INTO ... SELECT 或 LOAD DATA INFILE这样的语句放入这个文件中，就可以在服务启动时，从持久稳固的数据源装载表。
⑥max_heap_table_size系统变量约束每个MEMORY表中可放置的数据量大小。
（4）、MERGE
①索引：BTREE索引
②表锁
③MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，MERGE表本身没有数据，可进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。
④对MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，FIRST或LAST值，使得插入操作被相应的作用在第一个或最后一个表上，不定义这个子句或定义NO，表示
不能对这个MERGE表执行插入操作。
⑤MERGE表在磁盘上保存2个文件，.frm文件存储表定义，另一个.MRG文件包含组合表信息，可以通过.MGE文件修改MERGE表，但是修改后要通过FLUSH TABLES刷新。
⑥create table merge_table (
id bigint(20)
) engine=merge union=(表1,表2) INSERT_METHOD=LAST;

⑦MERGE表并不能智能的将记录写到对应的表中，而分区表是可以的。