Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the use of the index in the database. The introduction in the article is very detailed and has certain reference value. Interested friends must read it!

The biggest role of database index is to speed up the query speed, it can fundamentally reduce the number of record rows that need to scan the table, database index is the database data structure, further said that the data structure stores all the values of a column in a table, that is, the index is based on a column in the data table.

A database index is an identifier attached to a table field to increase query speed. I have seen many people mechanically understand the concept of index, thinking that adding index has only advantages and no disadvantages. Here is a summary of the previous index study notes:

First understand why the index will increase speed, DB in the execution of a Sql statement, the default way is to scan the whole table according to the search conditions, meet the matching conditions to join the search result set. If we add an index to a field, the query will first index the number of rows in the list to a specific value at a time, greatly reducing the number of rows that traverse the match, so it can significantly increase the speed of the query. Should it be indexed at all times? Here are a few counterexamples: 1. If you need to get all the table records every time, you must scan the whole table anyway, and it makes no sense to add indexes. 2. For non-unique fields, such as fields with a large number of duplicate values such as "gender," it makes no sense to add indexes. For tables with fewer records, increasing the index will not bring speed optimization but waste storage space, because the index needs storage space, and there is a fatal disadvantage that for each update/insert/delete execution, the index of the field must be recalculated.

When is it appropriate to add an index? Let's look at an example from Mysql manual, where there is a SQL statement:

SELECT c.companyID, c.companyName FROM Companies c, User u WHERE c.companyID = u.fk_companyID AND c.numEmployees >= 0 AND c.companyName LIKE '%i%' AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = 'Executive')

This statement involves joining three tables and includes many search criteria such as size comparison, Like matching, etc. The number of rows Mysql needs to scan without an index is 77721876. By adding indexes to the companyID and groupLabel fields, we can scan only 134 rows. The number of scans can be viewed in Mysql by explaining Select. As you can see, in this case of joined tables and complex search conditions, the performance boost from an index is far more important than the disk space it takes up.

So how is indexing implemented? Most DB vendor implementations of indexing are based on a single data structure--the B-tree. Because the characteristic of B-tree is suitable for organizing dynamic lookup tables on direct storage devices such as disks. A B-tree of order m(m>=3) is an m-ary tree satisfying the following conditions:

1. Each node consists of the following scopes (j, p0, k1, p1, k2, p2,...) ki, pi) where j is the number of keywords and p is the child pointer

2. All leaf nodes are on the same layer, and the number of layers is equal to the tree height h.

3. The number of keywords contained in each non-root node satisfies [m/2-1]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.