Why create an index? This is because creating indexes can greatly improve the performance of the system. First of all, by creating a unique index, the uniqueness of each row of data in the database table can be guaranteed. Second, it can greatly speed up the retrieval of data, which is also the main reason for creating indexes. Third, it can speed up the connection between tables, which is particularly meaningful in realizing the referential integrity of data. Fourthly, when using the grouping sort clause for data retrieval, it can also significantly reduce the time of grouping sort in the query. Fifth, by using the index, we can use the optimized hidden device to improve the performance of the system during the query process.
Some people may ask: adding an index has so many benefits, why not create an index for each column in the table? This idea is reasonable, but it is also one-sided. Although indexes have many advantages, it is unwise to add indexes to every column in a table. This is because increasing the index also has many disadvantages. First of all, it takes time to create and maintain indexes, which increases with the increase of data volume. Second, the index needs to occupy physical space. In addition to the data table occupying data space, each index also occupies a certain physical space. If you want to create a clustered index, you will need more space. Thirdly, when adding, deleting and modifying the data in the table, the index should also be maintained dynamically, which reduces the speed of data maintenance.
Indexes are built on some columns of database tables. Therefore, when creating an index, you should carefully consider which columns can be indexed and which columns cannot be indexed. Generally speaking, you should create indexes on these columns, for example, you can speed up the search on columns that need to be searched frequently; On the column as the primary key, enforce the uniqueness of the column and the arrangement structure of the data in the organization table; Among the columns often used for connection, these columns are mainly foreign keys, which can speed up the connection; Create an index on a column that often needs to be searched by range, because the index has been sorted and its specified range is continuous; Create an index on the column that needs sorting frequently, because the index has been sorted, so the query can use the sorting of the index to speed up the sorting query time; Create indexes on the columns frequently used in the where clause to speed up the judgment of conditions.
Similarly, indexes should not be created for some columns. Generally speaking, these columns that should not be indexed have the following characteristics: First, indexes should not be created for columns that are rarely used or referenced in queries. This is because these columns are rarely used, and whether there is an index or not can not improve the query speed. On the contrary, due to the increase of index, the maintenance speed of the system is reduced and the space requirement is increased. Second, indexes should not be added to columns with few data values. This is because, because the values of these columns are few, such as the gender column of the personnel table, in the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the data rows in the table that need to be searched account for a large proportion. Increasing the index can't obviously speed up the retrieval. Third, indexes should not be added to columns defined as text, image and bit data types. This is because the amount of data in these columns is either quite large or has few values. Fourthly, when the modification performance is much greater than the retrieval performance, the index should not be created. This is because the modification performance and retrieval performance are contradictory. When the index increases, the retrieval performance will improve, but the modification performance will decrease. When the index decreases, the modification performance will increase and the retrieval performance will decrease. Therefore, when the modification performance is far greater than the retrieval performance, the index should not be created.