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

This article mainly introduces "the implementation of the index in MySQ". In the daily operation, I believe that many people have doubts about the implementation of the index in MySQ. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about the implementation of the index in MySQ. Next, please follow the editor to study!

First of all, you need to understand the relevant background knowledge: B-Tree & B+Tree, half search (Binary Search), database performance problems, and the basic storage structure of the data. The following is a brief analysis:

1.B-Tree & B+Tree

Http://en.wikipedia.org/wiki/B%2B_tree

Http://en.wikipedia.org/wiki/B-tree

two。 Semi-search (Binary Search)

Http://en.wikipedia.org/wiki/Binary_search_algorithm

3. Performance problems of database

The performance of ① disk IO is very low, which seriously affects the performance of database system.

② disk sequential read and write performance is much higher than random read and write.

4. Basic storage structure of data

① disk space is divided into many blocks (Block) or pages (Page) of the same size.

② these data blocks of a table are concatenated in a linked list.

③ data is stored in Row blocks on disk in rows, as shown in the figure.

When ④ accesses data, it reads or writes at least one complete Block from disk at a time.

Realization of basic operation of data

Basic operations include: INSERT, UPDATE, DELETE, SELECT.

1.SELECT

① positioning data

② reads out the block where the data is located and processes the data.

③ returns data to the user

2.UPDATE 、 DELETE

① positioning data

② reads out the block where the data is located and modifies the data

③ writes back to disk

3.INSERT

① locates the page where the data is to be inserted (if the data needs to be sorted)

② reads out the data page to be inserted and inserts the data.

③ writes back to disk

How to locate the data?

4. Table scan (Table Scan)

Read out all the data blocks from the disk in turn, and match the data one by one.

The time complexity is O (n) if all the data takes up 100 blocks. Although only one row of data is queried, all 100 blocks of data need to be read out.

A large number of disk IO operations are required, which greatly affects the performance of data positioning.

Because the data positioning operation is a necessary operation for all data operations, the efficiency of the data positioning operation will directly affect the efficiency of all data operations.

So we started to think, how to reduce the IO of disks?

5. Reduce disk IO

Reduce the disk space occupied by data: compression algorithm, optimize data storage structure

Reduce the total amount of data accessed: some of the data read out or written is necessary for data manipulation, which is called valid data. The rest of the data is not necessary for data operation and is called invalid data. For example, query a record whose name is' Zhang San'. Then this record is a valid record, and other records are invalid records. We should strive to reduce access to invalid data.

At this point, the study on "the implementation of indexes in MySQ" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.