Knowledge point difference and lack of stickers: index classification

First, Introduction
　　Tonight, suddenly asked about the index classification of the problem, incredibly, there is a short-circuit of the brain, did not answer up, it is not supposed to collect information, is Licicunzhao.
Second, the advantages of establishing an index：
1. Greatly accelerate the retrieval speed of data;
2. Create a unique index that guarantees the uniqueness of each row of data in a database table;
3. Accelerate the connection between tables and tables;
4. Use the group and sort clauses toData Retrieval, you can significantly reduce the time to group and sort in the query.

Third, the index type:
Depending on the functionality of the database, you canDatabase DesignFour kinds of indexes are created in the converter:Unique indexNon -Unique index, primary key index, andClustered Index。 AlthoughUnique indexHelps locate information, but for best performance results, it is recommended that you use a primary key orUNIQUE Constraint。

Unique index:
A unique index is one that does not allow any two rows to have the same index value. When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table.
For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

Non-unique index:
A non-unique index is a relative unique index that allows any two rows to have an index of the same index value. When duplicate key values exist in the existing data, the database is allowed to save the newly created index with the table. The database does not prevent the addition of new data that will create duplicate key values in the table.

Primary KEY index:
Database tables often have one column or column combination whose values uniquely identify each row in the table.   This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique.
When a primary key index is used in a query, it also allows quick access to the data.

Clustered Index(also calledClustered Index）：
InClustered Index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values.   A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table and the value of the keyLogical Orderdoes not match.
AndNonclustered IndexesClustered indexes often provide faster data access than the other.

Knowledge point difference and lack of stickers: index classification