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

This article shows you what the function of database index is, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First of all, to understand why the index will increase the speed, when DB executes a Sql statement, the default way is to scan the full table according to the search criteria, and join the search results collection if there are matching conditions. If we add an index to a field, the query will first go to the number of rows in the index list that locate a specific value at a time, which greatly reduces the number of rows traversing matching, so it can significantly increase the speed of the query. So should it be indexed at any time? Here are several counterexamples: 1. If you need to fetch all the table records every time, and you have to scan the whole table anyway, there is no point in indexing or not. 2. For non-unique fields, such as "gender", which have a large number of duplicate values, there is no point in adding an index. 3. For tables with fewer records, increasing the index will not lead to speed optimization but a waste of storage space, because the index needs storage space, and there is a fatal disadvantage that the index of the field must be recalculated and updated for each execution of update/insert/delete.

So when is it appropriate to add an index? Let's look at an example in the 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 a join of three tables and includes many search criteria such as size comparison, Like matching, and so on. The number of rows scanned by Mysql without an index is 77721876 rows. By adding indexes to the companyID and groupLabel fields, the number of rows scanned is only 134rows. You can view the number of scans through Explain Select in Mysql. You can see that in the case of such joined tables and complex search conditions, the performance improvement brought by the index is far more important than the disk space it takes up.

So how is the index implemented? Most DB vendors implement indexes based on a data structure-B-tree. Because the characteristic of B-tree is that it is suitable for organizing dynamic lookup tables on direct storage devices such as disks. The definition of B-tree is as follows: a B-tree of order m (m > = 3) is an m-fork tree that satisfies the following conditions:

1. Each node includes 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 height of the tree.

3. The number of keywords contained in each non-root node satisfies [m _ prime 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.