Why MySQL Index Improves Query Efficiency

Mysql TutorialThe column introduces why indexes improve query efficiency.

However, although the index can speed up the query speed and improve the processing performance of MySQL, but too much The use of indexes will also cause the following disadvantages:

• It takes time to create and maintain indexes, and this time increases with the increase of data volume.
• In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you want to build a clustered index, the space required will be even larger.
• When adding, deleting and modifying the data in the table, the index should also be maintained dynamically, which reduces the speed of data maintenance.

Note: Indexes can speed up queries in some cases, but in some cases, they will reduce efficiency.

Indexing is only one factor to improve efficiency, so the following principles should be followed when building an index:

• Create an index on the column that needs to be searched frequently, Can speed up the search.
• Create an index on the column as the primary key, enforce the uniqueness of the column, and organize the arrangement structure of the data in the table.
• Create indexes on columns that are frequently used for table joins. These columns are mainly foreign keys, which can speed up table joins.
• Create indexes on columns that often need to be searched based on ranges. Because the indexes are sorted, the ranges specified are continuous.
• Create indexes on columns that often need to be sorted. Because the indexes are already sorted, you can use the sorting of the indexes to speed up the sorting query when querying.
• Create indexes on columns that often use WHERE clauses to speed up the judgment of conditions.

Now everyone knows why the index is so fast, in fact, it is just a sentence, through the structure of the index to maximize the reduction of the number of IO of the database, after all, the time of an IO is really too long. . .

Summary

As far as the interview is concerned, we can actually master a lot of knowledge easily, but if you want to learn for the purpose, you will find a lot We have to go deep into the computer to discover its mysteries. Many people ask me how to remember so many things. In fact, learning itself is a very helpless thing. Since we have to learn, why not study hard? To learn to enjoy it? Recently, I am also supplementing the basics, and I will start to update the computer basics and network-related knowledge later.

More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of why the MySQL index improves query efficiency. For more information, please pay attention to other related articles on 1024programmer.com!