The overall structure of MySQL processing requests

1: Connection Manager : Listen on TCP 3306 port, receive request

There are four types of common MySQL connections:

• DBA database administrator: Through administrative tools such as: phpadmin, etc.

• Applications: such as PHP scripts through connector such as: Php-mysql

• Programmer: Via API interface

• User: Via MySQL statement

2: thread manager : Establishing a threading request (MySQL using single-process multithreaded model)

The MySQL process generates a different thread response for each request, recycles the thread after processing the request with thread reuse, and processes the subsequent request

3: User Management : User-level connection permissions, whether the user has access to the use of MySQL

4: command Distribution Module : preprocessing after receiving command

• Cache Module : View whether the command request content has a cache

• Log module : Log command logs

• parser : After the cache has no corresponding content, pass to the parser

5: parser: Parse command type, generate parse tree, deliver to corresponding management module according to type

• Optimizer : Optimized for SELECT statements (optimization of how commands are executed, resulting in optimal execution)

• Table Modification Module : Related to Table modification: such as table creation, deletion, UPDATE, INSERT, etc.

• Table Maintenance module : Table repair Related: Inspection, backup, recovery, optimization (defragmentation) parsing

• Replication Module : master-slave replication related

• Status Report Module : status information record

• access Control Module : For the above module, whether the user has Operation rights control

6: Table Manager : After the user has the appropriate operation rights, the specific management of the table

• Responsible for creating, reading, and modifying the table structure definition file

The table structure is also the data, the table structure definition file specifically stores the structure of the table (table size, what fields are there, etc.) and the table data is a different file

• Maintain table Descriptor caching

When you work on a table, you need to know that there is no such table in the library, the name and definition of the table needs to be cached in memory, the query first to see if there is no such file, and then specifically through the storage engine processing

• Manage Table Locks

7: Storage Engine : Final processing of logical relationships between tables and files

user Query Request process:

When a user requests in, connection management receives the user request through the Connection Manager, responds to the request allocation thread, threads through the thread manager, uses thread reuse, generates the idle thread, authenticates the user, establishes a secure connection, and when the user queries, the MySQL server starts analyzing the request internally, Inside the MySQL process, a parse tree is created and optimized to select the optimal execution path (parser and optimizer action) and finally cache some specific results.

Optimizer some bugs

The optimizer is the self-intelligent judgment of the program, perhaps the optimization in some circumstances is not optimal, so it can be specified manually ;

MySQL is plug-in storage engine, the optimizer does not consider the storage engine different, so to give Optimizer Tips the cause

This article is from the "Call Me boxin" blog, so be sure to keep this source http://boxinknown.blog.51cto.com/10435935/1676975

The overall structure of MySQL processing requests