What is a non-clustered index in mysql?

The non-clustered index in mysql means that the storage of the index and the storage of the data are separated. That is to say, if the index is found but the data is not found, you need to query the table again based on the value on the index, that is, the primary key. Non-clustered index Cluster index is also called auxiliary index.

The non-clustered index in mysql is:

The storage of index and the storage of data are separated, that is, That is to say, if the index is found but the data is not found, you need to query the table again based on the value (primary key) on the index. Non-clustered indexes are also called auxiliary indexes.

An example

Below we create a student table and do three queries to illustrate when it is a clustered index and when it is not.

create table student (
    id bigint,
    no varchar(20) ,
    name varchar(20) ,
    address varchar(20) ,
    PRIMARY KEY (`branch_id`) USING BTREE,
    UNIQUE KEY `idx_no` (`no`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Related learning recommendations: mysql video tutorial

The first method is to directly query and obtain all field data based on the primary key. At this time, the primary key is the cluster Cluster index, because the index leaf node corresponding to the primary key stores the values ​​of all fields with id=1.

select * from student where id = 1

The second method is to query the number and name based on the number. The number itself is a unique index, but the queried column contains the student number and student name. When the number index is hit, the The data of the indexed node is stored in the primary key ID, and it needs to be queried again based on the primary key ID, so no is not a clustered index in this query

select no,name from student where no = 'test'

The third way, we query based on the number Number (someone may ask if you know the number but still need to query? Yes, you may need to verify whether the number exists in the database). When this kind of query hits the number index, the number is returned directly, because the required data is the index, no need Back to the table query, in this scenario no is the clustered index

select no from student where no = 'test'

Summary:

The primary key must be the clustered index, There must be a primary key in MySQL's InnoDB. Even if the developer does not set it manually, a unique index will be used. If there is no unique index, the id of a row inside the database will be used as the primary key index. Other ordinary indexes need to be distinguished from SQL Scenario, when the column of the SQL query is the index itself, we call it the ordinary index in this scenario, which can also be called a clustered index. The MyisAM engine does not have a clustered index.

The above is the detailed content of What is a non-clustered index in mysql?. For more information, please follow other related articles on the PHP Chinese website!