Mysql database index creation rule _ MySQL

Mysql database index creation rule bitsCN.com

Mysql database index creation rules

1. the table's primary key and foreign key must have an index;

2. indexes should be available for tables with more than 300 of the data volume;

3. for tables that are often connected to other tables, indexes should be created on the connection fields;

4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;

5. The index should be built on highly selective fields;

6. indexes should be built on small fields. do not create indexes for large text fields or even extra-long fields;

7. the establishment of a composite index requires careful analysis. try to replace it with a single field index:

A. correctly select the primary column field in the composite index, which is generally A highly selective field;

B. do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered;

C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;

D. if the composite index contains more than three fields, consider the necessity and reduce the composite fields;

E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;

8. do not create too many indexes for tables that frequently perform data operations;

9. delete useless indexes to avoid negative impact on the execution plan. these are the basis for judging general index creation.

In a word, the establishment of indexes must be careful. the necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater.

BitsCN.com