深入理解MySQL数据库（Innodb存储引擎）

1.B+Tree

什么是B Tree

1）树中每个结点至多有M棵子树（即至多含有m-1个关键字）

2）若根节点不是终端结点，则至少有两颗子树

3）所有的叶子结点都出现在同一层次上，并且不带信息

什么是B+Tree

• 一个节点里面可以有多个元素
• 进行过排序
• B+树的叶子结点之间是有指针
• 非叶子结点的数据都冗余了一份在叶子结点（B+树的所有元素都能在叶子结点上找到）
  

MySQL中的B+Tree是如何生成的

B+树相当于B树的升级版，这里叶子结点之间是一个单向指针，在MySQL中的B+树叶子结点之间是一个双向指针

2.Innodb的存储结构

Innodb是通过页式存储来存数据的，每页的大小为16kb

可以通过语句看出Innodb的页大小为16384bit，16384/1024=16kb，说明Innodb的页的大小为16kb。
也就说明Innodb从磁盘当中读数据和取数据的最小单位是页，当Innodb想要往磁盘当中存数据时就要在磁盘中开辟一个16kb大小的内存空间

3.Innodb页结构是什么样的

• 首先创建一个 t1 表 并设定存储引擎为Innodb
• 然后查看当前t1表中的索引可以看出当前表只有一个主键索引，而且表的类型为B树，是因为B+树也是B树的一种
  
  接着我们不按顺序插入几条数据

insert into t1 values(4,3,1,1,'d');
insert into t1 values(1,1,1,1,'a');
insert into t1 values(8,8,8,8,'h');
insert into t1 values(2,2,2,2,'b');
insert into t1 values(5,2,3,5,'e');
insert into t1 values(3,3,2,2,'c');
insert into t1 values(7,4,5,5,'g');
insert into t1 values(6,6,4,4,'f');

select * from t1

可以看出虽然没有按照顺序插入数据，但是数据还是进行了排序，原因是Innodb底层是B+树结构，所以数据按照B+树进行了自动排序

来看看普通查询是如何执行的

如果我们执行select * from t1 where a=7 这条语句按照正常逻辑应该是这样的：
每进拿一行数据就要调用CPU将数据从磁盘中将数据拿到内存中，进行一次磁盘IO。

如图可以看出一共8条语句要进行7次磁盘IO才能拿到想要的数据，这样的执行效率非常低。

但是如果存储引擎是Innodb插入和查询是如何执行

因为Innodb的存储结构是页式存储，所以它只需要进行一次磁盘IO，从磁盘中取出一页的数据也就是16kb的数据在内存中。然后再从内存中去操作查询a=7的数据，这样速度就快很多。

Innodb的页结构图大概如下：

Innodb插入是如何执行的

Innodb插入数据：

当Innodb插入第一条数据的时候也就相当于没有页，所以就需要新开辟一页，再将数据插入到用户数据区域，效果如图：

当插入第二条数据的时候Innodb是会自动根据字段的主键进行排序的，也正是因为这样所以很多时候都建议用主键自 增方式插入主键，这样Innodb就只会直接在后面插入，而不用重新排序。

插入第二条数据：

Innodb查询是如何执行的

Innodb执行查询语句
示例图如下：

但是如果我们去查询a=30000： select * from t1 where a=30000

我们去遍历整个数据链表时就需要一次遍历所有数据直到找到a=30000，这样的话遍历效率就非常的低，这页是链表的一个缺点，查询效率慢。

Innodb对于这一个问题也有一个非常好的解决办法，那就是页目录，图实例如下：
我们页目录中存放一个当前目录的第一条数据的主键和指针

如果一页数据已经存满16kb那么就会新开一页，结构如下：

假如我们现在要查询a=6这条数据，数据库会怎么去执行？

因为数据库根本不知道a=6这条数据到底在第几页，所以它就会从磁盘中先取出第一页，然后再也目录中查看页目录为3的所有数据发现没有，然后再通过next指针查找第二页，在第二页的页目录为5里面查找a=6这条数据，如果这样的话，我们有很多很多页的数据，而且数据在最后一页的话，就需要从磁盘中一页一页去取，这样效率也是非常的低。
Innodb是这样处理的：

对于上面这种问题Innodb会重新开辟一个页，用来存储每页的页目录的最小的主键值和指针，这样我们只需要从新开辟的这一页中去查看我们的数据具体会在哪一页。大大提高了查询效率

4.主键索引生成的B+树

我们根据这种结构优化一下就能得到Innodb的主键索引生成的B+树的结构，如图：

如果我们通过主键索引a字段去查找a=6这数据，就会走索引，执行路线为：

这样执行查询语句的效率就非常的高

5.什么是全表扫描

如果不根据主键索引查找：select * from t1 where b=6:

