For select statements like

select * from table where indexed_col='abc';

sql would go to index table and fetch row address and return required. But what about dml statements like

update table set indexed_col='abc' where condition;

how is consistency ensured between table and indexed table?

CodePudding user response：

MySQL updates all indexes that include the column indexed_col when you update that column.

It must update the clustered index (aka primary key index) of course. The columns of a given row are stored in the leaf node of the clustered index, so changing any column requires updating that index.

Other unique indexes on the same table that include the updated column must be updated at the same time. In other words, when you execute UPDATE, the time it takes for that statement to execute includes the time to update the clustered index and also any unique indexes that include the column indexed_col.

For non-unique secondary indexes, MySQL's default storage engine InnoDB uses a change buffer, which is a temporary list of pending changes to those indexes. When you update the column indexed_col, MySQL adds an entry to the change buffer for each index that column is part of. Then it calls the execution of your UPDATE done, and returns control to the client.

If you subsequently do a SELECT query as you show, MySQL checks both the table's indexes and the change buffer. Any entries in the change buffer for that index take priority, since they reflect more recent changes.

Eventually, MySQL runs a background thread to merge change buffer entries into the respective index.