Understand the MySQL architecture design in one article, no longer have to worry about the interviewer asking too deep

Many development students know a lot about SQL optimization, but they don't know much about MySQL architecture. Is it not that I only see the leaves, but not the forest, and I will eventually fall into the details and cannot extricate myself.

Today, let’s learn about the layered architecture of MySQL, and have an in-depth understanding of the underlying implementation principle of MySQL and the role of each layer. Which layer is our common SQL optimization optimized?

1. MySQL overall architecture

As can be seen from the figure, the MySQL architecture is mainly divided into the Server layer and the storage engine layer .

The Server layer is further divided into connectors, caches, analyzers, optimizers, and executors. All functions across storage engines are implemented in this layer, such as: functions, stored procedures, triggers, views, etc.

The storage engine is pluggable. Common storage engines include MyISAM, InnoDB, and Memory. Before MySQL 5.5, the default was MyISAM, and then the default was InnoDB.

2. Connector

Connectors are mainly used to manage client connections and user authentication.

The connection between the client and the server uses the TCP protocol. After the TCP handshake and the connection is established, the connector begins to authenticate.

 > mysql -hlocalhost -P3306 -uroot -p

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

You can view information about all connections in the system with the show processlist command:

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

3. Query cache

The client request will not directly go to the storage engine to query the data, but will first query whether the result exists in the cache. If the result already exists, return it directly, otherwise execute the query process again, and cache the result after the query is over.

If the data table changes, the invalidation cache will be emptied, such as insert, update, delete, alter operations, etc.

For frequently changed data tables, the cache hit rate is low. Using the cache actually reduces the read and write performance, so the cache module was removed after MySQL 8.0.

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

4. Analyzer

The analyzer mainly performs lexical analysis and syntax analysis on SQL statements.

First, perform lexical analysis to analyze MySQL keywords and the meaning of each word. Then perform grammatical analysis to check whether the SQL statement conforms to the MySQL grammatical requirements.

By identifying MySQL keywords such as column name, table name, where, select/update/insert, etc. in the string, MySQL judges whether the sql satisfies the grammar according to the grammar rules, and finally generates an abstract syntax tree (AST).

For example, if the where keyword is omitted 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 actually executing the SQL statement, it also needs to be processed by the optimizer.

The well-known execution plan (Explain) is generated by the optimizer.

The optimizer has two main functions: logical optimization and physical optimization .

Logic optimization mainly performs equivalent predicate rewriting, condition simplification, subquery elimination, join elimination, semantic optimization, grouping and merging, selection pushdown, index optimization query, table query replacement view query, Union replacement or operation, etc.

The main function of physical optimization is to estimate the cost of each execution method according to the cost estimation model through a greedy algorithm. And use the index to optimize the table join, and finally generate the query execution plan.

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

If you want to know the estimation result information of the optimizer, you can view it through Explain, pay attention to Yideng, and the next article will explain the specific usage of Explain in detail.

6. Actuator

After the optimizer optimizes the SQL and generates an execution plan, it passes the execution plan to the executor.

The executor calls the storage engine interface to actually execute the SQL query. The query result returned by the storage engine is obtained, and the result is returned to the client. At this point, the execution of the SQL statement ends.

7. Summary

This article mainly takes you to understand the MySQL layered architecture and the role of each layer of the architecture. It can be seen that each layer of MySQL has a clear division of labor and clear logic, which profoundly reflects the design idea of "high cohesion, low coupling" in architecture design. When we usually do architecture design, we should also learn more about the design idea of this layered architecture.

The article is continuously updated, and you can search for "One Light Architecture" on WeChat to read more technical dry goods as soon as possible.