Server Layer

server layer
Includes:

  • Connector: Responsible for establishing connections with the client, obtaining permissions, maintaining, and managing connections.
  • Query Cache: Used to temporarily store previously executed statements and their results.
  • Analyzer: Carries out lexical and syntactical analysis of the statement.
  • Optimizer: Determines how to execute queries efficiently.
  • Executor: Actually executes the SQL statements.

Storage Engine Layer

Responsible for the storage and retrieval of data. It has a plug-in type of architecture, supporting multiple storage engines such as InnoDB, MyISAM, Memory, and others.

  • Default Storage Engine: Starting from MySQL version 5.5.5, the most commonly used storage engine is InnoDB.

Details

  • When creating a table, if the engine type is not specified, it defaults to InnoDB.
  • You can select other engines by specifying the storage engine type like engine=memory.
  • After a connection is completed, idle connections enter the sleep state, which can be viewed using the show processlist command.
  • If the client does not respond for a long time, the connector will disconnect. This is controlled by the wait_timeout parameter, which defaults to 8 hours.
  • Long connections might lead to excessive memory usage. Solutions include:
    1. Periodically disconnecting long connections.
    2. In MySQL 5.7 or higher versions, you can use mysql_reset_connection to reset connection resources.
  • It’s generally not recommended to use the query cache in most cases. Due to the frequent invalidation of the query cache, its functionality was removed in MySQL 8.0.
  • Before starting the execution, the executor will check table permissions.