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

为什么需要?

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

由于许多记录只能在一个字段上排序,我们可以声明,在未排序的字段上搜索需要线性搜索,这需要 N/2 阻塞访问(平均),其中 N 是表所跨越的块数。如果该字段是非键字段(即不包含唯一条目),则必须在以下位置搜索整个表空间 n 块访问。

而对于排序字段,可以使用二进制搜索,它具有 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万行,未编制索引。现在将分析几个查询的性能。这是一个使用 身份证件 (一个已排序的关键字字段)和一个使用 第一名字 (非键的未排序字段)。

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

我们的样本数据库 r = 5,000,000 固定大小的记录,记录长度为 R = 204 字节,它们使用使用默认块大小的myisam引擎存储在表中。 B = 1,024 字节。表的阻塞因子为 bfr = (B/R) = 1024/204 = 5 每个磁盘块的记录数。保存表所需的块总数为 N = (r/bfr) = 5000000/5 = 1,000,000 阻碍。

在ID字段上进行线性搜索需要平均 N/2 = 500,000 如果ID字段是键字段,则块访问以查找值。但由于ID字段也被排序,因此可以执行二进制搜索,平均需要 log2 1000000 = 19.93 = 20 阻止访问。我们一眼就能看出这是一个巨大的进步。

现在 第一名字 字段既不是排序字段,也不是键字段,因此不可能进行二进制搜索,而且值也不是唯一的,因此表将需要搜索到末尾以获得精确的 N = 1,000,000 阻止访问。正是这种情况下,索引的目的是纠正。

由于索引记录只包含索引字段和指向原始记录的指针,因此它将小于它指向的多字段记录。因此,索引本身所需的磁盘块比原始表少,因此迭代所需的块访问也更少。上的索引的架构 第一名字 字段概述如下:

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 = 277,778 阻碍。

现在使用 第一名字 字段可以利用索引来提高性能。这允许对索引进行二进制搜索,平均值为 log2 277778 = 18.08 = 19 阻止访问。找到实际记录的地址,这需要进一步的块访问来读取,使总数达到 19 + 1 = 20 块访问,与查找 第一名字 在非索引表中匹配。

什么时候使用?

考虑到创建索引需要额外的磁盘空间(上面例子中的27778块是额外的,增加了约28%),并且太多的索引可能导致文件系统大小限制问题,必须仔细考虑选择要索引的正确字段。

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

我第一次读这本书对我很有帮助。谢谢您。

从那时起,我对创建索引的缺点有了一些了解: 如果你在桌子上写字( UPDATE 或 INSERT )对于一个索引,您实际上在文件系统中有两个写入操作。一个用于表数据,另一个用于索引数据(以及它的重定值(如果是聚集的,则是表数据的重定值))。如果表和索引位于同一硬盘上,则会花费更多的时间。因此,没有索引(堆)的表将允许更快的写操作。(如果您有两个索引,那么最后将执行三个写操作,依此类推)

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

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

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

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

此外,集群索引和非集群索引之间的区别也相当重要。

帮助我: What do Clustered and Non clustered index actually mean?

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

有关更多信息,我建议: How do database indexes work? And, how do indexes help?

经典实例 “书籍索引”

考虑一本1000页的“书”,除以100节,每节有x页。

简单,嗯?

现在,如果没有索引页,要查找以字母“s”开头的特定部分,除了扫描整本书之外,没有其他选择。即:1000页

但是在开始有一个索引页,你就在那里。更重要的是,要阅读任何重要的特定部分,您只需要一次又一次地查看索引页。找到匹配的索引后,您可以通过跳过其他部分有效地跳转到该部分。

但是,除了1000页之外,您还需要另外10页来显示索引页,所以总共需要1010页。

因此,索引是一个单独的部分,它按排序顺序存储索引列的值+指向索引行的指针,以便进行有效的查找。

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

现在,假设我们要运行一个查询来查找任何名为abc的雇员的所有详细信息?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

没有索引会发生什么?

数据库软件实际上必须查看Employee表中的每一行,以查看该行的雇员名称是否为abc。而且,因为我们需要每一行中都有名称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=abc的select*from employee这样的查询时,数据库将检查查询的列是否有索引。假设Employee_name列上确实创建了索引,那么数据库将不得不决定使用索引查找要搜索的值是否有实际意义,因为在某些情况下,使用数据库索引实际上效率较低,而只扫描整个表时效率更高。

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

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

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

也见

1. What columns generally make good indexes?
2. How do database indexes work

简单描述!!!!!!!!!!!!!!

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

例如,我们有一个名为user的数据库表,它有三列:name、age和address。假设用户表有数千行。

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

SELECT * FROM User 
WHERE Name = 'John'

数据库软件实际上必须查看用户表中的每一行,以查看该行的名称是否为john。这需要很长时间。
这就是index帮助我们“index用于加速搜索查询的地方,它实质上减少了表中需要检查的记录/行的数量”。
如何创建索引

CREATE INDEX name_index
ON User (Name)

索引由一个表中的列值(如john)组成,这些值存储在数据结构中。
因此,现在数据库将使用索引查找名为john的员工,因为该索引可能按用户名的字母顺序排序。而且,因为它是排序的,这意味着搜索一个名称要快得多,因为所有以j开头的名称都将在索引中彼此相邻!

只是个简短的建议……由于索引需要额外的写入和存储空间,因此,如果应用程序需要更多的插入/更新操作,您可能希望使用不带索引的表,但如果它需要更多的数据检索操作,则应该使用索引表。

只需把数据库索引看作是一本书的索引。 如果你有一本关于狗的书,你想找到一个关于德国牧羊人的信息,你当然可以翻遍书的所有页面,找到你要找的东西,但这当然是费时的,而且不是很快。另一种选择是,您可以直接进入本书的索引部分,然后使用正在查找的实体的名称(在本例中,是德国牧羊人)查找要查找的内容,并查看页码以快速查找要查找的内容。在数据库中,页码被称为指针,它将数据库指向实体所在磁盘上的地址。使用相同的德语shepherd类比,我们可以得到类似的结果(德语shepherd,0x77129),其中0x77129是存储德语shepherd行数据的磁盘上的地址。

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