数据库索引 - 索引简介

数据库索引 - 索引简介

本节很多内容来自 pg数据库的官方文档

索引简介

索引是什么

索引是一种常用提高数据库性能的方式. 索引能够帮助数据库快速地找到特定的数据行;
但是索引也会增加整个数据库系统的 开销 (overhead)，所以应该明智地使用它们。

为什么要有数据库索引

答: 提高查询效率

我们在数据准备中, 创建了 product 表;

SELECT * FROM product WHERE id = 12311

如果没有主键的索引, 数据库不得不扫描整个 product 表, 一行一行地找;

就如上面的sql, id = 12311 只有一条;
但是, 数据库仍然要扫描整个表; 这显然是一种低效的查询方式;

但是, id 上有索引, 它可以使用更为高效的方式去定位到 id = 12311 的数据行;
例如, 它可以仅仅在索引的搜索树上进行几次查询, 就能够得到数据;

索引创建之后, 不需要有更多的操作了; 当表有变化时, 数据库会更新索引;
并且, 数据库在它认为使用索引比扫描全表更高效时, 会选择使用索引;

索引同时也可以加快 UPDATE 和 DELETE 命令.
索引此外还使用在 join 查询中. 如果 join 的条件中有索引, 那么会显著提升查询效率;

但是, 索引同时也是一种负担. 对于不常使用的索引, 应该删掉;

什么情况下不适合加索引

答:
尽管索引可以提高数据库的查询效率;
但是, 索引也会降低数据库的性能.

下面的情况下, 应该避免使用索引;

• 不应该在小表上加索引
• 数据重复且分布平均的字段 (例如: 性别)
• 频繁修改的表或者列
• 索引不应该用于很多 null 的列.

索引的需求定义

以下内容大部分来源于极客时间 数据结构与算法之美-索引 一节;

功能性需求对于功能性需求需要考虑的点，我把它们大致概括成下面这几点。

数据是格式化数据还是非格式化数据？

要构建索引的原始数据，类型有很多。总体上可以分为两类;

• 一类是结构化数据，比如，MySQL 中的数据；
• 另一类是非结构化数据，比如搜索引擎中网页。

对于非结构化数据，我们一般需要做预处理，提取出查询关键词，对关键词构建索引。

数据是静态数据还是动态数据？

如果原始数据是一组静态数据，也就是说，不会有数据的增加、删除、更新操作，
所以，我们在构建索引的时候，只需要考虑查询效率就可以了。这样，索引的构建就相对简单些。

不过，大部分情况下，我们都是对动态数据构建索引，
也就是说，我们不仅要考虑到索引的查询效率，在原始数据更新的同时，我们还需要动态地更新索引。
支持动态数据集合的索引，设计起来相对也要更加复杂些。

索引存储在内存还是硬盘？

如果索引存储在内存中，那查询的速度肯定要比存储在磁盘中的高。
但是，如果原始数据量很大的情况下，对应的索引可能也会很大。
这个时候，因为内存有限，我们可能就不得不将索引存储在磁盘中了。

实际上，还有第三种情况，那就是一部分存储在内存，一部分存储在磁盘，这样就可以兼顾内存消耗和查询效率。

如果, 我设计索引, 我想在B-tree的前两层, 甚至所有的非叶子节点应该都可以放到内存中;

单值查找还是区间查找？

所谓单值查找，也就是根据查询关键词等于某个值的数据 (等值查询)。
这种查询需求最常见。

所谓区间查找，就是查找关键词处于某个区间值的所有数据。
例如: id > 100 and id < 200

单关键词查找还是多关键词组合查找？

比如，搜索引擎中构建的索引，既要支持一个关键词的查找，比如“数据结构”，
也要支持组合关键词查找，比如“数据结构 AND 算法”。

对于单关键词的查找，索引构建起来相对简单些。
对于多关键词查询来说，要分多种情况。像 MySQL 这种结构化数据的查询需求，我们可以实现针对多个关键词的组合，建立索引；
(例如: 创建联合索引)

对于像搜索引擎这样的非结构数据的查询需求，我们可以针对单个关键词构建索引，然后通过集合操作，比如求并集、求交集等，
计算出多个关键词组合的查询结果。

---

不管是存储在内存中还是磁盘中，索引对存储空间的消耗不能过大。

如果存储在内存中，索引对占用存储空间的限制就会非常苛刻。

毕竟内存空间非常有限，一个中间件启动后就占用几个 GB 的内存，开发者显然是无法接受的。

如果存储在硬盘中，那索引对占用存储空间的限制，稍微会放宽一些。
但是，我们也不能掉以轻心。因为，有时候，索引对存储空间的消耗会超过原始数据。
在考虑索引查询效率的同时，我们还要考虑索引的维护成本。

索引的目的是提高查询效率，但是，基于动态数据集合构建的索引，我们还要考虑到，索引的维护成本。
因为在原始数据动态增删改的同时，我们也需要动态地更新索引。而索引的更新势必会影响到增删改操作的性能。

构建索引的常用数据结构有哪些

我们先想一下常用的动态数据结构:

• 链表
  • 链表不太适合做为索引;
• 散列表 hashmap
  • 散列表的增删改查的时间复杂度是O(1), 可以做为索引;
  • 散列表适合一些等值查询;
  • 但是, 不适合做区间查询
• 红黑树
  • 红黑树是平衡二叉树, 查找新增删除的时间复杂度是 O(log n)
  • 可以做为索引
• B-tree
  • B-tree 现在广泛使用于各大数据库的索引
  • B-tree 因为分叉很多, 树的高度较低; 需要的I/O次数少, 适合存放到硬盘中;
  • B-tree 的叶子节点
• 跳表 skip list
  • 支持快速添加、删除、查找数据。而且，我们通过灵活调整索引结点个数和数据个数之间的比例，可以很好地平衡索引对内存的消耗及其查询效率
  • Redis 中的有序集合，就是用跳表来构建的

推荐阅读

What is a “Bitmap heap scan” in a query plan?

数据结构与算法之美-索引

不深入而浅出 Roaring Bitmaps 的基本原理