【索引】反向索引

简介:
1,Creating data
Reversed key indexes use b-tree structures, but preprocess key values before inserting them. Simplifying, b-trees place similar values on a single index block, e.g., storing 24538 on the same block as 24539. This makes them efficient both for looking up a specific value and for finding values within a range. However if the application inserts values in sequence, each insert must have access to the newest block in the index in order to add the new value. If many users attempt to insert at the same time, they all must write to that block and have to get in line, slowing down the application. This is particularly a problem in clustered databases, which may require the block to be copied from one computer's memory to another's to allow the next user to perform. their insert.

Reversing the key spreads similar new values across the entire index instead of concentrating them in any one leaf block. This means that 24538 appears on the same block as 14538 while 24539 goes to a different block, eliminating this cause of contention. (Since 14538 would have been created long before 24538, their inserts don't interfere with each other.)


2,Querying data
Reverse indexes are just as effective as for finding specific values, although they aren't helpful for range queries, which turn out to be uncommon for artificial values such as sequence numbers. When searching the index, the query processor simply reverses the search target before looking it up.

3,Deleting data
Another benefit impacts applications that delete data. Typically, applications delete data that is older on average (with lower values of the sequence) before deleting newer data. In standard b-trees, many index blocks end up containing few values, with a commensurate increase in unused space, referred to as "rot". Rot not only wastes space, but slows query speeds, because a smaller fraction of a rotten index's blocks fit in memory at any one time. In a b-tree, if 14538 gets deleted, its index space remains empty. In a reverse index, if 14538 goes before 24538 arrives, 24538 can reuse 14538's space.
相关文章
|
4月前
|
存储 关系型数据库 MySQL
了解和认识索引
了解和认识索引 。
30 0
|
4月前
|
存储 SQL 关系型数据库
索引
索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构。 索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。
16 0
|
4月前
|
关系型数据库 MySQL 数据库
了解和认识索引
了解和认识索引。
19 0
|
4月前
|
关系型数据库 MySQL 索引
索引(2)
索引(2)。
12 0
|
8月前
|
数据库 索引
请注意这些情况下,你的索引会不生效!
数据库性能优化是确保系统高效运行的关键要素之一。而索引作为提升数据库查询性能的重要工具,在大部分情况下都能发挥显著的作用。然而,在某些情况下,索引可能会失效或不起作用,导致查询性能下降,甚至引发性能瓶颈。
|
12月前
|
存储 SQL 关系型数据库
【名词解释与区分】聚集索引、非聚集索引、主键索引、唯一索引、普通索引、前缀索引、单列索引、组合索引、全文索引、覆盖索引
【名词解释与区分】聚集索引、非聚集索引、主键索引、唯一索引、普通索引、前缀索引、单列索引、组合索引、全文索引、覆盖索引
192 1
【名词解释与区分】聚集索引、非聚集索引、主键索引、唯一索引、普通索引、前缀索引、单列索引、组合索引、全文索引、覆盖索引
|
索引
反向表
反向表
85 0
|
存储 缓存 自然语言处理
正排索引
介绍ElasticSearch相关正排索引
|
存储 关系型数据库 MySQL
索引是什么
索引是什么
184 0