mysql索引

索引的分类

主键索引

        对表的主键创建的索引，默认创建，只能有一个，字段必须唯一且非空

唯一索引

        对表中的具有唯一性的字段可以加唯一索引，字段必须唯一

普通索引

        对字段添加索引 

全文索引

通过ngram算法，将一段文本分成多个由n个连续单元组成的term形成索引 只能对 char,varchar text类型的数据添加

innodb聚集索引

        在innodb中聚集索引是指把数据和索引放到一起，叶子节点直接保存完整的行数据，主键索引就是聚集索引。

如果表没有主键。会使用第一个唯一索引作为聚集索引。

如果也没有唯一索引，会自动生成一个隐藏的rowid作为聚集索引

innodb二级索引

  在innodb中聚集索引是指把数据和索引分开，叶子节点放的是主键和索引列值。

索引覆盖与回表

覆盖

        覆盖是指找到对应的叶子节点之后，拿到了我们所需要的所有字段：例如 

一张表有 id,name,age等字段

select  id,name,age from T where id=1；

我们按id查询，主键id是聚集索引，叶子节点包含了行数据，当我们查找对应的叶子节点，就可以拿到我们需要的字段数据id,name,age。

回表

        回表是指找到对应叶子节点之后，没有拿到 我们要的全部字段，需要根据主键再去查一次找到我们要的字段。例如：

T add indxe name_idx(name)

我们对name添加普通索引，这是一个二级索引，叶子节点保存主键和索引列值

select  id,name,age from T where name="xx"；

我们找到叶子节点之后，找不到age字段，要根据叶子节点的主键再去查找一次

如果我们只查找 id和name如： select  id,name from T where name="xx"；

这样我们到叶子节点我们就拿到了我们所要的所有字段，就不需要再回表了

索引下推

       索引下推是mysql5.6引入的， 当我们使用联合索引，联合索引中的部分列索引失效，使用未失效的索引查询出数据很多，要进行多次回表查询数据来过滤where条件

        索引下推会帮我们使用失效的索引列对where条件进行数据过滤，减少回表次数

例如：

表里创建联合索引（name, age）。

select * from T where name like 'X%' and age=22;

无索引下推：

age字段索引失效，根据name查到的数据会很多，那么此时会每一条数据都回表查询，然后再过滤age=22

有索引下推

age字段索引失效，根据name查到的数据，会直接对age字段先过滤一遍，剩下的数据再回表查询，这样就减少了回表的次数。

最左前缀法则

如果我们建立联合索引 例如 T add indxe c1_c2_c3_idx(c1,c2,c3)

我们对C1，C2,C3 三个字段建立联合索引

1.我们在查询的时候 where条件必须包含C1字段，即索引最左边的一个字段，否则不会走索引

2.如果查询的where条件跳过一个索引字段，那么只会走跳过之前的索引

  例如select xxx from T where c1=x and  c3=x；

  跳过了C2字段 那只会用到C1字段索引  C2，C3字段都不会生效

索引失效

1. 联合索引未遵循最左前缀法则
2. 索引字段进行计算、函数、类型转换、
3. 模糊查询用%开头，%在右边不会导致单列索引失效，但是在联合索引中，会导致%号右边的列失效。
4. 字符串不加单引号，发生隐式转换
5. 使用or，or两侧中有一个没有索引那个两边都会失效;
6. 范围查询右侧的列索引失效  > <
7. mysql优化，如果mysql认为使用索引比全表扫描更慢，就不会用索引