MySql的主要存储引擎
- MyISAM： 拥有较高的插入，查询速度，但不支持事务
- InnoDB ：5.5.8版本后Mysql的默认数据库引擎，支持ACID事务，支持行级锁定
- BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
- Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
- Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
- Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
- Federated： 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
- Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用
- CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
- BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继
主要说下MyISAM和InnoDB这两个存储引擎，两个存储引擎都支持B+树结构
MySIAM和InnoDB的优缺点
两个不同存储引擎的文件
InnoDB
InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定保证数据一致性提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。默认使用B+TREE数据结构存储索引。
- *.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
- *.ibd：InnoDB DATA，表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据
特点
- 支持事务，支持4个事务隔离（ACID）级别
- 行级锁定（更新时锁定当前行）
- 读写阻塞与事务隔离级别相关
- 既能缓存索引又能缓存数据
- 支持外键
- InnoDB更消耗资源，读取速度没有MyISAM快
- 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
- 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
适合业务
- 需要支持事务的场景（银行转账之类）
- 适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的
- 数据修改较频繁的业务
引擎调优
- 主键尽可能小，否则会给Secondary index带来负担
- 避免全表扫描，这会造成锁表
- 尽可能缓存所有的索引和数据，减少IO操作
- 避免主键更新，这会造成大量的数据移动
ACID
- A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.
- C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
- I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.
- D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.
InnoDB一定要设置一个自增的主键id， 如果没有一个主键id它内部也是会使用一个内部的id来标明每一行记录的，而且InnoDB使用的是B+树，使用自增连续的id作为主键有利于B+树数据的排放和查询，根据id查数据的时候不需要回表查了
MyISAM
MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。默认使用B+TREE数据结构存储索引。
- *.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
- *.MYD：MyISAM DATA，用于存储MyISAM表的数据
- *.MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息
特点
- 不支持事务
- 表级锁定（更新时锁定整个表）
- 读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
- 只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
- 不支持外键
- 读取速度快
- 业务场景
适合业务
- 不需要支持事务的场景（像银行转账之类的不可行）
- 一般读数据的较多的业务
- 数据修改相对较少的业务
- 数据一致性要求不是很高的业务
MyISAM引擎调优
- 设置合适索引
- 启用延迟写入，尽量一次大批量写入，而非频繁写入
- 尽量顺序insert数据，让数据写入到尾部，减少阻塞
- 降低并发数，高并发使用排队机制
- MyISAM的count只有全表扫描比较高效，带有其它条件都需要进行实际数据访问
磁盘的基本知识
数据库的数据存储在文件系统中。文件系统是操作系统用来 明确 存储设备（常见的是磁盘，也有基于NAND Flash的固态硬盘）或分区上的文件 的方法和数据结构。磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、块号(磁道上的盘块)。
硬盘只是磁盘的一种，或说是经典代表，以下通过硬盘模型图讲解磁盘中的各个概念。
硬盘整体模型图
硬盘模型图
磁盘重点概念
- 盘片（platter）：硬盘中承载数据存储的介质
- 硬盘一般由多个盘片组成，每个盘片包含两个面，每个盘面都对应地有一个读/写磁头。受到硬盘整体体积和生产成本的限制，盘片数量都受到限制，一般都在5片以内。盘片的编号自下向上从0开始，如最下边的盘片有0面和1面，再上一个盘片就编号为2面和3面。
- 磁头（head）：通过磁性原理读取磁性介质上数据的部件，可以上下读取盘片的数据
- 磁道（track）：当磁盘旋转时，磁头若保持在一个位置上，则每个磁头都会在磁盘表面划出一个圆形轨迹，这些圆形轨迹就叫做磁道
- 扇区（sector）：磁盘上的每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区，同一块硬盘上的扇区大小是一致的
“每个磁道的扇区数一样的”说的是老的硬盘，外圈的密度小，内圈的密度大（简单理解就是，磁盘存储媒介为
磁性记忆材料，在内圈涂的密度高），故每圈可存储的数据量是一样的。新的硬盘数据的密度都一致，这样磁道的周长越长，扇区就越多，存储的数据量就越大。 - 柱面（cylinder）：在有多个盘片构成的盘组中，由不同盘片的面，但处于同一半径圆的多个磁道组成的一个圆柱面
- 物理扇区（physical sector）与逻辑扇区（logical sector）
近年来，为了最求更高的硬盘容量，便出现了扇区存储容量为2048、4096等字节的硬盘，我们称这样的扇区为”物理扇区”。这样的大扇区会导致许多兼容性问题，有的系统或软件无法适应。为了解决这个问题，硬盘内部将物理扇区在逻辑上划分为多个扇区片段并将其作为普通的扇区（一般为512字节大小）报告给操作系统及应用软件。这样的扇区片段我们称之为“逻辑扇区”。实际读写时由硬盘内的程序（固件）负责在逻辑扇区与物理扇区之间进行转换，上层程序“感觉”不到物理扇区的存在。
逻辑扇区是硬盘可以接受读写指令的最小操作单元，是操作系统及应用程序可以访问的扇区，多数情况下其大小为512字节。我们通常所说的扇区一般就是指的逻辑扇区。物理扇区是硬盘底层硬件意义上的扇区，是实际执行读写操作的最小单元。是只能由硬盘直接访问的扇区，操作系统及应用程序一般无法直接访问物理扇区。当要读写某个逻辑扇区时，硬盘底层在实际操作时都会读写逻辑扇区所在的整个物理扇区。
磁盘容量计算
旧式——非ZBR区位记录（不同磁道扇区数相同）
存储容量 ＝ 磁头数 × 磁道(柱面)数 × 每道扇区数 × 每扇区字节数
比如上图最右边硬盘容量：6 * 7 * 12 * 512 = 258048 byte
新式——ZBR区位记录（不同磁道扇区数不同）
块（Block）/簇（Cluster）
块/簇两者指的是同一个逻辑上的概念，只是在Linux与Windows中的称呼不同。
块/簇 是操作系统中最小的逻辑存储单位。操作系统与磁盘打交道的最小单位是块/簇。
在Windows下如NTFS等文件系统中叫做簇；在Unix和Linux下如Ext4等文件系统中叫做块（block）。
每个簇或者块可以包括2、4、8、16、32、64…2的n次方个扇区。
块/簇 用来干什么的
磁盘的最小单位是扇区，操作系统使用的是 块/簇 作为IO的基本单位。
读取方便：扇区容量小，数据多会加大寻址难度。操作系统将相邻的扇区组合一起形成块，再对块整体操作
分离对底层的依赖：操作系统忽略对底层物理存储结构的设计。通过虚拟出来磁盘块的概念，在系统中认为块是最小的单位
扇区是对硬盘而言，块是对文件系统而言，出于不同的需要。
查看块/簇的大小
不同文件系统中block的大小不一样。
.
Windows:（使用管理员命令提示行）
fsutil fsinfo ntfsinfo E:Linux：
stat /home | grep “IO Block”
如下所示，Windows下E盘的Cluster的大小为4Kb大小，如下所示：
页（Page）
操作系统经常与内存和硬盘这两种存储设备进行通信，类似于“块”的概念，都需要一种虚拟的基本单位。与内存操作，是虚拟一个页的概念来作为最小单位。与硬盘打交道，就是以块为最小单位。
扇区、块/簇、页的关系
扇区： 硬盘的最小读写单元
块/簇： 是操作系统针对硬盘读写的最小单元
页： 是内存与操作系统之间操作的最小单元。
扇区 <= 块/簇 <= 页
MySQL的InnoDB数据存储结构
create table innodb_table(id int primary key)engine=innodb default charset=utf8;
拓展：定位一条表记录的过程
select * from user where id = 29
这里id是主键,我们通过这棵B+树来查找，首先找到根页，你怎么知道user表的根页在哪呢？
其实每张表的根页位置在表空间文件中是固定的。系统经过解析sql语句，首先从找到user表的跟页面（一个表通常需要多个页面组成，跟页面就是起始页），层级遍历非叶子节点页（索引）读取到key值为29的指针（遍历非叶子节点的过程随着节点的遍历会将一个或多个页加载到内存），最后到指针指向的叶子节点所在的页中，然后遍历找出该条记录。
如果使用了二级索引则先读取二级索引page遍历这个二级索引，找到装有主键信息叶子节点page页，遍历找到该主键。然后再根据主键索引寻找到该条记录
前面说到B+树来查找，我们简单的了解一下B+树
要了解B+树先来了解一下二叉搜索树，B+树也是二叉搜索树的变种
二叉搜索树
了解下二叉搜索树有助于我们理解B-树、B+树，二叉搜索树的特点是：
所有非叶子结点至多拥有两个儿子（Left和Right）；
.所有结点存储一个关键字；
非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；
以下都是二叉搜索树：
如果要找到65，左边的二叉树需要扫描3层（3次IO），而右边的却需要6层。
B-Tree（B树）
B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。事实上，B-tree就是指的B树。
B树是一种多路搜索树，一棵m阶的B树满足下列条件：
树中每个结点至多有m个孩子
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字个数 = 指向子节点的指针个数-1；
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
所有叶子结点位于同一层；
以下是3阶B树
磁盘读取数据是以盘块(block)为基本单位的。
以下结合磁盘块作图
B树的特征：
关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；
B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；
B+ Tree
B+树是B-树的变体，也是一种多路搜索树：（❀ 表示两者间的不同点）
树中每个结点至多有m个孩子
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
❀ 非叶子结点的子树指针与关键字个数相同；
❀ 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区间）；
❀ 为所有叶子结点增加一个链指针；
❀ 所有关键字都在叶子结点出现；
B+树的特征：
所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
更适合文件索引系统；
B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
为什么B+ 树比B 树更适合作为索引？
B+ 树的磁盘读写代价更低
B+ 树的数据都集中在叶子节点，分支节点 只负责指针（索引）；B 树的分支节点既有指针也有数据 。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。
B+ 树的查询效率更加稳定
B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。
B+树更便于遍历
由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。
B+树更擅长范围查询
B+树叶子节点存放数据，数据是按顺序放置的双向链表。B树范围查询只能中序遍历。
B+ 树占用内存空间小
B+ 树索引节点没有数据，比较小。在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引。
Hash
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。Memory存储引擎使用Hash。
Hash索引仅仅能满足”=”,“IN”和”<=>”查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
从上面的图来看，B+树索引和哈希索引的明显区别是：
如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；这有个前提，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
InnoDB的数据存储
因为InnoDB存储引擎的索引和数据都是存放在同个地方的，所以使用它的主键是聚族索引
MyISAM也使用B+Tree数据结构存储索引，但都是非聚簇索引，因为索引和数据不是在同个地方
以下是MyISAM主键索引存储图
可见，索引和数据是分开的 索引的data部分只是索引的地址值。其实上文也提到过，.MYI就是MyISAM表的索引文件，MYD是MyISAM表的数据文件。
