Architectural Overview

MySQL implements a four-layer architecture that handles operations from client connections down to physical file storage. Each layer addresses specific responsibilities, enabling MySQL to balance connectivity, query processing, data storage, and file management as distinct concerns.

Connection Layer

The connection layer handles incoming client requests and manages authentication. MySQL employs a thread-per-connection model where each client connection corresponds to a server-side thread. To avoid the overhead of creating and destroying threads for every connection, MySQL maintains a thread pool that reuses existing threads.

When a client connects, the server performs authentication through username/password verification or SSL certificates. Post-authentication, the server validates whether the client possesses the necessary privileges to execute specific queries. This layer follows the same architecture found in most database systems and network applications.

Service Layer

The service layer constitutes MySQL's core functionality, implementing features that operate across different storage engines. This layer contains several esential components:

Connection Pool: Maintains a cache of connections and manages thread reuse to improve performance under heavy load.

Management Services: Provides administrative utilities including backup/restore operations, replication controls, and cluster management.

SQL Interface: Receives SQL commands from clients and returns requested results.

Query Parser: Validates and parses incoming SQL statements, checking syntax correctness and user permissions.

Query Optimizer: Analyzes query execution plans and selects the most efficient approach for retrieving data.

Cache Layer: Stores query results for previously executed statements, enabling faster retrieval on subsequent identical queries.

Storage Engine Layer

Storage engines handle the actual storage and retrieval of data. MySQL uses a plugin-based architecture allowing different engines for different tables within the same database. The query execution engine communicates with storage engines through standardized APIs, abstracting implementation differences.

Key characteristics of MySQL's storage engine architecture:

• Engines are selected on a per-table basis, not per-database
• The server communicates with engines through defined interfaces
• Each table generates a .frm file containing structural metadata regardless of the chosen engine
• Custom engines can be developed by implementing the storage engine API

File System Layer

This layer manages physical storage operations, interacting directly with the operating system's file system. It handles data files, transaction logs, indexes, error logs, and query logs.

Storage Engine Types

CSV Engine

Stores data in comma-separated value format.

Characteristics:

• No index support available
• Requires columns to be defined as NOT NULL
• Cannot define auto-incrementing columns
• Directly editable through file manipulation
• Requires FLUSH TABLE command after file modifications

Use Cases:

• Quick data import/export operations
• Converting spreadsheets to CSV format

Archive Engine

Compresses data using zlib compression before storage.

Characteristics:

• Supports only INSERT and SELECT operations
• Indexes allowed exclusively on auto-increment columns
• Implements row-level locking
• No transaction support
• Minimal disk space consumption due to compression

Use Cases:

• Logging systems requiring long-term storage
• Sensor data aggregation from numerous devices

Memory Engine

Keeps all data in RAM for maximum I/O performance.

Characteristics:

• Supports HASH and B-tree indexes (HASH by default, providing O(1) lookup complexity)
• Fixed-length storage for all fields (VARCHAR(32) consumes space equal to CHAR(32))
• Prohibits BLOB and TEXT column types
• Uses table-level locking
• Data persists only during server runtime

Use Cases:

• Caching frequently accessed lookup data
• Temporary tables created by the optimizer during sorting or grouping operations (when data fits within the 16MB default limit)

MyISAM Engine

The default engine for MySQL versions prior to 5.5.

Characteristics:

• SELECT COUNT(*) executes without full table scans
• Stores data (.MYD) and indexes (.MYI) in separate files
• Table-level locking mechanism
• No transaction support

Use Cases:

• Optimizer-generated temporary tables for large result sets during sorting and grouping
• Reporting systems and data warehousing

InnoDB Engine

The default storage engine since MySQL 5.5.

Characteristics:

• Full ACID transaction support
• Row-level locking for concurrent access
• Clustered index organization based on primary keys
• Foreign key constraints for referential integrity

Storage Engine Comparison

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	Yes
Full-text search indexes	Yes	No	Yes	No	No
Geospatial data types	Yes	No	Yes	Yes	Yes
Geospatial indexing	Yes	No	Yes	No	No
Hash indexes	No	Yes	No	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support	Yes	Limited	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics	Yes	Yes	Yes	Yes	Yes

Physical Files

MySQL stores various file types on disk for persistence and operational requirements.

Log Files

Error Log: Records critical warnings, errors, and server startup/shutdown events. Enabled by default.

Configuration example:

SHOW VARIABLES LIKE '%log_error%';

General Query Log: Documents all client connections and executed statements.

SHOW VARIABLES LIKE '%general%';

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';

Binary Log: Records data-modifying statements in binary format, used for replication, point-in-time recovery, and auditing. Does not log SELECT or SHOW statements.

RESET MASTER;
PURGE MASTER LOGS;

SHOW VARIABLES LIKE '%log_bin%';
SHOW VARIABLES LIKE '%binlog%';
SHOW VARIABLES LIKE '%datadir%';
SHOW BINARY LOGS;
SHOW MASTER STATUS;

Slow Query Log: Tracks queries exceeding long_query_time threshold or those bypassing indexes.

SHOW VARIABLES LIKE '%slow_query%';

Key variables:

• slow_query_log: Enable/disable slow query logging
• slow_query_log_file: Output file location
• long_query_time: Threshold in seconds (default: 10)
• log_queries_not_using_indexes: Log queries without index usage

Configuration Files

On Linux systems, the main configuration file my.cnf typically resides in /etc/my.cnf or /etc/mysql/my.cnf. All MySQL configuration parameters are defined within this file.

Data Files

Retrieve the data directory location:

SHOW VARIABLES LIKE 'datadir';

File Types:

• db.opt: Stores the database's default character set and collation
• .frm files: Contains table structure definitions for every table
• .MYD files: MyISAM-specific data files
• .MYI files: MyISAM-specific index files
• .ibd files: InnoDB tables using独占表空间 (separate tablespace mode)
• ibdata files: InnoDB shared tablespace containing system tables and optionally user data
• ib_logfile0, ib_logfile1: Redo log files for transaction recovery

Process ID Files

The PID file stores the mysqld process identifier, similar to other Unix/Linux service processes.

Socket Files

Unix socket files enable local client connections without TCP/IP networking on Unix-like systems.