An elementary school graduate who can’t learn to copy and paste the best posture

How does a query actually execute?

SELECT * FROM table_name WHERE id = 10;
Copy the code

Mysql has two layers

  1. Server layer; Connectors, analyzers, optimizers, actuators
  2. Engine layer, InnoDB, MyISam, Memory

Server layer workflow

The connector

Responsible for client to establish links, obtain and verify permissions, maintain and manage links

Link is a complex process, try to link reuse

Profiler -> Generate execution plan

Lexical analysis: parsing the string input by the client, such as identifying the update statement and other statements, identifying table_name as a table name, id as a column name. From, join, WHERE, on, etc

Syntax analysis: Checks whether the user’s input statement meets the MySQL syntax rules. For example:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELECT COUNT(*) FROM user_infos' at line 1
Copy the code

The optimizer

The optimal evaluation of the execution plan, such as whether to use indexes, which indexes to use, and so on, is performed here

actuator

Get lock, open table, meta data, get data

The executor is the interface that calls the engine layer

At the start of the execution, you need to determine whether the link has the corresponding permission for this, if not, will return no permission error.

In engineering implementation, if the query cache is hit, permission verification will be done when the query cache returns. The query will also call precheck to verify permissions before the optimizer, because after the parser, it will know what the statement needs to do. For example, query that table, modify the data of that table.


mysql> select * from T where ID=10;

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

If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to the definition of the table engine.

SELECT * FROM table_name WHERE id = 10;
Copy the code

If the id field in table table_name does not have an index, it is executed like this.

  1. Call the innoDB engine interface, take the first row in the table, determine if the value of id is 10, if not skip, if it is save the row in the result set.
  2. Call the engine interface to read the next row and repeat the same logic until the last row of the table is fetched.
  3. The executor returns the recordset of appeal traversal results as a result set to the client.

Select table_name from table_name where id is an index.

  1. The InnoDB engine interface is called to fetch the first row of the table that meets the criteria
  2. Read in a loop, calling the interface that meets the next line until the condition is not met

The ROws_EXAMINED field, which shows how many rows were scanned during the execution of this statement. This value is accumulated each time the executor calls the engine to get the number of rows.

Return to the connector and user module, clean up, and wait for new requests.

Question:

1. Why aren’t permissions checked before the optimizer?

A: SQL statements operate on tables that are not literal. For example, if there is a trigger, it can only be determined at the executor stage, not before the optimizer stage. If you say more, you can’t do it before the statement is executed.