Database index creation principles

1. fields that are often used to filter records.

1. primary key field. The system automatically creates a primary key index;

2. unique kye field. The system automatically creates the corresponding index;

3. the field defined by the foreign key constraint as the foreign key;

4. fields used to connect to the table in the query;

5. It is often used as the benchmark field for sorting (order by field;

2. Indexes occupy disk space, and creating unnecessary indexes is a waste.

3. Data operations must be taken into account when creating indexes.

1. The content is rarely changed and often queried. It is not called to create more indexes for it;

2. Regular, regular table changes require careful creation of necessary indexes;

Iv. Differences between primary key and unique key
1. The domain/domain group used as the Primary Key cannot be null. The Unique Key is acceptable.
2. A table can have only one Primary Key, and multiple Unique keys can exist at the same time.
The bigger difference lies in the logic design. The Primary Key is generally used as the record identifier in the logic design, which is also set
The original intention of Primary Key, and Unique Key is only to ensure the uniqueness of the domain/domain group.

V. Composite Index and single index

Composite index refers to multi-field joint index. During query, these fields are often combined as conditions before query.
The unique index mainly uses the primary key ID index, and the storage structure order is consistent with the physical structure.

For example, create index idx on tbl (a, B)
Sort by a first, and sort by B in the same order as a. so when you query a or AB,
This index can be used. However, when you only query B, the index does not help you much. You may be able to skip the search.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/tenfyguo/archive/2009/03/09/3974448.aspx