mysql两种常用引擎的区别和索引的底层原理

一、MySQL 两个常用引擎对比

二、MySQL的索引

1、 啥是索引

索引是一种用来实现 MySQL 高效获取数据的数据结构（索引是数据结构）。
建立索引就是 MySQL 对相关字段以索引这种数据结构来存储，然后查找的时候就有对应的查找算法。索引采用一些查找算法优化查找，比如二分查找、快速查找、顺序查找等，显然不同的算法所要求的数据结构也不同，顺序查找和二分查找要求数据劲量有序、折半查找则用于二叉树或者红黑树结果上。在这种情况下，mysql系统就维护了一套指向不同算法的数据结构，而这种数据结构就是索引。

2、 常用的索引结构： B-Tree

1）二叉树相关概念

结点是数据结构中的基础，是构成复杂数据结构的基本组成单位。
结点拥有的子树数目称为结点的度。

树中结点的最大层次数称为树的深度或高度

二叉树是每个结点最多有两个子树的树结构。通常子树被称作左子树和右子树。每个节点最多有两棵子树，即二叉树不存在度大于2的节点。二叉树的子树有左右之分，其子树的次序不能颠倒。

2）B-Tree（B树）

（1） B树相关概念

B树事实上是一种自平衡（所有的叶子节点拥有相同的高度）的多叉查找树，其每一个结点的孩子数可以多于两个，且每个结点出可以存储多个元素，每个元素之间都存在一定的顺序。

为什么数据库要用B树：
   a、 平衡二叉树的查找效率为O（log2N）与树的深度相关，通过降低树的深度，可 以提高查找效率，但是还有一个瓶颈就是，每次查找一次就只能得到一个节点元素，它    无法一次得到多个节点元素，无法在同样的高度查找更多的元素。
   b、 B树能容纳更多的数据，并且B树的高度是可以控制的，而二叉树是不可以的， B树能更加方便得让子节点存放在硬盘，所以B树能减少机械磁盘的磁头跳转的次数，B 树更加适合大量数据动态操作，因此很多数据库使用B+树来实现存储和检索。
B树的阶： B树中所有结点中孩子结点个数的最大值成为B树的阶，通常用m表示。
m阶B树： B树开m个子节点（m>=2），称之为m阶B树。
m阶B树有以下特性：
   a、 每个节点至多可以拥有m棵子树，每棵树以及每棵树中的关键字都是有序的。
   b、 根节点，只有至少有2个节点（或者就只有一个根节点）。
   c、 非根非叶的节点至少有的ceil(m/2)个子树(ceil表示向上取整)。
   d、 根结点中关键字的个数为1 至 m-1，比节点数目少一个；非根结点的关键字个数最多为 m-1；非根节点含有n个关键字就有n+1个子节点；非跟节点关键字取值范围ceil(m/2) -1<= n <=m-1。
   e、 从根到叶子的每一条路径都有相同的长度，也就是说，叶子节点在同一层。
   f、 关键字集合分布在整颗树中,任何一个关键字出现且只出现在一个节点中。

（2）B-Tree（B树）查找

B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针。
具体操作:
   a、 先让key与根结点中的关键字比较，如果key等于k[i]（k[]为结点内的关键字数组），则查找成功；
   b、 若key<k[1]，则到p[0]所指示的子树中进行继续查找（p[]为结点内的指针数组）；
   c、 若key>k[n]，则道p[n]所指示的子树中继续查找。
   d、 若k[i]<key<k[i+1]，则沿着指针p[I]所指示的子树继续查找。
   e、 如果最后遇到空指针，则证明查找不成功。

3）B+树

（1）B+树相关概念

B+树是B树的升级版
   a、 具有n个关键字的结点有n个分支(在B树种，含有n个关键字就有n+1个子节点)。
   b、 所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接。
   c、 非叶子节点可以看成索引部分，节点中仅含有其子树中的最大（或最小）关键字和指向子节点的指针。
   d、 每个结点（除根结点外）中的关键字个数n的取值为ceil(m/2) <= n <=m,根结点的取值范围为1<=n<=m。

（2）B+树查找

B+树的查找过程与B树类似但也有区别，区别在于如果在非叶子节点上的关键字等于key，并不直接返回，而是继续沿着指针直到叶子节点位置继续查找，每次查找都是走了一条从根到叶子节点的路径。

（3）使用B+树的原因（红黑树也带索引，为啥不用红黑树）

索引是存储在磁盘上的，索引查找过程中就要从磁盘获取数据（I/O操作），所以要想提高效率就要优化索引的结构组织，尽量减少查找过程中磁盘I/O的存取次数。在数据库设计中将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
在B+树中，d(节点的度)很大，h(树的深度)一般很小，每次检索最多访问h个节点，所以I/O次数就会很少，效率就高。
在红黑树中，d(节点的度)很小，h(树的深度)一般很大，每次检索最多访问h个节点，所以I/O次数就会很大，效率就小。

三、MySQL索引的实现

1、MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构。
在叶子节点中存储的是数据的地址。

2、InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，这棵树的叶子节点data域保存了完整的数据记录,其具体实现方式与MyISAM缺不相同。
InnoDB与myisam最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息)

3、总结：

myisam
主键索引/非主键索引:叶子节点上均带有行号，通过行号进行索引
myisam属于堆表，数据写入一直累积;此时写入性能比innodb好，但是无论是主键查询还是非主键查询，都不可避免的需要二次io。
innodb
主键索引(聚簇索引):叶子节点上带有数据
非主键索引(第二索引):叶子节点上带有主键id
innodb属于索引组织表，在主键索引情况下,主键查找时只需要一次io即可返回所有想要的数据，在非主键索引查询时如果在索引内不能完成查询记录返回则需要多次的io ；另外为了保证数据有序，innodb的插入是比较麻烦的。