Indexing

前言

索引的目的是为了提高数据的查询效率。

但是索引不是一定能提升效率,比如

  • 数据量较小,如果有回表操作,那么用索引的效率可能更低。

  • 字段的可选值很少,且分布较均匀,比如性别。注意可选值少还不够,还需要分布较均匀,比如有个字段只有 1,0 取值,1 有 一千万条,0 有十条,那么当这个字段建了索引,查询 0 的时候也会比较快。

索引从功能上看主要有四种:

  • 普通索引,没有任何约束,主要用于提高查询效率。

  • 唯一索引,在普通索引基础上增加了唯一性约束,一张表可以有多个。

  • 主键索引,在唯一索引的基础上增加了非空约束,一张表只能有一个。

  • 全文索引,用的不多,MySQL 自带的全文索引仅支持英文。

普通索引和唯一索引的效率问题。 原理上,唯一索引由于增加了唯一性约束,找到了关键字会停止检索。而普通索引还会往下继续检索,效率在理论上会第一点。 但是这点差别可以忽略不计,原因是,根据数据库存储方式,InnoDB 会把整个数据页加载到内存中,一个页中可能存在上千条记录,普通所以仅仅是在内存中多几次判断下一条记录,这点时间可以忽略不计。

从物理实现上有两种:

  • 聚集索引,按照主键的顺序存储数据,一张表只能一个,叶子节点存储了数据记录。

  • 非聚集索引,也叫二级索引,一张表可以有多个,叶子节点存储的是数据位置。

按照字段个数,可以分为单一索引联合索引,联合索引注意字段顺序,有最左匹配原则

常见索引数据结构

  • Hash 表:本质是数组+链表的数据结构。

    • 缺点:做区间查询速度很慢。

    • 适用场景:只有等值查询的情况。比如 Memcached 、Lucene 等。

  • 有序数组:等值查询和范围查询都很快。等值查询用用二分查找,时间复杂度O(log(N))。范围查询先用等值查询找到第一个,然后往后遍历。

    • 缺点:插入数据效率很低。

    • 适用场景:静态数据,比如 2015年上海市的人口信息。

  • LSM 树

MySQL 中的 Memory 存储引擎支持 Hash 存储;另外 MySQL 的 InnoDB 有一个自适应 Hash 索引,当某个索引使用非常频繁时,会在 B+ 树的基础上再创建一个 Hash 索引。不过 Hash 索引相对于 B+ 树有一些缺点:

  • 不支持范围查询。

  • 不支持联合索引的最左匹配原则,因为 hash 函数对于输入的一点微小变化都很敏感。

  • 不支持 ORDER BY 排序。

  • 不支持 LIKE 'XX%'。

最佳实践

什么时候创建索引

  • 字段数值有唯一限制,唯一索引和主键索引都能起到唯一性的约束。

  • 作为 WHERE 查询条件的字段,尤其表较大时,包括 SELECT、UPDATE、DELETE、JOIN。

  • 频繁作为 GROUP BY 和 ORDER BY 的字段。

  • DISTINCT 字段。

  • 多表 JOIN 时,用于连接的字段。不过,连接表尽量不要超过 3 张。

对于同时存在 GOURP BY 和 ORDER BY 的语句,如:GROUP BY a ORDER BY b

  • 若对 a,b 分别创建单列索引,则只有一个会生效。

  • 创建(b, a)联合索引没有(a, b)联合索引效率高,因为先进行 GROUP BY。

什么时候不创建索引

  • 非查询条件的字段,查询条件指的是 WHERE、GROUP BY、ORDER BY、DISTINCT。

  • 数据量较小。

  • 字段中有大量重复数据,且分布较均匀。

  • 频繁更新的字段,因为更新时也会更新索引。

什么时候索引失效

  • 索引进行了表达式计算:WHERE id + 1 = 9001 => WHERE id = 9000

  • 索引进行了函数计算:WHERE SUBSTRING(a, 1, 3) = 'abc' => WHERE a LIKE 'abc%'

  • OR 条件中有一个列没有索引,那么其他索引列也会失效。如 WHERE a = 1 OR b = 2,a 有索引,b 没有,那么 a 的索引会失效。

  • LIKE 不能以 `%` 开头。

  • 索引列与 NULL 或 NOT NULL 判断时。

MySQL 索引

自增主键

自增主键指插入记录时,若不指定主键值,则会获取当前主键值并加 1 作为下一条主键的值。通过 NOT NULL PRIMARY KEY AUTO_INCREMENT定义

优点:插入数据是递增的,主键索引不会发生页分裂。且整型占用空间少,二级索引占用的空间也少。

索引重建

索引可能因为删除、页分裂等情况,导致数据空洞,重建索引会把数据按照顺序插入,页面利用率就高。

若需要重建索引,下面方法是否可行?

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

重建索引 k 是合理的。但是重建主键索引不合理,删除、创建主键都会重建整个表,所以前面的重建 k 就白做了。可以使用alter table T engine=InnoDB替代。

索引覆盖

若查询需要的结果已经包含在二级索引里面,不需要回表,称为索引覆盖。是一个常用的性能优化手段。

最左匹配

最左匹配可以是联合索引的最左边的 N 个字段,也可以是字符串索引的最左 M 个字符。

联合索引的顺序考虑:

  • 如果能够通过调整顺序,减少一个索引,那么应该使用这个顺序。比如已经有 (a,b) 索引,就不需要单独的 a 索引了。

  • 空间原则,比如 name 和 age 两个字段,name 长度大于 age,那么应该创建 (name,age) 和 (age),而不是 (age,name) 和 (name)。

索引下推

MySQL 5.6 引入索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录。比如下面语句:

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

在 5.6 之前需要回表四次:

在 5.6 仅需回表两次:

Last updated