This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

It is helpful to understand mysql if you have a clear understanding of the architecture diagram and the relationship between the various components. Here is the architecture diagram

Mysql is divided into service layer and storage engine layer:

The Service layer contains most of mysql’s core functionality, except for the connectors, query caches, profilers, optimizers, actuators, and all the built-in functions (date, time, math, and encryption functions) noted in the diagram. All cross-storage engine functionality is implemented in this layer: stored procedures, triggers, views, etc.

The storage engine layer is responsible for storing and extracting data in mysql. Like the various file systems under Linux, each storage engine has its own advantages and disadvantages. Various storage engines provide apis to interconnect with the Service layer, and use apis to mask differences among storage engines. Common storage engines include InnoDB, MyISAM, and Memory. InnoDB is the most commonly used storage engine, and it has become the default storage engine since mysql5.5. Before 5.5, MyISAM was the default storage engine

1. The connector

The connector is the first module in the mysql Service layer and the module that handles client requests.

The connection between the client and the server uses the classic TCP protocol, and after a TCP handshake, the connector begins authentication

The login command

mysql -h$ip -P$port -u$user -p
Copy the code

  • If the account or password is incorrect, “Access denied for user” is displayed

  • If yes, the authentication succeeds. The system queries the permission of the current user, and all subsequent user operations are within the permission range. If the permission is changed during the connection period, it takes effect only after the connection is reconnected.

    The following command can also be used, but this command will leak the password. You are advised to use the command as shown in the preceding figure. It is more secure to enter the password in interactive mode

    mysql -h$ip -P$port -u$user -p$password
Copy the code

    2. Query the cache

    Just so you know, mysql8.0 removes the cache module

    After the client sends an SQL query request, it checks the cache to see if it exists. If the SQL has been executed before and the results are cached, the query will return the results directly from the cache, without going through the parser, optimizer, or executor. If there is no hit in the cache, the module will continue to the next module.

    The cache is stored in the form of key-value, which is stored in a reference table. The key is a reference to a hash value, which includes the query itself (SQL), the database currently being queried, the version of the client protocol, and other information that will affect the returned results. The query result is treated as value(any change on a character, such as a space, the comment will result in a cache miss).

    If a TABLE is changed, all caches are invalidated. TABLE changes are data changes and TABLE structure changes, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

    For databases that change frequently, the cache hit ratio is very low, and query caches often do more harm than good. Therefore, mysql caches are not recommended, and Redis caches can be used for tables that do not change for a long time. Mysql completely removed the query cache module in 8.0.

    3. The analyzer

    We write an SQL statement according to the mysql syntax and send it to the service layer. The parser performs lexical analysis and syntax analysis on the SQL statement.

    Mysql identifies the column name, table name, WHERE, SELECT /update/ INSERT and other Mysql keywords in the string, determines whether the SQL meets the syntax according to the syntax rules, and eventually generates an abstract syntax tree (AST).

    The mysql analyzer uses mysql syntax rules to validate and parse queries, such as to verify that incorrect keywords are used or that keywords are used in the correct order, or to verify that quotes match correctly.

    If You have an error in your SQL syntax, pay attention to the content after use near

    mysql> elect * from iam_user where id = 0;
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 'elect * from iam_user where id = 0' at line 1
Copy the code

    4. The optimizer

    The syntax tree generated by the parser is considered legitimate and is converted into an execution plan by the optimizer

    Mysql determines what needs to be done with this SQL and optimizes it in a variety of ways, including rewriting the query, determining the order in which the tables should be read, and choosing the appropriate indexes.

    Mysql uses a cost-based optimization optimizer that tries to predict the cost of a query using a particular execution plan, choosing the smallest one.

    For example, when there are multiple indexes in a table, decide which one to use. When using a federated index, the order of the WHERE conditions is adjusted according to all;

    If you want to know how the optimizer makes optimization decisions, you can get information about optimizations by using Explain, which is explained in the following sections.

    5. Actuators

    Call the storage engine’s API to manipulate the data

    After the optimizer completes the optimization of the SQL, it provides an execution plan to the executor, which then executes the execution plan to manipulate the data.

    Execute the query phase: mysql simply executes the instructions given by the execution plan step by step, by calling the interface implemented by the storage engine.

    Summary: Summarize with a SQL execution process

  • The client sends a query to the server.
  • The server first checks the query cache and returns the result in the cache if it hits. Otherwise, go to the next stage.
  • The server parses the SQL, preprocesses it, and the optimizer generates the execution plan.
  • Mysql calls the storage engine’s API to execute the query according to the execution plan.
  • The result is returned to the client.

Public account: Notes of Mr. Ji