Geek time “MySQL In Action” column learning harvest

I have just purchased geek Time’s course MySQL Practice 45. I will summarize what I have learned here.

This section focuses on the infrastructure of MySQL. For example, when executing the following statement:

mysql> select * from T where ID=10;
Copy the code

How this statement is handled inside MySQL.

Infrastructure diagram


Server includes: connector, cache query, analyzer, optimizer, and executor. Stored procedures, triggers, views, and other functions are handled in the Server layer.

Storage engines are responsible for storing and extracting data. Common ones are InnoDB and MyISAM.

The connector

Connectors establish connections to clients, obtain permissions, and maintain and manage connections.

Common commands:

mysql -uroot -ppassword

The TCP handshake is used to establish a connection with the server to complete the authentication process.

  • Error: “Access denied for user”
  • If the authentication succeeds, query the permissions in the permission table

The permission information is read from the permission table only during the connection process. The modification of the permission does not affect the established connection. The new permission information is used only after you log in again.

If the connection has been idle for a long time, the “show ProcessList “command will show the connection to the sleep state. If there is no activity within the specified time, the system automatically disconnects. The specified time is controlled by wait_timeout, which defaults to 8 hours. Lost Connection to MySQL Server during Query: Lost Connection to MySQL Server during Query

To prevent a large amount of memory in the database, you can use the following methods to solve the problem:

  1. Periodically disconnect long connections or connections that occupy too much memory.
  2. MySQL5.7 and above, one larger operation at a time, can be executed"mysql_reset_connection"Command to initialize the connection resource. This operation does not reconnect or re-authorize, but simply restores the state where the connection was established.

The query cache

Once the connection is established, you can perform the SELECT operation, which performs the second part: query caching.

A request comes in and first queries the cache to see if the record exists. The statement that has been executed before is stored in memory with the statement as the key and the result set as the value.

  • If it is found in the cache, the callback is returned directly to the client.
  • If no record is found, proceed and store the statement and the result in memory as key-value, respectively.

Use of query caching is not recommended, it does more harm than good (MySQL8.0 will remove this feature)

Any update to a table clears the cache of all references to that table. – Set query_cache_type to DEMAND. By default, SQL does not use the query cache. For statements that need to be cached, use SQL_cache to display the specified values, for example:

select SQL_CACHE * from T where ID = 10;
Copy the code

analyzer

What does this SQL do

  1. Lexical analysis

    Identify SQL keywords and propose key components. MySQL according to"select"This is a query statement, according to"from T"Identify table T, will"ID"Identifies as the column name.
  2. Syntax analysis

    Check whether the SQL syntax conforms to the syntax rules. If an error occurs, the following message is displayed:"You have an error in your SQL syntax...", the back is the wrong place, need your attention"use naer"After the content.

The optimizer

How do I do this SQL

This step will select the optimal execution mode, for example:

  1. When multiple indexes are involved, decide which index to use
  2. When multiple tables are associated, the join order is determined 
    select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
Copy the code

    This step determines whether the ID value c=10 from t1 or d=20 from T2 should be retrieved first.

actuator

Actual execution steps

Determine whether the connector has permissions on table T based on the permissions obtained by the connector.

  • If you have permission, open the table and continue operations.

After opening the table, use the interface provided by the engine according to the engine definition of the table. Ex. :

  • Alter table T alter table T alter table T

    • Call the interface provided by InnoDB, fetch the first row, and when ID=10, put the data for that row into the result set. If not, call the engine interface to get the next line, and judge again until the last line of data.
    • The result set is cached and returned to the client.

  • Alter table T alter table T alter table T

    • Call the InnoDB"Get the first row that satisfies the condition."Interface, the Server layer will determine if the value is correct again, then put the result set in, and continue the access"The next row that satisfies the condition"Interfaces that are already defined by the engine.

In the MySQL slow query log, the ROws_EXAMINED field indicates how many rows were scanned during the statement execution, which was added when the engine was called to fetch the data rows.

In some scenarios, many rows are scanned internally by the engine at one invocation of the executor, so the number of rows scanned by the engine is not exactly the same as rows_EXAMINED, as I’ll explain later.

Comment section:

  • Question: At what stage does an error occur if a field in a query statement does not exist?

    • Answer: An error is reported in the parser phase.
    • High-performance MySQL talks about parsers and preprocessor parsers: parsing syntax and parsing queries, generating a corresponding parse tree. Preprocessor: Further checks the validity of the parse tree, such as whether tables and columns exist, and whether aliases are ambiguous. If it passes, a new parse tree is generated and submitted to the optimizer.

  • Connect_timeout refers to the wait time “while connecting.

  • Wait_timeout refers to the waiting time “while the connection is complete”

This article contains geek time “MySQL Field” picture and part of the original text, if any infringement, please contact me immediately delete section 2: MySQL series one of the update SQL life course