MySQL 索引

概念

聚簇索引

将数据存储和索引放到了一块,找到了索引也就找到了数据

psMySQLInnoDB引擎中,索引数据结构是B+树,主键索引叶子节点的值存储的就是MySQL的数据行,普通索引的叶子节点的值存储的是主键值。

总结:

  1. 定义了主键,则该主键就是聚簇索引

  2. 未定义主键,第一个not NULL unique列是聚集索引

  3. 所有字段均可为NULLInnoDB会创建一个隐藏的row-id作为聚集索引

非聚簇索引

以主键以外的列值作为键值构建的 B+ 树索引,我们称之为非聚集索引。

InnoDB引擎中,一般一个表只能有一个聚簇索引,但可以有多个非聚簇索引,每一个非聚簇索引的叶子节点都存储了主键的值。

唯一索引

索引列的值必须唯一,但允许有空值,且空值只能有一个

全文索引

MySQL5.7版本之前只在MyISAM引擎上支持,全文索引可以在CharVarcharText类型的列上创建。

联合索引

多列联合组成索引。

最左前缀优先原则

最左前缀原则(假设索引为a, b, c)

则有效索引为:

  • a
  • a,b
  • a,b,c

覆盖索引

查询字段中,索引 已经“覆盖了”我们的查询需求,我们称为覆盖索引。

举例: select ID from T where k between 3 and 5
这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表

回表

InnoDB中,非聚簇索引的叶子节点上存储的值为主键的值,并不包含完整行的数据,

所以需要拿着主键的值,到主键索引树上去查询整行的数据,这个过程称为回表。

索引下推(Index Condition Pushdown)

启用版本

Mysql5.6的版本上推出,用于优化查询。

可通过下述配置关闭

set optimizer_switch='index_condition_pushdown=off';

优点

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

流程变化

不使用ICP

  1. MySQL Server发送查询请求到存储引擎(查询条件里包含索引条件)
  2. 存储引擎根据索引检索数据,将检索出的数据返回给MySQL Server
  3. MySQL Server再根据条件筛选数据

使用ICP

  1. MySQL Server发送查询请求到存储引擎(查询条件里包含索引条件),并将条件传递给存储引擎
  2. 存储引擎根据索引检索数据,将符合条件的数据返回给MySQL Server
  3. MySQL Server不需要根据索引字段再筛选了

适用场景

  1. 当需要整表扫描

  2. 适用InnoDB引擎和MyISAM引擎查询,5.6版本不适用分区查询,5.7版本可以用于分区表查询

  3. InnoDB引擎仅仅适用二级索引(即非聚簇索引),原因: InnoDB聚簇索引将整行数据读到InnoDB缓冲区

不能使用索引下推的情况

  1. 子查询条件不能下推
  2. 调用存储过程条件不能下推
  3. 触发条件不能下推(这个不太明白)

索引失效

  1. 使用 like 关键字, 且 % 在前面
  2. 联合索引不符合最左前缀原则
  3. or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引,该索引失效
  4. 数据类型出现隐式转化,如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描
  5. 在索引字段上使用not<>!=, 只会产生全表扫描
  6. 对索引字段进行计算操作、字段上使用函数
  7. 当全表扫描速度比索引速度快时,MySQL会使用全表扫描,此时索引失效

优缺点

优点

  1. 索引大大减小了服务器需要扫描的数据量,从而大大加快数据的检索速度
  2. 索引可以帮助服务器避免排序和创建临时表
  3. 索引可以将随机IO变成顺序IO
  4. 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组,提高了表访问并发性
  5. 关于InnoDB、索引和锁:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)

缺点

  1. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
  2. 对表进行增删改时,数据库本身会增加一个对索引维护的步骤,所以效率会受到影响

数据结构

Hash

特点

InnoDB引擎中,

  • 采用除法散列函数

  • 解决冲突机制采用链表法

自适应哈希索引

InnoDB引擎中,用户无法手动创建哈希索引

InnoDB会自调优(self-tuning),如果判定建立自适应哈希索引(Adaptive Hash Index, AHI),能够提升查询效率,**InnoDB自己会建立相关哈希索引**。

key是索引键值(或者键值前缀)。

value是索引记录页面位置。

优点

检索效率非常高,索引的检索可以一次定位

缺点

  1. Hash索引仅仅能满足=in()!=,不支持范围查询
  2. Hash索引无法被用来避免数据的排序操作
  3. Hash索引不能利用部分索引键查询通过组合索引的前面一个或几个索引键进行查询的时候,Hash索引也无法被利用
  4. Hash索引在任何时候都不能避免表扫描,由于不同索引键存在相同Hash值,所以即使取满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
  5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高

B树

特点

  • 每个节点包含了索引值和表记录的信息
  • 叶节点具有相同的深度
  • 叶子节点的指针为空
  • 节点中的数据key从左到右递增排列

优点

BTree的结构可以弥补红黑树的缺点,解决数据量过大时整棵树高度过大的问题,
相同的数据量只需要更少的层,相同的深度可以存储更多的数据,查找效率更高。

缺点

在查询单条数据是很快的,但是如果范围查询的话,BTree结构每次都需要从根节点查询一遍,会影响效率;

读取索引数据时,会连带着将整行数据加载进内存,占用更多的内存

B+ 树

特点

  1. 非叶子节点不存储数据,只存储key(索引值),会有冗余
  2. 叶子节点存储数据,包含行所有字段
  3. 叶子节点用指针连接(双向)-便于范围查找,提高区间访问的性能

优点

  1. 顺序访问指针,提高区间访问的性能
  2. 层高不会太高,查询速度快

为什么采用B+树,而不是B树

在操作系统中有个叫“”的概念,是用来存储数据的一种单位,大小为4kMySQL中也有“页”的概念,但大小为16k,你可以理解为MySQL中的“页”就是上面B树的一个个节点。

为了尽可能使索引树“变矮”从而减少磁盘IO,最好的做法是让一个节点尽可能地塞入更多的数据。

所谓的B+树,就是把原先B树中分散在各个节点的数据都“赶到”最底层的叶子节点,非叶子节点只存储主键-addr形式的数据

这样每个非叶子节点,能存储的主键就变多了,整棵索引树就变矮了,磁盘IO自然也就减少了。

并且B+树查询效率是稳定的,每次查询一定都要查询到叶子节点才可以获取到数据。

B树的查询效率是不稳定的,最好情况是根节点,最差情况是叶子节点。