Fundamental MySQL Concepts

1. What are the primary storage engines in MySQL and their core differences?
2. Compare InnoDB and MyISAM in terms of transaction support and locking.
3. Define the ACID properties within the context of MySQL transactions.
4. Explain the phenomena of Dirty Reads, Non-repeatable Reads, and Phantom Reads.
5. Which transaction isolation levels does MySQL support?
6. What is the default isolation level in InnoDB?
7. Distinguish between CHAR and VARCHAR data types.
8. Analyze the performence implications of VARCHAR(128) vs VARCHAR(256).
9. How do DECIMAL, FLOAT, and DOUBLE differ in precision and storage?
10. Compare DATETIME and TIMESTAMP regarding timezone handling.
11. What are the distinctions between Primary Keys, Unique Keys, and standard Indexes?
12. Categorize the different types of indexes available in MySQL.
13. Define Clustered and Non-Clustered (Secondary) indexes.
14. Explain the Leftmost Prefix Rule for composite indexes.
15. What is a Covering Index and why is it beneficial?
16. Describe Index Condition Pushdown (ICP).
17. How do you interpret the key, rows, and extra columns in an EXPLAIN output?
18. List the different join types in EXPLAIN (e.g., const, eq_ref, ref, range, index, ALL).
19. What is the logical exectuion order of a SQL statement?
20. Differentiate between WHERE, HAVING, and ON clauses.
21. Compare INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
22. Explain the difference between UNION and UNION ALL.
23. Analyze the performance of EXISTS versus IN.
24. Contrast DELETE, TRUNCATE, and DROP operations.
25. How do you optimize LIMIT queries on large datasets?
26. Compare the behavior of COUNT(*), COUNT(1), and COUNT(column_name).
27. What is the difference between NULL and an empty string ('')?

Locking Mechanisms in MySQL

Global Locks

Global locks affect the entire database instance. The command FLUSH TABLES WITH READ LOCK (FTWRL) is the standard method to make the database read-only. This is typically used during physical backups to ensure consistency, though modern tools like mysqldump with --single-transaction are preferred for InnoDB to avoid blocking DML.

Table-Level Locks

Table locks are applied to the entire table structure. While older engines like MyISAM rely on these, InnoDB uses them during specific DDL operations (e.g., ALTER TABLE). Table locks are simple and low-overhead but severely limit concurrency in high-write environments.

Row-Level Locks

Specific to the InnoDB engine, row-level locks provide granular control:

• Shared Locks (S): Allows multiple transactions to read a row but prevents modifications. Triggered via SELECT ... LOCK IN SHARE MODE.
• Exclusive Locks (X): Prevents other transactions from reading or writing. Triggered by DML (INSERT, UPDATE, DELETE) or SELECT ... FOR UPDATE.

Advanced InnoDB Locking

• Gap Locks: Locks the gap between index records to prevent inserts, solving the Phantom Read problem in REPEATABLE READ isolation.
• Next-Key Locks: A combination of a record lock and a gap lock.
• Intention Locks (IS/IX): Table-level locks that indicate which type of row lock a transaction will acquire later, allowing the engine to quickly check for table-level conflicts without scanning every row.

Query Optimization and Performance Tuning

Optimization Vector	Methodology
Indexing	Implement covering indexes; remove redundant indexes; utilize composite indexes for frequent filters.
Query Refactoring	Eliminate SELECT *; replace subqueries with JOINs where appropriate; simplify complex logic.
Data Partitioning	Separate historical data; use vertical or horizontal splitting for massive tables.
Pagination	Use index-based pagination (where ID > last_id) instead of high OFFSET values.
Lock Contention	Keep transactions short; optimize WHERE clauses to use indexes and avoid full table locks.
Buffer Optimization	Use ANALYZE TABLE to refresh optimizer statistics for better plan selection.

Enabling the Slow Query Log

To enable logging temporarily via the CLI:

