参考《深入浅出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 |
+------------+---------------------+-----------+