MySQL Core Concepts Interview Q&A: 10 Essential Topics (Part 2)

1. Explain InnoDB lock‑upgrade mechanism and why locks automatically upgrade to a table lock after exceeding 5,000 rows; its impact and how to avoid it

Lock‑upgrade mechanism:

InnoDB uses row‑level locks by default; when the number of locks exceeds innodb_row_locks (default 5000), it automatically upgrades to a table lock.

The threshold is controlled by the innodb_row_locks parameter.

Upgrade occurs when a transaction attempts to acquire a new lock and the current lock count is near the threshold.

Why lock upgrade is needed:

Reduces lock‑management overhead (structures, conflict checks).

Decreases lock conflicts and improves concurrency.

Protects system resources and prevents memory exhaustion caused by massive locks.

Impact:

After upgrade, other transactions cannot access the table, which may degrade performance.

Compared with the overhead of many row locks, upgrade usually improves overall performance.

Some transactions may experience longer wait times.

How to avoid:

Optimize queries to reduce the number of rows locked per transaction.

Split large transactions into smaller ones.

Use an appropriate isolation level (e.g., READ COMMITTED).

Adjust the innodb_row_locks parameter cautiously.

Extra note:

Lock upgrade balances performance and resource consumption.

In high‑concurrency scenarios, frequent upgrades may require isolation‑level tuning or query optimization.

Monitor lock upgrades with SHOW ENGINE INNODB STATUS.

2. Explain InnoDB Redo Log write process, why "write‑ahead log" is needed, and how its persistence strategy affects performance and reliability

Write‑Ahead Log (WAL) principle:

Log first, data later.

Ensures that committed transactions can be redone after a crash.

Guarantees durability.

Redo Log write flow:

On transaction commit, the redo log is written to the redo log buffer.

The buffer is flushed to disk under the control of innodb_flush_log_at_trx_commit.

Finally, data pages are written to the Buffer Pool.

Persistence strategies: innodb_flush_log_at_trx_commit=0: flush once per second – best performance, possible loss of up to 1 second of data. innodb_flush_log_at_trx_commit=1: flush on every commit – safest, worst performance. innodb_flush_log_at_trx_commit=2: write to log buffer on each commit, flush once per second – balances safety and performance.

Impact:

Strategy choice directly influences data safety and write performance.

Option 0 may lose recent data but offers highest throughput.

Option 1 guarantees durability at the cost of speed.

Option 2 provides a compromise, with a possible 1‑second data loss.

Extra note:

Redo Log’s pre‑write makes writes sequential, greatly improving performance.

Double Write Buffer supplements Redo Log to prevent partial‑write failures.

In high‑availability setups, option 1 is usually chosen to ensure consistency.

3. Explain InnoDB MVCC version‑chain management, how to avoid overly long version chains, and the working mechanism of the Purge thread

Version‑chain formation:

Each row stores DB_TRX_ID (last modifying transaction ID) and DB_ROLL_PTR (pointer to the Undo Log).

Every update creates a new version and links the old version via DB_ROLL_PTR, forming a chain: new → old → older …

Problems caused by long version chains:

Query performance degrades because more versions must be traversed.

Undo Log storage pressure increases.

Potential disk‑space exhaustion.

How to avoid long chains:

Purge thread: periodically cleans up obsolete versions.

Transaction‑ID wrap‑around handling (InnoDB uses a 64‑bit ID but recycles the lower 32 bits).

Commit transactions promptly to reduce chain length.

Purge thread operation:

Regularly scans version chains, identifies versions no longer needed by any active transaction.

Deletes those versions, freeing Undo Log space.

Number of purge threads is controlled by innodb_purge_threads.

Extra note:

If chains become too long, consider tuning innodb_max_purge_lag.

In high‑concurrency workloads, the purge thread can become a bottleneck.

Long‑running transactions block the purge thread, causing chain growth.

4. Explain InnoDB tablespace management and the differences and use cases of system tablespace, file‑per‑table tablespace, and general tablespace

System tablespace (ibdata1):

Stores InnoDB system data such as the data dictionary, Undo Log, and insert buffer.

Default file is ibdata1; multiple files can be configured.

Used for core system data, not for user tables.

File‑per‑table tablespace (.ibd files):

Each table gets its own .ibd file.

Enabled with innodb_file_per_table=ON.

Suitable when flexible management (backup, migration, space release on drop) is needed.

General tablespace (.ibd files created via CREATE TABLESPACE):

Created with CREATE TABLESPACE, can hold multiple tables.

Tables can be assigned to it with ALTER TABLE ... TABLESPACE.

Used when several tables should share a single tablespace for specific management needs.

Differences and selection:

System tablespace stores internal metadata; not for user data.

File‑per‑table gives each table its own file, simplifying management but may create many small files.

General tablespace reduces file count by sharing a file among tables, at the cost of less granular control.

Extra note:

File‑per‑table is recommended since MySQL 5.6 for better table‑level management.

General tablespace was introduced in MySQL 5.7 for more flexible space handling.

The size of the system tablespace affects startup time because the data dictionary must be loaded.

5. Explain InnoDB page‑split and merge mechanisms, why page splits degrade performance, and how to avoid them

Page‑split mechanism:

When inserting data exceeds the free space of a page, InnoDB splits the page into two.

Example: a 16 KB page that becomes larger than 16 KB after insertion is split into two pages.

Why page splits hurt performance:

Additional I/O is required to write the new page.

Index structures must be updated, adding overhead.

Fragmentation may increase, reducing query efficiency.

Page‑merge mechanism:

When a page’s utilization falls below a threshold (default 50 %), InnoDB merges it with an adjacent page.

