MYSQL——存储引擎

参考《深入浅出MySQL》

对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

存储引擎的建立基于表，而不是库。

一、MyISAM 

MyISAM 是mysql的默认存储引擎，MyISAM 不支持事务、也不支持外键，支持全文索引，其优势是访问的速度快，对事务完整性没有要求。

特点：IO效率低，匹配效率差。B+树作为索引结构，非聚集索引，支持表锁，数据和索引分开。

1、存储方式

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

.frm（存储表定义）； 

.MYD（MYData，存储数据）； 

.MYI （MYIndex，存储索引）。

2、存储格式

MyISAM 的表又支持 3 种不同的存储格式，分别是：

静态（固定长度）表：表中的字段都是非变长字段。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。

动态表：包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片

压缩表： myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

二、InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

特点：支持外键，支持事务，不支持全文索引，B+树作为存储结构，聚集索引，行锁。

1、 自动增长列

插入一组数据，i的值为0或者空的话，InnoDB会自动补全i的值，默认值从1开始。可以通过“ALTER TABLE ***AUTO_INCREMENT = n;”语句强制设置自动增长列的初识值，但是这个值在本次数据库关闭之后就会消失，需要重新设置。

对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于 MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

2、外键约束

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包 含RESTRICT、CASCADE、SET NULL 和 NO ACTION。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。

RESTRICT： 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新。

CASCADE ：表示父表在更新或者删除时，更新或者删除子表对应记录。

SET NULL ：表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。

3、存储方式

nnoDB 存储表和索引有两种：

a、共享表空间存储，表结构保存在.frm 文件中，数据保存在 innodb_data_home_dir ，索引保存innodb_data_file_path 定义的表空间中，可以是多个文件。
 b、多表空间存储，表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。

使用多表空间存储方式需要：设置参数 innodb_file_per_table；重新启动服务器。新建的表按照多表方式创建，但是就得表依然是原来的存储方式。

三、 MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

特点：数据保存在内存中，适应于临时表。varchar当成char来处理，数据都是固长。不支持大文件和图片字段TEXT BOLB，如果有数据类型为TEXT BOLB教给MYISAM存储，导致数据存放到硬盘上，此时效率不一定高。

给 MEMORY 表创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引。

四、MERGE

MERGE存储引擎是MyISAM表的组合，要求这些表结构完全相同，对MERGE进行增删查改就是对MyISAM表进行操作。可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响。

特点：适合存储日志型数据，数据量大，查询较少不支持修改操作，支持insert和select操作。表大小无限制

mysql> create table payment_2006(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> KEY idx_fk_country_id (country_id)
-> )engine=myisam;

mysql> create table payment_2007(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> KEY idx_fk_country_id (country_id)
-> )engine=myisam;

mysql> CREATE TABLE payment_all(
-> country_id smallint,
-> payment_date datetime,
-> amount DECIMAL(15,2),
-> INDEX(country_id)
-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

向 payment_2006 插入数据(1,'2006-05-01',100000),(2,'2006-08-15',150000)

向payment_2007 插入数据(1,'2007-02-20',35000),(2,'2007-07-15',220000);

然后查看payment_all的结果是两个表的数据之和

+------------+---------------------+-----------+
| country_id | payment_date | amount |
+------------+---------------------+-----------+
| 1 | 2006-05-01 00:00:00 | 100000.00 |
| 2 | 2006-08-15 00:00:00 | 150000.00 |
| 1 | 2007-02-20 00:00:00 | 35000.00   |
| 2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+