Advance knowledge of software development (32) database index

An index occupies a very important place in a database, which is a structure that sorts the values of one or more columns in a database table. Using indexes correctly, you can quickly access specific information in database tables, thereby improving the execution efficiency of database scripts.

1. Index classification

A database table often has a combination of columns or columns whose values uniquely identify each row in the table, which is called the primary key of the table.

In a database, the indexes are grouped into three categories: a unique index, a primary key index, and a clustered index.

A unique index is an index that does not allow any of the two rows to have the same index value, that is, the value of the indexed column does not allow duplication.

A primary key index is a specific type of unique index that requires each value in the primary key to be unique.

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A database table can contain only one clustered index.

2. Index Creation sample

Suppose you have an employee information table that contains the work number, name, and age fields, and you want to create a unique index on the work Number field, and index the first name segment. as follows (based on Oracle database implementation):

CREATE TABLE Tb_employeeinfo
    
(
    
    employeeno         varchar) not       null       --employee work number
    
    EmployeeName    varchar not      null        --employee name
    
    employeeage       int                              null       --employee Age
    
);
    
Create unique index idx1_tb_employeeinfo on tb_employeeinfo (Employeeno);
    
Create INDEX Idx2_tb_employeeinfo on Tb_employeeinfo (EmployeeName);

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/project/

3. Principles for indexing

Consider establishing an index on a data column if the following conditions are true:

(1) A column in a datasheet that contains a large number of distinct values (you can establish a unique index on that column).

(2) A search keyword that is frequently used as a query statement (exact matching query or range query) (that is, a data table field that frequently appears in the WHERE clause of a statement such as Select).

(3) As the key column associated with the table with other tables.

4. Some Considerations for indexing

Once the data table has been indexed, we need to learn to use it flexibly. Based on the actual project experience of the author, the following considerations are noted for indexing:

(1) All statements that define the index of a datasheet are immediately after the table is defined for easy review and modification.

(2) If the table is rebuilt for some reason, it is important to rebuild the associated index.

(3) If you change the structure of the table, be sure to check the index associated with the table is correct; If you modify the name of the data table field that is indexed, be sure to change the name of the field corresponding to the index establishment statement at the same time.

(4) The order of the fields in the WHERE clause should be consistent with the order of the fields in the index.

(5) An index value that is too large (such as an index on a field of char (40)) can cause a large amount of I/O overhead (even more than the I/O overhead of the table scan). Therefore, try to use an integer index.

(6) If the value of a field may be empty, do not index the field.

(7) The number of large table indexes is no more than 5, and the maximum number of fields in the composite index is no more than 5.

(8) In database scripts, avoid using the or condition as much as possible, because the or does not use an index.

(9) Should avoid the establishment of the index in the frequently changed fields, in the frequent increase, delete, change the field does not build the index.

(10) Detailed list, log table, Operation record table, etc. (especially historical backup table) Time fields must be indexed, because such tables often rely on time fields for querying, statistics, backup and migration.

In the actual software project, the proper use of the index can improve the execution efficiency of the database statement, thus saving the resources of the database.