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

1. Grammar check

If there is no corresponding execution plan in the SQL plan cache, the server will first validate the syntax of the SQL statement requested by the user. If there is a syntax error, the server will end the query operation and return the corresponding error message to the calling application.

Note: the error message returned at this time will only contain basic syntax error messages, such as select written as selec, etc. If the error message contains a column that is not in the list, the server will not check it at this time, because it is only syntax verification, and whether the semantics are correct is placed in the next step.

two。 Check semantics

After the syntax is consistent, it begins to verify that its semantics are correct. For example, whether database objects such as table names, column names, stored procedures, and so on actually exist, and if anything is found that does not exist, it will report an error to the application and end the query.

3. Get the parsing lock of the object

The next step is to get the parsing lock of the object. when we query a table, the server will lock the object first, in order to ensure the unity of the data. if there is no lock, there will be data insertion, but because there is no lock, the query has read this record, and some inserts will be rolled back due to transaction failure, resulting in dirty reading.

4. User access authentication

The next step is to verify the database user permissions. The syntax and semantics of SQL statements are correct, and the query results may not be obtained at this time. If the database user does not have the corresponding access rights, the server will report an error of insufficient permissions to the application. In larger projects, a project often contains several database connection strings. These database users have different permissions, some are read-only, some are write-only, and some are read and writeable. Select different users to execute according to different operations. A little careless, no matter how well-written your SQL statement is, it's useless to be perfect.

5.SQL optimization to select the most efficient query algorithm

The final step in parsing is to determine the final execution plan. When the syntax, semantics, and permissions are verified, the server will not immediately return the results to you, but will optimize your SQL and choose different query algorithms to return to the application in the most efficient form.

For example, when doing a table federation query, the server will finally decide whether to use hash join,merge join or loop join according to the overhead cost, which index will be more efficient, and so on. However, its automatic optimization is limited, and if you want to write an efficient query SQL, you still need to optimize your own SQL query statements.

When the execution plan is determined, the execution plan is saved to the SQL plan cache, and the next time there is the same execution request, it is taken directly from the plan cache to avoid recompiling the execution plan.

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.