Seven common principles for rational use of index queries

Index query is an important record query method in the database, if you want to enter the index and the index on those fields should be combined with the actual database system query requirements, the following gives some practical general principles:

1. Indexing on fields that are often used as filters;

2. In the SQL statement often in the group by, order by the field to establish the index;

3. There is no need to establish indexes in fields with fewer values, such as gender fields;

4. Avoid indexing of frequently accessed columns;

5. Indexing of columns used for joins (main/external);

6. Establish a composite index on multiple columns that are frequently accessed, but note that the order in which the composite index is established is determined by the frequency of use;

7. A nonclustered index is established by default. However, it is a good idea to consider a clustered index, such as a limited number of (not very few) unique columns, a wide range of queries, and a full use of the index to reduce the number of table scan i/0 and effectively avoid searching the entire table. Of course, a reasonable index should be based on the analysis and prediction of various queries, and also on the database structure designed by the DBA.