Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is indexing?

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article mainly explains "what is database indexing". The explanation in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "what is database indexing" together!

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. It can be seen that in this case of joined tables and complex search conditions, the performance improvement of the index is much more important than the disk space it occupies.

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.

Views: 275

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report