MySQL(三)索引基本知识,数据结构,匹配方式

索引基本知识

索引的优点

1、大大减少了服务器需要扫描的数据量
2、帮助服务器避免排序和临时表
3、将随机io变成顺序io

索引的用处

1、快速查找匹配WHERE子句的行
2、从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4、当有表连接的时候，从其他表检索行数据
5、查找特定索引列的min或max值
6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
7、在某些情况下，可以优化查询以检索值而无需查询数据行

索引的分类

• 主键索引
  主键唯一且非空,相当于数据库帮忙创建的特殊的唯一索引
  InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须且只有一个聚集索引：
  （1）如果表定义了主键,则PK就是聚集索引；
  （2）如果表没有定义主键,则第一个非空唯一索引(not NULL unique)列是聚集索引;
  （3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引;(这个row-id是6位的,所以尽量我们指定主键)

• 唯一索引

• 普通索引

• 全文索引
  fulltext,一般用在varchar,text上,用的较少

• 组合索引
  对于一些经常需要组合查询的条件列,就可以把多个列组合起来共同创建一个所以哦那

面试技术名词

• 回表
  https://www.zhihu.com/question/347087093/answer/830934717
  MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值
  不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。需要取其他列时,要根据主键再去查主键的B+树.
  回表是什么意思？就是你执行一条sql语句，需要从两个b+索引中去取数据。
• 索引覆盖(or覆盖索引)
  回表需要查询两次B+树,IO次数就比较多;
  如果建立的普通索引/组合索引,我们查询的时候只需要查询该索引(或者主键),不需要查询其他列,就不需要回表了,这就是索引覆盖.
  常见于组合索引
• 最左匹配
  建立组合索引后,比如对三个列name,age和sex 建立了组合索引 index_name_age_sex
  那么where name=? and age=? and sex=?时肯定可以用到索引
  where name=? 或者 where where name=? and age=?时也可以用到索引 (使用多个where条件时,顺序无所谓,MySQL会帮我们优化调整成最左匹配的顺序)
  如果达不到最左匹配,比如where age=?,则不会使用该组合索引
  那怎么办?
  可以把组合索引调整一下顺序,改成 index_age_name_sex;
  或者给age单独建立一个索引
  如果需要在为name和age单独创建索引中 二选一,那么我会选择age;因为age占用磁盘空间更少,减少磁盘IO.
• 索引下推
  (谓词下推?)
  查询sql的过滤条件,比如where name=? and