MySQL索引、使用场景、失效场景、回表、索引覆盖

一、MySQL存储引擎

1、MySQL比较常见的存储引擎：InnoDB、MyISAM，使用的是B+树结构
2、InnoDB中，主键索引的B+树叶子节点存储的是整行数据
3、MyISAM中，主键索引的B+树叶子节点存储的是整行数据所在内存中的地址

二、索引的使用场景

1、适合创建索引的情况
【1】主键自动建立唯一索引
【2】频繁作为查询条件的字段应该创建索引
【3】查询中与其它表关联的字段，外键建立索引
【4】查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
【5】查询中统计或者分组字段

2、不适合创建索引的情况
【1】表数据量太少
【2】where条件里用不到或关联条件用不到的字段
【3】值大量重复或差异化很小的字段，不适合建立索引，性价比不高

三、索引失效场景
1、类型不匹配：查询条件中索引列类型与值类型不一致
2、索引计算：在索引上做计算
3、最左原则：查询条件的列与索引列的字段相同，顺序不同、从不同顺序列开始后边都不走索引
4、or：减少使用or
5、索引覆盖：不走索引覆盖
6、索引范围：索引列上不能有范围查询，比如大于，小于，大于等于，小于等于
7、null：字段的is not null 与is null
8、like：like的前后模糊匹配，like模后不模前
9、不等：使用不等于（!= 或者 <>）的时候

四、索引回表

1、MySQL中我们常说的主键索引，就是聚簇索引。主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引、或者叫作辅助索引

2、主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一区别在于叶子结点中存储的内容不同：
【1】主键索引的叶子节点存储的是一行完整的数据
【2】非主键索引的叶子节点存储的则是主键(id)的值

3、当我们查询的时候：
【1】例如 select * from user where id=100，那么此时只需要检索一次主键索引的 B+Tree(索引树) 就可以找到完整数据
【2】例如 select * from user where username='张三'，那么此时需要先搜索 username 这一列的 B+Tree(索引树)，检索完成后得到主键值，然后再用主键值去检索主键的 B+Tree(索引树)，就可以获取到一行完整的数据。也就是通过两次检索 B+Tree(索引树) 才可以找到完整数据

小结：
对应第【2】种情况，共检索了两棵 B+Tree(索引树)，第一次检索 B+Tree(索引树) 拿到主键值后再去检索主键索引的 B+Tree(索引树)，这个过程就是所谓的回表

五、索引覆盖

1、使用非聚簇索引也就是非主键索引一定会回表吗？不一定
如果查询的列字段本身就存在于索引叶子节点中，那么即使使用二级索引，也是不需要回表的

2、索引覆盖验证
【1】例如有一张用户表t_user_info，id是主键索引，user_name是非主键索引，如果通过非主键索引查询整行记录，就需要先通过非主键索引user_name查询到聚簇索引也就是主键索引拿到id，再根据id查询到整条记录，这个需要回表
【2】如果通过user_name查询id字段，这个id字段在非主键索引user_name索引树的叶子节点中直接可以查询到，就不需要回表
验证脚本：

create table t_user_info
(
    id          bigint auto_increment comment '主键id'
        primary key,
    user_name   varchar(100)                         not null comment '姓名',
    pwd         varchar(100)                         not null comment '密码',
    email       varchar(100)                         null comment '邮箱',
    price       decimal(10, 2)                       null comment '价格',
    is_enable   tinyint(1)                           null comment '是否有效(0否，1是)',
    remark      text                                 null comment '备注',
    deleted     tinyint(1) default 0                 not null comment '是否已删除，0-未删除，1-已删除',
    create_by   bigint                               null comment '创建人Id',
    create_date datetime   default CURRENT_TIMESTAMP not null comment '创建时间',
    update_by   bigint                               null comment '修改人Id',
    update_date datetime   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间'
)
    comment '用户基础信息表' charset = utf8;
 
create index index_user_name
    on t_user_info (user_name);
 

explain select id,user_name from t_user_info where user_name = '张三';

执行计划：

说明：
可以看到，此时使用到了user_name索引，但是最后的 Extra 的值为 Using index，这就表示用到了索引覆盖扫描，此时直接从索引中过滤不需要的记录并返回命中的结果，这一步是在 MySQL 服务器底层完成的，并且不需要回表

画外音：
平衡二叉树：如果数据较多，就会平衡出很多树杈，树深度太高，不利于快速检索
多路平衡树(B+树)：在根节点和叶子节点之间只有一层树杈，可以通过索引树快速检索叶子节点，如果叶子节点没有可以通过指针到其他叶子节点检索，效率更高