Example: two pages each below 50 % utilization are merged into one.

How to avoid page splits:

Choose appropriate primary keys (e.g., auto‑increment IDs) to reduce split likelihood.

Pre‑allocate sufficient space when creating tables.

Prefer batch inserts over single‑row inserts.

Periodically run OPTIMIZE TABLE to reduce fragmentation.

Extra note:

Page splits are inherent to B+‑tree structures and cannot be fully eliminated.

Write‑performance impact is usually larger than read‑performance impact.

In write‑heavy workloads, frequent splits can cause noticeable slowdown.

6. How does MySQL's optimizer choose an execution plan, how is statistics collected, and why does the optimizer sometimes pick a sub‑optimal plan?

Execution‑plan selection process:

The optimizer parses the query.

It gathers statistics (row counts, index selectivity, etc.).

Multiple possible execution plans are generated.

Each plan is cost‑estimated (I/O, CPU, etc.).

The plan with the lowest estimated cost is chosen.

Statistics collection:

Executed via ANALYZE TABLE.

Also viewable with SHOW TABLE STATUS.

Statistics include row count, average row length, index cardinality, etc.

Reasons for wrong plan selection:

Out‑of‑date statistics.

Complex query predicates that the optimizer cannot evaluate accurately.

High index selectivity that is mis‑estimated.

Multiple joins where join order estimation fails.

How to resolve:

Regularly refresh statistics with ANALYZE TABLE.

Force a specific index using FORCE INDEX.

Inspect plans with EXPLAIN and rewrite queries for better optimizer understanding.

Extra note:

MySQL 8.0 introduced a smarter optimizer that handles complex queries better.

Accurate statistics are crucial for optimal plan selection.

In high‑concurrency scenarios, the optimizer may still mis‑estimate costs.

7. Explain InnoDB crash‑recovery process from MySQL startup to data restoration

Recovery steps:

Startup phase: MySQL checks data and log files.

Redo Log application: Replays committed transactions from the Redo Log.

Undo Log application: Rolls back uncommitted transactions.

Data consistency check: Ensures data files are consistent.

Detailed flow:

MySQL starts successfully and restores data to the state before the crash.

Undo Log rolls back all uncommitted transactions.

Data consistency is verified.

From the last checkpoint, all committed Redo Log entries are applied.

Ensures Redo Log and data files are in sync.

Scanning Redo Log finds the most recent checkpoint.

Key points:

Redo Log guarantees transaction durability.

Undo Log guarantees atomicity.

Checkpoint is critical for crash recovery.

Extra note:

Recovery time depends on Redo Log size and data volume.

Parameter innodb_fast_shutdown can affect recovery duration.

8. Explain the relationship between MySQL binary log (binlog) and Redo Log, why both are needed, and how they cooperate

Binlog purpose:

Records all data‑changing statements (INSERT, UPDATE, DELETE).

Used for master‑slave replication and data recovery.

Redo Log purpose:

Records physical modifications to data pages.

Used for crash recovery.

Why both are needed:

Binlog ensures replication consistency by recording logical operations.

Redo Log ensures crash‑recovery integrity by recording physical changes.

They complement each other to guarantee overall data consistency and reliability.

Cooperation mechanism:

During recovery, Redo Log replays committed transactions.

In replication, Binlog replays statements on the slave.

Prepare phase writes to Redo Log; Commit phase writes to Binlog (two‑phase commit).

Extra note:

Binlog + Redo Log collaboration is the foundation of MySQL high‑availability and data consistency.

Binlog is the source for replication; Redo Log is the basis for crash recovery.

The combined mechanism ensures reliability across various scenarios.

9. Explain InnoDB adaptive hash index, why it is needed, and how it works

Purpose of adaptive hash index:

Provides faster access for hot queries.

Accelerates equality searches via a hash table.

Why it is needed:

B+‑tree index requires tree traversal for equality queries.

Hash index can reduce lookup time from O(log N) to O(1).

Significantly improves performance for hotspot queries.

Working mechanism:

InnoDB automatically analyzes query patterns and detects hot queries.

Creates a hash index for those queries.

The hash index resides in the Buffer Pool and is not persisted to disk.

When query patterns change, the hash index is automatically adjusted.

Advantages:

No manual creation of hash indexes required.

Automatically adapts to workload.

Boosts performance of hotspot queries.

Extra note:

Adaptive hash index is an automatic optimization mechanism in InnoDB.

Applicable only to equality queries, not range queries.

In high‑concurrency scenarios, it can markedly improve performance.

Its activation can be controlled with innodb_adaptive_hash_index.

10. Explain MySQL memory‑management mechanisms, including Buffer Pool, connection pool, thread cache, and other memory areas, and how they affect performance

Buffer Pool:

Caches data pages to reduce disk I/O.

Managed by an LRU algorithm.

Size controlled by innodb_buffer_pool_size.

Larger pool → higher cache hit rate → better performance.

Connection pool:

Manages database connections to avoid frequent creation/destruction.

Maximum connections controlled by max_connections.

Pool size impacts concurrent performance.

Thread cache:

Caches threads to avoid repeated thread creation.

Size controlled by thread_cache_size.

Influences performance under high concurrency.

Other memory areas:

Query Cache (removed in MySQL 8.0) – caches query results.

Sort Buffer – used for sorting operations.

Join Buffer – used for join processing.

Thread Stack – stack space for each thread.

Memory‑management optimization:

Allocate Buffer Pool to about 50‑70 % of system memory.

Adjust connection pool size appropriately.

Monitor memory usage to avoid out‑of‑memory failures.

Use SHOW ENGINE INNODB STATUS to monitor memory consumption.