 | This articleneeds additional citations forverification. Please helpimprove this article byadding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Database index" – news ·newspapers ·books ·scholar ·JSTOR(May 2024) (Learn how and when to remove this message) |
Adatabase index is adata structure that improves the speed of data retrieval operations on adatabase table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time said table is accessed. Indexes can be created using one or morecolumns of a database table, providing the basis for both rapid randomlookups and efficient access of ordered records.
An index is a copy of selected columns of data, from a table, that is designed to enable very efficient search. An index normally includes a "key" or direct link to the original row of data from which it was copied, to allow the complete row to be retrieved efficiently. Some databases extend the power of indexing by letting developers create indexes on column values that have been transformed by functions orexpressions. For example, an index could be created onupper(last_name), which would only store the upper-case versions of thelast_name field in the index. Another option sometimes supported is the use ofpartial index, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing onuser-defined functions, as well as expressions formed from an assortment of built-in functions.
Mostdatabase software includes indexing technology that enablessub-linear timelookup to improve performance, aslinear search is inefficient for large databases.
Suppose a database contains N data items and one must be retrieved based on the value of one of the fields. A simple implementation retrieves and examines each item according to the test. If there is only one matching item, this can stop when it finds that single item, but if there are multiple matches, it must test everything. This means that the number of operations in the average case isO(N) orlinear time. Since databases may contain many objects, and since lookup is a common operation, it is often desirable to improve performance.
An index is any data structure that improves the performance of lookup. There are many differentdata structures used for this purpose. There are complex design trade-offs involving lookup performance, index size, and index-update performance. Many index designs exhibit logarithmic (O(log(N))) lookup performance and in some applications it is possible to achieve flat (O(1)) performance.
Indexes are used to policedatabase constraints, such as UNIQUE, EXCLUSION,PRIMARY KEY andFOREIGN KEY. An index may be declared as UNIQUE, which creates an implicit constraint on the underlying table. Database systems usually implicitly create an index on a set of columns declared PRIMARY KEY, and some are capable of using an already-existing index to police this constraint. Many database systems require that both referencing and referenced sets of columns in a FOREIGN KEY constraint are indexed, thus improving performance of inserts, updates and deletes to the tables participating in the constraint.
Some database systems support an EXCLUSION constraint that ensures that, for a newly inserted or updated record, a certain predicate holds for no other record. This can be used to implement a UNIQUE constraint (with equality predicate) or more complex constraints, like ensuring that no overlapping time ranges or no intersecting geometry objects would be stored in the table. An index supporting fast searching for records satisfying the predicate is required to police such a constraint.[1]
The data is present in arbitrary order, but thelogical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index,
There can be more than one non-clustered index on a database table.
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indexes can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).
When multiple databases and multiple tables are joined, it is called acluster (not to be confused with clustered index described previously). The records for the tables sharing the value of a cluster key shall be stored together in the same or nearby data blocks. This may improve the joins of these tables on the cluster key, since the matching records are stored together and less I/O is required to locate them.[2] The cluster configuration defines the data layout in the tables that are parts of the cluster. A cluster can be keyed with aB-tree index or ahash table. The data block where the table record is stored is defined by the value of the cluster key.
The order that the index definition defines the columns in is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.
For example, in a phone book organized by city first, then by last name, and then by first name, in a particular city, one can easily extract the list of all phone numbers. However, it would be very tedious to find all the phone numbers for a particular last name. One would have to look within each city's section for the entries with that last name. Some databases can do this, others just won't use the index.
In the phone book example with acomposite index created on the columns (city, last_name, first_name), if we search by giving exact values for all the three fields, search time is minimal—but if we provide the values forcity andfirst_name only, the search uses only thecity field to retrieve all matched records. Then a sequential lookup checks the matching withfirst_name. So, to improve the performance, one must ensure that the index is created on the order of search columns.
Indexes are useful for many applications but come with some limitations. Consider the followingSQL statement:SELECTfirst_nameFROMpeopleWHERElast_name='Smith';. To process this statement without an index the database software must look at the last_name column on every row in the table (this is known as afull table scan). With an index the database simply follows the index data structure (typically aB-tree) until the Smith entry has been found; this is much less computationally expensive than a full table scan.
Consider this SQL statement:SELECTemail_addressFROMcustomersWHEREemail_addressLIKE'%@wikipedia.org';. This query would yield an email address for every customer whose email address ends with "@wikipedia.org", but even if the email_address column has been indexed the database must perform a full index scan. This is because the index is built with the assumption that words go from left to right. With awildcard at the beginning of the search-term, the database software is unable to use the underlying index data structure (in other words, the WHERE-clause isnotsargable). This problem can be solved through the addition of another index created onreverse(email_address) and a SQL query like this:SELECTemail_addressFROMcustomersWHEREreverse(email_address)LIKEreverse('%@wikipedia.org');. This puts the wild-card at the right-most part of the query (nowgro.aidepikiw@%), which the index on reverse(email_address) can satisfy.
When the wildcard characters are used on both sides of the search word as%wikipedia.org%, the index available on this field is not used. Rather only a sequential search is performed, which takes time.
A bitmap index is a special kind of indexing that stores the bulk of its data asbit arrays (bitmaps) and answers most queries by performingbitwise logical operations on these bitmaps. The most commonly used indexes, such asB+ trees, are most efficient if the values they index do not repeat or repeat a small number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the sex field in a customer database usually contains at most three distinct values: male, female or unknown (not recorded). For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.
A dense index indatabases is afile with pairs of keys andpointers for everyrecord in the data file. Every key in this file is associated with a particular pointer toa record in the sorted data file.In clustered indices with duplicate keys, the dense index pointsto the first record with that key.[3]
A sparse index in databases is a file with pairs of keys and pointers for everyblock in the data file. Every key in this file is associated with a particular pointerto the block in the sorted data file. In clustered indices with duplicate keys, the sparse index pointsto the lowest search key in each block.
A reverse-key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.
An inverted index maps a content word to the document containing it, thereby allowing full-text searches.
The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.
It is used to index fields that are neither ordering fields nor key fields (there is no assurance that the file is organized on key field or primary key field). One index entry for every tuple in the data file (dense index) contains the value of the indexed attribute and pointer to the block or record.
![[icon]](/mt/?noimg=&dark=on&url=http%3a%2f%2fen.wikipedia.org%2fwiki%2fFile%3aWiki_letter_w_cropped.svg) | This section is empty. You can help byadding to it.(April 2023) |
Another type of index used in database systems islinear hashing.
Indices can be implemented using a variety of data structures. Popular indices includebalanced trees,B+ trees andhashes.[4]
InMicrosoft SQL Server, theleaf node of the clustered index corresponds to the actual data, not simply a pointer to data that resides elsewhere, as is the case with a non-clustered index.[5] Each relation can have a single clustered index and many unclustered indices.[6]
An index is typically being accessed concurrently by several transactions and processes, and thus needsconcurrency control. While in principle indexes can utilize the common database concurrency control methods, specialized concurrency control methods for indexes exist, which are applied in conjunction with the common methods for a substantial performance gain.
In most cases, an index is used to quickly locate the data records from which the required data is read. In other words, the index is only used to locate data records in the table and not to return data.
A covering index is a special case where the index itself contains the required data fields and can answer the required data.
Consider the following table (other fields omitted):
| ID | Name | Other Fields |
|---|---|---|
| 12 | Plug | ... |
| 13 | Lamp | ... |
| 14 | Fuse | ... |
To find the Name for ID 13, an index on (ID) is useful, but the record must still be read to get the Name. However, an index on (ID, Name) contains the required data field and eliminates the need to look up the record.
Covering indexes are each for a specific table. Queries which JOIN/ access across multiple tables, may potentially consider covering indexes on more than one of these tables.[7]
A covering index can dramatically speed up data retrieval but may itself be large due to the additional keys, which slow down data insertion and update. To reduce such index size, some systems allow including non-key fields in the index. Non-key fields are not themselves part of the index ordering but only included at the leaf level, allowing for a covering index with less overall index size.
This can be done in SQL withCREATEINDEXmy_indexONmy_table(id)INCLUDE(name);.[8][9]
No standard defines how to create indexes, because the ISO SQL Standard does not cover physical aspects.Indexes are one of the physical parts of database conception among others like storage (tablespace or filegroups).[clarify] RDBMS vendors all give aCREATEINDEX syntax with some specific options that depend on their software's capabilities.
{{cite book}}:|work= ignored (help)| Types | |
|---|---|
| Concepts | |
| Objects | |
| Components | |
| Functions | |
| Related topics | |
