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

This article mainly introduces the reasons why mysql should be indexed, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Because indexes can quickly improve query speed; if you do not use indexes, mysql must start with the first record and then read the entire table until you find the relevant rows

The bigger the watch, the more time it takes, but that's not always the case.

An index is a data structure

Therefore, in addition to the data, the database system also maintains data structures that meet specific search algorithms, which refer to (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This kind of data structure is the index.

Analysis of index data structure

What is the structure of this index? In other words, why can this structure improve the speed of retrieval?

Without an index, when searching for a record (for example, finding name='wish'), you need to search all records, because there is no guarantee that there is only one wish, you must search them all.

If you build an index on name, mysql will search the whole table once, search and arrange the name values of each record in ascending order, and then build index entries (name and row_id) and store them in the index segment. When the query name is wish, you can directly find the corresponding place.

3. Creating an index is not necessarily used. After mysql automatically counts the information of the table, decide whether to use the index or not. When there is little data in the table, the speed of using full table scan is very fast, so there is no need to use the index.

Give an example to illustrate the working mechanism of the index.

There are two fields in Table A

Id,name

There are now 10 million pieces of data in the table

Requirements: query the corresponding id according to name

If there is no index, you have to query all the records in the table, and you have to check all 10 million pieces of data one by one.

Now build the index based on name

Index table structure:

Id,name,value

Where value is the id of Table An and is stored as an json array (because there will be multiple cases with the same name)

Name can then be sorted according to the collation

According to the algorithm, the position of name in the index table can be located directly.

You can then take out the record where the id in Table An is located.

In short, by indexing, you can go directly to the records in Table A.

Of course soon, if you want to query Table A, you have to query 10 million pieces of data. By establishing an index, the amount of query is greatly reduced by the algorithm.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the reasons why mysql should be indexed. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.