Comparison between unique indexes and primary key indexes

Unique Index
The unique index does not allow two rows to have the same index value.
If duplicate key values exist in existing data, most databases do not allow you to save the newly created unique index with the table. When the new data duplicates the key value in the table, the database also rejects the data. For example, if a unique index is created on the employee's last name (lname) column in the employee table, all employees cannot have the same surname.

Primary Key Index
Primary key indexes are special types of unique indexes.
A database table usually has a column or a combination of columns. Its values are used to 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 the database relationship diagram will automatically create a primary key index, which is a special type of unique index. The primary key 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 data.

Their comparison:
(1) The primary key, unique constraint, Oracle, SQL Server, MySQL, and so on will automatically create a unique index;
(2) The primary key does not necessarily contain only one field. Therefore, it is necessary to create a unique index for one of the primary key fields;
(3) The primary key can be used as an external key and the unique index cannot be used;
(4) The primary key cannot be blank, and the unique index can be;
(5) The primary key is also a combination of multiple fields;
(6) The primary key is different from the unique index:
A. It has the not null attribute;
B. Each table can have only one.

 

1 , Primary key

Primary Key ID. A primary key is both a constraint and an index, and is also used for the key value cached by the object.

2 , Index

* Child tables that combine or Reference Links (when the data volume is large ), A non-clustered index (such as the product ID field in the order list and the order Id field in the order list) needs to be created on the columns of the associated primary table)

* The index key size cannot exceed 900 bytes. When the list size exceeds 900 bytes or the sum of several columns exceeds 900 bytes, the database reports an error.

* If a table has a large number of indexes, the performance of the insert, update, and delete statements will be affected, because when the data in the table is changed, all indexes must be adjusted as appropriate. Avoid too many indexes on frequently updated tables and keep the indexes narrow. That is to say, as few columns as possible.

* Create an index for the predicates that are frequently used for query, such as the Code and name used for the drop-down refer to quick search. The like Condition Statement in the query SQL statement referenced in the existing drop-down list on the platform must be replaced with no prefix wildcard. We also need to pay attention to the index design of the order by and group by predicates. The predicates of order by and group by need to be sorted. In some cases, we need to create an index for the predicates of order by and group, this avoids sorting during query.

* For columns with basically repeated content, such as only 1 and 0, index creation is prohibited. Because the index has very low selectivity, the Optimizer may be misled to make incorrect choices under specific circumstances, the query speed is greatly reduced.

 

* When an Index consists of multiple columns, you should note that columns with strong selectivity are placed in front. Performance may vary by order of magnitude only.

* Indexing small tables may not produce optimization results, because the Query Optimizer may take longer to traverse the index used for data search than to perform a simple table scan, when designing indexes, consider the table size. Do not create indexes for tables with a maximum of 100 records. We do not recommend that you create indexes for frequently-operated fractional scales (no more than 5000 records)

Additional reading:

Http://topic.csdn.net/t/20041108/15/3532352.html