How does MySql(InnoDB) update secondary non unique index?

Question

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.

Answer 1

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)}

Answer 2

In addition to what Nikita said,...

The "change buffer" is an optimization. Any changes (insert/delete/update) to non-unique indexes are thrown into the change buffer, which lives (logically) inside the buffer_pool, which is in RAM.

When you use a non-unique index, it will check both the change buffer and the real BTree for that index. Blocks of that BTree are cached in RAM, so a lookup might be entirely in RAM.

When you change a non-unique index -- either the column(s) of the index or the column(s) of the appended PK -- a short indication of such is added into the change buffer.

In the background, a process flushes the change buffer entries into the real BTrees for the indexes.

Hence, the Change buffer speeds up writes by not stopping to do all the work of updating the non-unique indexes. Note, however, the PRIMARY KEY (which is unique) and all UNIQUE indexes must be accessed (even if not yet cached in RAM) before the write can complete.

You can help by minimizing the number of unique indexes and, to a lesser degree, minimize the number of non-unique indexes on a table. Also, by not modifying indexed columns.

Think of your example as being implemented via two BTrees:

person_id, last_name, first_name
    Unique index on person_id,
    ordered by person_id
last_name, person_id
    Not unique on last_name,
    unique on the pair of columns
    ordered by the pair.

Any change to the value of person_id or last_name requires modifying both BTrees. Only the data BTree needs changing if first_name changes.

Insert and Delete require modification to both BTrees.

Nikita explained how to get from one BTree to the other -- either direction.