25 Advanced MySQL Interview Questions for Senior Backend Developers

A **clustered index** determines the physical order of data in a table. Because of this, a table can only have one clustered index. In InnoDB, the primary key is the clustered index by default. If no primary key exists, InnoDB uses the first unique, non-nullable index.

A **non-clustered index** (or secondary index) has a separate structure from the data rows. It contains the index key values and a pointer to the corresponding data row (in InnoDB, this pointer is the primary key value). A table can have multiple non-clustered indexes.

Queries that use the clustered index for lookups are very fast, as the index entry directly contains the data.

The `EXPLAIN` statement shows how the MySQL query optimizer plans to execute a query. It’s crucial for identifying performance bottlenecks.

Key columns to analyze are:

• `type`: The join type. `const` and `ref` are good; `index` and `ALL` are often bad, indicating a full table or index scan.
• `key`: The index that MySQL decided to use. `NULL` means no index was used.
• `rows`: An estimate of the number of rows MySQL must examine to execute the query. Lower is better.
• `Extra`: Contains important information like `Using filesort` (bad, an expensive sort operation) or `Using index` (good, a covering index is being used).

Read the official documentation on `EXPLAIN`.

A covering index is a non-clustered index that includes all the columns required to satisfy a query, both in the `WHERE` clause and the `SELECT` list. When a covering index is used, MySQL can answer the query by only looking at the index, without having to read the actual data rows from the table. This is highly efficient and is indicated by `Using index` in the `EXPLAIN` output’s `Extra` column.

Learn about Covering Indexes from MySQL.

A composite index is an index on two or more columns. The order of the columns in the index definition is critical. MySQL can use a composite index for queries that test a prefix of the index columns. For an index on `(col1, col2, col3)`, it can be used for lookups on `(col1)`, `(col1, col2)`, and `(col1, col2, col3)`, but not for lookups on `(col2)` or `(col3)` alone.

The general rule is to place the column with the highest cardinality (most unique values) first to maximize the index’s effectiveness at filtering data.

A full table scan can be more efficient than an index lookup when the query needs to access a large percentage of the table’s rows. In this case, the overhead of random I/O from jumping between the index and the table data for each row can be much higher than the cost of sequentially reading the entire table. The MySQL optimizer will typically choose a full table scan automatically when it estimates that more than 20-30% of the rows will be accessed.

ACID is an acronym that describes the four key properties of a reliable transaction:

• Atomicity: All operations within a transaction are treated as a single, indivisible unit. Either all succeed, or none do.
• Consistency: The database must remain in a consistent state before and after the transaction. The transaction cannot violate integrity constraints.
• Isolation: Concurrent transactions should not interfere with each other. The effects of an incomplete transaction should not be visible to other transactions.
• Durability: Once a transaction is committed, its changes are permanent and will survive any subsequent system failure (e.g., power loss or crash).

• Read Uncommitted: The lowest level. Allows dirty reads, non-repeatable reads, and phantom reads.
• Read Committed: Prevents dirty reads. Still allows non-repeatable reads and phantom reads. This is the default for many databases like PostgreSQL and Oracle.
• Repeatable Read: Prevents dirty reads and non-repeatable reads. Still allows phantom reads. This is the default for MySQL’s InnoDB.
• Serializable: The highest level. Prevents all three phenomena by placing range locks. It effectively serializes transactions, which can significantly impact concurrency.

Phenomena: – Dirty Read: Reading uncommitted data from another transaction. – Non-Repeatable Read: The same row reads differently within a single transaction because another transaction modified it. – Phantom Read: New rows appear in a query within a single transaction because another transaction inserted them.

Read the official documentation on Transaction Isolation Levels.

A deadlock is a situation where two or more transactions are waiting for each other to release locks, resulting in a standstill. For example, Transaction A locks Row 1 and wants to lock Row 2, while Transaction B has locked Row 2 and wants to lock Row 1.

