《深入浅出MySql》学习笔记（一存储引擎）

路漫漫其修远兮，吾将上线而求索。

《深入浅出MySql》学习笔记

最近在看《深入浅出MySql》，打算系统学习一下MySql数据库，做些许记录，权做学习笔记。

一、存储引擎概述

登录数据库以后使用show engines命令可以查看当前安装数据库所支持的存储引擎，笔者安装的数据库版本为5.7.28，使用命令后查询出9种存储引擎，但是有一种并不支持。如下图：

以上存储引擎用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。各个存储引擎主要不同之处如下表所示：

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N\A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

二、MyISAM

MyISAM存储引擎并不支持事务与外键，但其优势为访问的速度快，对事务完整新没有要求或者以SELECT、INSERT为主的应用可以考虑使用MyISAM来创建数据库表。

(一)MyISAM数据存储

每个MyISAM表在磁盘上存储为三个文件，文件名与表明相同，分别为：
.frm(存储表定义)
.MYD(MYData,存储数据)
.MYI(MYIndex,存储索引)
另外为提升访问速度，可以在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定数据文件和索引文件放在不同的目录，以平均分布IO。

(二)数据存储格式

MyISAM有三种数据存储格式，分别为：
静态(固定长度)表
字段均为非变长字段，每个记录均为固定长度，存储迅速，容易缓存，出现故障容易恢复，但是占用空间比动态表多。**静态表的数据存储会按照列的宽度定义补足空格，然后在访问数据时会去掉空格，且原有数据右侧存在空格时也会去掉！
动态表
动态表包含变长字段，占用空间较少，但是频繁的更新和删除记录会产生碎片，需定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，且出现故障时相对比较困难
压缩表
压缩表只能由myisampack工具创建，占据空间小，访问开支小。

三、InnoDB

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

(一)自动增长列

使用较多，不在赘述。需要注意的是指定自动增长的初始值时，对应的初始值保存在内存中，该值使用之前重启数据库的话会丢失。

(二)外键约束

在创建索引时，可以指定在删除、更新父表时对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL、NO ACTION。RESTRICT和NO ACTION相同，指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则表示父表在更新或者删除时，子表对应字段被SET NULL。选择后两种方式时应谨慎，防止数据丢失。
SET FOREIGN_KEY_CHECKS = 0或者1可以关闭和打开外键约束。

(三)存储方式

InnoDB
·共享表空间存储
表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间种，可以是多个文件。
·多表空间存储
表结构存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中，如果是个分区表，则每个分区对应单独的.ibd文件，文件名为“表名+分区名”，可在创建分区时指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。
多表空间的数据文件没有大小限制，不需设置初始大小，也不需设置文件的最大限制，扩展大小等参数。

四、MEMORY

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

五、MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，其本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作，其实际上是对内部的MyISAM表进行的，对MERGE表进行的DROP操作也只是删除MERGE的定义，对其内部数据没有影响。
MERGE表在磁盘上保留两个文件，.frm存储表的定义，.MRG存储表文件包含组合表的信息，包括MERGE表由哪些表组成、插入新的数据时的依据等。
对MERGE表的插入操作，是通过INSERT_METHOD字句定义插入的表，有三个不同的值，可以使用FIRST、LAST使得被插入的值作用在第一或者最后一个表上，不定义或者定义为NO，则表示对MERGE表不能进行插入操作。
例如：

---

OVER

---

---