MySQL Internal Architecture: Service Layer, Storage Engine and File System Coverage

MySQL Architecture Explained

MySQL’s architecture can be roughly divided into four main layers from top to bottom:

1. Network Connection Layer

The network connection layer is responsible for handling communication between clients and MySQL servers:

  • Connection management: Handle client connection requests, including authentication
  • Thread management: Use thread pool technology to handle concurrent connections
  • Protocol support: Support multiple connection protocols (TCP/IP, Unix socket, etc.)
  • Connection cache: Maintain connection state information

2. Service Layer

The service layer is the core processing layer of MySQL:

  • SQL interface: Receive SQL statements, provide DDL, DML, stored procedure and other interfaces
  • Parser: Perform lexical and syntax analysis, generate parse tree
  • Query optimizer: Optimize SQL and select optimal execution plan
  • Cache management: Manage query cache

3. Storage Engine Layer

The storage engine layer is responsible for data storage and retrieval:

  • InnoDB: Supports transactions, row-level locking, foreign keys, default engine after MySQL 5.5
  • MyISAM: Does not support transactions but has high query performance
  • Memory: Data stored in memory, fast but data is lost after service restart

Main Storage Engine Comparison

FeatureInnoDBMyISAM
Default VersionAfter MySQL 5.5Before MySQL 5.5
Transaction SupportSupportedNot supported
Lock LevelRow-level lockTable-level lock
Foreign Key SupportSupportedNot supported

4. System File Layer

The system file layer is responsible for persisting data to physical storage devices:

  • Data files: .ibd file (InnoDB), .myd file (MyISAM)
  • Log files: redo log, undo log, binlog
  • Configuration files: my.cnf/my.ini

Log Files

  1. Error Log: Records error information when MySQL service starts, runs, or stops
  2. General Query Log: Records all SQL statements reaching MySQL server
  3. Binary Log: Records all SQL statements that change data, used for master-slave replication and data recovery
  4. Slow Query Log: Records SQL statements that exceed execution time threshold

Service Layer - Six Key Components

Connection Pool

  • Manage all connections between clients and database
  • Uses thread pool technology, supports connection reuse

SQL Interface

  • Supported command types: DML, DDL, stored procedures, view operations

Parser

  • Lexical analysis decomposes SQL statements into tokens
  • Syntax analysis constructs parse tree
  • Semantic check verifies if tables/columns exist

Query Optimizer

  • Cost-based optimization (CBO)
  • Index selection optimization
  • Subquery optimization

Cache & Buffer

  • Query cache, table cache, record cache, permission cache
  • Note: MySQL 8.0 has removed query cache functionality