As we all know, the index can speed up the query, similar to reading when the first look at the directory and then turn to the specific page.
One, clustered and nonclustered indexes
1. Clustered index
Clustered index A table can only exist one.
The clustered index is physically contiguous (if the data structure is btree, then it exists directly on the leaf node), so querying a range of data can be fairly fast.
2. Nonclustered indexes
A table of nonclustered indexes can be shaken.
A nonclustered index is logically contiguous (if the data structure is btree, then the location information of the leaves node is stored), so
The InnoDB engine in MySQL supports clustered indexes, and the primary key index in MySQL is the clustered index.
The index in MySQL is divided into: Normal index, primary key index, unique index, federated index. The BTREE data structure is used by default when creating indexes in MySQL. INNODB data structures that do not support hashing
1. Primary KEY index
In MySQL, the primary key index is the clustered index by default. Primary key indexes are not repeatable.
Primary (Key)
ALTER TABLE test add primary key (ID)
2. General Index
ALTER TABLE test add index index_test (name)
if it is a character type, the specified index field length is supported
ALTER TABLE test add index Index_test (name (10))
3. Unique index
The field value for a unique index cannot be duplicated and can be empty.
ALTER TABLE test add index Index_test ()
4. Federated Indexing
A federated index is a normal index or a unique index that consists of multiple fields.
ALTER TABLE test Add index Index_test (id,name) using Btree
Related SQL statements
Index of the query table
Show index from Test
Indexes in MySQL