数据库索引如何使搜索更快-Howdodatabaseindicesmakesearchfaster

4  

Indexing isn't (much) about duplicates. It's about order.

索引不是(很多)关于重复。这是关于订单。

When you do a search, you want to have some kind of order that lets you (for example) do a binary search to find the data in logarithmic time instead of searching through every record to find the one(s) you care about (that's not the only type of index, but it's probably the most common).

当你进行搜索时,你希望有某种顺序让你(例如)进行二进制搜索,以对数时间查找数据,而不是搜索每条记录以找到你关心的那些(这是不是唯一的索引类型,但它可能是最常见的)。

Unfortunately, you can only arrange the records themselves in a single order.

不幸的是,您只能在一个订单中自行安排记录。

An index contains just the data (or a subset of it) that you're going to use to search on, and pointers (or some sort) to the records containing the actual data. This allows you to (for example) do searches based on as many different fields as you care about, and still be able to do binary searching on all of them, because each index is arranged in order by that field.

索引仅包含您要用于搜索的数据(或其子集),以及包含实际数据的记录的指针(或某种类型)。这允许您(例如)基于您关心的多个不同字段进行搜索,并且仍然能够对所有字段进行二进制搜索,因为每个索引按该字段按顺序排列。

4  

Because the index in the DB and in the given example is sorted alphabetically. The raw table / book is not. Then think: How do you search an index knowing it is sorted? I guess you don't start reading at "A" up to the point of your interest. Instead you skip roughly to the POI and start searching from there. Basically a DB can to the same with an index.

因为DB和给定示例中的索引按字母顺序排序。原始表/书不是。然后想一想:你如何搜索已知排序的索引?我想你不会开始阅读“A”,直到你感兴趣的程度。相反,你大致跳过POI并从那里开始搜索。基本上DB可以与索引相同。

1  

It is faster because the index contains only values from the column in question, so it is spread across a smaller number of pages than the full table. Also, indexes usually include additional optimizations such as hash tables to limit the number of reads required.

它更快,因为索引仅包含来自相关列的值,因此它分布在比完整表少的页面上。此外,索引通常还包括其他优化,例如哈希表,以限制所需的读取次数。