Prevention/Handling:

1. Consistent Lock Order: Ensure all transactions acquire locks on resources in the same order.
2. Short Transactions: Keep transactions as short as possible to minimize the time locks are held.
3. Deadlock Detection: InnoDB has a built-in mechanism that automatically detects deadlocks, aborts one of the transactions (the “victim”) by rolling it back, and allows the other to proceed. Applications should be designed to gracefully handle and potentially retry transactions that fail due to deadlocks.

These are locking mechanisms used by InnoDB’s default `REPEATABLE READ` isolation level to prevent phantom reads.

• Gap Lock: A lock on the “gap” between index records, preventing other transactions from inserting new data into that gap.
• Next-Key Lock: A combination of a record lock on an index record and a gap lock on the space before that index record. This effectively locks a record and the gap preceding it, preventing both updates to the record and insertions into the gap.

Read about InnoDB Locking Mechanisms.

• Locking: InnoDB uses row-level locking, which is excellent for concurrency. MyISAM uses table-level locking, which causes major contention in write-heavy applications.
• Transactions: InnoDB is fully ACID compliant and supports transactions. MyISAM does not support transactions.
• Durability: InnoDB uses a transaction log for crash recovery. MyISAM tables are more susceptible to corruption after a crash.
• Foreign Keys: InnoDB supports foreign key constraints; MyISAM does not.

InnoDB is the default and preferred engine for virtually all modern applications due to its support for transactions, row-level locking, and crash safety.

The InnoDB buffer pool is a crucial memory area on the server that caches table and index data. When data is accessed, it’s read from disk into the buffer pool. Subsequent reads of the same data can then be served directly from memory, which is orders of magnitude faster than reading from disk.

Properly sizing the buffer pool (`innodb_buffer_pool_size`) is the single most important configuration setting for a MySQL server. Ideally, it should be large enough to hold the most frequently accessed data (the “working set”) of your application.

Learn about the InnoDB Buffer Pool.

The query cache was a feature that stored the text of a `SELECT` statement along with its result set. If an identical query was received later, the result was returned from the cache.

It was removed because it suffered from major scalability issues. Any write operation to a table would invalidate *all* cached queries for that table, causing significant contention and cache invalidation overhead on write-heavy systems. Modern applications are better served by application-level caching (e.g., Redis) or a properly configured InnoDB buffer pool.

The binary log is a set of log files that contains a record of all data modification events (INSERTs, UPDATEs, DELETEs) as well as data definition changes (e.g., `CREATE TABLE`). Its primary purposes are:

• Replication: The binlog is read by replica (slave) servers to reproduce the changes made on the source (master) server.
• Point-in-Time Recovery: It allows for restoring a database from a backup and then “replaying” the binlog events to bring the database up to a specific point in time before a disaster.

Normalization is the process of organizing columns and tables in a relational database to minimize data redundancy and improve data integrity.

• First Normal Form (1NF): Ensures that table cells hold single values, and each record is unique.
• Second Normal Form (2NF): Must be in 1NF. All non-key attributes must be fully functionally dependent on the entire primary key (this applies to composite keys).
• Third Normal Form (3NF): Must be in 2NF. All attributes must be dependent only on the primary key, not on other non-key attributes (no transitive dependencies).

Denormalization is the process of intentionally introducing redundancy into a database to improve read performance. You would consider it for read-heavy applications or reporting systems where complex joins on a highly normalized schema are causing performance bottlenecks. For example, you might add a `product_name` column to an `order_items` table to avoid joining with the `products` table every time you display an order.

Partitioning is the process of dividing a large table into smaller, more manageable pieces (partitions) while still treating it as a single table logically. MySQL handles routing queries to the correct partition based on a partitioning key (e.g., partitioning a `sales` table by year).

Benefits:

