Database Indexes (Index)

What is a database index? What's the use? What is the principle? Best practices What?

What's the index?

An index is a data structure that contains not only a copy of the data from a column or columns in a table, but also a link to that row of data;

Structurally, the data in the index is also organized into data structures that satisfy certain search algorithms, such as B-trees. This improves query efficiency from the database.

What's the use?

　　creating an index can improve the query performance of the data (improve speed by data retrieval operation). Specifically:

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

Second, the retrieval speed of the data can be greatly accelerated, which is also the most important reason for creating the index (looking directly at the link instead of the row-by-line scan of the data content).

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

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort 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.

It also has the role of monitoring database constraints , such as a unique index.

The cost of building an index

According to the above description, it is known that increasing the index will inevitably increase the cost of the database storage space, while creating and modifying the index is also more than the absence of an index to spend extra time.

Index type and Index method

Waiting to be added ...

Best practices

Guiding principle: Modifying performance and retrieving performance are contradictory and do not build indexes when indexing is more harmful than beneficial.

The case where the index should be built

1. Often need to search the column, you can speed up the search;
2. On a frequently connected column, you can speed up the connection;
3. On columns that are often required to search by range, the index is sorted and the range is faster based on the ordered index;
4. Indexes are created on columns that often need to be sorted, and indexes are sorted to speed up sorting

The situation where the index should not be built

1. Queries are seldom used on columns;
2. There are few data values on the column, because the value of the number is less, the index effect is not obvious, such as the value of only 0 and 1 cases;
3. Some data types, such as text,image, have large data volumes, and the cost of establishing and maintaining indexes is small.

Not to be continued ...

Reference article:

"1" Database index--Wikipedia.

The implementation principle of "1" Database index by FAI Tsai

Database Indexes (Index)