Database index types and how to implement them

1. Index definition

A database index is like a directory in front of a book, speeding up the query speed of a database. An index is a structure that sorts the values of one or more columns in a database table (for example, the last name (lname) column of an employee table). If you want to find him or her by the name of a particular employee, the index helps you get information faster than searching all the rows in the table.

2, the advantages and disadvantages of establishing indexes:

Advantages:
    1. Greatly accelerate the retrieval speed of data;   
    2. Create a unique index that guarantees the uniqueness of each row of data in a database table;   
    3. Accelerate the connection between tables and tables;   
    4. When using grouping and sorting clauses for data retrieval, you can significantly reduce the time to group and sort in queries.

Disadvantages:

1. Indexes need to occupy physical storage space outside the data table

2. Creating indexes and maintaining indexes takes a certain amount of time

3. When updating the table, the index needs to be rebuilt, which reduces the maintenance speed of the data.

3. Index Type:
  Depending on the capabilities of your database, you can create indexes in the Database Designer: unique indexes, primary key indexes, and clustered indexes . Although a unique index helps locate information, for best performance results, it is recommended that you use a primary KEY or a unique constraint instead.  

Unique index:   unique     For example: Create unique index Stusno on student (SNO);
Indicates that each index value for this index only corresponds to a unique data record, which guarantees that a single column does not contain duplicate values for single-column uniqueness indexes. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.
Primary KEY index:   primary key
Database tables often have one column or column combination whose values uniquely identify each row in the table. This column is called the primary key of the table.   

Clustered index (also known as clustered index): Cluster  
In a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.   If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.

4. How to implement the index

1 B + Tree We often hear that B + tree is the concept, with the purpose of this tree and the red and black trees are similar, but also to try to maintain the balance of the tree, of course, the red and black tree is two fork tree, but the B + tree is not a binary tree, the node below can have many sub-nodes, the database developer will , so the B + tree is generally more chunky, while the red and black trees are more tall.
The insertion and deletion of B + trees will involve some algorithms to maintain the balance of the tree, which is not detailed here. The default index for Oracle is this structure.
If it is often necessary to make an and query on two fields at once, then using two separate indexes is not as good as building a composite index, because two separate indexes typically use only one of the databases, whereas using a composite index can be much more efficient because the index itself corresponds to two fields.

2 Hash Index
The second index, called the hash Index, is an index that is positioned by a hash function, but is rarely used by a hash index alone, but rather as a hash file that is organized in more than one way.
Hash file organization is based on a key through the hash calculation of the corresponding records are placed in the same slot, so that the same key value corresponding records must be placed in the same file, it also reduces the number of file reads, improve efficiency.
Hash index is based on the corresponding key hash code to find the final index of the technology, in fact, and the B-tree is almost, that is, an index of Level two secondary index, I understand that the hash index is a two-level or more advanced sparse index, otherwise the bucket is too much, the efficiency is not very high.

3-bit Graph index
Bitmap indexing is a simple query for multiple fields design a special index, the scope of the application is small, only for fixed field values and the value of a few kinds of situations, such as gender, only male and female, or level, state, and so on, and only when the number of such fields query to reflect the advantages of bitmaps.
The basic idea of a bitmap is to use 0 or one for each condition, if there are 5 records, gender is male, female, male, male, female, then if using a bitmap index will build two bitmaps, corresponding to the male 10110 and the corresponding female 01001, what is the benefit of doing this? That is, if you are querying multiple fields of this type at the same time, you can use bitwise-and-bitwise OR to directly get results.

B + trees are most commonly used, and performance is not bad, both for range queries and single-valued queries. In particular, the scope of the query, must use the B + tree in order to be able to.
Hash if only for the single-valued query speed will be faster than the B + tree, but Oracle does not seem to support hash index, only support hash table space.
The use of bitmaps is very limited, only a few cases can be used, it is necessary to determine the real suitability of the use of such an index (the value of the type is very small and need to compound query), otherwise building a lot of bitmaps is meaningless.

Database index types and how to implement them