MySQL Optimizer (1): MySQL architecture

First, the logical structure

MySQL also uses a layered architecture. The plug-in storage engine architecture separates query processing from other system tasks and data access, so that the appropriate storage engine can be selected based on business requirements.

1. Hierarchical structure

Connection Layer--Business logic processing layer (processing SQL)--data storage engine--hard disk

(1) Connection layer

is a number of client and connection services, mainly done similar to connection processing, authorization authentication, and related security programs. This layer introduces the concept of connection pooling to provide threads for clients that authenticate secure access. This layer can also implement SSL-based secure links.

(2) Service layer

Before processing the underlying data, the SQL is analyzed, optimized, and executed in the built-in functions, which determine the order of the query tables and whether to utilize the indexes, and finally generate the corresponding execution actions. Before parsing the query, to query the cache, the cache can only save the query information and the result data, if the request a query in the cache, there is no need to parse, optimize and execute the query, directly return the cache of the query stored in the results. The capabilities provided by each storage engine are focused on this layer, such as stored procedures, triggers, views, and so on.

(3) Engine layer

The storage engine is really responsible for storing and extracting data from MySQL, and the server interacts with the storage engine through the storage Engine API, which contains many underlying operations, such as starting a transaction, or fetching a row with a specific primary key. The storage engine cannot parse SQL, nor can it communicate with each other, simply responding to requests from the server.

(4) Storage layer

It stores data primarily on file systems running on bare devices and completes interaction with the storage engine.

　　

2. Logic Module

　　

(1) Connectors: refers to the interaction with SQL in different languages.

(2) Management serveices & Utilities: System Management and control tools.

(3) Connection pool: Connection pooling

Manage buffering of user connections, threading, and other needs that require caching. Responsible for listening to various requests for MySQL Server, receiving connection requests, forwarding all connection requests to the thread management module. Each client request to a MySQL Server on a connection is assigned (or created) by a connection thread for its individual service. The main task of connection thread is to be responsible for the communication between MySQL server and client, accept the command request from client, pass the result information of server side and so on. The thread management module is responsible for managing and maintaining these connection threads. This includes the creation of threads, the cache of threads, and so on.

(4) SQL Interface:sql interface.

Accepts the user's SQL command and returns the result that the user needs to query. For example, select from is called SQL Interface.

(5) Parser: parser.

When the SQL command is passed to the parser, it is validated and parsed by the parser. The commands we used to send all client side to Server in MySQL are called query.

Main functions:
A. The SQL statements are parsed into the data structure by the semantic and syntactic analysis, then classified according to different operation types, then forwarded to the next step, then the SQL statements are transferred and processed based on this structure.
B. If an error is encountered in the decomposition composition, then it is unreasonable to describe the SQL statement.

　(6) Optimizer: query optimizer.

The SQL statement uses the query optimizer to refine the query before querying, to get an optimal strategy that tells the subsequent program how to obtain the result of the query statement, using the Select-drop-join strategy to query.

(7) Cache and buffer: query cache.

The returned result set of the Select Class query request that the client submits to MySQL is in memory and corresponds to a hash value of the query. After any data changes occur to the base table of the data taken by the query, MySQL automatically invalidates the query's cache. In applications with very high read-write ratios, Query cache is a significant improvement in performance. Of course, its memory consumption is also very large. If the query cache has hit query results, the query can go directly to the query cache to fetch the data. This caching mechanism is made up of a series of small caches. such as table cache, record cache, key cache, permission cache, and so on.

　(8) Storage Engine interface

Plug-in management, currently a variety of database products, basically only MySQL can achieve its underlying data storage engine plug-in management, this module is actually an abstract class. By default MySQL is using the MyISAM engine, it queries fast, has good index optimization and data compression technology, but it does not support transactions, InnoDB support transactions, and provides row-level locks, applications are quite extensive, MySQL also support their own custom storage engine.
Note: The storage engine is table-based, not a database , and even a different table in a library uses a different storage engine.

Second, the storage engine

View the default and current storage engines: Show variables like '%storage_engine%;

Refer to: https://www.cnblogs.com/kerrycode/p/6571868.html

Comparison of MyISAM and InnoDB

　　

Reference Link: https://www.cnblogs.com/zcbing/p/6397507.html

MySQL Optimizer (1): MySQL architecture