初入了解MySQL储存引擎和B+Tree索引

InnoDB存储引擎

1. 从mysql5.5.8开始，InnoDB是默认的存储引擎。在InnoDB中存在着缓冲管理，通过缓冲池，将索引和表数据全部缓存起来，加快查询的速度。
2. 由 .frm文件、表空间（分为独立表空间或者共享表空间）和日志文件（redo log）组成。
3. 最大特色，支持事务和行级锁，具有ACID的特征，行级锁大幅提高了许多用户并发操作的性能。但是DB的行锁只有在Where条件命中索引时才有效的。
4. DB的主键是B+Tree结构的聚簇索引，将主键组织到一颗B+Tree中，而行数据就存储在叶子节点上，聚簇索引是按大小排列的，因此对于范围查询的效率很高。DB中除了主键索引，其他辅助索引都是非聚簇索引，叶子节点则储存的是主键的值，所以通过辅助索引查询其实需要两个过程，先确定数据的主键，再通过主键进行查询。

InnoDB存储引擎中有页（Page）的概念
适用场景：需要事务支持的业务，行级锁对高并发有很好的适应能力，但需确保查询通过索引完成，数据一致性要求较高的业务，硬件设备内存较大，可用InnoDB较好的缓存能力来提高内存利用率，尽可能减少IO。

MyISAM存储引擎

1. MylSAM在键盘上储存三个文件，其中 .frm 文件储存表定义， .MYD 为数据文件， .MYI为索引文件。可被压缩，储存空间较小。
2. 强调的是性能，执行速度比DB块，但不支持事务。
3. 不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用。
4. 只支持表级锁，所以并发性差。如果执行大量的select，MylSAM比DB好，但是在增删改的时候需要锁定整个表，效率会低一些。
5. MylSAM的索引都属于B+Tree结构的非聚簇索引，索引与数据是分开的，B+Tree的叶子节点上储存的是数据的地址。主键索引和辅助索引没有区别，只是主键索引必须是唯一的。
6. 它的缓冲池只缓冲索引文件，而不缓冲数据文件。

InnoDB和MyISAM的对比

1. 由于锁粒度的不同，InnoDB比MyISAM支持更高的并发。
2. InnoDB为行级锁，MyISAM为表级锁，所以InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁。
3. 在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案。
4. 查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据。
5. SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要逐行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存。
6. MyISAM的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而InnoDB的表数据文件为:.ibd和.frm(表结构定义)。
   

ARCHIVE存储引擎

• ARCHIVE提供了压缩功能，拥有高效的插入速度，只允许自增ID列建立索引；，所以查询性能较差一些。 archive存储引擎支持insert、replace和select操作，但是不支持update和delete。

场景1：存储引擎基本上用于数据归档；它的压缩比非常的高, 存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合，由于它不支持索引同时也不能缓存索引和数据，所以它不适合作为并发访问表的存储引擎。
场景2：由于高压缩和快速插入的特点Archive非常适合日志系统的存储引擎，但是前提是不经常进行查询操作。

Memory存储引擎

1. 数据都是存储在内存中，IO效率要比其他引擎高很多，但是如果数据库重启或发生奔溃，表中数据都将消失，支持hash索引，B tree索引，默认hash（查找复杂度0(1)）。
2. Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。

这里先简单介绍4个还有很多的储存引擎，大家可以去了解。

接下来：B-Tree和B+Tree的区别

B+Tree是由二叉查找树——平衡二叉树——B Tree——B+Tree演化而来。
二叉查找树：
二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

因为二叉树查找树可以任意地构造，所以导致查询效率低。

平衡二叉树：
若想提高二叉树的查询效率所以需要平衡。平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。
左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1。

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：LL（左左）、RR（右右）、LR（左右）、RL（右左）。

AVL树失去平衡之后，可以通过旋转使其恢复平衡。旋转也是4种方式，这里暂时跳过。

平衡多路查找树（B-Tree）：

1、每个节点中不仅包含数据的key值，还有data值。
key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

一般查询需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B+Tree

1. 从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
2. 将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息。
   
3. 通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
4. B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。

B-Tree和B+Tree的区别

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

转载自：https://blog.csdn.net/a764340703/article/details/82621781

MyISAM和DB实现B+Tree区别

MyISAM实现：

可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引实现

1. 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
   
2. 因为DB是聚簇索引，数据文件本身要按照主键聚簇，所有必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
3. MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

例如，下图为定义在Col3上的一个辅助索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

聚集索引和非聚集索引（辅助索引）的区别

聚集索引：

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。一个表只能有一个聚集索引。

好处：

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。
2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置会随着数据库里数据的修改而发生变化，使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

缺点：

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。
2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，建议使用int主键自增.
3. 如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。
   非聚簇索引：将数据与索引分开存储，叶结点包含索引字段值及指向数据页数据行的逻辑指针，叶节点包含索引字段值及数据对应的位置。

为什么主键通常建议使用自增id

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

转载自：https://www.jianshu.com/p/fa8192853184