Master MySQL Fundamentals: Transactions, Indexes, Locks, and Performance Optimization

What is MySQL?

MySQL is an open‑source relational database widely used in Java enterprise development. It runs on the default port 3306 and is licensed under GPL, allowing free download and modification.

Transaction Related

What is a transaction?

A transaction is a logical group of operations that must either all succeed or all fail.

Four ACID properties

Atomicity: the transaction is indivisible; it either completes fully or has no effect.

Consistency: data remains consistent before and after the 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 from another transaction.

Lost update: two transactions modify the same row, and the first update is overwritten.

Non‑repeatable read: a row read twice within a transaction yields different values because another transaction modified it.

Phantom read: a transaction re‑executes a query and sees newly inserted rows.

Difference between non‑repeatable read and phantom read

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

Isolation levels and MySQL default

The SQL standard defines four isolation levels:

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

READ‑COMMITTED: prevents dirty reads but still permits non‑repeatable and phantom reads.

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

SERIALIZABLE: the highest level, preventing all three anomalies.

InnoDB’s default isolation level is REPEATABLE‑READ . You can verify it with: SELECT @@tx_isolation; InnoDB uses the Next‑Key lock algorithm under REPEATABLE‑READ, which effectively eliminates phantom reads, making its behavior comparable to SERIALIZABLE.

Index Related

Content compiled from "Database Two Magic Weapons [Indexes and Locks]" by Java3y.

Why indexes improve query speed

MySQL stores records in pages; pages form a doubly linked list, and records within a page form a singly linked list. Without an index, a query must traverse these lists, resulting in O(n) complexity.

Using an index (implemented as a B+ tree) allows the engine to locate the target page via a binary search (≈O(log n)) instead of scanning the whole list.

Leftmost prefix principle

For a composite index (e.g., (name, city)), the query can use the index only if it filters the leftmost column(s) consecutively.

SELECT * FROM user WHERE name = xx AND city = xx;   -- can use index
SELECT * FROM user WHERE name = xx;               -- can use index
SELECT * FROM user WHERE city = xx;               -- cannot use index

Avoid redundant indexes

Redundant indexes (e.g., (name, city) and (name)) provide no extra benefit; prefer extending existing indexes instead of creating new ones. In MySQL 5.7+, you can detect redundant indexes via the schema_redundant_indexes view in the sys schema.

How to add indexes

Primary key:

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

Unique index: ALTER TABLE `table_name` ADD UNIQUE (`column`); Normal index:

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

Full‑text index:

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

Multi‑column index:

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

Storage Engine

Common commands

SHOW ENGINES;

The default engine in modern MySQL is InnoDB, which supports transactions, row‑level locking, foreign keys, and MVCC. MyISAM, the older default, offers high read performance but lacks transaction support and can’t recover safely after a crash.

Optimistic vs Pessimistic Locks

Pessimistic lock

Assumes conflicts will happen; acquires a lock before accessing data (e.g., synchronized or ReentrantLock in Java).

Optimistic lock

Assumes conflicts are rare; checks for modifications at commit time, often using a version field or CAS algorithm.

When to use each

Optimistic locks suit read‑heavy scenarios with few writes, while pessimistic locks are better for write‑intensive workloads.

Lock mechanisms and InnoDB lock algorithms

Record lock: locks a single row.

Gap lock: locks a range between rows (no row data).

Next‑key lock: locks a row plus the gap after it, preventing phantom reads.

Large‑table optimization

Limit query ranges (e.g., filter by date).

Read/write splitting: master for writes, slaves for reads.

Vertical partitioning: split a wide table into logical groups of columns.

Horizontal partitioning (sharding): split rows across multiple tables or databases.

These techniques reduce I/O, improve concurrency, and keep query performance acceptable even with millions of rows.