什么是索引

根据索引类型，可以分成主键索引和二级索引（非主键索引）

主键索引：主键索引是叶子结点保存主键对应行的全部数据， 在 InnoDB 中，主键索引，也被叫做聚簇索引。二级索引(非主键索引）：二级索引的叶子结点保存的是索引值和主键值，当二级索引进行查询时，需要进行回表操作。

  • select * from t_user where id=1 即主键查询方式，则只需要搜索id这棵B+树

  • select * from t_user where name=‘张三’ 即普通索引查询方式，则需要先搜索name索引树，得到id的值为3，再到id索引树搜索一次。这个过程称为回表

可以看到，基于二级索引的查询需要多扫描一颗索引数，因此，尽量使用主键查询。

什么是覆盖索引

场景：10W条数据，我要从其中查出100条不连续的数据，给你id，来查name和password进行展示,如何才能高性能的去使用？

当 SQL 语句所求查询字段 （select 列）和查询条件字段（where) 全都包含在一个索引中（联合索引), 可以直接使用索引而不需要回表，这个就是覆盖索引。

联合索引

在某一列上加索引以提升相关语句查询效率，联合索引就是在多个列上加索引。

相对单列来说，就是在多个列上加索引

create table t_user (
id bigint(20) not null auto_increment ,
name varchar(255) not null,
password varchar(255) ,
index(name)
primary key (id)
)engine=innodb default character set=utf8 collate=utf8_general_ci

看个例子：

select id from user_table where name= '张三'

name 是二级索引，索引树的叶子结点存储的保存有 name 和 id 值，所以通过 name 索引树查找到 id 之后，可以直接提供查询结果，不需要回表。这个查询里 索引 name 覆盖了我们的查询需求，我们称为是覆盖索引。

select password from user_table where name= '张三'

name 索引树上找到叶子结点， name = "张三“ 对应的主键 id, 通过 id 在主键树上找到满足条件的数据。

主键和索引有什么区别

主键索引

  • 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

  • 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

唯一索引

  • 唯一性索引列允许空值，而主键列不允许为空值。

  • 主键列在创建时，已经默认为非空值 + 唯一索引了。

  • 主键可以被其他表引用为外键，而唯一索引不能。

  • 一个表最多只能创建一个主键，但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

索引下堆

SET optimizer_switch = 'index_condition_pushdown=on';

在MySQL 5.6中 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

不使用索引下堆:

根据（username,is_del）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。每一个虚线表示一次回表操作。

使用索引下堆

InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除，对于逻辑删除的记录，直接判断并跳过虚线表示回表，使用索引下堆，回表次数减少为 2 次。

参考资料

https://www.cnblogs.com/Leo_wl/p/13093705.html
https://www.jianshu.com/p/bdc9e57ccf8b

https://mp.weixin.qq.com/s/cWP99GtD0q_8ZGSbLHjndA