The index in MySQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.