The difference between key and index in MySQL

MySQL's key and index are somewhat confusing, which actually examines the understanding of the database architecture. 1 key is the physical structure of the database, it contains two layers of meaning, one is the constraint (which is biased to the structural integrity of the database), and the second is the index (auxiliary query).  Includes primary key, unique key, foreign key, and more. Primary key has two functions, one is the binding function (constraint), used to standardize a storage primary key and uniqueness, but also set up an index on this key, the unique key also has two functions, one is the binding effect (constraint), Specification data uniqueness, but also set up an index on this key; foreign key also has two functions, one is the binding function (constraint), the canonical data referential integrity, but also on this key set up an index; MySQL key is both constraint and index meaning, which may differ from other database performance.  (at least a foreign key is established on Oracle, and index is not automatically established), so there are several ways to create a key: (1) At the field level as key, such as CREATE TABLE T (ID int not null primary key);  (2) established in CONSTRAINT mode at the table level, such as CREATE TABLE T (id int, CONSTRAINT pk_t_id PRIMARY key (ID));  (3) Set at the table level in key mode, such as CREATE TABLE T (ID int, primary key (ID)); Other key creation is similar, but in that way, both the constraint and index are established, but the index is using this constraint or key. 2 index is the physical structure of the database, it is only a secondary query, it is created in a separate table space (InnoDB table space in MySQL) in a similar directory of the structure of the storage.  Index to classify, divided into prefix index, full-text index, etc., so the index is just an index, it does not constrain the behavior of indexed fields (that is what key to do). For example, CREATE TABLE t (ID int, index inx_tx_id (ID)); 3 Final Explanation: (1) We say index classification, sub-primary key index, unique index, ordinary index (this is the pure index), and is based on whether to consider index as key. For example, CREATE TABLE t (ID int, unique index inx_tx_id (id)); --index as a key use (2) The most important is that, no matter how it is described, the understanding that index is a purely index, or as a key, there will be two kinds of meaning or play two roles.

http://blog.csdn.net/javaACMer/article/details/10517721

The difference between key and index in MySQL