-- Activate the log
SET GLOBAL slow_query_log = 'ON';

-- Set execution threshold to 2 seconds
SET GLOBAL long_query_time = 2;

-- Identify log location
SHOW VARIABLES LIKE 'slow_query_log_file';

For persistent configuration in my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 2
log_queries_not_using_indexes = 1

Deep Dive into EXPLAIN Extra Fields

• Using index: The query is satisfied using only the index tree without needing to look up the actual data rows (Covering Index).
• Using where: The server filters rows after the storage engine retrieves them.
• Using temporary: MySQL creates an internal temporary table to hold results, often seen in complex GROUP BY or DISTINCT queries.
• Using filesort: Indicates an external sort is required because the index could not satisfy the ORDER BY clause.
• Using index condition: The server pushes the filtering logic down to the storage engine (ICP) to reduce row reads.
• Using MRR: Multi-Range Read optimization; reads data in batches sorted by primary key to improve I/O efficiency.

Optimizing Large Scale Deletions

Deleting millions of rows directly can cause several issues:

1. Buffer Pool Saturation: The massive cleanup fills the buffer pool with modified pages, potentially evicting hot data.
2. Log Pressure: Excessive Redo and Undo log generation increases I/O overhead.
3. Locking Issues: Range-based deletes can trigger extensive Next-Key locks, blocking concurrent inserts.
4. Fragmentation: Large deletes leave "holes" in data files, leading to storage inefficiency.

Best Practice: Execute deletions in smaller batches (e.g., 5000 rows at a time) with a sleep interval, or create a new table with the required data and swap it with the old one.

Storage Structure: B+Tree vs B-Tree

InnoDB utilizes B+Trees because:

• Lower Tree Height: Internal nodes only store keys, allowing for a higher branching factor and fewer I/O operations.
• Range Scans: Leaf nodes are linked in a doubly-linked list, making sequential access and range queries highly efficient.
• Clustered Storage: The leaf nodes contain the actual row data, reducing the need for secondary lookups.

Managing Large Table Schema Changes

For tables with millions of rows, adding indexes can be disruptive.

Online DDL (MySQL 5.6+):

ALTER TABLE production_orders 
ADD INDEX idx_status_created (order_status, created_at),
ALGORITHM = INPLACE, 
LOCK = NONE;

• INPLACE: Performs the operation without rebuilding the entire table where possible.
• INSTANT (8.0+): Only modifies metadata; completion is nearly immediate.
• LOCK=NONE: Allows concurrent DML (Reads and Writes) during the index build.

Analyzing Lock Contention and Blocking

Use the following queries to diagnose performance bottlenecks caused by locking:

-- View current lock waits
SELECT * FROM sys.innodb_lock_waits;

-- Inspect active transactions
SELECT * FROM information_schema.innodb_trx 
WHERE trx_state = 'LOCK WAIT';

-- Identify the blocking process
SHOW PROCESSLIST;

Maintenance Operations

• ANALYZE TABLE: Updates the distribution of index keys, helping the optimizer choose better paths.
• OPTIMIZE TABLE: Reorganizes the physical storage of table data and indexes to reduce fragmentation and reclaim space. In InnoDB, this maps to ALTER TABLE ... FORCE.

Architectural and Design Considerations

1. What are the pros and cons of using Auto-increment IDs vs UUIDs as primary keys?
2. How does MySQL handle Distributed Transactions (XA)?
3. Explain the Write-Ahead Logging (WAL) mechanism (Redo Log).
4. How does Multi-Version Concurrency Control (MVCC) work in InnoDB?
5. Describe the Binlog synchronization process between Master and Replica.
6. Compare Row-based, Statement-based, and Mixed logging formats.
7. How do you resolve Master-Slave replication lag?
8. When should you choose Vertical Sharding over Horizontal Sharding?
9. Explain the purpose of the Buffer Pool and the LRU eviction policy.
10. What is the Doublewrite Buffer and how does it prevent partial page writes?