Master MySQL Fundamentals: Transactions, Indexes, 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.

What is a transaction?

A transaction is a logical group of operations that must either all succeed or all fail, ensuring data consistency (e.g., a money transfer).

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.

Problems caused by concurrent transactions

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

Lost update : two transactions read the same value and both modify it, causing one update to be lost.

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

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

Isolation levels

SQL defines four isolation levels:

READ‑UNCOMMITTED : allows dirty reads.

READ‑COMMITTED : prevents dirty reads but allows 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.

The InnoDB storage engine’s default isolation level is REPEATABLE‑READ , which uses the Next‑Key lock algorithm to avoid phantom reads, effectively providing SERIALIZABLE‑level guarantees.

SELECT @@tx_isolation;

Why indexes improve query speed

MySQL stores records in pages linked as a double‑linked list; each page contains a singly‑linked list of records. Without an index, a query must scan pages and records sequentially (O(n)). An index (implemented as a B+‑tree) provides a sorted directory that allows binary search (≈O(log n)) to locate the target page quickly.

Example steps for SELECT * FROM user WHERE id=8 with an index:

Use the index’s directory to locate the page containing the target record (binary search).

Within the page, locate the record directly.

Index usage rules

MySQL can use a composite index only for the leftmost prefix of its columns. For a composite index on (name, city), queries filtering by name alone or name and city can use the index, but a query filtering only by city cannot.

When the query order matches the index order, the optimizer rewrites the condition to use the index.

Avoid redundant indexes

Redundant indexes (e.g., (name, city) and (name)) waste space; prefer extending existing indexes instead of creating new ones. In MySQL 5.7+, the schema_redundant_indexes view in the sys schema helps identify 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

Common commands:

SHOW ENGINES;

SHOW VARIABLES LIKE '%storage_engine%';

SHOW TABLE STATUS LIKE 'table_name';

InnoDB is the default engine from MySQL 5.5 onward and the only transactional engine; MyISAM is non‑transactional and uses table‑level locks.

MyISAM vs. InnoDB

Lock granularity: MyISAM uses table‑level locks; InnoDB supports row‑level locks (default) and table‑level locks.

Transactions: MyISAM has none; InnoDB provides ACID‑compliant transactions, crash recovery, and foreign‑key support.

MVCC: only InnoDB supports multi‑version concurrency control.

Lock mechanisms in InnoDB

Record lock: locks a single row.

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

Next‑key lock: locks a range plus the index record, preventing phantom reads.

Optimizing large tables

Limit data range in queries (e.g., filter by recent dates).

Read/write splitting: use a master for writes and replicas for reads.

Vertical partitioning: split a wide table into multiple tables based on column relevance.

Horizontal partitioning (sharding): split rows across multiple tables or databases to keep each table size manageable.

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