Wednesday, 27 November 2019

MySQL Clustered Index

Summary: in this tutorial, you will learn about the MySQL clustered index and how clustered indexes are managed in InnoDB tables.

What is a clustered index

Typically, an index is a separate data structure such as B-Tree that stores the key values used for faster lookups.
A clustered index, on the other hand, is actually the table. It is an index that enforces the ordering on the rows of the table physically.
Once a clustered index is created, all rows in the table will be stored according to the key columns used to create the clustered index.
Because a clustered index store the rows in sorted order, each table have only one clustered index.

MySQL clustered indexes on InnoDB tables

Each InnoDB table requires a clustered index. The clustered index helps an InnoDB table optimize data manipulations such as SELECTINSERTUPDATE and DELETE.
When you define a primary key for an InnoDB table, MySQL uses the primary key as the clustered index.
If you do not have a primary key for a table, MySQL will search for the first UNIQUE index where all the key columns are NOT NULL and use this UNIQUE index as the clustered index.
In case the InnoDB table has no primary key or suitable UNIQUE index, MySQL internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column which contains the row ID values.
As the result, each InnoDB table always has one and only one clustered index.
All indexes other than the clustered index are the non-clustered indexes or secondary indexes. In InnoDB tales, each record in the secondary index contains the primary key columns for the row as well as the columns specified in the non-clustered index. MySQL uses this primary key value for the row lookups in the clustered index.
Therefore, it is advantageous to have a short primary key otherwise the secondary indexes will use more space. Typically, the auto-increment integer column is used for the primary key column.

0 comments:

Post a Comment