The index in MySQL

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