Master MySQL Transactions, Indexes, and Locking: From ACID to Large‑Table Optimization

What is MySQL?

MySQL is a widely used open‑source relational database, default port 3306 . It is free, extensible, and trusted by large enterprises such as Alibaba.

Transaction Basics

A transaction is a logical unit of work that either fully succeeds or fully fails. The classic example is a money transfer: both the debit and credit must happen together, otherwise the system would be inconsistent.

ACID Properties

Atomicity: All operations in a transaction are indivisible; they either all happen or none happen.

Consistency: Data remains consistent before and after a transaction.

Isolation: Concurrent transactions do not interfere with each other.

Durability: Once committed, changes survive crashes.

Concurrency Problems

Dirty read: A transaction reads uncommitted changes made by another transaction.

Lost update: Two transactions read the same value, modify it, and the later commit overwrites the earlier one.

Non‑repeatable read: A transaction reads the same row twice and gets different values because another transaction modified it in between.

Phantom read: A transaction re‑executes a query and sees new rows inserted by another transaction.

Non‑repeatable read concerns updates, while phantom read concerns newly inserted or deleted rows.

Isolation Levels

SQL defines four isolation levels:

READ‑UNCOMMITTED: Allows dirty reads, non‑repeatable reads, and phantom reads.

READ‑COMMITTED: Prevents dirty reads but still allows non‑repeatable and phantom reads.

REPEATABLE‑READ: Prevents dirty and non‑repeatable reads; phantom reads may still occur.

SERIALIZABLE: Highest level; prevents all three anomalies.

In InnoDB the default isolation level is REPEATABLE‑READ . You can check it with: SELECT @@tx_isolation; InnoDB uses the Next‑Key Lock algorithm under REPEATABLE‑READ, which eliminates phantom reads, effectively providing SERIALIZABLE‑level guarantees without a performance penalty.

Most databases default to READ‑COMMITTED, but InnoDB’s REPEATABLE‑READ is safe and performant.

Index Fundamentals

Indexes speed up queries by turning unordered data into an ordered structure (typically a B+ tree). MySQL stores rows in pages; pages form a doubly‑linked list, and rows within a page form a singly‑linked list.

Without an index, a query like SELECT * FROM user WHERE id=8 must:

Traverse the page list to locate the target page (O(n)).

Scan the row list inside the page (O(n)).

This results in linear time complexity. An index provides a directory that can be binary‑searched, reducing the lookup to O(log n).

Left‑most Prefix Principle

For a composite index (e.g., (name, city)), the index can be used if the query predicates match the leftmost columns in order. Examples:

SELECT * FROM user WHERE name='xx' AND city='xx';  -- uses index

SELECT * FROM user WHERE name='xx';               -- uses index

SELECT * FROM user WHERE city='xx';               -- cannot use index

If the conditions are written in a different order, the optimizer reorders them to match the index.

Avoid Redundant Indexes

Redundant indexes (e.g., (name, city) and (name)) waste space. In MySQL 5.7+ you can query sys.schema_redundant_indexes to find them.

Creating Indexes

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

ALTER TABLE `table_name` ADD UNIQUE (`column`);

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`);

Storage Engines

List all engines with: SHOW ENGINES; The default engine in modern MySQL (5.7+) is InnoDB , which supports transactions. MyISAM, the older default, lacks transaction support and row‑level locking.

Key differences:

Lock granularity: MyISAM uses table‑level locks; InnoDB uses row‑level locks by default.

Transaction support: InnoDB is ACID‑compliant; MyISAM is not.

Foreign keys: Supported only by InnoDB.

MVCC: Available only in InnoDB for READ‑COMMITTED and REPEATABLE‑READ.

Lock Mechanisms in InnoDB

Record lock: Locks a single row.

Gap lock: Locks a range between rows (no rows themselves).

Next‑key lock: Locks the index record plus the gap, preventing phantom reads.

Gap locks can be disabled by using READ‑COMMITTED or setting innodb_locks_unsafe_for_binlog=1.

Large‑Table Optimization

Limit query scope: Always include a WHERE clause that restricts the data range.

Read/Write splitting: Use a master for writes and replicas for reads.

Vertical partitioning: Split a wide table into multiple tables (or databases) based on column groups.

Horizontal partitioning (sharding): Distribute rows across multiple tables or databases. Sharding improves storage capacity but complicates joins and transactions.

Sharding can be implemented via client‑side proxies (e.g., Sharding‑JDBC, TDDL) or middleware (e.g., Mycat, Atlas, DDB).