This is the 28th day of my participation in the August Text Challenge.More challenges in August

MySQL consists of Server layer and storage engine layer

  • The Server layer consists of: connectors, storage caches, profilers, optimizers, actuators, built-in functions, cross-storage functions (triggers, views, stored procedures)
  • The storage engine layer is mainly used to store and extract data. Its architecture mode is plug-in, support InnoDB, MyISAM, Memory

The main flow of the Server layer shared by multiple storage engines is shown in the figure below:

The following is the composition of MySQL to analyze the process of the entire query statement

Connector: Creates connections, user rights, and manages and maintains connections with clients

The main steps of the connector are as follows:

  1. Run the mysql -hip− PIP -pip−pport -u$user -p command
  2. Enter the password in the window [Entering the password directly in the command line may cause password leakage, especially when connecting to the production server.]
  3. The connector authenticates users by username and password.
    • If the user name or password is incorrect. Access denied for user is displayed. The client program finishes executing
    • If the user name and password are correct, the connector reads the permissions of the current user from the permissions table.
    • The subsequent permission judgment logic also depends on the permission information read at this time. Once the connection is successful, it does not affect the user even if the user administrator changes the user’s permission. The newly authorized permissions will not be used unless you disconnect and then reconnect.
    • If the client does not perform any operation after the connection is successful, the connection is idle. If you type show processList, you can see that command is sleep.
    • If the client is not connected for a long time, it is automatically disconnected. The automatic disconnect is controlled by the wait_timeout parameter, which defaults to 8 hours
    • Lost Connection to mySQL Server during Query: lost connection to mySQL Server during Query

Long connection VS short connection

Long connection: Persistent requests from clients – the same connection is used all the time
Short link: Disconnects after executing a few queries each time, and re-establishes another query the next time

Due to the complexity of the connection process, it is recommended to minimize the connection actions as much as possible, that is, to use long connections

However, if the connection is always long, the memory temporarily used by MySQL during execution is managed in the connection object, and will be released only when the connection is disconnected. Therefore, the accumulated memory may occupy too much memory and be forcibly killed by the system (OOM). The MySQL database is abnormally restarted. So there are two solutions to the above situation:

  1. Disconnection for a long period of time: fixed a time/program to determine the execution of a large memory after the query
  2. MySQL5.7 / newer – After each large operation, reinitialize the connection resource by executing mysql_reset_connection. This process does not require reconnection and re-authentication of permissions, but restores the connection to the state in which it was established

Store cached

  • MySQL can check the query cache to see if the statement has been executed.
  • The query cache stores statements and results as key-values. If a key value is found, value is returned directly.
  • If not, proceed and store the returned result in the storage cache.
  • But caching does more harm than good. As soon as there is an update operation to the table, all query caches will be completely emptied. For databases that are under pressure to update, the hit ratio of the query cache is low.
  • So you can use it on DEMAND – set the query_cache_type parameter to DEMAND. The default SQL statement does not use the query cache. To determine whether to use the query cache, use SQL_CACHE to specify it explicitly.

analyzer

  • The parser first performs lexical analysis – identifying what the strings are and what they mean
  • Then the syntax analysis – according to the mySQl syntax rules to determine whether to meet the mySQl syntax

The optimizer

  • When there is an index, decide which index to use
  • When multiple tables are associated, the connection sequence of each table is determined

actuator

  • Start statement execution
  • Check whether the query permission exists
    • No permission – Error
    • Have permissions – Open the table and use the interface provided by the engine according to the engine definition of the table

Why aren’t permissions checked before the optimizer?

  • Because sometimes the tables to be manipulated are more than just the literal meaning of SQL statements. For example, the trigger can only be determined during the execution, not before the optimizer stage.