InnoDB存储结构的原理以及索引的原理

InnoDB存储原理

首先我们有一个表tI，tI中有a,b,c,d4个字段，其中a为主键。
a int
b int
c int
d varchar
此时我们需要向t1中插入4条数据

INSERT INTO t1 VALUES(4,5,2,'u');
INSERT INTO t1 VALUES(2,44,2,'u');
INSERT INTO t1 VALUES(1,2,3,'u');
INSERT INTO t1 VALUES(3,6,2,'u');

这是我们在查询表中的所有数据，我们会发现查出来的数据已经是根据a字段排序后的结果了，那么为什么会出现这个种情况呢？

原因是因为当我们在进行插入操作的时候，mysql的InnoDB引擎会根据主键索引进行比较，比如我们插入第一条数据a的值为4，接着插入第二条数据a的值为2，这时InnoDB就会进行一个比较将值小的数据放到前面。

为什么在存储的时候会进行排序呢？

比如我们这里有一个数列 5，7，1，4，8，2，3要求写一个算法找到数值2，那么一般我们的第一个想法就是先对这个数列进行排序。
是的，之所以InnoDB会在存储的时候进行排序的原因就是为了增加查询效率。在这里需要说到一个概念那就是在进行数据库查询的时候查询的数据是按照最小单位页来查询的，mysql中一页可以存储16kb的数据，意思就是说比如你要查询a=2的数据，此时从数据库里取出的数据可能不只有a=2的这一条数据，有可能是a=1到4的所有数据直到将一页16kb的空间占用完为止。（这里假设a=1到4的着4条数据刚好占16kb）此时我们查询a=2.5当数据库比较到a=3时，就不会继续在往后比较了，因为数据已经是排序好了，所以会直接判断数据库中没有这个值。

索引的原理

采用b+tree的数据结构，非叶子节点保存了叶子页中最小主键的值（因为叶子节点是用来存数据的，而mysql查询数据的最小单位是页，一页中可能存在多条数据，所以要取最小的主键，方便比较）以及页地址。形象点来说索引就相当于一本书的目录，可以帮助我们在查询的时候锁定一个范围，避免全表扫描。

索引为什么可以提高查询效率？

因为索引是模仿的B+树这种数据结构，它的每个节点都可以存在多个元素，且它的非叶子节点的每个元素分成了两块信息第一个就是保存了孩子页最小索引值以及孩子节点的地址，比如我们设A字段为主键索引现在有两个非叶子节点，他们的孩子页最小索引值分别是1和5，这时我们要查询A=4，那么他就会拿这个4去和非叶子节点的1和5进行比较，最终就可以确定4这个值要么就在1这个节点的分支下，要么不存在，不可能在5分支下因为非叶子节点保存的是孩子节点的最小值，所以5这个分支下不可能存在比5小的数据。索引就是基于这个原理提高的查询效率。

最左前缀原则：

比如我们创建了一个联合索引A,B,C三个字段那么基于b+树的数据结构，这些节点的值就是ABC这三个字段的值，在进行查询比较的时候首先会跟A字段的值比较，如果相等在和B字段的值比较以此类推。
基于这个原理我们得出，sql的查询条件必须要有A这个字段，否则是没办法比较的。
（比如现在有两个非叶子节点他们的值分别是234和546
这时我们的sql条件为Where A=3 and B=5 and C=6
执行过程就是首先拿A的值3和非叶子节点中的2和5进行比较就能得到356这个值肯定是在234这个分支下的。）
（在比如我们的sql条件为Where B=5 and C=6
执行过程就是首先拿A的值进行比较，但条件没有A值所以就没办法进行比较，设想一下35可以和234或546进行比较吗？肯定不可以！
但如果我们的查询条件是Where A=3 and C=6那么索引是可以起到一定作用的，因为可以确定36肯定是在234这个分支下或者不存在，绝对不可能在546这个分支下因为这个分支下的数据最小就是546）
如果存的数据类型是字符的话，那么会根据字符的ASCII编码来进行存储和排序。原理一样