Mysql Learning-Design principles for indexing

An unreasonable design of an index or lack of an index can create obstacles to the performance of the database and the application. Efficient indexing is important for good performance. The design index is that the guidelines should be considered:

(1) The index is not 讹夺 the better, if a table has a large number of indexes, not only occupy disk space, but also affect the performance of statements such as Insert/delete/update, because when the data changes in the table, the index will also be adjusted and updated.

(2) Avoid using indexes for frequently updated tables, and as few columns as possible in the index, and for fields that are frequently used for queries, you should create an index, but avoid adding unnecessary fields.

(3) The table with small data is best not to use the index, because the data is relatively small, the time spent in the query can be less than the time to traverse the index, the index may not produce an optimized effect.

(4) in the conditional expression, the process uses a number of different values of the column on the index, on the columns with a few different values do not index. For example, an index is built on the "Gender" column of a table. The weak-building index not only will not improve the query efficiency, but will seriously reduce the data update speed.

(5) When uniqueness is a feature of the data itself, specify a unique index. Using a unique index ensures the data integrity of the defined columns to improve query speed.

(6) Indexing on columns that are frequently sorted or grouped (that is, group by or order by operations), where there are multiple columns to be sorted by the weak, and a composite index can be established on those columns.

Mysql Learning-Design principles for indexing