MySQL architecture design example analysis

1. MySQL overall architecture

As you can see from the figure, the MySQL architecture is mainly divided into Server layer and Storage engine layer.

Server layer is divided into connectors, caches, analyzers, optimizers, and executors. All cross-storage engine functions are implemented in this layer, such as functions, stored procedures, triggers, views, etc.

Storage engine is pluggable. Common storage engines include MyISAM, InnoDB, Memory, etc. The default was MyISAM before MySQL5.5, and the default after that was InnoDB.

2. Connector

Connector is mainly used to manage client connections and user authentication.

TCP protocol is used for client-server connections. The connection is established by performing a TCP handshake, and based on this, the connector begins authentication.

> mysql -hlocalhost -P3306 -uroot -p

If authentication fails, an error will occur ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES).

You can view the information of all connections in the system through the show processlist command:

The Commond column represents the connection status, and the Daemon represents the background process , Query represents query, and Sleep represents idle connection.

3. Query cache

The query requested by the client will not directly access the storage engine, but first check whether the result exists in the cache. If the result has been cached, return it directly; otherwise, re-execute the query process and cache the result after the query is completed.

The invalidation cache will be cleared when the data table changes, such as insert, update, delete, and alter operations.

For frequently changing data tables, the cache hit rate is very low. Because using cache will reduce read and write performance, the cache module was removed in versions after MySQL 8.0.

You can check whether the cache is enabled by running the following command:

4. Analyzer

The analyzer is mainly Perform lexical analysis and grammatical analysis on SQL statements.

For MySQL keywords and the meaning of each word, lexical analysis is required. Then perform syntax analysis to detect whether the SQL statement meets the MySQL syntax requirements.

MySQL identifies the column name, table name, where, select/update/insert and other MySQL keywords in the string, and determines whether the sql satisfies the grammar according to the grammar rules, and finally generates an abstract syntax tree (AST) .

For example: If you omit the where keyword in the SQL statement, an error will be prompted.

mysql> select * from user id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1

5. Optimizer

Before the SQL statement is actually executed, it needs to be processed by the optimizer.

The execution plan (Explain) we know well is generated by the optimizer.

The optimizer mainly has two functions: Logical optimization and Physical optimization.

Logical optimization mainly performs equivalent predicate rewriting, conditional simplification, subquery elimination, connection elimination, semantic optimization, group merging, selection pushdown, index optimization query, table query replacement view query, Union replacement or Operation etc.

Through the greedy algorithm and cost estimation model, the main purpose of physical optimization is to estimate the cost of each execution method. And use indexes to optimize table connections and finally generate query execution plans.

Attached is the MySQL optimizer architecture diagram, you can clearly see the optimization process:

6. Executor

After the optimizer optimizes the SQL and generates the execution plan, it will pass the execution plan to the executor.

The executor calls the storage engine interface to actually execute the SQL query. The execution of the SQL statement ends after obtaining the query results returned by the storage engine and returning them to the client.

The above is the detailed content of MySQL architecture design example analysis. For more information, please follow other related articles on the PHP Chinese website!