Advantages and disadvantages of mysq index Establishment

Advantages and disadvantages of mysq index Establishment

 Advantages and necessity of index creation:

1. By creating a unique index, You can ensure the uniqueness of each row of data in the database table;

Second, it can greatly speed up data retrieval, which is also the main reason for creating indexes;

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity;

Fourth, when using grouping and sorting clauses for data retrieval, it can also significantly reduce the time for grouping and sorting in queries;

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance;

Disadvantages of indexing:

1. It takes time to create and maintain indexes. This time increases with the increase of data volume;

2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. If you want to create a clustered index, you need more space;

3. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed;

 

Fields suitable for index creation:

First, you can speed up the search on columns that frequently need to be searched;

Second, in the column as the primary key, force the uniqueness of the column and the data arrangement structure in the organization table;

Third, these columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection;

4. Create an index on a column that often needs to be searched by range. The specified range is continuous because the index has been sorted;

Fifth, create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;

6. Create an index on a column frequently used in the WHERE clause to speed up condition judgment;

Fields not suitable for index creation:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, having an index or no index does not increase the query speed. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements;

Second, indexes should not be added to columns with only few data values. Adding indexes does not significantly accelerate the search speed;

Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small;

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. When the modification performance is much higher than the retrieval performance, you should not create an index.