Interview Summary (database index, B-tree, + + tree)

1. The database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index. The implementation of an index typically uses a B-tree and its variants, plus trees.

Creating an index can greatly improve the performance of your system.

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, the speed of data retrieval can be greatly accelerated, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, especially in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, it is also possible to significantly reduce the time of grouping and sorting in queries.

By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Because there are many disadvantages to increasing the index.

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

2. B-Tree and + + Tree

1) B-Tree

Each node in the B-tree contains a key value and a key value for the data object that holds the address pointer, so a successful search for an object can be done without reaching the leaf node of the tree.

Successful searches include intra-node searches and searches along a path, and successful search times depend on the level of the key code and the number of key codes within the node.

The way to find a given keyword in a B-tree is to first take the root node, K1,..., kj in the root node to find the given keyword (available order lookup or binary lookup), and if a keyword equal to the given value is found, the search succeeds; otherwise, you can definitely identify the keyword you want to check in a Ki or ki+ 1, then take the next layer of index node that the PI refers to continues to find, until it is found, or the pointer pi is empty when the lookup fails.

2) B + Tree

The key code stored in a B + tree non-leaf node does not indicate the address pointer of the data object, but the non-leaf node is just the index part. All the leaf nodes are on the same layer, which contains all the key codes and the corresponding data objects ' storing address pointers, and the leaf nodes are linked from small to large in order to key code. If the actual data objects are stored in the order in which they are added, rather than by key number of times, the index of the leaf node must be a dense index, and if the actual data store is stored in key order, the leaf node is indexed with sparse indexes.

B + trees have 2 head pointers, one is the root node of the tree, and the other is the leaf node of the minimum key code. So the B + Tree has two methods of searching:

One is to search by the list of links pulled by the leaf nodes themselves.

One is to start the search from the root node, similar to the B-tree, but if the key code of the non-leaf node equals the given value, the search does not stop, but continues along the right pointer, always checking the key code on the leaf node. So whether the search succeeds or not, all the layers of the tree are going to be finished.

In a B + tree, the insertion and deletion of data objects is done only on leaf nodes.

The differences between the two data structures that handle indexes are:

The same key value does not appear multiple times in a, B-tree, and it may appear in leaf nodes or in non-leaf nodes. The keys of the B + tree are bound to appear in the leaf nodes and may be repeated in non-leaf nodes to maintain the balance of the B + tree.
b, because the B-tree key position is variable, and in the entire tree structure only one occurrence, although the storage space can be saved, but the complexity of the insertion and deletion operations increased significantly. B + trees are a better compromise than the other.
The query efficiency of the C and B-tree is related to the position of the key in the tree, the maximum time complexity is the same as the B + tree (at the time of the leaf node), the minimum time complexity is 1 (at the root node point). The complexity of the B + tree is fixed for a built tree.

Add:

B-Tree:

A) Each node in the tree contains a maximum of M children (m>=2);

b) root nodes and leaf nodes, each of the other nodes has at least [Ceil (M/2)] Children (where ceil (x) is an upper-bound function);

c) Joghen node is not a leaf node, then at least 2 children (special case: No Child root node, that is, the root node is a leaf node, the whole tree has only one root);

d) All leaf nodes appear on the same layer, and leaf nodes do not contain any keyword information.

e) Each non-terminal node contains n keyword information: (N,P0,K1,P1,K2,P2,......,KN,PN). which
A) Ki (I=1...N) is the keyword, and the keyword is sorted in ascending order of K (i-1) < Ki.
b) Pi is a contact point pointing to Subtree, and the key of the pointer P (i-1) to all nodes of the subtree is less than Ki, but both are greater than K (i-1).
c) The number of keywords n must satisfy: [Ceil (M/2) -1]<= n <= m-1.

B+-tree:

B + trees are a kind of b-tree that are produced as required by the file system.

The similarities and differences between a M-order B + tree and a M-order tree are:

A. There are n-1 keywords in the nodes of n subtrees tree; (It is quite controversial, the B + tree in the end is the second tree with the N-subtrees tree has n-1 keyword consistency, or inconsistent: B-tree n subtrees tree node contains n keywords, pending verification.

B. All the leaf nodes contain information about all the keywords, and pointers to the records that contain them, and the leaf nodes themselves are linked by the size of the keywords from a large order of origin. (The leaf node of the B-tree does not include all the information it needs to find)

C. All non-terminal nodes can be viewed as an indexed part, with only the largest (or smallest) keywords in the nodes of their sub-roots. (The non-final node of the B-tree also contains valid information that needs to be found)

b* Tree:

B*-tree is a variant of B+-tree , based on the B + tree (all the leaf nodes contain information about all the keywords and pointers to the records containing these keywords), and the b* and non-leaf nodes of the tree add pointers to the brothers; b* The tree defines the number of non-leaf node keywords at least (2/3) *m, that is, the minimum usage of the block is 2/3 (instead of 1/2 of the B + tree).

3. The index is mainly to improve the data query speed. When DML is in progress, the index is updated. Therefore, the more indexes, the slower the DML, and the need to maintain the index. Therefore, there is a tradeoff between creating indexes and DML.

To create an index:
Single index: Create index <Index-Name> on<table_name> (column_name);

Composite Index: Create index i_deptno_job onemp (deptno,job); The Deptno and job columns of the EMP table are indexed.

DBAs often use REBUILD to rebuild indexes to reduce hard disk fragmentation and improve application performance.

ALTER INDEX Emp_ix REBUILD REVERSE; modifying indexes

Drop index pk_dept; Delete Index

Interview Summary (database index, B-tree, + + tree)