If you can build an architecture diagram in your mind of how the various components of MySQL work together, it will help you deeply understand the MySQL server.

#The top-level service is not unique to MySQL, most network-based client/server tools have a similar architecture. Such as connection processing, authorization authentication, security, etc.

The second layer architecture is the more interesting part of MySQL. Most of MySQL's core service functions are in this layer, including query parsing, analysis, optimization, caching, and all built-in functions (such as date, time, math, and encryption functions). All cross-storage engine functions are in this layer. Implementation: stored procedures, triggers, views, etc.

The third layer contains the storage engine. The storage engine is responsible for the storage and retrieval of data in MySQL. Like various file systems under GNU/Linux, each storage engine has its advantages and disadvantages. The server communicates with the storage engine through APIs. These interfaces shield the differences between different storage engines, making these differences transparent to the upper-layer query process. The storage engine API contains more than a dozen low-level functions for performing operations such as "starting a transaction" or "extracting a row of records based on the primary key." But the storage engine will not parse SQL (note: InnoDB is an exception, it will parse foreign key definitions, because the MySQL server itself does not implement this function), and different storage engines will not communicate with each other, but will simply respond to the upper layer. Server request.

Connection Management and Security

Each client connection will have a thread in the server process. The query for this connection will only be executed in this separate thread, which can only take turns in Running in a certain CPU core or CPU. The server will be responsible for caching threads, so there is no need to create or destroy threads for each new connection. (Note: MySQL 5.5 or later provides an API that supports the thread pool plug-in, which can use a small number of threads in the pool to serve a large number of connections).

When a client (application) connects to the MySQL server, the server needs to authenticate it. Authentication is based on username, original host information and password. If you use a secure socket (SSL) connection, you can also use X.509 certificate authentication. Once the client connects successfully, the server will continue to verify whether the client has permission for a specific query (for example, whether the client is allowed to execute a SELECT statement on the Country table of the world database).

Optimization and Execution

MySQL will parse the query and create an internal data structure (parse tree), and then perform various optimizations on it, including rewriting the query and determining the reading order of the table. As well as choosing appropriate indexes, etc. Users can influence the optimizer's decision-making process through special keyword hints. You can also request the optimizer to explain various factors in the optimization process, so that users can know how the server makes optimization decisions, and provide a reference baseline to facilitate users to reconstruct queries and schemas, and modify related configurations to make the application as efficient as possible. run.

The optimizer does not care what storage engine is used, but the storage engine has an impact on optimizing queries. The optimizer will request the storage engine to provide capacity or cost information of a specific operation, as well as statistical information of table data, etc. For example, certain indexes of certain storage engines may be optimized for certain queries.

For SELECT statements, before parsing the query, the server will first check the query cache (Query Cache). If the corresponding query can be found in it, the server does not have to perform the entire process of query parsing, optimization and execution. Instead, the result set in the query cache is returned directly.

If you can build an architecture diagram in your mind of how the various components of MySQL work together, it will help you deeply understand the MySQL server.

#The top-level service is not unique to MySQL, most network-based client/server tools have a similar architecture. Such as connection processing, authorization authentication, security, etc.

The second layer architecture is the more interesting part of MySQL. Most of MySQL's core service functions are in this layer, including query parsing, analysis, optimization, caching, and all built-in functions (such as date, time, math, and encryption functions). All cross-storage engine functions are in this layer. Implementation: stored procedures, triggers, views, etc.

The third layer contains the storage engine. The storage engine is responsible for the storage and retrieval of data in MySQL. Like various file systems under GNU/Linux, each storage engine has its advantages and disadvantages. The server communicates with the storage engine through APIs. These interfaces shield the differences between different storage engines, making these differences transparent to the upper-layer query process. The storage engine API contains more than a dozen low-level functions for performing operations such as "starting a transaction" or "extracting a row of records based on the primary key." But the storage engine will not parse SQL (note: InnoDB is an exception, it will parse foreign key definitions, because the MySQL server itself does not implement this function), and different storage engines will not communicate with each other, but will simply respond to the upper layer. Server request.

Connection Management and Security

Each client connection will have a thread in the server process. The query for this connection will only be executed in this separate thread, which can only take turns in Running in a certain CPU core or CPU. The server will be responsible for caching threads, so there is no need to create or destroy threads for each new connection. (Note: MySQL 5.5 or later provides an API that supports the thread pool plug-in, which can use a small number of threads in the pool to serve a large number of connections).

When a client (application) connects to the MySQL server, the server needs to authenticate it. Authentication is based on username, original host information and password. If you use a secure socket (SSL) connection, you can also use X.509 certificate authentication. Once the client connects successfully, the server will continue to verify whether the client has permission for a specific query (for example, whether the client is allowed to execute a SELECT statement on the Country table of the world database).

Optimization and Execution

MySQL will parse the query and create an internal data structure (parse tree), and then perform various optimizations on it, including rewriting the query and determining the reading order of the table. As well as choosing appropriate indexes, etc. Users can influence the optimizer's decision-making process through special keyword hints. You can also request the optimizer to explain various factors in the optimization process, so that users can know how the server makes optimization decisions, and provide a reference baseline to facilitate users to reconstruct queries and schemas, and modify related configurations to make the application as efficient as possible. run.

The optimizer does not care what storage engine is used, but the storage engine has an impact on optimizing queries. The optimizer will request the storage engine to provide capacity or cost information of a specific operation, as well as statistical information of table data, etc. For example, certain indexes of certain storage engines may be optimized for certain queries.

For SELECT statements, before parsing the query, the server will first check the query cache (Query Cache). If the corresponding query can be found in it, the server does not have to perform the entire process of query parsing, optimization and execution. Instead, the result set in the query cache is returned directly.

The above is the content of [MySQL] MySQL logical architecture. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!