How to build indexes and maintain SQL databases_Chang ping China focuses on high quality website construction

An index is a table of correspondence between column values and record rows established in a certain order according to one or more columns in the table. In order to improve query performance, you can create a prime index.

If the SQL database is indexed and maintained

1. Create index

In SQL, CREATE INDEX statements are used to build indexes. The general format is as follows:

CREATE [UNIQUE] [CLUSTER] INDEX<index name>

ON<basic table name>(<column name>[<order>], [,<column name>[<order>]]...);

explain:

(1) UNIQUE stipulates that each index value of the index only corresponds to the unique record in the table.

(2) CLUSTER specifies that this index is a clustered index. The so-called clustered index means that the order of index entries is consistent with the physical order of records in the table. Clustered indexes are particularly effective for columns that often search for range values. After using the clustered index to find the row containing the first value, you can ensure that the row containing the subsequent index value is physically adjacent. Using clustered indexes can greatly improve query performance. Omitting CLUSTER means that the created index is a nonclustered index, which is similar to the index in a book. Data is stored in one place, the index is stored in another place, and the index has Website production The pointer points to the storage location of the data. The items in the index are stored in the order of the index key values, while the information in the table is stored in another order (which can be specified by a clustered index).

(3) <Order>: When building an index, the index table of the specified column name is ASC (ascending order) or DESC (descending order). If not specified, it defaults to ascending order.

(4) The arrangement of the index created by this statement is as follows: first, sort by the name and value of the first column; The records with the same column value will be sorted by the next column name.

[Example 3.8] Create a nonclustered index on the attribute column Sno of the Student table.
CREATE INDEX IDX_DNO_SNO
ON Student(Sno ASC);
Example 3.91 Create a clustered index on the attribute column Sname of the Student table.

CREATE CLUSTER INDEX IDX_SNAME ON Student(Sname ASC);

2. Delete index

Although indexes can improve query efficiency, too many or improper indexes will lead to system inefficiency. Every time a user adds an index to a table, the database needs to do more work. Too many indexes may even lead to index fragmentation, reducing system efficiency. Therefore, unnecessary indexes should be deleted in a timely manner. The format of deleted indexes is as follows:

DROP INDEX<index name>

Note: This statement will delete the defined index, and the description of the index in the data dictionary will also be deleted.
[Example 3.10] Delete the index IDX_DNO_SNO of the Student table.

DROP INDEX IDX DNO SNO;

If the SQL database is indexed and maintained

Date：2015-07-17 Source: Shangpin China Type: website encyclopedia
Word Size： small medium big

+86 10-60259772