Database indexing principle Understanding ___ Database

Qingming Festival A person at home, has been mixed for a day, think or write something.
The previous understanding of the database always stay in the use of the stage, did not go to study the deep-seated things, these two days just have the time (in fact, the job needs), looked at the database index some of the basis of things, hope that through this blog, tidy up their own ideas.
1. What is an index.
I think the people who used the database should know that. Index similar to the directory of books, mainly used to improve the efficiency of queries, that is, query the index, and then through the index to find the relevant data, the index is equivalent to record a keyword, assigned to a different file, or file in different locations, Of course the index itself is also saved by file.
2. Type of index
There are two basic index structures, that is, how the index files are saved, one is the sequential index, which is sorted according to the order of value (the value in this file, which is the value of the field indexed for it, is placed in the index file in sequence), the other is the hash index, or the value is evenly divided into several hash buckets, Positioned through a hash function.
2.1. Sequential indexing
There are many concepts under the sequential index.
If the indexed fields themselves are sorted in a certain order, the index is called a clustered index. Otherwise, it is called a nonclustered index.
If each value of the field being indexed has an index corresponding to it, the index is called a dense index, otherwise it is called a sparse index.
Sequential indexes are divided into two categories, one-level index (not used), and multilevel indexes (usually B + trees, which are used extensively).
Single-level index is to put all the index fields and the corresponding file location in order, this index to find a slow, because it is sequential storage, you can use the binary lookup method, but the overall efficiency is not high, this index is the most basic index, generally not, Oracle does not seem to support this index.
Multi-level indexes are actually indexed on top of a single index (sparse index), that is, the index to the index, the two-level index can add a three-level index, can be constantly added to the last top only one node (root node), became a tree-like structure.
We often hear that the B + tree is this concept, with the purpose of this tree and the red and black trees, also in order to try to maintain the balance of the tree, of course, the red-black tree is two fork tree, but B + tree is not a binary tree, the node can have more than one child node, database developers will set the number of child nodes a maximum value, this value will not be too small, so B + The trees are generally chunky, and the red and black trees are more lanky.
About the B + Tree Insert, delete, will involve some algorithms to maintain the balance of the tree, here is not detailed. The default index for Oracle is this structure.
If you often need to query two fields at once, use two separate indexes rather than creating a composite index, because two individual indexes usually use only one of the databases, and a composite index can be greatly improved because the index itself corresponds to two fields.
2.2 Hash Index
The second index, called a hash index, is an index that is positioned through a hash function, but there are few separate hash indexes, but the hash file organization uses more.
Hash file organization is based on a key through the hash of the corresponding records are placed in the same slot, so that the same key value of the corresponding records must be placed in the same file, also reduces the number of file reads, improve efficiency.
The hash index is the technique of finding the final index entry based on the hash code of the corresponding key. In fact, and B-tree is almost the same as a two-level secondary index on the index, I understand that the hash index is a level two or more advanced sparse index, otherwise the bucket is too much, efficiency is not very high.
2.3-bit Graph index
A bitmap index is a special index for simple query design for multiple fields, the scope of application is small, only applicable to the field value fixed and a very small number of values, such as gender, can only be male and female, or level, status, etc., and only in the meantime to multiple such fields query can reflect the advantages of the bitmap.
The basic idea of a bitmap is to use 0 or one of each condition to indicate that if there are 5 records, gender is male, female, male, male, female, then if the use of bitmap index will create two bitmap, corresponding to the male 10110 and the corresponding female 01001, what is the benefit of doing so? That is, if you are querying for and or or queries on multiple fields of this type at the same time, you can use bitwise-and-bitwise OR to get results directly.

Summarize:
B + trees are most commonly used, performance is not bad, for both range query and single value query can be. In particular, the scope of the query, must use B + Tree This order can be.
If the hash is just for a single value query faster than the B + tree, but Oracle does not seem to support the hash index, only the hash table space.
The use of bitmaps is very limited, only a small number of cases can be used, it is necessary to determine the true use of such indexes (the type of value is very small and require a composite query), otherwise the establishment of a large number of bitmaps is meaningless.