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

InnoDB存储原理首先我们有一个表tItI中有a,b,c,d4个字段其中a为主键。a intb intc intd varchar此时我们需要向t1中插入4条数据INSERTINTOt1VALUES(4,5,2,u);INSERTINTOt1VALUES(2,44,2,u);INSERTINTOt1VALUES(1,2,3,u);INSERTINTOt1VALUES(3,6,2,u);这是我们在查询表中的所有数据我们会发现查出来的数据已经是根据a字段排序后的结果了那么为什么会出现这个种情况呢原因是因为当我们在进行插入操作的时候mysql的InnoDB引擎会根据主键索引进行比较比如我们插入第一条数据a的值为4接着插入第二条数据a的值为2这时InnoDB就会进行一个比较将值小的数据放到前面。为什么在存储的时候会进行排序呢比如我们这里有一个数列 5714823要求写一个算法找到数值2那么一般我们的第一个想法就是先对这个数列进行排序。是的之所以InnoDB会在存储的时候进行排序的原因就是为了增加查询效率。在这里需要说到一个概念那就是在进行数据库查询的时候查询的数据是按照最小单位页来查询的mysql中一页可以存储16kb的数据意思就是说比如你要查询a2的数据此时从数据库里取出的数据可能不只有a2的这一条数据有可能是a1到4的所有数据直到将一页16kb的空间占用完为止。这里假设a1到4的着4条数据刚好占16kb此时我们查询a2.5当数据库比较到a3时就不会继续在往后比较了因为数据已经是排序好了所以会直接判断数据库中没有这个值。索引的原理采用btree的数据结构非叶子节点保存了叶子页中最小主键的值因为叶子节点是用来存数据的而mysql查询数据的最小单位是页一页中可能存在多条数据所以要取最小的主键方便比较以及页地址。形象点来说索引就相当于一本书的目录可以帮助我们在查询的时候锁定一个范围避免全表扫描。索引为什么可以提高查询效率因为索引是模仿的B树这种数据结构它的每个节点都可以存在多个元素且它的非叶子节点的每个元素分成了两块信息第一个就是保存了孩子页最小索引值以及孩子节点的地址比如我们设A字段为主键索引现在有两个非叶子节点他们的孩子页最小索引值分别是1和5这时我们要查询A4那么他就会拿这个4去和非叶子节点的1和5进行比较最终就可以确定4这个值要么就在1这个节点的分支下要么不存在不可能在5分支下因为非叶子节点保存的是孩子节点的最小值所以5这个分支下不可能存在比5小的数据。索引就是基于这个原理提高的查询效率。![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e9487450cfefb9a444eea6d0d3447147.png最左前缀原则比如我们创建了一个联合索引A,B,C三个字段那么基于b树的数据结构这些节点的值就是ABC这三个字段的值在进行查询比较的时候首先会跟A字段的值比较如果相等在和B字段的值比较以此类推。基于这个原理我们得出sql的查询条件必须要有A这个字段否则是没办法比较的。比如现在有两个非叶子节点他们的值分别是234和546这时我们的sql条件为Where A3 and B5 and C6执行过程就是首先拿A的值3和非叶子节点中的2和5进行比较就能得到356这个值肯定是在234这个分支下的。在比如我们的sql条件为Where B5 and C6执行过程就是首先拿A的值进行比较但条件没有A值所以就没办法进行比较设想一下35可以和234或546进行比较吗肯定不可以但如果我们的查询条件是Where A3 and C6那么索引是可以起到一定作用的因为可以确定36肯定是在234这个分支下或者不存在绝对不可能在546这个分支下因为这个分支下的数据最小就是546如果存的数据类型是字符的话那么会根据字符的ASCII编码来进行存储和排序。原理一样