Question 1

What is the InnoDB buffer pool and why is it important for MySQL performance?

Accepted Answer

The InnoDB buffer pool is a memory cache that stores frequently accessed table and index data, reducing disk I/O. It is the single most important tuning parameter for InnoDB performance and should be set to 70-80% of available RAM on dedicated servers. A properly sized buffer pool can improve query speed by thousands of times by serving data from RAM instead of disk.

Question 2

What is the difference between InnoDB and MyISAM storage engines?

Accepted Answer

InnoDB is a transaction-safe storage engine with ACID compliance, row-level locking, crash recovery, and foreign key support, making it ideal for applications requiring data integrity. MyISAM uses table-level locking and lacks transactions, foreign keys, and automatic crash recovery, but is simpler and can be faster for read-heavy workloads. InnoDB is the default since MySQL 5.5 and recommended for most applications.

Question 3

How does InnoDB implement MVCC (Multi-Version Concurrency Control)?

Accepted Answer

InnoDB implements MVCC by storing multiple versions of each row using hidden system columns (DB_TRX_ID and DB_ROLL_PTR) and maintaining old versions in undo logs. Each transaction gets a read view that determines which row version to show based on transaction IDs. This allows concurrent reads without blocking writes and vice versa, providing excellent concurrency.

Question 4

What is the leftmost prefix rule for composite indexes?

Accepted Answer

The leftmost prefix rule states that a composite index can only be used if the query includes the leftmost column(s) in the index definition. For an index on (category, subcategory, brand), queries can use the index for category alone, category+subcategory, or all three columns, but not for subcategory or brand alone. This is critical for designing efficient multi-column indexes.

Question 5

What are the ACID properties and how does MySQL InnoDB implement them?

Accepted Answer

ACID stands for Atomicity (all-or-nothing transactions via undo log), Consistency (enforced through constraints and triggers), Isolation (using MVCC and locking to prevent transaction interference), and Durability (committed transactions survive crashes via redo log and double-write buffer). These four properties ensure reliable transaction processing in MySQL InnoDB.

Question 6

What transaction isolation levels does MySQL support?

Accepted Answer

MySQL supports four isolation levels: READ UNCOMMITTED (allows dirty reads), READ COMMITTED (prevents dirty reads), REPEATABLE READ (default, prevents dirty and non-repeatable reads), and SERIALIZABLE (full isolation preventing all anomalies). InnoDB uses MVCC for REPEATABLE READ and SERIALIZABLE, providing strong consistency with high concurrency.

Question 7

What is a phantom read and how does InnoDB prevent it?

Accepted Answer

A phantom read occurs when a transaction re-executes a query and finds different rows than before due to another transaction's INSERT or DELETE. InnoDB prevents phantom reads at REPEATABLE READ level using next-key locks, which combine row locks and gap locks to lock both existing rows and the gaps between them, preventing other transactions from inserting rows in the locked range.

Question 8

What is the difference between asynchronous and semi-synchronous replication?

Accepted Answer

Asynchronous replication is MySQL's default where the primary commits immediately without waiting for replicas, providing maximum write performance but risking data loss if the primary crashes. Semi-synchronous replication requires at least one replica to acknowledge receiving the transaction before the primary commits, ensuring better durability at the cost of slightly higher latency.

Question 9

What is the difference between statement-based and row-based replication?

Accepted Answer

Statement-based replication (SBR) logs the actual SQL statements and produces smaller logs but can cause inconsistencies with non-deterministic functions. Row-based replication (RBR) logs actual row changes, is completely deterministic and safer, but generates larger logs for bulk operations. MySQL's default MIXED format automatically switches between them based on statement type.

Question 10

What do the type values in EXPLAIN output mean?

Accepted Answer

The type column in EXPLAIN shows the access method used, from best to worst: const (single row by primary/unique key), eq_ref (one row per join), ref (non-unique index lookup), range (index range scan), index (full index scan), and ALL (full table scan). Lower rows examined and avoiding ALL for large tables indicates better query performance.

Question 11

How does the MySQL query optimizer work?

Accepted Answer

The MySQL query optimizer is a cost-based optimizer that analyzes multiple execution plans and selects the one with the lowest estimated cost. It uses table statistics, index cardinality, and data distribution to evaluate different access methods (table scans, index scans, joins) and considers factors like rows to examine, I/O operations, and CPU cost to choose the most efficient plan.

Question 12

What is the data dictionary in MySQL 8.0?

Accepted Answer

