Master MySQL: Core Scenarios, Engines, and Performance Optimization

MySQL Main Use Cases

1. Web site systems

2. Log recording systems

3. Data warehouse systems

4. Embedded systems

MySQL Architecture Diagram

MySQL Storage Engine Overview

1) MyISAM Storage Engine – Each table is stored as three physical files: .frm (table definition), .MYD (data), and .MYI (index). Supports B‑Tree, R‑Tree, and Full‑text indexes.

2) InnoDB Storage Engine – Supports transactions, multi‑version concurrency control, improved locking, foreign keys, and other features.

3) NDBCluster Storage Engine – Used in MySQL Cluster distributed environments.

4) Merge Storage Engine – Provides a single access point for multiple MyISAM tables with identical structure.

5) Memory Storage Engine – Stores data in memory, supports Hash and B‑Tree indexes, does not persist data to disk.

6) BDB (BerkeleyDB) Storage Engine – Open‑source engine supporting transactions.

7) FEDERATED Storage Engine – Provides access to remote MySQL tables, similar to Oracle DBLINK.

8) ARCHIVE Storage Engine – Stores rarely accessed historical data in compressed form; supports only INSERT and SELECT.

9) BLACKHOLE Storage Engine – Discards all written data, similar to /dev/null.

10) CSV Storage Engine – Stores data in plain CSV files without indexes.

Factors Affecting MySQL Server Performance

1. Business Requirements – Example: real‑time forum post count updates.

2. System Architecture – Certain data types (binary multimedia, queue data, huge text) are unsuitable for storage in a relational database.

3. Query Impact – Example query to retrieve users of a group ordered by join time, with two solution alternatives:

<code>SELECT id,nick_name FROM user,user_group WHERE user_group.group_id=1 and user_group.user_id=user.id ORDER BY user_group.gmt_create desc limit 100,20;</code>

<code>SELECT user.id,user.nick_name FROM(SELECT user_id FROM user_group WHERE user_group.group_id=1 ORDER BY gmt_create desc limit 100,20) t,user WHERE t.user_id=user.id;</code>

The second solution processes far fewer rows and is therefore more efficient.

4. Schema Design – Reduce unnecessary database requests and avoid querying unused data.

5. Hardware Environment

• OLTP systems – Require large memory for caching active data, high IOPS, strong CPU, and adequate network bandwidth.

• OLAP systems – Require large storage capacity, high throughput disks, moderate CPU, and high‑speed inter‑node networking.

• High‑frequency small‑query systems (e.g., DNS) – Benefit from large memory caches, modest CPU, and standard network interfaces.

MySQL Locking Mechanisms

Row‑level, table‑level, and page‑level locks. MyISAM, Memory, CSV use table‑level locks; InnoDB and NDBCluster use row‑level locks; BerkeleyDB uses page‑level locks.

MySQL Query Optimization

Key principles:

Optimize the queries that need it most.

Identify performance bottlenecks.

Set clear optimization goals.

Start with EXPLAIN.

Use profiling.

Drive large result sets with small ones.

Perform sorting in indexes when possible.

Select only required columns.

Use effective filter conditions.

Avoid complex JOINs and subqueries.

Index types:

B‑Tree Index – Default for most engines; InnoDB actually uses B+Tree.

Hash Index – Used by Memory engine; supports only =, IN, <>; cannot avoid table scans.

Full‑text Index – Supported by MyISAM for CHAR, VARCHAR, TEXT columns.

Guidelines for creating indexes: index frequently used columns, avoid indexing low‑cardinality or highly volatile columns, do not index columns not appearing in WHERE clauses.

JOIN optimization: minimize nested loop iterations, ensure join columns are indexed, adjust join buffer when needed.

ORDER BY / GROUP BY optimization: leverage existing indexes to avoid explicit sorting; increase max_length_for_sort_data and sort_buffer_size where appropriate.

Schema Design Performance Optimization

Techniques include reducing database accesses, eliminating unnecessary data queries, moderate redundancy to reduce JOINs, vertical and horizontal partitioning, appropriate data types, and real‑time summary tables.

Vertical partitioning splits tables across servers, simplifying rules but may require application‑level joins and can complicate transactions.

Horizontal partitioning distributes rows based on a rule, allowing full database‑level joins and better scalability, though partition rules can be complex.

Scalability Design – Data Sharding

Vertical sharding separates tables/modules; horizontal sharding separates rows. Each has distinct advantages and drawbacks regarding join handling, performance, and maintenance complexity.

Scalability Design – Cache and Search Utilization

Introduce caching layers (Redis, Memcached) to reduce database load and improve performance. Use search engines (Lucene, Solr, Elasticsearch) for full‑text and fuzzy searches that caches cannot handle.