Common MySQL Interview Questions

Publish on: 2025-03-24 Classify at: MySQL

Words: 455 Read:≈ 3mins Views:

Storage Engine Comparison

In InnoDB, primary key index and data are stored together, with the primary key index B+ tree containing complete data rows; in MyISAM, index and data are stored separately, with the B tree storing index values and physical addresses of rows
InnoDB supports ACID transactions, MyISAM does not
InnoDB supports row-level locking, MyISAM does not

What is Page Split

When inserting new data into a full index page (usually 16KB), the storage engine will split the current page into two pages to accommodate the new data

This process involves: allocating a new page, moving part of the data to the new page, and updating pointers of previous and next pages

Page splits cause performance degradation because they are relatively expensive operations

For pure auto-increment primary key sequential writes, InnoDB indeed prioritizes allocating new pages rather than splitting existing pages, which is an important reason for the performance advantage of auto-increment primary keys. However, in actual production environments, due to factors like concurrency, a small amount of page splitting may still occur.

Why Does It Happen?

Each node (page) must maintain order. When the primary key is non-auto-increment, we must ensure that all primary key values in the next data page are greater than the maximum primary key value in the previous data page.

Fill Factor

Defines the initial fill rate of pages (e.g., innodb_fill_factor=80 means pages are only filled to 80%).

Remaining space is used for subsequent insertions, reducing the probability of page splits.

Solution

1
2
3
4
5
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- Auto-increment primary key (clustered index)
  snowflake_id BIGINT UNIQUE,            -- Snowflake ID as business unique key
  ...
);

InnoDB’s Repeatable Read Isolation Level Solves Phantom Reads

For snapshot reads (normal SELECT): Through MVCC, at repeatable read isolation level, ReadView is created at the first query of the transaction
For current reads (SELECT…FOR UPDATE/LOCK IN SHARE MODE/INSERT/UPDATE/DELETE): Phantom reads are avoided through gap locks and Next-Key Locks.

SELECT * FROM t WHERE id > 100 FOR UPDATE will lock all gaps where id>100

Ruoke Zhang