数据库索引如何工作？[关闭]

为什么需要它？

当数据存储在基于磁盘的存储设备上时，它将存储为数据块。这些块被完整地访问，使它们成为原子磁盘访问操作。磁盘块的结构与链表大致相同;两者都包含一个数据部分，一个指向下一个节点（或块）位置的指针，并且两者都不需要连续存储。

由于一个字段只能对许多记录进行排序，因此我们可以说，在未排序的字段上进行搜索需要线性搜索，这需要块访问（平均），其中是表跨越的块数。如果该字段是非键字段（即不包含唯一条目），则必须在块访问时搜索整个表空间。(N+1)/2NN

而对于排序字段，可以使用具有块访问的二叉搜索。此外，由于数据是在给定非键字段的情况下排序的，因此一旦找到更高的值，就不需要搜索表的其余部分以查找重复值。因此，性能的提高是可观的。log2 N

什么是索引？

索引是一种对多个字段上的多条记录进行排序的方法。在表中的字段上创建索引会创建另一个数据结构，该结构包含字段值，以及指向与其相关的记录的指针。然后对该索引结构进行排序，允许对其执行二进制搜索。

索引的缺点是，这些索引需要额外的磁盘空间，因为索引是使用 MyISAM 引擎一起存储在表中的，如果对同一表中的许多字段进行索引，则此文件可以很快达到底层文件系统的大小限制。

它是如何工作的？

首先，让我们概述一个示例数据库表架构;

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

注意：使用 char 代替 varchar 以允许准确的磁盘大小值。 此示例数据库包含 500 万行，并且未编制索引。现在将分析几个查询的性能。这些是使用 id（排序键字段）的查询和使用 firstName（非键未排序字段）的查询。

示例 1 - 已排序字段与未排序字段

鉴于我们的固定大小的记录示例数据库，给出了字节的记录长度，并且它们使用 MyISAM 引擎存储在表中，该引擎使用默认块大小字节。表的阻塞因子是每个磁盘块的记录数。容纳表所需的块总数为块。r = 5,000,000R = 204B = 1,024bfr = (B/R) = 1024/204 = 5N = (r/bfr) = 5000000/5 = 1,000,000

如果 id 字段是键字段，则对 id 字段进行线性搜索需要平均块访问才能找到值。但是，由于 id 字段也进行了排序，因此可以进行二进制搜索，需要平均块访问。我们一眼就看出这是一个巨大的改进。N/2 = 500,000log2 1000000 = 19.93 = 20

现在，firstName 字段既不是排序字段，也不是键字段，因此不可能进行二进制搜索，值也不是唯一的，因此该表将需要搜索到末尾才能找到确切的块访问。索引旨在纠正这种情况。N = 1,000,000

假设索引记录仅包含索引字段和指向原始记录的指针，则按理说它将小于它所指向的多字段记录。因此，索引本身需要的磁盘块比原始表少，因此需要更少的块访问来循环访问。firstName 字段上的索引架构概述如下;

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

注意：MySQL 中的指针长度为 2、3、4 或 5 字节，具体取决于表的大小。

示例 2 - 索引

给定我们的记录示例数据库，索引记录长度为字节，并使用默认块大小字节。索引的阻塞因子是每个磁盘块的记录数。持有索引所需的块总数是块。r = 5,000,000R = 54B = 1,024bfr = (B/R) = 1024/54 = 18N = (r/bfr) = 5000000/18 = 277,778

现在，使用 firstName 字段的搜索可以利用索引来提高性能。这允许对索引进行二进制搜索，并平均块访问。要查找实际记录的地址，这需要进一步的块访问才能读取，从而使块访问总数达到块访问，这与在非索引表中查找 firstName 匹配项所需的 1,000,000 次块访问相去甚远。log2 277778 = 18.08 = 1919 + 1 = 20

什么时候应该使用？

鉴于创建索引需要额外的磁盘空间（从上面的示例中增加了 277,778 个块，增加了 ~28%），并且过多的索引可能会导致文件系统大小限制引起的问题，因此必须仔细考虑以选择要索引的正确字段。