The data dictionary in MySQL 8.0 is a transactional, centralized metadata repository stored in InnoDB tables that replaced the old file-based system (.frm files). It stores information about database objects in a consistent, crash-safe format, enabling atomic DDL operations, better performance, simplified architecture, and making MySQL more reliable and easier to manage.

Question 13

What is SELECT FOR UPDATE and when should you use it?

Accepted Answer

SELECT FOR UPDATE acquires exclusive locks on selected rows, preventing other transactions from reading (with FOR UPDATE/SHARE) or modifying them until you commit. It's essential for preventing race conditions in scenarios like inventory management, seat booking, or financial transactions where you need to read data and ensure it doesn't change before your update.

Question 14

How do you monitor MySQL replication lag?

Accepted Answer

Replication lag is monitored using SHOW SLAVE STATUS (or SHOW REPLICA STATUS in MySQL 8.0.22+) by checking the Seconds_Behind_Master field, which measures the time difference between when a transaction was committed on the primary and the current time on the replica. Better metrics include GTID comparison and performance schema tables like replication_applier_status_by_worker for multi-threaded replication.

Question 15

What are common high availability solutions for MySQL?

Accepted Answer

Common MySQL HA solutions include MySQL Replication (async/semi-sync with manual or automated failover), MySQL Group Replication (multi-master with built-in conflict detection), MySQL InnoDB Cluster (integrated HA stack with automatic failover), Galera Cluster (synchronous multi-master), and orchestrator tools like Orchestrator or MHA for automating traditional replication failover. Choice depends on RPO/RTO requirements, consistency needs, and operational expertise.

MySQL Architecture(3 questions)

Explain the MySQL server architecture (connection layer, SQL layer, storage engine layer).

Explain the MySQL buffer pool and its role in performance.

What is the data dictionary in MySQL 8.0?

Storage Engines(3 questions)

What is the difference between InnoDB and MyISAM?

Why is InnoDB the default storage engine since MySQL 5.5?

What are the key features of InnoDB (ACID, row-level locking, foreign keys)?

InnoDB Internals(4 questions)

Explain the InnoDB storage architecture (tablespaces, pages, extents).

What is the InnoDB redo log and how does it work?

How does InnoDB implement MVCC?

How do you configure innodb_buffer_pool_size?

Indexing(6 questions)

What is a clustered index in InnoDB?

What is a secondary index and how does it differ from a clustered index?

What is a covering index and index-only scan?

How does column order affect composite index usage?

What is the leftmost prefix rule for composite indexes?

How do you analyze index usage with EXPLAIN?

Query Optimization(5 questions)

How does the MySQL query optimizer work?

How do you read and interpret EXPLAIN output?

What do the type values in EXPLAIN mean (ALL, index, range, ref, eq_ref, const)?

What is the Hash Join algorithm introduced in MySQL 8.0?

What is filesort and how do you avoid it?

Transactions and Locking(6 questions)

What are the ACID properties and how does MySQL implement them?

What transaction isolation levels does MySQL support?

What is a phantom read and how does InnoDB prevent it?

What is gap locking in InnoDB?

How do you detect and resolve deadlocks?

How do you use SELECT ... FOR UPDATE and SELECT ... FOR SHARE?

Replication(5 questions)

What is the difference between asynchronous and semi-synchronous replication?

Explain statement-based replication (SBR) vs row-based replication (RBR).

What is GTID (Global Transaction Identifier) replication?

What is the binary log and how does it enable replication?

How do you monitor replication status and lag?

High Availability(4 questions)

What are the common high availability solutions for MySQL?

What is MySQL Group Replication?

What is MySQL InnoDB Cluster and MySQL Router?

What is the difference between ProxySQL and MySQL Router?

Partitioning(4 questions)

What is table partitioning in MySQL?

What partitioning types does MySQL support (RANGE, LIST, HASH, KEY)?

What is partition pruning?

What are the limitations of partitioning in MySQL?

Backup and Recovery(4 questions)

What is the difference between logical and physical backups?

How do you use mysqldump for logical backups?

What is Percona XtraBackup and how does it work?

How do you perform point-in-time recovery?

Performance Tuning(5 questions)

What are the most important MySQL configuration parameters?

How do you tune innodb_buffer_pool_size?

How do you identify slow queries with the slow query log?

What is Performance Schema?

How do you use sys schema for performance analysis?

Security(4 questions)

How does MySQL authentication work?

What is caching_sha2_password?

How do you implement SSL/TLS connections?

What are the security best practices for MySQL in production?