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

What are the advantages and disadvantages of indexing in MySQL database? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Why create an index?

This is because creating an index can greatly improve the performance of the system. First, by creating a uniqueness index, we can ensure the uniqueness of each row of data in the database table. Second, it can greatly accelerate the speed of data retrieval, which is also the main reason for creating an index. Third, the connection between the meter and the table can be accelerated, especially in achieving the referential integrity of the data. Fourth, when using grouping and sorting clauses for data retrieval, it can also significantly reduce the time of grouping and sorting in the query. Fifth, by using the index, we can use the optimization hidden device in the process of query to improve the performance of the system.

One might ask: adding an index has so many advantages, why not create an index on each column in the table? Although this idea has its rationality, it also has its one-sidedness. Although an index has many advantages, it is very unwise to add an index to every column in the table.

This is because there are many disadvantages to adding an index:

First, it takes time to create and maintain an index, which increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also needs to occupy a certain physical space. If you want to build a clustered index, you will need more space.

Third, when the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

What kind of fields are suitable for creating indexes:

The index is built on top of some columns in the database table. Therefore, when creating an index, you should carefully consider on which columns you can create an index and on which columns you cannot.

In general, indexes should be created on these columns, such as:

First, you can speed up the search on the columns that often need to be searched.

Second, on the column as the primary key, force the uniqueness of the column and organize the arrangement structure of the data in the table.

Third, on the columns that are often used in joins, these columns are mainly foreign keys, which can speed up the connection.

Fourth, create an index on a column that often needs to search by scope, because the index has been sorted and its specified range is continuous

Fifth, create an index on columns that often need to be sorted, because the index is already sorted, so that the query can make use of the sorting of the index to speed up the sorting query time.

Sixth, create an index on the columns that are often used in the WHERE clause to speed up the judgment of conditions.

To set up an index, it is generally established according to the where condition of select, for example: the condition of select is wheref1andf2, then it is useless for us to index on field F1 or field f2, only it is useful to set up index on field F1 and f2 at the same time.

After reading the above, have you mastered the advantages and disadvantages of indexing in MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.