经典示例“图书索引”

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

简单，嗯？

现在，假设你想找到一个包含“炼金术士”一词的特定章节。如果没有索引页，除了扫描整本书/章节，你别无选择。即：1000页。

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

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

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

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

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

简单描述！

索引只是一个数据结构，它存储表中特定列的值。在表的列上创建索引。

示例：我们有一个名为User的数据库表，其中有三列：Name、Age和Address。假设User表有数千行。

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

SELECT * FROM User 
WHERE Name = 'John'

数据库软件实际上必须查看User表中的每一行，以查看该行的名称是否为“John”。这需要很长时间。

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

如何创建索引：

CREATE INDEX name_index
ON User (Name)

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

所以现在数据库将使用索引查找名为John的员工因为索引将按用户名。而且，因为它是排序的，所以它意味着搜索名称因为所有以“J”开头的名字都是正确的在索引中彼此相邻！

为什么需要它？

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

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

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

什么是索引？

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

索引的缺点是，这些索引需要额外的磁盘空间，因为索引使用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-排序字段与未排序字段

给定我们的示例数据库，r＝5000000条固定大小的记录，记录长度为r＝204字节，它们使用MyISAM引擎存储在表中，该引擎使用默认的块大小B＝1024字节。表的阻塞因子为bfr=（B/R）=1024/204=每个磁盘块5条记录。保存表格所需的块总数为N=（r/bfr）=5000000/5=1000000个块。

如果id字段是关键字字段，则对id字段进行线性搜索将需要平均N/2=500000次块访问才能找到值。但是，由于id字段也被排序，因此可以进行二进制搜索，平均需要log2 1000000＝19.93＝20个块访问。我们马上就能看到这是一个巨大的进步。

现在firstName字段既不是排序字段，也不是关键字字段，因此二进制搜索是不可能的，值也不是唯一的，因此表将需要搜索到底，以获得精确的N=1000000个块访问。索引旨在纠正这种情况。

由于索引记录只包含索引字段和指向原始记录的指针，因此它将比它所指向的多字段记录小。因此，索引本身需要的磁盘块比原始表少，因此需要更少的块访问来进行迭代。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=5000000条记录，索引记录长度r=54字节，使用默认块大小B=1024字节。索引的阻塞因子为bfr=（B/R）=1024/54=18条记录/磁盘块。保持索引所需的块总数为N=（r/bfr）=5000000/18=277778个块。

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

何时使用？

考虑到创建索引需要额外的磁盘空间（与上面的示例相比，增加了277778个块，增加了约28%），而且索引过多会导致文件系统大小限制引起的问题，因此必须仔细考虑选择要索引的正确字段。

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

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

从那以后，我对创建索引的缺点有了一些见解：如果用一个索引写入表（UPDATE或INSERT），那么文件系统中实际上有两个写入操作。一个用于表数据，另一个用于索引数据（以及它的重新排序（如果是集群的话，还包括表数据的重新排序））。如果表和索引位于同一硬盘上，这将花费更多的时间。因此，没有索引的表（堆）将允许更快的写入操作。（如果您有两个索引，那么最终将有三个写操作，依此类推）

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

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

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

例如：-如果你有很多竞争性的写作，但只有一个晚上在工作时间外阅读报告。

此外，区分聚集索引和非聚集索引非常重要。

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

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

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

另一种选择是，你可以直接进入书中的索引部分，然后通过使用你要查找的实体的名称（在本例中为德国牧羊犬）并查看页码来快速查找你要查找什么。

在数据库中，页码被称为指针，它将数据库指向实体所在磁盘上的地址。使用同样的德国牧羊犬类比，我们可以得到类似的结果（“德国牧羊犬”，0x77129），其中0x77129是存储德国牧羊犬行数据的磁盘上的地址。

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

经典示例“图书索引”

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

简单，嗯？

现在，假设你想找到一个包含“炼金术士”一词的特定章节。如果没有索引页，除了扫描整本书/章节，你别无选择。即：1000页。

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

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

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

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

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