MySQL Database Core Interview Preparation Guide

InnoDB implements MVCC by maintaining historical row versions inside undo log segments located within tablespaces or undo tablespaces, linking older versions via roll pointers. When a transaction executes a read operation under Repeatable Read, it establishes a Read View containing active transaction IDs. PostgreSQL, by contrast, stores historical row versions directly within the data table heap pages using xmin and xmax transaction ID headers on each tuple, requiring periodic vacuuming to clean up dead tuples. InnoDB relies on dedicated purge threads to remove obsolete undo log records asynchronously once no active read views require them, avoiding table heap bloat but introducing undo log growth risks under long-running transactions.

Next-key locks are a combination of a record lock on the index record itself and a gap lock on the gap preceding the index record. A gap lock is a lock on an index record interval, or a lock on the gap before the first or after the last index record. Their primary purpose is to prevent phantom reads by blocking concurrent transactions from inserting new rows into the locked interval. For example, SELECT * FROM users WHERE age > 30 FOR UPDATE acquires a next-key lock on existing records greater than 30 and a gap lock on the interval beyond to ensure concurrent transactions cannot insert new users matching that criteria before the transaction commits.

Statement-based replication (SBR) logs the exact SQL statements executed on the primary. While it consumes less disk space, it is prone to replication divergence if statements contain non-deterministic functions (e.g., NOW(), UUID(), or LIMIT clauses without ORDER BY). Row-based replication (RBR) records the actual changes made to individual table rows, guaranteeing absolute replication safety and consistency across replicas at the cost of significantly higher disk and network traffic. Mixed replication dynamically switches between Statement and Row formats depending on the nature of the query, using statement format for deterministic DDL/DML and row format for non-deterministic operations.

When MySQL restarts after a crash, the InnoDB storage engine initiates crash recovery by inspecting the checkpoint LSN (Log Sequence Number) recorded in the data files and comparing it with the latest LSN in the redo log files. InnoDB first performs roll-forward (REDO phase) by replaying all committed and uncommitted changes recorded in the redo log from the last checkpoint to restore the buffer pool and data pages to their exact state at the time of the crash. Subsequently, it performs the UNDO phase, utilizing undo log segments to roll back any transactions that were active and uncommitted when the crash occurred, ensuring atomicity and ACID durability guarantees.

InnoDB clustered indexes store data pages ordered sequentially by the primary key value in a B+Tree structure. Sequential integers ensure that new inserts append data to the end of the index tree, resulting in high page fill rates and minimal structural rebalancing. Random UUIDv4 values generate completely unpredictable insertion points across the entire keyspace. This causes frequent B+Tree page splits, low page fill efficiency (often around 50%), increased disk I/O, and severe fragmentation of the InnoDB buffer pool, degrading both write throughput and range scan performance.

High undo log generation is typically caused by long-running transactions (such as massive batch updates, unclosed reporting connections, or idle transactions holding open read views) that prevent InnoDB purge threads from cleaning up obsolete historical row versions. To diagnose this, inspect INFORMATION_SCHEMA.INNODB_TRX for long-running transaction IDs and check undo tablespace usage via SHOW TABLE STATUS or performance schema tables. Resolution involves terminating rogue client sessions using KILL CONNECTION, breaking large batch updates into smaller batched chunks, and ensuring application code properly closes transactions and database connections promptly.

Index Merge is an optimization strategy where the MySQL query optimizer uses multiple secondary indexes simultaneously for a single table by retrieving row identifier intersections, unions, or sort-unions. For example, querying WHERE col1 = A AND col2 = B might trigger an index merge if separate indexes exist on col1 and col2. However, index merge operations often indicate poor indexing strategy and can be less efficient than a single well-designed composite index (col1, col2), which allows the storage engine to traverse a unified B+Tree path directly without merging intermediate result sets.

Operating systems typically write data to disk in 4KB blocks, whereas InnoDB data pages are 16KB in size. If a power failure occurs midway through writing a 16KB InnoDB page to disk, a 'torn page' occurs where only a portion of the page is written, resulting in unrecoverable corruption. The Doublewrite Buffer prevents this by first writing 16KB pages sequentially to a contiguous disk space buffer before writing them to their ultimate data file locations. If a torn page crash occurs, InnoDB can recover the pristine page copy from the doublewrite buffer during startup validation.

MySQL 8.0 completely eliminated file-based metadata storage (.frm, .par, .TRN files) by introducing a transactional data dictionary stored inside InnoDB internal system tablespaces. This change enables atomic DDL operations (Atomic DDL), ensuring that schema changes are fully crash-safe and transactional. If an ALTER TABLE statement fails midway, the transaction rolls back cleanly without leaving orphaned files or corrupt metadata states, a limitation that plagued earlier MySQL 5.7 architectures.

Read-write splitting should be chosen when an application is read-heavy (e.g., 90% reads, 10% writes) and the primary database node's CPU or memory is exhausted by serving read queries. Adding read replicas behind a proxy like ProxySQL scales read capacity effectively without altering schema design. Database sharding, by contrast, becomes necessary when write volume, total storage size, or active working set exceeds the physical resource limits of a single master node (vertical scaling ceiling). Sharding partitions data horizontally across multiple independent instances, introducing distributed query complexity.

Metadata lock contention occurs when a long-running read query or transaction holds a shared metadata lock on a table, blocking an incoming DDL operation (like ALTER TABLE or DROP TABLE) that requires an exclusive metadata lock. Once the exclusive lock request queues up, all subsequent read and write queries attempting to access that table are blocked behind the DDL request, causing a sudden connection pile-up and application outage. Resolution requires identifying and terminating the blocking transaction ID via SHOW PROCESSLIST and INFORMATION_SCHEMA.METADATA_LOCKS.