那么这样就没法走索引了，就只能从第一页开始进行查询知道查找到b=6这条数据，这样的过程叫做全表扫描

6.什么是索引页、数据页、聚簇索引（聚集索引）

上面的叫做索引页，下面叫做数据页，整个叫做聚簇索引（聚集索引），在Innodb里面主键索引就是做聚簇索引

7.Innodb如何支持范围查找走索引的？

上面我们说了使用select * from t1 where a=6这条语句能够走主键索引

那么如果使用select * from t1 where a > 6能不能走索引呢？用代码实现看看

它的底层是先利用索引找到a=6这条数据，然后返回a=6后面的所有数据，所以它还是走了索引。

8.为什么要遵守最左前缀原则才能利用到索引？

创建主键索引Innodb会根据主键进行排序，那么创建bcd索引Innodb也会根据bcd进行排序，首先按照b排序，如果b字段一样大就会看c字段谁小谁就在前面，依次类推再看d字段。

首先创建一个联合bcd索引，那么bcd索引生成的B+树结构按照惯例应该是下面这样：

但是如果bcd索引的B+树结构是上述这样的话会出现一个问题，那就是如果执行更新数据的操作后，所有的索引的叶子结点都需要去修改，这样既浪费了空间，有存了一些没有必要的字段。

那么进行优化后的结构就位最终的bcd索引的B+树：

经过优化后如果我们要执行查找select * from t1 where b=1 and c=1 and d=1就可以根据bcd索引找到相应的主键值，然后再通过主键索引回表查询到相应的数据

通过拿到主键值，再通过主键索引查找对应数据这样的操作叫做回表

• 如果我们执行select * from t1 where c=1 and d=1 and b=1 那么这条语句会走bcd索引吗？

由此可以得出一个结论走不走bcd索引跟它的条件字段的顺序是没有关系的，只要条件里面包含了bcd字段就能走bcd索引。

• 那如果执行select * from t1 where c=1 and d=1 条件里没有了b字段看看会不会走bcd索引？

原因：如果是上面的语句可以从条件看出条件是这样的 ? 1 1 b是没有值，c=1 d=1，所以没有满足最左前缀原则，就不会走bcd索引。

• 再看看执行select * from t1 where b=1 and d=1

原因：条件是这样的1 ? 1，只有中间c没有值，a=1，d=1，满足了最左前缀原则

• 再看看执行select * from t1 where b=1

综合上面几种情况就能弄清楚什么是最左前缀原则

9.范围查找导致索引失效原理分析

执行范围查找有时候会导致索引失效的原因是什么？

执行一条语句： select * from t1 where b>1

如果执行 select * from t1 where b>6

那么就只会进行一次回表操作，所以这条语句就会走索引

总结：范围查找的范围越精确才不容易导致索引失效

10.覆盖索引的底层原理

执行 select b from t1 where b>6

我们通过条件b>1查找b字段，走索引只能查找到所有不完整的字段，而恰巧这些不完整的字段当中含有b字段，那么我们就不需要进行回表查询，直接将b字段返回，这种就叫做覆盖索引。

11.order by为什么会导致索引失效

执行SELECT * FROM t1 ORDER BY b,c,d

为什么会导致不走索引而是全表扫描呢？

虽然走索引的话不需要额外排序，因为我们是查找表中所有数据，Select * 所以我们每找到一条数据就要通过数据的主键值进行回表操作，所以在这里需要进行8次回表操作，而如果进行全表扫描的话就只要需要将所有的数据读取到内存中，然后再内存中进行排序，内存的速度是非常快的所以效率要比走索引高得多。

12.MySQL中数据类型转换

MySQL自动会将varchar类型的字母转换成0

比如： 'a'=0返回的是1，true。 'a'=1返回的是0，false。会将字符串中的数字转成对应的数字： '123'=123。

13.为什么对字段进行操作后会导致索引失效

如：SELECT * FROM t1 WHERE b+1=1-1

对b字段进行了+1的操作就会导致索引的B+树结构破坏。

因为如果一旦对字段进行了操作后就会破坏原有的B+树的结构，所以对字段进行操作后是不会走索引的。

14.什么是索引下推

比如我们执行SELECT * FROM t1 WHERE b=1 and c=1

因为此时我们走bcd索引时由于没有b字段的条件相当于b字段的索引断了，那么从字段的索引也会浪费，所以就会找到索引中a=1的所有数据，比如111,123,1**，如果此时没有索引下推，那么就会根据找到的所有的字段进行回表，比如根据111所对应的主键值进行回表找到对应的数据判断c=1，这样的话就会进行多次回表,。

如果加上索引下推，就会根据索引中的c=1字段，找到相应的索引，这里只有111符合，那么就只会进行一次回表操作就能找到对应的数据。