MySQL-索引学习笔记

mgs2002 2020年02月03日 259次浏览

为什么需要索引

索引就是为了提高数据库检索数据的效率而出现,是存储引擎快速找到记录的一种方式。类似于书的目录,对于数据库而言,索引就是它的“目录”。尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要,减少磁盘io次数,加速查询。合理的使用索引将使查询速度提高几个量级。

常见模型

索引设计常见模型有hash表,有序数组,搜索树

hash表

类似于Javahashmap,K-V存储结构,就是将key通过hash函数算出具体的位置后存放,有可能出现hash碰撞(多个key的hash值一致),出现这种情况用链表法解决。
hash表只能用于等值查询,如果有范围查询则必须遍历范围数据,效率低。

有序数组

类似于JavaArrayList。等值和范围查询效率都很高,缺点是添加/删除数据需移动整个数组开销比较大。

搜索树

搜索树有二叉树和多叉树。二叉树的父节点下面有2个子节点,其中左儿子节点小于父节点,右儿子节点大于父节点。多叉树就是父节点下面有多个子节点,每个子节点依次递增的顺序排列。

二叉树搜索效率最高,但是数据库都是使用多叉树设计。原因是索引不止在内存,还要写到磁盘上面,如果数据库的数据量大,那么二叉树的树就会比较高,那么查询数据寻址的次数就会比较多,访问磁盘次数也也比较多,那么效率就比较低。而多叉树由于子节点比较多,那么每个子节点数据存储肯定比二叉树多,树高也比二叉树矮,数据寻址次数比二叉树低得多,所有效率有优势。

MySQLinnoDb引擎就是采用多叉树设计的(B+树)。每一个索引在 InnoDB 里面对应一棵 B+树。

索引类型

索引有两种类型:主键索引(聚簇索引)和非主键索引(二级索引)

聚簇索引

以主键列(primary key)创建的索引,叶子节点存储的是一条完整的数据。使用InnoDB存储引擎的时候,每建一个表,就需要给一个主键。

如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引,如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引。在许多情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序,聚集索引能够特别快地访问针对范围值得查询。

二级索引

以非主键列创建的索引,叶子节点存储的是数据的主键值和包含主键ID的书签(bookmark)

书签你可以理解为是一个{'字段名称',字段值,主键id值}的数据,用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据

回表

假定表t有主键字段ID,普通索引字段a,其他字段c,现在有如下SQL语句:

select * from t where a=?

该语句的流程是这样的

  • 先搜索a索引树,得到主键ID的值。
  • 通过主键ID的值再到ID索引树查询一次得到数据,这样的过程被称为回表。
    回表流程.png

建议

  • 尽量为每个表设立一个自增长类型的主键

Tips:因为B+树为了维护索引有序性,在插入新值的时候需要做必要的维护,新增数据时如果行ID在最后一个ID之前,如果ID不是自增主键则相对麻烦,需要逻辑上挪动后面的数据,空出位置。
而更糟的情况是,如果后面ID所在的数据页已经满了,根据 B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。
自增主键的插入数据模式,正符合了递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高

  • 尽量使用int或者bigint类型自增类型的字段作为主键

Tips: 这是从存储空间角度考虑,int类型占用4个字节空间,bigint占用8个字节,字符串类型做为主键(比如身份证和UUID)占用空间肯定超过上面两个。由于二级索引存储的是主键值,所以主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小

  • 业务字段做主键场景:表里只有一个索引而且该索引必须为唯一索引,这就是典型的K-V场景。由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。

覆盖索引

覆盖索引可以减少回表次数,提高搜索效率。假定表t有主键字段ID,普通索引字段k,执行如下SQL语句

select id from t where k=1

这个SQL执行不需要回表,因为索引k里面保存了主键id的值,所以不需要去主键索引里面查询,如果把语句改成select * from t where k=1 就会多执行一次回表,具体分析可以看上面的回表介绍。

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段

最左前缀原则

  • 无论是单个索引(a)或者联合索引(a,b,c)都是从左到右开始匹配数据,比如
select * from t where a=1 and b=1

索引会依次匹配a,b。

  • 如果查询条件没有索引项最左边的a,比如是b=1或者b=1 and c=1,那么将不会匹配索引。如果需要单独查询b或者c的数据需要单独建立普通二级索引。

因为建立搜索树的时候a就是第一个比较因子,必须要先根据a来搜索才能知道下一步去哪里查询

  • 如果查询条件包含了a,但是顺序不在第一位,比如c=1 and a=1,Mysql会自动优化为a=1 and c=1 以匹配索引
  • 由于最左前缀原则,在创建联合索引时,索引字段的顺序需要考虑字段值去重之后的个数,较多的放前面。ORDER BY子句也遵循此规则。

索引下推

表t有(a,b)联合索引,现在有

select * from t where a like '赵%' and b > 10

的查询条件,5.6前的版本会通过a索引开始一个个回表查询符合条件的数据,5.6后的版本则会先过滤掉b<=10的数据,在剩余数据里面通过a索引回表查询,这样减少了回表的次数。
索引下推简单流程.png
如上图,InnoDB 在 (a,b) 索引内部就判断了b是否大于10,对小于等于10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID3、ID6 这两条记录回表取数据判断,就只需要回表 2 次。如没有索引下推流程,需要回表 4 次。