mysql innerdb 索引_mysql——InnoDB索引

1 InnoDB索引介绍

底层是页结构，每次insert时都会按照主键进行排序，因此最好按照主键顺序进行insert或者设置主键自增，不然会影响插入效率。

如果要插入的数据一页已经不够，则会分页，新插入的数据会根据主键顺序选择插入位置而并不一定插入在新的一页上

但是这样多个的结构本身就构成链表，在搜索不同的页时也会很慢，因此在此基础上形成b+树，如下图所示

上图是按照主键索引进行构建的b+树，页(Page)是 Innodb 存储引擎用于管理数据的最小磁盘单位，除了叶子结点都叫索引页，而叶子结点则是数据页，这里索引和数据都放在一起，我们叫做聚簇索引。

2 B+树的特点

①叶子结点之间有指针；

②数据只存储在叶子结点上，有数据冗余；

③b+树的一个结点可以存储多个数据(b树也可以)

3 查数据的两种方式：索引和全表扫描

走索引就是根据索引，一步一步往下查找；全表扫描就是直接在叶子结点一个一个进行查找。

那么，为什么InnoDB里面的b+树的叶子结点是双向指针呢？

举个例子，执行sql: select * from table1 where a<5; 会先通过索引定位到等于5的值，再将5前面的所有值查询出来，这样双向指针的作用就体现出来了。

4 如果要按照联合索引进行查询呢？

执行sql：create index idx_t1_bcd on table1(b,c,d);

结果如下：

这样会导致另外一个问题，在使用不同的索引时会重新复制一份新的数据，消耗了大量的空间，真正的结果如图所示，非叶子节点还是和上图一样，但是叶子结点改为只存储相应的索引值对应的字段，不是索引不存储，在根据该索引进行数据库操作时，会通过其中的主键索引回表到主键索引里面查找。(下图里面黄色部分表示每一行数据对应的主键)

最左前缀原则

(1)select * from table where b=1 and c=1 and d=1; --可以根据索引进行查询

(2)select * from table where c=1 and d=1; --不可以根据索引进行查询，因为根据索引查询b是首先需要确定的

(3)select * from table where b>5; --可以根据索引进行查询，但是会进行全表扫描，因为定位到等于5的数据后会查询到5后面的数据，但是每一个数据都需要根据其主键(黄色部分)查询到完整的数据

--(3)的优化：select b from table where b>5;、select b,c from table where b>5;

--注意：select b,c,d,a from table where b>5也不需要回表，但是select b,c,d,a,efrom table where b>5需要，因为e字段不在联合索引里面

(4)select b from table; --这里也用到了bcd联合索引，而没有用全表查找，是因为在联合索引b+树里面么一个叶子节点都是存储的不完整的数据，所以在每一页里面都可以存储更多的数据，这样就减少了查找时间。此时，执行select a from table也会用bcd联合索引。

注意

create create index idx_t1_bcd ontable1(e);--这里面的e字段是varchar类型的

因此在执行 select * from table1 e=1时，会把e字段里面的所有数据全都转成int类型的再和1进行比较，所以进行的是全表扫描，而select * from table1 e=’1‘会执行索引，原因如下：

select 1=1; --1

select 1='p'; --0

select 0='p'; --1 mysql在进行不同类型的比较时会把‘p'转换成0

mysql里面只要对字段进行了操作，都不能走索引，如：select * from table1 a+1=1；这里面的a+1就是对字段进行了操作