“This is the 25th day of my participation in the Gwen Challenge.

Welcome to pay attention to the public number: Luka Duo duo

By the eye:

Here comes the first MySQL column,

Cut the crap and start the whole thing:

The basic architecture of mysql


MySQL can be divided into Server layer and storage engine layer.

Different storage engines –> Shared server layer; Connector to actuator

The storage engine is responsible for data storage and extraction.

The most commonly used storage engine is InnoDB, which has been the default storage engine since MySQL 5.5.5.

Server layer composition :(special dry goods)

  • Connector: establish connection, enable authentication, and confirm permission

  • Query cache: Queries whether a cache statement is hit

  • Analyzer: syntax, morphology, statement validity check

  • Optimizer: statement execution process scheme, efficiency formulation

  • Executor: A procedure of an executor that executes a statement

In simple terms, statements are validated, ordered, and then executed,

Connector:

Essentially: Establish a connection with the client

– Obtain permission

– Managing connections

mysql -hport -u$user -p

The above is the command line, but the production server cannot write this, will give away your password;

Mysql > select * from ‘mysql’;

Mysql -uroot -pxxx

Copy the code

A client tool used to establish a connection with a server

After the Tcp connection is established, the user name and password of the client are authenticated and identity authentication is enabled

If the username or password is wrong, you’ll get an error saying “Access denied for user” and the client program will stop executing.

If it is correct, the permissions will be read and obtained in the permissions table. Modifying permissions will not affect the current link. Restart can use the new permissions

After successful connection:

SHOW PROCESSLIST // Check out the link wherecommand"Sleep" means this is a free link

Copy the code

If the client does not operate for a long time, the connector will disconnect after wait_timeout, which is 8 hours by default

If the client sends a request again after the connection has been disconnected, it will receive an error message: Lost Connection to MySQL Server during Query. At this point, if you want to continue, you need to reconnect and then execute the request.

The process of establishing a connection can be complicated – usually long connections are used -> avoid connection actions

However, with full use of long connections, you may find that sometimes MySQL memory usage increases very quickly, because the memory temporarily used by MySQL during execution is managed in connection objects. These resources are released when the connection is disconnected. If long connections are accumulated, the system may use too much memory and kill it forcibly (OOM). In this case, MySQL restarts abnormally.

MySQL connection process often OOM, how to do?

How to solve this problem? There are two options you can consider.

  • Disconnect long connections periodically. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

  • If you are using MySQL 5.7 or later, you can re-initialize the connection resource by executing mysql_reset_connection after each large operation. This process does not require reconnection and re-authentication of permissions, but restores the connection to the state it was in when it was created.

The query cache

Now that the current connector is set up, you can execute the select query,

It’s not going to execute it directly, it’s going to query it in the cache based on key_value, where key is the statement itself, value is the result of execution,

If the current key has already been executed, value will be returned if the query select is not executed

Matters needing attention:

1. The cache requires statement equality, including parameters.

2. The table will be invalid after updating. Therefore, you can manually enable the cache only when the table update frequency is not high and the query statements are completely consistent.

Note: After mysql8, caching is disabled

So we generally don’t use caching because it does more harm than good

mysql> select SQL_CACHE * from T whereID = 10; // SQL_CACHE displays the specified cache, but after MySQL8.0 this function is removed

Copy the code

Analyzer (statement recognition)

If the current cache does not exist, or there is no cache hit, the SQL statement is executed directly.

Syntax recognition, which identifies parameters and keywords one by one


The optimizer

After the parser syntax analysis, this SQL has passed the standard, can be executed

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins. For example, if you execute a statement like the following, this statement performs a join on two tables:

Which is the fastest way to optimize linked list queries

Similar to the route a train takes to its destination,

actuator

MySQL knows what you want to do from the parser, it knows how to do it from the optimizer, so it goes to the executor phase and starts executing statements.

By parsing the parser’s good syntax, the optimizer optimizes the execution plan — to execute specific statements;

The executor first checks whether the current statement has permissions on the table, and precheck if so

Execute the statement according to the interface provided by the current storage engine,

mysql> select * from T where ID=10;



ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'



Copy the code

For example

For example, in table T of our example, the ID field has no index, then the execution flow of the executor is like this:

  • Call InnoDB engine interface to fetch the first row of the table, check whether the ID value is 10, if not skip, if yes, save the row in the result set;

  • Call the engine interface to fetch “next row” and repeat the same logic until the last row of the table is fetched.

  • The executor returns the recordset of all rows that meet the criteria in the above traversal as a result set to the client. At this point, the statement is complete

Luca q&a

Select * from T where k=1; Unknown column ‘k’ in ‘where clause’ error: Unknown column ‘k’ in ‘where clause’ At what stage do you think this error was reported?

The answer:

Parser will analyze what you are currently doing, syntax, lexical analysis, if the current word is not —- my answer is parser;

Because the parser is part of the current statement, whether it is valid or not includes keywords

The optimizer —- can be executed under the condition that all parameters are valid and syntax is no problem, and what kinds of execution schemes are there?

Actuators execute according to the storage engine interface

Luca’s message:

Recently in the brush dinqi MySQL, the database is more impressed, so I want to do a column output my notes, which for the MySQL architecture has a clear understanding, because for the database, we must first understand the architecture composition, in order to better execution, solve the problem.

I’m Luca, a person trying to find answers to life, welcome to follow the public account, Luca Duo, good night