Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what is the query process of Mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mysql query flow chart

Mysql query flow handles connections

There are three connection methods for Mysql, including:

TCP/IP protocol, remote connection is used, and the ip port can be used to establish the connection.

Named pipes or shared memory, named pipes or shared memory can be used for inter-process communication under windows, but the server is required to be on the same machine as the customer server.

Unix domain sockets, Unix-like operating systems can use Unix domain socket files for interprocess communication.

Mysql limits the number of simultaneous connections to the server, the system variable (we'll explain what the system variable is later, and the state variable): max_connections represents the maximum number of connections (the default is 151).

Query cache

Mysql8.0 will cache the query results before, and the second time it uses the same statement to query, it will first check whether there is any in the cache, and if it returns the query results directly.

Note: starting from MySQL 5.7.20, it is not recommended to use query caching and delete it in MySQL 8.0. (personally, the small concurrency of the project is not very high, but the concurrency is not high, which has little impact on caching. However, if the project is large and the data changes frequently, the cache overhead will be very high, so it is OK to keep it closed regardless of the size of the project.

Hit condition of cache

The query statement must be the same before and after, and the difference between the two query requests on any character (for example, spaces, comments, case) will cause the cache to miss.

If the query request contains certain system functions, user-defined variables and functions, and some system tables, such as tables in mysql, information_schema, and performance_schema databases, the request will not be cached.

Conditions for deletion of cache

MySQL's cache system monitors each table involved, and as long as the structure or data of the table is modified, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE statements are used against the table, all cached queries that use the table will become invalid and deleted from the cache.

Grammatical analysis

The main purpose of this stage is to check the syntax of the query statement that missed the cache, and extract the tables and various query conditions used in the query statement and put them on some data structures used in the MySQL server.

Query optimization

Mysql will optimize the query statement we write to generate an execution plan (you can add explain in front of the query statement to see the execution plan, what do you think of the execution plan, and summarize it later), this execution plan can see which indexes will be used for the query, the optimized join order between tables, and so on.

Storage engine execution

MySQL has provided plug-in storage engine API since 2007. Generally speaking, connection management, query cache, syntax parsing, query optimization, which do not involve the functions of real data storage, are called MySQL server, but what really executes the query is the function of storage engine, which really performs data access operations. There are many storage engines, all of which are developed based on mysql's API specification. These include the following:

Among them, InnoDB and MyISAM are our most commonly used, and the default storage engine for Mysql is InnoDB.

This is the end of the content of "what is the query process of Mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.