MySQL System Architecture

The MySQL server can be divided into four layers:

1. Connection Layer – Includes client connections, connection pooling (authentication, caching), and thread pool management.
2. Service Layer – Contains SQL interface, query parser, optimizer, caches/buffers, management tools, and built-in functions. It parses queries, creates parse trees, optimizes execution (e.g., join order, index usage), and serves cached results for SELECT statements.
3. Engine Layer – Pluggable storage engines that handle data storage and retrieval. The server communicates with engines via APIs.
4. Storage Layer – File system where data is physically stored, interacting with the storage engine.

Connection Layer

Handles client connections (local sockets or TCP/IP), authentication, and security. Thread pooling is used to manage client threads efficiently.

Service Layer

Core services include:

• SQL parsing and analysis
• Query optimization and caching
• Execution of cross-engine features (stored procedures, functions, triggers)

Engine Layer

Storage engines implement data storage and extraction. Each table uses a specific engine.

Storage Layer

Data is persisted on the file system and interacts with the storage engine.

MySQL Storage Engines

A storage engine is the technology that storees data, builds indexes, and supports queries/updates. Engines are table-level, not database-level; different tables in the same database can use differant engines.

MySQL uses a pluggable architecture, supporting multiple engines (unlike Oracle or SQL Server which have only one).

-- View supported engines
SHOW ENGINES;

-- View default storage engine
SHOW VARIABLES LIKE 'storage_engine';

By default, InnoDB is the engine, supporting transactions, row-level locking, and foreign keys.

Engine Comparison

Key features to compare: transaction safety, locking mechanism, foreign key support.

• InnoDB: Default engine; supports ACID transactions, row-level locks, foreign keys, crash recovery. Sacrifices some write performance and uses more disk space for data and indexes.
• MyISAM: Older engine; no transactions or foreign keys; faster reads and inserts.

InnoDB Details

Transaction Example

CREATE TABLE goods_innodb (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

START TRANSACTION;
INSERT INTO goods_innodb(id, name) VALUES(NULL, 'Meta20');
COMMIT;

Foreign Key Constraints

Foreign key creation requires that the parent table's referenced column has an index. The child table automatically creates an index for the foreign key.

CREATE TABLE country_innodb (
    country_id INT NOT NULL AUTO_INCREMENT,
    country_name VARCHAR(100) NOT NULL,
    PRIMARY KEY(country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE city_innodb (
    city_id INT NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(50) NOT NULL,
    country_id INT NOT NULL,
    PRIMARY KEY(city_id),
    INDEX idx_fk_country_id (country_id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_id)
        REFERENCES country_innodb(country_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Testing the constraints:

INSERT INTO country_innodb VALUES(NULL,'China'),(NULL,'America'),(NULL,'Japan');
INSERT INTO city_innodb VALUES(NULL,'Xian',1),(NULL,'NewYork',2),(NULL,'BeiJing',1);

-- This fails (RESTRICT):
DELETE FROM country_innodb WHERE country_id = 1;

-- This succeeds (CASCADE updates child rows):
UPDATE country_innodb SET country_id = 100 WHERE country_id = 1;

Table and Index Storage

InnoDB stores data and indexes in two ways:

• Shared tablespace: Uses one or more files defined by innodb_data_home_dir and innodb_data_file_path. Table structure in .frm file.
• Multiple tablespaces: Each table uses its own .ibd file for data and indexes; structure still in .frm.

MyISAM Details

• No transactions, no foreign keys.
• Faster read/write for simple operations.
• Each MyISAM table uses three files (all with the same base name):
  • .frm – table structure
  • .MYD – data
  • .MYI – indexes

CREATE TABLE goods_myisam (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Memory and Merge Engines

• Memory: Data stored in RAM, only .frm file on disk. Fast access, uses HASH index by default, but data is lost on server restart. Suitable for small, infrequently updated tables.
• Merge: A logical combination of structurally identical MyISAM tables. Operations on the MERGE table apply to the underlying MyISAM tables. Useful for breaking size limits and distributing data across disks.

Choosing the Right Engine