什么是数据库索引？

SQL 查询的工作原理

SQL 查询用于从数据库中检索数据。我们使用 SELECT 语句来检索数据，通常还会使用 WHERE 子句来筛选数据。
让我们来看看 SELECT 语句是如何工作的。

首先，我们来定义一下表。我们有一个posts表，其结构如下：

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    title TEXT,
    body TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    FOREIGN KEY (user_id) REFERENCES users(id)
);

假设我们要检索所有帖子。我们可以使用 SELECT 语句来实现这一点。

SELECT * FROM posts;

这将返回表中所有帖子posts。

让我们更进一步。我们想要筛选出标题包含“Java”一词的所有帖子（因为那是我最喜欢的语言）。

SELECT * FROM posts WHERE title = "Java";

这将返回标题为“Java”的所有帖子。该WHERE子句用于筛选数据。

目前一切都很方便。查询可能很复杂，但这应该足以理解幕后发生的事情。

我们来分析一下查询的工作原理。系统在后台做了什么？

• 第一步是找到我们要查询的表。所以它会读取 FROM 子句。现在它知道需要查询该posts表了。
• 然后，它会读取 WHERE 子句并找到title其中指定的列。现在它知道需要根据该title列筛选结果。如果 WHERE 子句中包含多个列，它将根据所有列筛选结果。
• 下一步是遍历title每一行的每一列，找到包含单词“Java”的行。
• 最后，它将返回所有包含单词“Java”的行。

假设这张表是某个大型数据库的一部分。由于它需要遍历整个数据库，因此运行时间会很长。
这时就需要用到索引的概念了。

如果按标题列的字母顺序对行进行排序，
查询数据库的速度会不会更快？

我们可以通过在标题列上创建索引来实现这一点。

我们来看看如何为该列创建索引title。

CREATE INDEX posts_title_index ON posts (title);

这将在该列上创建索引title。
该索引本质上是一个查找表，它由两部分组成：

1. 键——它是我们正在建立索引的列的值——在本例中title。
2. 一个值——它是指向包含整个数据的行的指针。post

注意：也可以将多个列一起建立索引。例如，如果我们想查找所有包含“Java”和“Python”这两个词的帖子，我们可以将“Java”title列和“Python”body列一起建立索引。
示例：

CREATE INDEX posts_title_body_index ON posts (title, body);

这里的索引将有两个键。第一个键是title列名，第二个键也是body列名。当两个键都匹配时，索引将指向该行。

因此，如果我们再次运行查询，它将在索引中查找键并返回行 ID。然后可以使用行 ID 返回整行数据。

这样如何加快了查找速度？

• 查询优化器会检查 WHERE 子句，看看能否在该列上找到索引。
• 如果可以，它将使用索引来查找行。
• 如果无法执行，则会正常运行查询。

在我们的例子中，查询优化器会找到列上的索引，title并使用该索引查找行。速度取决于索引的实现方式。
例如，

• 如果索引实现为哈希表（对于可以进行一致性哈希的值可以这样做），那么查询速度将非常快——O(1)
• 如果索引实现为二叉搜索树（例如，对于可排序的值），则查询速度会略微降低，但仍然比不使用索引的线性时间复杂度查询要快。- O(log n)

实现索引的方法有很多种，每种方法都有其自身的优缺点。欲了解更多信息，请参阅：https: //en.wikipedia.org/wiki/Index_(database)

那为什么不为每一列创建一个索引呢？

虽然可以为每一列创建索引，但这并非必要。这样做会降低性能。等等，为什么？

我们看到索引可以加快查询速度，但是写入数据库的速度又如何呢？

• 如果要写入数据库，也必须同时写入索引。索引需要实时维护。
• 因此，每次向表中添加新行时，都需要更新该列上的所有索引。索引越多，写入时间就越长。

自动索引

为了优化数据库性能，数据库引擎会自动为某些列创建索引。

1. 主键列——用于唯一标识一行数据的列。它会自动建立索引。
2. 具有唯一约束的列——这些列会自动建立索引。可以有多个列具有唯一约束。免责声明：虽然大多数数据库实现都遵循上述两条规则，但每个具体的数据库实现可能具有不同的索引规则。

如何决定要对哪些列建立索引？

影响索引速度的因素有很多，选择最有可能被查询的列非常重要。

决策标准：

1. 如果某一列可能会被频繁查询，则应该为其建立索引——例如，查找某个用户的所有帖子就是一个常见的查询。
2. 只有当多个列可能一起被查询时，才应该对它们建立索引——例如，查找所有包含单词“Java”和单词“Python”的帖子并不是一个常见的查询。
3. 不要为经常更改的列建立索引——这会导致很多不必要的索引更新。
4. 用于连接表的列非常适合建立索引，因为它们很可能会在查询中使用。

如您所见，决策标准并非总是显而易见，但总体思路是选择最有可能被查询的列。考虑到这一点，我们应该同时制定查询策略和索引策略。尽可能减少查询中包含的列数，并考虑为这些列创建独立索引的可能性。

感谢阅读。本文应该能让您对 SQL 数据库中的索引以及它们如何对 SQL 性能产生重要影响有所了解。如果您想与我联系，可以在 Twitter 上找到我：@abh1navv