Thresh

① Establish a connection(Connectors&Connection Pool)

Connect to MySQL through client/server communication protocol. The MySQL client communicates with the server in half-duplex mode. For every MySQL connection, there is a thread state that identifies what the connection is doing. (Half duplex: indicates the time at which data is either sent or received, but not simultaneously. Thread state: show processList; The root user can view all threads. Other users can only view their own ids. The thread ID can be executed by killing xx. User: indicates the user who starts the thread. Host: indicates the IP address and port number of the client that sends requests. Db: indicates the library in which the Command is being executed. PreparedStatement Close Stmt: a PreparedStatement is being closed. PreparedStatement Query: a statement is being executed. Sleep: a statement is being sent from the client. Time: indicates the Time that the thread is in its current State, in seconds. State: indicates the State of the thread. Updating: Indicates that matching records are being searched and modified. Checking table Closing table Checking table Closing table Sending Data: Indicates that a Select query is being processed and the results are being sent to the client. Info: indicates a statement executed by a general recording thread. The first 100 characters are displayed by default. To view the full processlist using show full processlist;Copy the code

② Query cache (Cache&Buffer)

This is a place where MySQL can optimize queries. If query caching is enabled and the same SQL statement is found during query caching, the query results are directly returned to the client. If the query cache is not enabled or the same SQL statement is not queried, the parser parses the syntax and semantics and generates a parse tree.

A. Cache the results and SQL statements of the Select query b. During the Select query, query the cache first to check whether there are available recordsets and whether the requirements (including parameter values) are identical. In this way, the cache data matches. C. Use SQL_NO_CACHE to query results greater than query_cache_limit. Now () d. show variables like '%query_cache%'; E. Show status like 'Qcache%'; // View more detailed cache parameters, available cache space, cache blocks, cache size, etcCopy the code

③ Parser

The SQL sent by the client is parsed to generate a parse tree. The preprocessor further checks the parse tree for validity according to some MySQL rules, for example, tables and columns are checked for existence, names and aliases are resolved to see if they are ambiguous, and a new parse tree is generated.

④ Query Optimizer

Generate an optimal execution plan based on the parse tree. MySQL uses many optimization strategies to generate optimal execution plans, which can be divided into two categories: static optimization (compile-time optimization) and dynamic optimization (runtime optimization).

A < b and a=5; b>5 and a=5; Count (*) = count(*); MyISAM = count(*); MySQL > select * from 'in'; select * from 'in'; select * from 'in'; For example where id in (2,1,3) becomes in (1,2,3)Copy the code

⑤ Query the execution engine

In this case, the query execution engine returns the query result to the client based on the storage engine type of the table in the SQL statement and the interaction between the corresponding API interface and the cache or physical file of the underlying storage engine. If query caching is enabled, SQL statements and results are stored completely in the query cache. If the same SQL statement is executed later, the result is returned directly.

If query caching is enabled, too many query results are returned by caching and the query results are returned in incremental modeCopy the code