The problem posed in the question can be broken down to solve it step by step. KEY and INDEX are synonymous in MySQL. The problem can be reduced to the difference between PRIMARY KEY, UNIQUE KEY and INDEX. And these three also happen to be the divisions of indexes, primary key indexes, unique indexes and ordinary indexes (INDEX).
Use INDEX to speed up reading data from the database.INDEX is usually added to columns that are JOIN, WHERE, and ORDER BY clauses.
When creating an index, you need to make sure that the index is applied as a condition of the SQL query statement (typically as a condition of the WHERE clause). In effect, the index is also a table that holds the primary key with the index fields and points to the records of the entity table.
Indexes also have their drawbacks: while indexes improve query speed, they slow down the speed of updating tables, such as INSERT, UPDATE, and DELETE to a table, because when updating a table, MySQL not only saves the data, but also saves the index file for a bit.
KEY vs INDEX in MySQL
KEY is usually a synonym for INDEX. PRIMARY KEY can also be specified as just KEY if the keyword attribute PRIMARY KEY is given in the column definition. this is done for compatibility with other database systems. PRIMARY KEY is a unique KEY, in which case all keyword columns must be defined as NOT NULL. if these columns are not explicitly defined as NOT NULL, MySQL should implicitly define them.
KEY that is, key value, is a part of the theory of the relational model, such as primary key (PRIMARY KEY), foreign key (Foreign KEY), etc., for data integrity check and uniqueness constraints. INDEX is at the implementation level, for example, you can index any column of the table, then when the indexed columns in the SQL statement in the Where condition, you can get fast data location, so that fast retrieval. As for UNIQUE INDEX, is just one of the INDEX, the establishment of a UNIQUE INDEX that the column data can not be duplicated, guess MySQL on the UNIQUE INDEX type of indexes can be done to further special optimization it.
So, when designing a table, KEY is just going to be at the model level, and when query optimization is needed, indexing the relevant columns is sufficient.
KEY
KEY is the physical structure of the database and contains two layers of meaning, one is constraint, which favors constraints and regulates the structural integrity of the database, and the other is index, which aids in querying.
-primary key has two roles, one is constraint, used to standardize a storage primary key and uniqueness, but also on this key to create an index;
The -unique key also serves two purposes, one is a constraint that standardizes the uniqueness of the data, but it also creates an index on the key;
-foreign key also serves two purposes, one is a constraint, which regulates the referential integrity of the data, but also creates an index on this key;
As you can see, key is both constraint and index.
INDEX
INDEX is also a physical structure of the database, but he only aids in the query, it will take up additional space at the time of creation. Indexes are categorized as prefix indexes, full-text indexes, and so on. Indexes are just indexes and do not constrain the behavior of indexed fields.
Difference between PRIMARY KEY and UNIQUE KEY
PRIMARY KEYs (primary keys) and UNIQUE KEYs (unique key constraints) are similar in that the PRIMARY KEY is usually a column, or possibly multiple columns, which usually determine a row of data. A table can have only one PRIMARY KEY, but it can have many UNIQUE KEYs. When a UNIQUE KEY is set on a column, no two rows can have the same data on that column. PRIMARY KEYs are not allowed to have NULL values, but UNIQUE KEYs are.
modification form`ALTER TABLE table_name ADD PRIMARY KEY(column_name, …)
To summarize, the same point:
-PRIMARY KEY and UNIQUE KEY are both used to ensure that the data on a column is prototypical.
-all can be added to one or more columns of the
Points of difference:
-There can be only one PRIMARY KEY for the same table, but multiple UNIQUE KEYs.
•
A PRIMARY KEY cannot have a null value, a UNIQUE KEY can. If 1 or more columns of the PRIMARY KEY are NULL, the column is automatically changed to NOT NULL when the PRIMARY KEY is added. The UNIQUE KEY has no requirement for the column is implemented by reference index, if the inserted values are all NULL, then according to the principle of indexing, the full NULL value is not recorded on the index, so the insertion of the full NULL value can be duplicated, while the other can not be inserted into the duplicate value.
alter table t add constraint uk_t_1 UNIQUE (a,b); insert into t (a ,b ) values (null,1); # non-repeatable insert into t (a ,b ) values (null,null);#Repeatable
In MySQL, for a PRIMARY KEY column, MySQL has automatically created a UNIQUE INDEX on it, and there is no need to repeatedly create an index on it.
An online explanation of PRIMARY KEY and UNIQUE INDEX:
Note that “PRIMARY” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) UNIQUE INDEX created (automatically).
Operation Index
Create index files that take up disk space for indexing.
CREATE INDEX IndexName ON mytable(username(length));
For CHAR and VARCHAR types, length can be less than the actual length of the field; for BLOB and TEXT types, length must be specified.
Indexes are created when the table is created:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(15) NOT NULL, INDEX [INDEXName] (username(length)) );
Delete Index
DROP INDEX [INDEXName] ON mytable;