MySQL High‑Performance Architecture and Optimization Techniques

Structure and History

Four transaction isolation levels are described: READ UNCOMMITTED (dirty read), READ COMMITTED (prevents dirty reads but can cause non‑repeatable reads and phantom reads), REPEATABLE READ (prevents dirty and non‑repeatable reads, implemented in MySQL via MVCC), and SERIALIZABLE (the strictest level with read locks).

Locks can be implicit (released at COMMIT or ROLLBACK) or explicit (e.g., SELECT … LOCK IN SHARE MODE). MySQL’s InnoDB engine uses MVCC by storing a creation version and a deletion version for each row; SELECT sees rows with version numbers earlier than the current transaction, INSERT stores the current version, DELETE marks a row with the current version, and UPDATE inserts a new version while marking the old one as deleted.

InnoDB also uses gap locks to prevent phantom reads. MyISAM supports concurrent inserts and delayed key writes (DELAY_KEY_WRITE).

Schema and Data‑Type Optimization

Avoid NULL columns when possible. Integer types: TINYINT uses 1 byte, BIGINT uses 8 bytes; the display width only affects client output. DECIMAL stores exact fixed‑point numbers (e.g., currency). VARCHAR saves space compared to fixed‑length CHAR but adds 1‑2 bytes for length and can cause fragmentation on UPDATE. CHAR pads to the defined length and strips trailing spaces on storage.

BLOB and TEXT are for large binary or text data. ENUM stores values compactly as integers. BIT can store up to 64 binary flags, but displays as decimal.

Counter‑table optimization: instead of a single row with an auto‑increment column, create a table with many rows and update a random row, then aggregate with SUM(a).

Efficient ALTER TABLE: create a new table with CREATE TABLE new LIKE old, modify its structure, lock the old table with FLUSH TABLES WITH READ LOCK;, replace the .frm file, then UNLOCK TABLES. For MyISAM, temporarily disable indexes with ALTER TABLE tbl DISABLE KEYS and re‑enable after bulk load.

Creating High‑Performance Indexes

B‑Tree indexes store values in order; they are ideal for range queries but require the leftmost column of a composite index to be used. R‑Tree indexes (supported by MyISAM) are suitable for spatial data. Prefix indexes reduce size for long VARCHAR columns; determine the optimal prefix with

SELECT COUNT(*) AS cnt, LEFT(col,3) AS pref FROM tbl GROUP BY pref ORDER BY cnt;

and create with ALTER TABLE t ADD KEY(col(7));. Multi‑column index order matters: place the most selective column first.

Clustered indexes store the row data in the leaf pages; InnoDB’s secondary index leaf nodes contain the primary key value, not a row pointer. Using UUIDs as primary keys harms performance because they are not sequential.

A covering index satisfies a query entirely from the index; the EXPLAIN output shows Using index. Deferred joins can improve pagination performance.

Query Performance Optimization

Key metrics: response time, rows examined vs rows returned, and access type shown by EXPLAIN. For large DELETE/UPDATE operations, process rows in batches with LIMIT to avoid long locks.

MySQL client‑server communication is half‑duplex; the server often holds the query lock until all data is sent, making max_allowed_packet important.

The query execution flow: client‑server communication → query cache → parser & pre‑processor → optimizer → statistics → execution engine → result return. Images illustrate the steps.

Thread states (e.g., Sleep, Query, Locked, Copying to tmp table, Sorting result, Sending data) help diagnose bottlenecks.

IN is implemented as a sorted list with binary search (O(log n)), often faster than multiple OR conditions.

JOINs are executed as nested‑loop joins; subqueries are materialized as temporary tables (derived tables). The optimizer chooses join order based on estimated row counts.

ORDER BY can trigger file‑sort ( Using filesort) or temporary tables ( Using temporary) depending on whether the ordering columns come from the first table.

Hints such as HIGH_PRIORITY, LOW_PRIORITY, DELAYED, FOR UPDATE, and LOCK IN SHARE MODE influence scheduling and locking.

MySQL Advanced Features

Partitioned tables are logical tables composed of multiple physical sub‑tables; each partition has its own index and no global index. Example creation:

CREATE TABLE sales (date DATETIME NOT NULL, …) ENGINE=InnoDB PARTITION BY RANGE(YEAR(date)) (PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_catchall VALUES LESS THAN MAXVALUE);

Query cache can speed up reads but becomes a contention point under high concurrency; it is often disabled in production.

Server and Hardware Optimization

Data and indexes are stored in large files; using RAID improves reliability and performance. RAID 0 offers highest speed without redundancy, RAID 1 provides mirroring, RAID 5 uses parity for cost‑effective redundancy, RAID 10 combines striping and mirroring, and RAID 15 (striped RAID 5) suits very large datasets.

Monitoring tools such as vmstat and iostat help assess I/O performance.

Replication

MySQL supports statement‑based and row‑based replication; both record changes in the binary log on the master and replay them on slaves, resulting in asynchronous data consistency.

Scalable MySQL

Scale up by adding more powerful hardware; scale out by using replication, sharding, or partitioning. For globally unique IDs, combine auto_increment_increment and auto_increment_offset, use a dedicated ID generator, or employ UUID/UUID_SHORT with caution (UUIDs are large and not ideal for InnoDB primary keys).

Multiple instances or clustering can further increase capacity, while archiving or cleaning unused data reduces storage pressure.

Backup and Recovery

Hot backup allows backups without downtime. Logical backups use mysqldump or SELECT … INTO OUTFILE; physical backups copy raw files or use filesystem snapshots. Incremental and differential backups capture changes since the last full backup. Backup scripts should automate these processes.

Source: http://ffffound.sinaapp.com/2015/04/08/高性能mysql/