Detailed explanation of Mysql logical architecture

1. Overall architecture diagram

Compared to other databases, MySQL is a bit different in that its architecture can be applied in a variety of different scenarios and work well. This is mainly reflected in the storage engine architecture. The plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. This architecture allows you to select the appropriate storage engine based on business needs and actual requirements.

Introduction of each layer:

1.1 Connection Layer

The top layer is a number of clients and connection services, including local sock communication and most of the client/server-based tools to achieve similar TCP/IP communication. It mainly completes some tasks such as connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced at this layer to provide threads for clients that have accessed securely through authentication. SSL-based secure links can also be implemented at this layer. The server will also verify the operation permissions of each client that securely accesses it.

1.2 Service Layer

1.3. Engine layer

Storage engine layer: The storage engine is actually responsible for the storage and extraction of data in MySQL. The server communicates with the storage engine through the API. Different storage engines have different functions, so we can choose according to our actual needs.

1.4. Storage Layer

The data storage layer mainly stores data on the file system running on the raw device and completes the interaction with the storage engine.

2. show profile

Use show profile to view the execution cycle of sql!

2.1 Enable profile

Check whether profiling is enabled: show variables like '%profiling%'

If it is not enabled, you can execute set profiling=1 to enable it!

2.2 Using profiles

Run show prifiles command to view the most recent queries.

Based on Query_ID , you can further execute show profile cpu,block io for query Query_id to view the specific execution steps of SQL.

2.3 General query process

The query process of mysql is roughly as follows:

The MySQL client establishes a connection with the MySQL server through the protocol, sends a query statement, checks the query cache first, and returns the result directly if it hits, otherwise it parses the statement. That is to say, before parsing the query, the server will first access the query cache (query cache) - it stores SELECT statements and corresponding query result sets. If a query result is already in the cache, the server will not parse, optimize, or execute the query again. It simply returns the results in the cache to the user, which will greatly improve the performance of the system.

Syntax parser and preprocessing: First, MySQL parses the SQL statement through keywords and generates a corresponding "parse tree". The MySQL parser will validate and parse the query using MySQL syntax rules; the preprocessor will further check whether the parsed number is legal according to some MySQL rules.

Query Optimizer When the parse tree is considered valid, it is converted into an execution plan by the optimizer. A query can be executed in many ways and still return the same result. The role of the optimizer is to find the best execution plan among them. .

Then, MySQL uses the BTREE index by default, and a general direction is: no matter how you mess with SQL, at least for now, MySQL will only use one index in the table at most.

2.4 SQL execution order

Handwritten order:

The actual execution order is:

As MySQL versions are updated, its optimizer is also constantly upgraded. The optimizer will analyze the different performance consumptions caused by different execution orders and dynamically adjust the execution order. The following is a common query sequence:

2.5 MyISAM and InnoDB

show engines : View all database engines

show variables like '%storage_engine%' to view the default database engine

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links