MySQL InnoDB index data structure is B+ tree, primary key index leaf node value stores MySQL data row, common index leaf node value stores primary key value, this is the premise to understand clustered index and non-clustered index

What is a cluster index?

It is very simple to remember that: if you find the index, you find the data you need, then the index is the clustered index, so the primary key is the clustered index, and changing the clustered index is actually changing the primary key.

What is a non-clustered index?

Index storage is separated from data storage, that is, if an index is found but no data is found, the table needs to be queried again according to the value (primary key) on the index. Non-clustered indexes are also called secondary indexes.

Clustered index Clustered index clustered index

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated. Note the words highlighted, cluster index is a term for primary key

A case in point

Below we create a student table and do three queries to show when a clustered index is 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;

Copy the code

The first method is to directly obtain all field data according to the primary key query. In this case, the primary key is a clustered index, because the index leaf node corresponding to the primary key stores all field values with ID =1.

select * from student where id = 1

Copy the code

The second method is to query the number and name according to the number. The number itself is a unique index, but the queried column contains the student number and student name. When the numbered index is matched, the data of the node of the index is stored by the primary key ID, and it needs to be queried again according to the primary key ID

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

Copy the code

The third, we according to the number query number (someone will ask know the number but also query? Yes, you may need to verify that the number exists in the database), this type of query hits the number of the index, because the required data is the index, do not need to query back to the table, in this case no is the clustered index

select no from student where no = 'test'

Copy the code

conclusion

MySQL InnoDB must have a primary key, which is a cluster index. MySQL InnoDB must have a primary key, which is a cluster index. MySQL InnoDB must have a primary key, which is a cluster index. When the column of the SQL query is the index itself, we call this normal index a clustered index. MyisAM engine does not have clustered indexes.