Master MySQL Architecture: Storage Engines, Indexes, Transactions & Optimization

MySQL Architecture Overview

MySQL consists of a server layer that parses SQL, a query optimizer that chooses execution plans, and pluggable storage engines that handle data storage. The architecture separates connection handling, query processing, and physical storage.

Storage Engines

MySQL supports multiple storage engines; the most common are InnoDB and MyISAM. InnoDB provides row‑level locking, transactions, and MVCC, while MyISAM uses table‑level locks and is simpler.

Engine Layers

Connection layer – handles client connections, authentication, and SSL.

Service layer – parses, optimizes, caches queries, and provides built‑in functions.

Engine layer – interacts with the chosen storage engine.

Storage layer – stores data files on the underlying file system.

Indexes

Indexes are data structures that speed up data retrieval. MySQL primarily uses B+Tree indexes for most storage engines, with special types such as hash indexes (Memory, NDB) and full‑text indexes (MyISAM, InnoDB 5.6+). Primary keys are clustered in InnoDB, meaning the leaf nodes contain the full row data.

Index Types

Record (row) locks – lock individual rows.

Gap locks – lock the gaps between index entries to prevent phantom rows.

Next‑key locks – combine record and gap locks to avoid phantom reads.

Common index operations include CREATE INDEX, ALTER TABLE ADD INDEX, DROP INDEX, and SHOW INDEX.

Query Execution and Optimization

The optimizer evaluates statistics and possible indexes to produce an execution plan. Use EXPLAIN to view the plan, which shows id, select_type, table, type, possible_keys, key, key_len, rows, and Extra columns. Aim for ref or range access types and avoid ALL scans.

Typical optimization steps:

Identify slow queries via the slow‑query log.

Examine the execution plan.

Ensure appropriate indexes (use left‑most prefix for composite indexes).

Rewrite queries to enable index usage (avoid functions on indexed columns, use equality or range predicates).

Consider covering indexes ( using index) to avoid table lookups.

Transactions and Isolation

MySQL transactions follow the ACID properties. InnoDB’s default isolation level is REPEATABLE‑READ, which uses next‑key locking to prevent phantom reads. Other levels are READ‑UNCOMMITTED, READ‑COMMITTED, and SERIALIZABLE.

Key concepts:

Dirty read – possible only under READ‑UNCOMMITTED.

Non‑repeatable read – prevented by REPEATABLE‑READ.

Phantom read – also prevented by REPEATABLE‑READ in InnoDB.

Transaction logs consist of redo (write‑ahead) logs for durability and undo logs for rollback.

Locking Mechanisms

MySQL provides shared (read) and exclusive (write) locks. Lock granularity can be table, page, or row. InnoDB uses row‑level locks with intention locks (IS, IX) to coordinate with table locks.

Deadlocks occur when transactions wait for each other’s locks; InnoDB detects deadlocks and rolls back the victim transaction. Use SHOW ENGINE INNODB STATUS to investigate.

Performance Tuning

Key factors affecting performance include query design, indexing, schema design, and hardware resources. Use EXPLAIN, slow‑query log, and SHOW PROFILE to diagnose bottlenecks. Optimize by:

Choosing appropriate data types (smaller, NOT NULL when possible).

Applying the left‑most prefix rule for composite indexes.

Ensuring queries can use indexes (avoid functions on indexed columns, avoid leading wildcards in LIKE).

Using covering indexes to eliminate table lookups.

Adjusting buffer sizes (e.g., sort_buffer_size, innodb_buffer_pool_size).

Partitioning

MySQL partitioning splits a large table into multiple logical partitions stored in separate files. Supported types include RANGE, LIST, HASH, and KEY. Partitioning improves query performance for range‑based scans and simplifies data management, but it is limited to a single server.

Sharding (Horizontal Scaling)

When a single server cannot handle the load, data can be sharded across multiple databases (horizontal scaling). Sharding can be vertical (splitting columns into separate tables) or horizontal (splitting rows based on a key, e.g., user ID hash, date range). Sharding requires application‑level routing and introduces complexity for cross‑shard queries and distributed transactions.

Replication

MySQL replication copies changes from a master to one or more slaves using binary logs. The master writes changes to the binlog; slaves read the binlog, store it in a relay log, and replay the events. Replication is asynchronous and can cause replication lag.

Normalization

Database design should follow the first three normal forms: 1NF (atomic columns), 2NF (no partial dependency on a composite key), and 3NF (no transitive dependency on non‑key attributes).

Large‑Scale Deletion

Deleting millions of rows can be slow due to index maintenance. A common technique is to drop indexes, delete the rows, then recreate the indexes, which can be faster than a single massive DELETE operation.