由于索引仅用于加快在记录中搜索匹配字段的速度，因此，在执行插入或删除操作时，仅用于输出的索引字段只会浪费磁盘空间和处理时间，因此应避免使用。此外，考虑到二进制搜索的性质，数据的基数或唯一性也很重要。对基数为 2 的字段进行索引会将数据分成两半，而基数为 1,000 的字段将返回大约 1,000 条记录。在如此低的基数下，有效性降低到线性排序，如果基数小于记录数的 30%，查询优化器将避免使用索引，从而有效地使索引浪费空间。

我第一次读到这篇文章时，它对我非常有帮助。谢谢。

从那时起，我对创建索引的缺点有了一定的了解： 如果写入具有一个索引的表 （ 或 ），则文件系统中实际上有两个写入操作。一个用于表数据，另一个用于索引数据（以及索引数据的排序（以及 - 如果聚类 - 表数据的排序））。如果表和索引位于同一硬盘上，则会花费更多时间。因此，没有索引（堆）的表将允许更快的写入操作。（如果你有两个索引，你最终会得到三个写入操作，依此类推）UPDATEINSERT

但是，在两个不同的硬盘上为索引数据和表数据定义两个不同的位置可以减少/消除时间成本增加的问题。这需要在所需硬盘上定义具有相应文件的附加文件组，并根据需要定义表/索引位置。

索引的另一个问题是，在插入数据时，索引会随着时间的推移而碎片化。 有帮助，您必须编写例程才能完成它。REORGANIZE

在某些情况下，堆比带有索引的表更有用，

例如：- 如果您有很多竞争性的文章，但每晚只在工作时间以外阅读一次用于报告。

此外，区分聚簇索引和非聚簇索引也相当重要。

帮助了我：- 聚簇和非聚簇索引实际上是什么意思？

索引只是一种数据结构，可以更快地搜索数据库中的特定列。此结构通常是 b 树或哈希表，但它可以是任何其他逻辑结构。

简单的描述！

索引只不过是一种数据结构，用于存储表中特定列的值。索引是在表的列上创建的。

示例：我们有一个数据库表，它有三列 – 和 。假设该表有数千行。UserNameAgeAddressUser

现在，假设我们想要运行一个查询来查找名为“John”的任何用户的所有详细信息。 如果我们运行以下查询：

SELECT * FROM User 
WHERE Name = 'John'

从字面上看，数据库软件必须查看表中的每一行，以确定该行是否为“John”。这将需要很长时间。UserName

这就是帮助我们的地方：索引用于通过减少表中需要检查的记录/行数来加快搜索查询速度。index

如何创建索引：

CREATE INDEX name_index
ON User (Name)

An 由一个表中的列值（例如：John）组成，这些值存储在数据结构中。index

因此，现在数据库将使用索引来查找名为 John 的员工 因为索引大概会按字母顺序排序 用户名。而且，因为它是排序的，所以这意味着搜索一个名字 速度要快得多，因为所有以“J”开头的名称都是正确的 在索引中彼此相邻！

现在，假设我们想要运行一个查询来查找任何名为“Abc”的员工的所有详细信息？

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

如果没有索引会发生什么？

从字面上看，数据库软件必须查看 Employee 表中的每一行，以查看该行的Employee_Name是否为“Abc”。而且，由于我们希望每一行都带有“Abc”的名称，因此一旦我们找到一行名称为“Abc”，我们就不能停止查找，因为可能还有其他名称为 Abc 的行。因此，必须搜索直到最后一行的每一行，这意味着在这种情况下，数据库必须检查数千行，以找到名称为“Abc”的行。这就是所谓的全表扫描

数据库索引如何提高性能

拥有索引的全部意义在于通过减少表中需要检查的记录/行数来加快搜索查询速度。索引是一种数据结构（最常见的是 B 树），用于存储表中特定列的值。

B树指数如何运作？

B-树之所以是索引最流行的数据结构，是因为它们具有时间效率，因为查找、删除和插入都可以在对数时间内完成。而且，B-树更常用的另一个主要原因是，存储在B-树中的数据可以被排序。RDBMS 通常确定索引实际使用的数据结构。但是，在某些使用某些 RDBMS 的场景中，您实际上可以在创建索引本身时指定希望数据库使用的数据结构。

