Mysql – How does MySql(InnoDB) update secondary non unique index

innodbMySQL

Let's say we have a table with the following structure:

CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE INDEX Lastname ON Persons (LastName);

Also, you can notice we have a secondary index called Lastname.

From the documentation of MySql(InnoDB engine), it is clear that the secondary index is referencing the primary key.
So my questions are:

  1. what happens if we update a primary key? How does InnoDB quickly update all secondary index entries? Is
    there a reference from the Primary key index page to all secondary index pages?
  2. what happens if we update the field "LastName"? As the secondary index is nonunique, how does MySQL search for the page to be updated in the secondary index? It looks like there should be a reference from PK index to all secondary indexes pages of the given row. But I can not find explicit mention of this in the documentation of MySql.

Best Answer

Every InnoDB table has a special index called the clustered index where the data for the rows is stored (in other words clustered index contains all columns of the table). By default InnoDB uses primary key as clustered index. So

  1. when you update a row in clustered index MySQL server uses other columns values to find corresponding records in non-clustered indexes and update them.
  2. All non-clustered indexes contain key columns of the clustered indexes. This way MySQL server can determine corresponding row in clustered index using these values.

Reference: 14.6.2.1 Clustered and Secondary Indexes (MySQL 5.7 Reference Manual)

Related Question