Overview of mysql infrastructure
MySQL can be divided into two parts: the server layer and the storage engine layer.
The Server layer includes connectors, query caches, analyzers, optimizers, executors, etc., including most of MySQL’s core service functions, as well as all built-in functions (such as date, time, math and encryption functions, etc.), all across storage engines All functions are implemented in this layer, such as stored procedures, triggers, views, etc., and there is also a general log module binglog log module.
The storage engine layer is responsible for data storage and reading. Its architecture mode is plug-in, supports InnoDB, MyISAM, Memory and other storage engines, and each storage engine has its own advantages and disadvantages, among which the InnoDB engine has its own log module redolog module. Now the most commonly used storage engine is InnoDB, which has become the default storage engine since MySQL 5.5.5, and MyISAM was the default before 5.5.
Connector
The connector is the first module of the mysql Server layer and also the module that handles client requests. Responsible for establishing a connection with the client, obtaining permissions, maintaining and managing the connection.
login command
mysql -h$ip -P$port -u$user -p
The mysql in the connection command is a client tool used to establish a connection with the server. After completing the classic TCP handshake, the connector will start to authenticate identity, this time using the username and password entered.
- If the username or password is incorrect, you will receive an “Access denied for user” error, and the client program will end execution.
- If the user name and password authentication is passed, the connector will check the permissions you have in the permission table. After that, the permission judgment logic in this connection will depend on the permission read at this time.
This means that after a user successfully establishes a connection, even if you use the administrator account to modify the user’s permissions, it will not affect the existing connection permissions. After the modification is completed, only new connections will use the new permission settings.
Query cache
The cache module of mysql8.0 version has been removed.
After the connection is established, you can execute the select statement. Execution logic will come to the second step: query cache.
After MySQL gets a query request, it will first go to the query cache to check whether this statement has been executed before. Previously executed statements and their results may be directly cached in memory in the form of key-value pairs. The key is the query statement, and the value is the query result. If your query can find the key directly in this cache, then the value will be returned directly to the client.
If the statement is not in the query cache, it will continue to the subsequent execution phase. After the execution is completed, the execution result will be stored in the query cache. You can see that if the query hits the cache, MySQL can directly return the result without performing subsequent complex operations, which is very efficient.
But in most cases it is not recommended to use query cache, why? Because query caching often does more harm than good.
Query cache invalidation is very frequent. As long as there is an update to a table, all query caches on this table will be cleared. Therefore, it is very likely that you have worked hard to save the result, and it will be completely emptied by an update before you use it. For databases with high update pressure, the hit rate of the query cache will be very low. Unless your business is to have a static table, it will be updated once in a long time. For example, a system configuration table, then the queries on this table are suitable for query caching.
Analyzer
If the query cache is not hit, it is time to actually execute the statement. First, MySQL needs to know what you want to do, so it needs to parse the SQL statement.
The first step is lexical analysis. An SQL statement is composed of multiple strings and spaces. First, keywords must be extracted, such as select, the table to be queried, field names, query conditions, and so on. After completing these operations, you will enter the second step.
The second step is grammatical analysis. According to the result of lexical analysis, the grammatical analyzer will judge whether the SQL statement you input meets the MySQL grammar according to the grammatical rules.
After completing these two steps, MySQL is ready to start execution, but how to execute, and how to execute is the best result? At this time, the optimizer is needed.
Optimizer
After passing the analyzer, MySQL knows what you are going to do. Before starting to execute, it must be processed by the optimizer.
The optimizer decides which index to use when there are multiple indexes in the table; or decides the connection order of each table when a statement has multiple table associations (join).
After the optimizer phase is completed, the execution plan of this statement is determined, and then enters the executor phase.
Actuator
MySQL knows what you want to do through the analyzer, and knows what to do through the optimizer, so it enters the executor stage and starts to execute the statement.
When starting to execute, you must first determine whether you have the permission to execute queries on the table test. If not, an error of no permission will be returned, as shown below:
mysql> select * from test where id = 1;ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'user'
If you have permission, open the table and continue execution. When opening the table, the executor will use the interface provided by the engine according to the engine definition of the table.
For tables with indexes, the execution logic is similar. The first call is the interface “get the first line that meets the condition”, and then loop to get the interface “the next line that meets the condition”. These interfaces are already defined in the engine.