Let you know software development in advance (32): database indexing

Part 1 Database SQL language

Database index

 

Indexes play an important role in the database. They are a structure for sorting values of one or more columns in a database table. Correct use of indexes allows you to quickly access specific information in database tables, thus improving the execution efficiency of database scripts.

 

1. Index classification

A database table often has a combination of columns or columns. The value uniquely identifies each row in the table. This column is called the primary key of the table.

In databases, indexes are classified into three types:Unique Index,Primary Key IndexAndClustered Index.

A unique index is an index that does not allow any two rows to have the same index value. That is, the value of an index Column cannot be repeated.

A primary key index is a specific type of unique index. Each value in the primary key must be unique.

In the clustered index, the physical order of the row in the table is the same as the logic (INDEX) Order of the key value. A database table can only contain one clustered index.

 

2. index creation example

Assume that an employee information table contains the employee ID, name, and age fields. You must create a unique index on the employee ID field and create an index on the name field. As follows (based on Oracle Database ):

Create table tb_employeeinfo

(

Employeeno varchar (20) not null, -- employee ID

Employeename varchar (20) 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 );

 

3. indexing principles

You can create an index on a data column in the following cases:

(1) A data table contains a large number of non-duplicate columns (you can create a unique index on this column ).

(2) frequently used as a search keyword for a query statement (exact match query or range query) (that is, a data table field that frequently appears in the where clause of a select or other statement ).

(3) as a key column associated with other tables.

 

4. Notes about Indexes

After a data table is indexed, we must learn how to use it flexibly. Based on the author's actual project experience, the notes for indexing are as follows:

(1) All statements defining the index of a data table follow the definition of the table for convenient query and modification.

(2) If the table is rebuilt for some reason, the related indexes must be rebuilt.

(3) If the table structure is changed, check whether the indexes related to the table are correct. If you modify the field name of the data table that creates the index, you must also change the field name at the index creation statement.

(4) The field order in the where clause should be consistent with that in the index.

(5) The index value is too large (for example, creating an index on a char (40) field ), it causes a large amount of I/O overhead (or even exceeds the I/O overhead of table scanning ). Therefore, try to use an integer index.

(6) If the value of a field may be blank, do not create an index on the field.

(7) The number of indexes in a large table cannot exceed 5, and the maximum number of fields in a composite index cannot exceed 5.

(8) avoid using the or condition in database scripts because or does not use the index.

(9) You should avoid creating indexes on fields that are frequently changed, and not on fields that are frequently added, deleted, or modified.

(10) An index must be added to the time fields of the detail table, log table, and operation record table (especially the historical backup table, because such tables often rely on time fields for query, statistics, backup, and migration.

 

In actual software projects, the proper use of indexes can greatly improve the execution efficiency of database statements, thus saving database resources.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)