哈希表索引如何工作？

使用哈希索引的原因是，哈希表在查找值时非常有效。因此，如果查询使用哈希索引，则与字符串进行比较的相等性查询可以非常快速地检索值。

例如，我们之前讨论的查询可以从在Employee_Name列上创建的哈希索引中受益。哈希索引的工作方式是，列值将是哈希表中的键，映射到该键的实际值只是指向表中行数据的指针。由于哈希表基本上是一个关联数组，因此典型的条目类似于“Abc => 0x28939”，其中 0x28939 是对内存中存储 Abc 的表行的引用。在哈希表索引中查找像“Abc”这样的值并取回对内存中该行的引用显然比扫描表以查找Employee_Name列中值为“Abc”的所有行要快得多。

哈希索引的缺点

哈希表不是排序的数据结构，并且有许多类型的查询，哈希索引甚至无法提供帮助。例如，假设您想找出所有年龄小于 40 岁的员工。你怎么能用哈希表索引做到这一点？嗯，这是不可能的，因为哈希表只适用于查找键值对——这意味着检查是否相等的查询

数据库索引中到底有什么？因此，现在您知道数据库索引是在表中的列上创建的，并且索引将值存储在该特定列中。但是，重要的是要了解数据库索引不会将值存储在同一个表的其他列中。例如，如果我们在 Employee_Name 列上创建一个索引，这意味着 Employee_Age 和 Employee_Address 列值也不会存储在索引中。如果我们只将所有其他列存储在索引中，那么它就像创建整个表的另一个副本一样——这将占用太多空间并且效率非常低下。

数据库如何知道何时使用索引？当运行类似“SELECT * FROM Employee WHERE Employee_Name = 'Abc'”的查询时，数据库将检查所查询的列是否有索引。假设Employee_Name列确实创建了一个索引，数据库将不得不决定使用索引来查找正在搜索的值是否真正有意义 - 因为在某些情况下，使用数据库索引实际上效率较低，而仅扫描整个表的效率更高。

拥有数据库索引的成本是多少？

它占用空间 - 表越大，索引就越大。索引的另一个性能影响是，每当在相应表中添加、删除或更新行时，都必须对索引执行相同的操作。请记住，索引需要包含与索引所涵盖的表列中的任何内容相同的最新数据。

作为一般规则，只有在频繁查询索引列中的数据时，才应在表上创建索引。

另请参阅

1. 哪些列通常是好的索引？
2. 数据库索引的工作原理

只需将数据库索引视为一本书的索引即可。

如果你有一本关于狗的书，你想找到关于德国牧羊犬的信息，你当然可以翻阅这本书的所有页面，找到你要找的东西——但这当然很耗时，而且不是很快。

另一种选择是，您可以转到本书的“索引”部分，然后使用您正在查找的实体的名称（在本例中为德国牧羊犬）并查看页码以快速找到您要查找的内容。

在数据库中，页码称为指针，用于将数据库定向到实体所在的磁盘上的地址。使用相同的德国牧羊犬类比，我们可以有这样的东西（“德国牧羊犬”，0x77129），其中是磁盘上存储德国牧羊犬行数据的地址。0x77129

简而言之，索引是一种数据结构，用于存储表中特定列的值，以加快查询搜索速度。

经典例子“书籍索引”

考虑一本 1000 页的“书”，分为 10 章，每节有 100 页。

很简单，对吧？

现在，想象一下你想找到一个包含“炼金术士”这个词的特定章节。如果没有索引页，您别无选择，只能浏览整本书/章节。即：1000 页。

这种类比在数据库世界中被称为“全表扫描”。

但是有了索引页，您就知道该去哪里了！此外，要查找任何重要的特定章节，您只需要一次又一次地查看索引页。找到匹配的索引后，您可以通过跳过其余部分来有效地跳转到该章节。

但是，除了实际的 1000 页之外，您还需要另外 ~10 页来显示索引，因此总共有 1010 页。

因此，索引是一个单独的部分，用于存储索引值 列 + 指针按排序顺序指向索引行，以便高效 查找。

学校里的事情很简单，不是吗？:P