存储引擎InnoDB的索引

我们常用的MySQL默认的存储引擎就是 InnoDB,在  InnoDB 里面，它是以主键为索引来组织数据的存储的，所以所以索引文件和数据文件是同一个文件，都在 .ibd 文件里面，

在 InnoDB 的主键索引的叶子节点上，它直接存储我们的数据。

聚集索引（聚簇索引）：索引键值的逻辑顺序和表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫做聚集索引。）

在 InnoDB 里面，它组织数据的方式叫做（聚集）索引组织表（clustered index organize table）， 所以主键索引是聚集索引，非主键索引都是非聚集索引。

主键之外的索引，比如在user表的name字段上的普通索引，又是怎么存储和检索数据的呢？

InnoDB 中，主键索引和辅助索引是有一个主次之分的。

辅助索引存储的是辅助索引和主键值，如果使用辅助索引，会根据主键值在主键索引中查询，最后在主键索引中查询到目标数据。

比如我们用 name 索引查询 name=‘青山’， 它会在叶子节点上找到主键值，也就是 id=1， 然后再到主键索引的叶子节点上拿到数据。

 

如果一张表没有主键怎么办？

1、如果我们定义了主键（primary key），那么 InnoDB 会选择主键作为聚集索引。

2、如果没有显示定义主键，那么 InnoDB 会选择第一个不包含 null  值得唯一索引作为主键索引。

3、如果也没有这样的唯一索引，那么 InnoDB 会选择内置 6 字节长得 rowId 作为隐藏得聚集索引，它会随着行记录得写入而主键递增。

 

索引使用原则：数据得离散度越高，越适合使用索引，就拿性别 gender 来说就不建议使用索引。

 

联合索引最左原则：

有时候我们的多条件查询得时候，会建立联合索引。举例:查询成绩的时候必须同时输入身份证和考号。

单例索引可以看作是特殊得联合索引。

联合索引在 B + Tree 中是复合得数据结构，它是按照从左到右得顺序来建立搜索树得（name在左边，Phone在右边）

 

覆盖索引：（回表）

回表：

非主键索引，我们先通过索引找到主键索引的键值，再根据主键值查到索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫做回表。

在辅助索引里面，不管是单例索引还是联合索引如果 select 的数据只用从索引中就可以获取到，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

 

索引的创建和使用

索引的创建：

1、在用于 where 判断 order 排序和 join 的 （on）字段上创建索引。

2、索引的个数不要过多。    ——— ——  浪费空间，更新变慢。

3、过长的字段，建立前缀索引。

4、区分度低的字段，例如性别，不要建立索引      ----- 离散度太低，导致扫描行数过多。

5、频繁更新的值，不要作为主键或者索引。     -------页分裂

6、随机无序的值，不建议作为主键索引，例如身份证、UUID 。 -------- 无序，分裂

7、联合索引把散列性高（区分度高）的值放前面。

8、创建复合索引，而不是修改单例索引。

 

什么时候用不到索引 ？  

1、索引例上使用函数（replace / SUBSTR / concat / sum count avg）、表达式计算 （+  -  *    /）。

2、字符串不加引号，出现隐式转换。

3、like  条件前面带  % 。（过滤的开销太大，这个时候可以使用全文索引）。

4、负向查询。例如 ： not like 不能 ， !=   (<>)   和 not in  在某些情况下可以

这些跟数据库版本，数据量，数据选择度都有关系。

其实用不用索引，最终都是 server 层的优化器说了算。

优化器是基于什么原则来优化的？

优化器基于 cost  开销 （ Cost Base Optimizer），它不是基于规则也不是基于语意。就是怎么开销小就怎么来，最小开销为准。

 

为什么推荐使用自增的 ID 作为 主键索引 ？

主键索引就是聚簇（聚集）索引，索引的逻辑顺序和完整数据行的物理顺序是一致的。

辅助索引data域存储的是主键的值，搜索需要检索两遍索引，先检索辅助索得到主键值，再用主键值从主索引获取详细数据，简单的主索引会降低辅助索引。

聚集索引在同一叶子节点内的各条数据按主键顺序存放，在每插入新纪录时，记录会顺序添加到当前索引节点的后续位置，磁盘页写满，自动开辟新磁盘页。这样会形成一个紧凑的索引结构，近似顺序排满。否则为了将新数据插入合适的位置而移动原存储数据，甚至有可能目标页面已经回写到磁盘上而缓存中已经清除，这时不得不从新从磁盘读到内存，再重新回写，这样增加很多开销，且数据的而频繁移动，每个磁盘页操作的大量碎片。