Shulou(Shulou.com)06/01 Report--

This article will explain to you in detail whether indexing can improve the speed of data query in the table. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading 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. (recommended study: MySQL video tutorial)

If you add an index, first go to the index list to query, and our index list is the data structure of class B tree, the time complexity of the query is O (log2N), it will be very fast to locate a specific worthwhile row, so the query speed will be very fast.

Why is B+-tree more suitable for file index and database index of operating system in practical application than B-tree?

1) the disk read and write cost of B+-tree is lower

The internal node of B+-tree does not have a pointer to keyword details. Therefore, its internal node is smaller than that of B-tree. If all the keywords of the same internal node are stored in the same disk, the more keywords the disk can hold. The more keywords you need to find when you read them into memory at once. Relatively speaking, the number of IO reads and writes has been reduced.

For example, suppose a disk block holds 16bytes, and a keyword 2bytes, a keyword specific information pointer 2bytes. The internal node of a 9th-order B-tree (up to 8 keywords per node) needs 2 disks to be fast. However, the internal node of the B+ tree only needs 1 disk fast. When the internal node needs to be read into memory, the B-tree has one more block search time than the B + tree (that is, the disk rotation time in the disk).

2) the query efficiency of B+-tree is more stable

Because the non-endpoint is not the node that ultimately points to the content of the file, but only the index of the keyword in the leaf node. Therefore, any keyword search must take a road from the root node to the leaf node. The path length of all keyword queries is the same, resulting in the same query efficiency for each data.

Indexes

Use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table, such as the last name (lname) column of the employee table. If you want to find a specific employee by last name, the index helps you get that information faster than if you have to search all the rows in the table.

Note: not all databases use indexes in the same way.

As a general rule, you need to create an index on a table only if you query the data in the index column frequently. Indexes take up disk space and slow down the speed of adding, deleting, and updating rows. In most cases, the speed advantage of an index for data retrieval greatly exceeds its.

On the establishment of an index can improve the speed of data query in the table? so much for sharing here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.