• Improved Performance: Queries that include the partitioning key in the `WHERE` clause can benefit from “partition pruning,” where the database only scans the relevant partitions.
• Easier Maintenance: Operations like dropping old data can be done instantly by dropping a partition (`ALTER TABLE … DROP PARTITION`) instead of running a slow `DELETE` query.

Read the documentation on Partitioning.

• `DATETIME`: Stores date and time information. It has a range from ‘1000-01-01’ to ‘9999-12-31’. It is **not** timezone-aware; it stores the literal date and time you give it.
• `TIMESTAMP`: Also stores date and time. It converts the value from the current connection’s time zone to UTC for storage, and converts it back to the current time zone on retrieval. Its range is smaller (‘1970-01-01’ to ‘2038-01-19’).

Use `TIMESTAMP` when you need to handle time zones automatically. Use `DATETIME` for storing literal date/time values like birthdays.

1. The master server writes all data modification events to its binary log (binlog).
2. The slave server has an I/O thread that connects to the master and requests a dump of the binlog events.
3. The master’s dump thread sends the events to the slave’s I/O thread.
4. The slave’s I/O thread writes these events to its own log, called the relay log.
5. The slave’s SQL thread reads events from the relay log and executes them on the slave’s database, thereby replaying the changes from the master.

This allows for creating read replicas to scale read traffic or for failover purposes. Learn about MySQL Replication.

• Statement-Based Replication (SBR): Logs the exact SQL statements that modify data. It’s compact but can be non-deterministic with certain functions like `UUID()` or triggers.
• Row-Based Replication (RBR): Logs the changes to individual rows (before and after images). It’s deterministic and safer, but can be much more verbose. This is the default in modern MySQL versions.
• Mixed-Format Replication (MIXED): Uses SBR by default but automatically switches to RBR for statements that are non-deterministic, providing a balance.

Replication lag is the delay between when a change is committed on the master and when it is applied on the slave. Significant lag means the read replica is serving stale data.

You can monitor it by checking the `Seconds_Behind_Master` column in the output of the `SHOW SLAVE STATUS` command. For more robust monitoring, tools like Percona Toolkit’s `pt-heartbeat` can provide a more accurate measurement.

Both perform calculations across a set of rows. However, an **aggregate function** (like `SUM()` or `COUNT()`) collapses the rows into a single result row. A **window function** performs a calculation across a set of rows related to the current row (the “window frame”) but does not collapse them. It returns a value for each and every row. They are defined using the `OVER()` clause. This is useful for tasks like calculating running totals, ranking, or moving averages.

Read the documentation on Window Functions.

A CTE, defined with the `WITH` clause, allows you to create a temporary, named result set that exists only for the duration of a single query. They are useful for breaking down complex queries into simpler, more readable logical steps. Recursive CTEs can be used to traverse hierarchical or graph-like data structures, such as an organizational chart or a bill of materials.

MySQL has a native `JSON` data type and a set of built-in functions for working with it. You can create, parse, and search JSON documents directly within the database. Functions like `JSON_EXTRACT` (or the `->` shorthand operator) allow you to query specific values from a JSON object. You can even create indexes on expressions that extract values from a JSON column to speed up queries.

A stored procedure is a set of pre-compiled SQL statements stored in the database that can be executed as a single unit.

Pros:

• Reduces network traffic by avoiding sending complex queries over the wire.
• Encapsulates business logic within the database.
• Can be used to enforce security by granting `EXECUTE` permissions instead of direct table access.

Cons:

• Business logic becomes tied to the database vendor.
• Harder to version control, test, and debug compared to application code.
• Can lead to performance issues if overused or poorly written.

A trigger is a special type of stored procedure that automatically executes in response to a specific data modification event (`INSERT`, `UPDATE`, or `DELETE`) on a table. A valid use case is creating an audit trail: a trigger on the `employees` table could automatically insert a record into an `employees_audit` table whenever a row is updated, logging the old values, the new values, the user who made the change, and the timestamp.