MySql数据库InnoDB与MyISAM索引结构的差别

一、索引
索引是存储引擎用于快速查找记录的一种数据结构，它可以是一列，也可以是多列，多列时字段的顺序也很关键。针对mysql而言，为何索引能提升查询效率？首先一般的顺序查找，复杂度为O(n)，而mysql的索引结构为B+tree，这种树的结构大大的降低了复杂度，还有就是索引能减少IO读取消耗（这和磁盘的预读以及索引的一个node节点可能含有多条信息有关）。

二、InnoDB与MyISAM引擎下的索引结构与区别
我们都知道这2种引擎的索引结构为B+Tree，所以我们首先要先了解B+Tree的结构。B+Tree是一种多路平衡查找树，它是在BTree上做了优化:

就索引的结构来看，2者的区别在于叶子节点存放的data值。在都是主索引的情况下，2者叶子节点都保存了完整的数据信息，不同之处在于innoDB存放就是数据信息，而MyISAM存放的是数据的物理地址；普通索引的话，innoDB的叶子节点data值会指向其主索引，而MyISAM与其主索引的时候没有太大区别。
通过上面的索引结构来看，我们在使用innoDB引擎时，就会引入回表的概念。意思就是我们在查询数据时由于需要查询的值不在所建的索引中，就需要通过主索引再次去查询一遍，这个过程就叫做回表。以下面的persion表为例：若我们通过age字段去查询name信息，此时在普通索引结构上没法直接获得name信息，那么就需要通过age去定位到id信息，再通过id信息获取name信息。

CREATE TABLE `persion` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id主键',
  `name` varchar(5) NOT NULL COMMENT '姓名',
  `age` tinyint(4) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

具体可以通过explain解析查看

修改索引

再次查询

比较extra的值，第二次为using index说明数据在一棵树上被查询到，explain具体用法可自行查找。
三、如何避免回表
综上来看二次查询对于使用者来说无感，但是肯定是要花费更多的时间的，因此我们想避免这种情况；在平常我们建一个innoDB的table时，我们就要考虑到哪些字段是要频繁被使用的，这样就可以把些字段组合建立联合索引。
这里附带说下联合索引的建立也是有规则，一般来说要把最频繁使用的字段放在最前，一个联合索引（假设是index(A,B,C)），如果我们只有B和C2个条件的话，我们去查询的时候是不走索引的，联合索引的生效规则和A\B\C的顺序是相关的，也就是先匹配A，再B，最后C，换言之，如果只有A条件，此时我们查询的时候索引是生效的。大家也可以通过explain去验证查看
下面在persion中增加一个adress字段（不然所有的字段都在索引上，方便演示），随意给上值

然后通过explain解析可以看出第一次通过name去匹配的没有使用索引，其余2此都有用到。

四、B+Tree结构特点

1. 树中每个非叶结点最多有 m 棵子树；
2. 根结点 (非叶结点) 至少有 2 棵子树。除根结点外, 其它的非叶结点至少有 m/2棵子树；有 n 棵子树的非叶结点有 n-1 个关键码。
3. 所有叶结点都处于同一层次上，包含了全部关键码及指向相应数据对象存放地址的指针，且叶结点本身按关键码从小到大顺序链接；
4. 每个叶结点中的子树棵数 n 可以多于 m，可以少于 m，视关键码字节数及对象地址指针字节数而定。
5. 若设结点可容纳最大关键码数为m1，则指向对象的地址指针也有 m1 个。
6. 结点中的子树棵数 n 应满足 n 属于[m1/2, m1]
7. 若根结点同时又是叶结点，则结点格式同叶结点。
8. 所有的非叶结点可以看成是索引部分，结点中关键码 Ki 与指向子树的指针 Pi构成对子树 (即下一层索引块) 的索引项 ( Ki, Pi )，Ki 是子树中最小的关键码。
9. 特别地，子树指针 P0所指子树上所有关键码均小于 K1。结点格式同B树。
10. 叶结点中存放的是对实际数据对象的索引。
11. 在B+树中有两个头指针：一个指向B+树的根结点，一个指向关键码最小的叶结点。