Master MySQL Fundamentals: Transactions, Indexes, and Performance Optimization

What Is MySQL?

MySQL is an open‑source relational database widely used in Java enterprise development because it is free, extensible, and stable; Alibaba’s database systems rely heavily on it. The default port is 3306 .

Transaction Basics

What Is a Transaction?

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

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; 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: New rows appear in a range query after another transaction inserts them.

Non‑repeatable read differs from phantom read: the former involves updates, the latter involves inserts or deletes.

Isolation Levels

SQL defines four isolation levels:

READ‑UNCOMMITTED: Allows dirty reads, non‑repeatable reads, and phantom 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: Prevents all three anomalies.

MySQL InnoDB’s 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 safety.

Index Fundamentals

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

Without an index, a query like SELECT * FROM user WHERE indexname='xxx' must scan pages (O(n)). With an index, the engine uses a page directory and binary search to locate the target page (≈O(log n)).

Left‑most 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';   -- uses index

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

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

If the conditions are reversed ( city='xx' AND name='xx'), the optimizer reorders them to match the index.

Avoid Redundant Indexes

Redundant indexes (e.g., (name, city) and (name)) provide no extra benefit; extend existing indexes instead of creating new ones. In MySQL 5.7+, you can query schema_redundant_indexes in the sys schema to find them.

Adding 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: SHOW ENGINES; The default engine in MySQL 5.7+ is InnoDB , the only transactional engine. You can view the default engine with: SHOW VARIABLES LIKE '%storage_engine%'; Check a specific table’s engine: SHOW TABLE STATUS LIKE 'table_name'; MyISAM (default before 5.5) offers high performance and features like full‑text search but lacks transactions, row‑level locking, and crash recovery. InnoDB supports transactions, row‑level locks, foreign keys, MVCC, and is generally preferred unless the workload is read‑only and you accept the risk of data loss.

Optimistic vs. Pessimistic Locks

Pessimistic Lock

Assumes conflicts are likely; locks data before use (e.g., synchronized, ReentrantLock in Java).

Optimistic Lock

Assumes conflicts are rare; checks for modifications at commit time using version numbers or CAS. Java’s java.util.concurrent.atomic package provides CAS‑based atomic classes.

When to Use

Optimistic locks suit read‑heavy scenarios; pessimistic locks suit write‑heavy scenarios.

Optimistic Lock Implementations

Version‑field method: Store a version column; update only if the version matches.

CAS algorithm: Compare the current value (V) with an expected value (A); if equal, replace with new value (B) atomically. Issues include the ABA problem, high CPU cost for long spin loops, and limitation to a single variable.

Java’s AtomicStampedReference mitigates ABA by attaching a stamp.

InnoDB Lock Algorithms

Record lock: Locks a single row.

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

Next‑key lock: Locks a row plus its gap, preventing phantom reads.

Gap locks can be disabled by using READ‑COMMITTED isolation or setting innodb_locks_unsafe_for_binlog=1, except when foreign‑key checks or unique constraints apply.

Large‑Table Optimization

Limit Data Scope: Avoid queries without WHERE clauses; restrict time ranges, etc.

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

Vertical Partitioning: Split a wide table into multiple tables based on column relevance, reducing I/O per query.

Horizontal Partitioning (Sharding): Distribute rows across multiple tables or databases when a table exceeds millions of rows. Prefer sharding at the database level to improve concurrency.

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

Conclusion

Understanding MySQL’s transaction model, indexing strategies, storage engines, and lock mechanisms is essential for writing efficient, reliable applications and for tackling performance challenges in large‑scale databases.