Master MySQL: Transactions, Indexes, Storage Engines, and Performance Optimization

What is MySQL?

MySQL is an open‑source relational database widely used in Java enterprise development; it runs under the GPL license, defaults to port 3306, and is known for stability because of extensive use by Alibaba.

Transaction Basics

A transaction is a logical unit of work that must be either fully committed or fully rolled back. A money‑transfer example shows why atomicity is required.

ACID Properties

Atomicity : all operations succeed or none do.

Consistency : data remains consistent before and after the transaction.

Isolation : concurrent transactions do not interfere.

Durability : once committed, changes survive crashes.

Concurrency Problems

Dirty read : reading uncommitted changes.

Lost update : two transactions overwrite each other’s changes.

Non‑repeatable read : same row returns different values within one transaction.

Phantom read : new rows appear in a later query.

Isolation Levels

SQL defines four levels: READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, SERIALIZABLE. MySQL InnoDB’s default is REPEATABLE‑READ, which uses Next‑Key locks to prevent phantom reads, effectively providing SERIALIZABLE isolation for most workloads.

Check the current level with SELECT @@tx_isolation;.

Index Fundamentals

MySQL stores rows in pages that form a doubly‑linked list; each page contains a singly‑linked list of records. Without an index, a query scans pages linearly (O(n)). An index (implemented as a B+‑tree) provides a page directory that can be binary‑searched (≈O(log n)).

Typical index operations:

Locate the page containing the target record via the page directory.

Search the page’s record list (or use the B+‑tree leaf) for the exact row.

Left‑most Prefix Rule

For a composite index (name,city), MySQL can use the index when the query predicates match the leftmost columns. Queries on only city cannot use the index.

Example:

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

Avoid Redundant Indexes

Indexes that duplicate the functionality of another (e.g., (name,city) and (name)) are redundant. MySQL 5.7+ can list them via SELECT * FROM sys.schema_redundant_indexes;.

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 (col1, col2, col3);

Storage Engines

Show available engines with: SHOW ENGINES; InnoDB is the default since MySQL 5.7 and is the only transactional engine; MyISAM is non‑transactional and uses table‑level locks.

Check the default engine with SHOW VARIABLES LIKE '%storage_engine%'; and a table’s engine with SHOW TABLE STATUS LIKE 'table_name';.

Locking Differences

MyISAM: table‑level lock only.

InnoDB: row‑level lock (default) and table‑level lock.

InnoDB lock algorithms: Record lock, Gap lock, Next‑Key lock.

Optimistic vs Pessimistic Locks

Pessimistic locks (e.g., synchronized, ReentrantLock) assume conflicts and acquire locks before access. Optimistic locks assume no conflict and verify a version or use CAS at commit time.

Version‑based example: a version column is incremented on each update; an update succeeds only if the stored version matches the current version.

CAS suffers from the ABA problem; Java’s AtomicStampedReference mitigates it. CAS also incurs CPU cost when spinning.

Large‑Table Optimization

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

Read/write splitting with master‑slave replication.

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

Horizontal partitioning (sharding): split rows across tables or databases; sharding can be implemented via client‑side libraries (Sharding‑JDBC, TDDL) or middleware (Mycat, Atlas, DDB).

Horizontal sharding improves storage capacity but makes cross‑shard transactions and joins difficult.