人的知识就好比一个圆圈，圆圈里面是已知的，圆圈外面是未知的。你知道得越多，圆圈也就越大，你不知道的也就越多。

0%

MySQL 索引

发表于 更新于 分类于

作用

索引其实就是为了提高数据查询的效率，就像书的目录一样。

常见模型

  • 哈希表
  • 有序数组
  • 二叉搜索树

特性：

  • 哈希表这种结构适用于只有等值查询的场景
  • 有序数组虽然在等值查询和范围查询场景中的性能就都非常优秀，但有序数组索引只适用于静态存储引擎
  • 由于索引不止存在内存中，还要写到磁盘上，因此若使用二叉搜索树，会因为频繁访问磁盘导致查询效率低下

因此，MySQL 是使用 B+ 树来实现索引的。

InnoDB 索引模型

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是：

1
2
3
4
5
6
create table T(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
) engine=InnoDB;

对应的索引组织结构如下：
InnoDB 索引组织结构示例

索引类型

普通索引

最基本的索引，它没有任何限制，用于加速查询。

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

由于基于非主键索引的查询需要多扫描一棵索引树（回表），因此我们在应用中应该尽量使用主键查询。

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

性能影响

  • insert 索引个数越多，对于 insert 操作来说，维护的成本就越大，插入一条数据的速度也就越慢。

  • delete delete 操作刚好和 insert 相反，当删除一条数据时，会把这条数据涉及到的多个索引中的数据删除。开销要比 insert 小。

  • update 这个操作不同于 insert、delete，只有当 update 的这个字段涉及到索引时，才需要维护索引，相对来说开销要小一些。

索引维护

页分裂

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。如果插入的数据所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

页合并

相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

主键的选择

如果使用自增主键，那么每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
另外，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

业务主键适用于以下场景：

  • 只有一个索引
  • 该索引必须是唯一索引

覆盖索引

  • 解释一：就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
  • 解释二：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
  • 解释三：是非聚集组合索引的一种形式，它包括在查询里的 Select、Join 和 Where 子句用到的所有列（即建立索引的字段正好是覆盖查询语句 select 子句 与查询条件 Where 子句中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。
注：如果第一个字段是范围查询需要单独建一个索引。
注：在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。
当创建 (a,b,c) 复合索引时，想要索引生效的话,只能使用 a 和 a,b 和 a,b,c 三种组合。

索引下推

索引条件下推（ICP）是对 MySQL 使用索引从表中检索行的情况的优化。如果没有 ICP，存储引擎会遍历索引以查找基表中的行，并将它们返回给 MySQL 服务器，该服务器会评估 WHERE 行的条件。启用 ICP 后，如果 WHERE 只使用索引中的列来评估部分条件，MySQL 服务器会推送这部分内容。WHERE 条件下到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且仅当满足该条件时才从表中读取行。ICP 可以减少存储引擎必须访问基表的次数以及 MySQL 服务器必须访问存储引擎的次数。

重建索引

对于普通索引，可以通过执行下面两行来重建索引：

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

但是对于主键索引，如果也是执行下面两行来重建索引：

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

则不够合理，这是因为不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。
可以改为执行alter table T engine=InnoDB。

字符串添加索引

  • 直接创建完整索引，这样可能比较占用空间
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
  • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描
小礼物走一走，来 Github 关注我