什么是索引

根据索引类型,可以分成主键索引和二级索引(非主键索引)

主键索引:主键索引是叶子结点保存主键对应行的全部数据, 在 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