SELECT query Principle

I am not a professional dBA, but as a B/S architecture developer, I can't do without a database. Generally, the developer only applies four classic SQL statements: Select, insert, delete, and update. But I have never studied how they work. In this article, I want to talk about how select works in the database. The most classic topic in the B/S architecture is nothing more than a three-tier architecture, which can be divided into the data layer, business logic layer, and presentation layer. The role of the data layer is generally to interact with databases, such as query records.
We often write the query SQL and then call Program Execute SQL. But what is the internal workflow? I think most of my friends may not be clear about what to do first and what to do.

Step 1: The application sends the SQL query statement to the server for execution.

When we execute SQL statements at the data layer, the application will connect to the corresponding database server and send the SQL statements to the server for processing.

Step 2: The server parses the request's SQL statement.

1: SQL plan CacheThe common query analyzer may know the fact that a query statement needs to be executed for a very long time during the first running, however, if you run the same statement immediately or within a certain period of time, the query results will be returned within a short period of time.

Cause:

1): After receiving the query request, the server does not query the database immediately. Instead, it checks whether there is an execution plan in the Plan cache of the database. If yes, the compiled execution plan is called directly, saving the Compilation Time of the execution plan.

2): If the queried row already exists inData Buffer Storage ZoneIn, you do not need to query physical files, but retrieve data from the cache. In this way, retrieving data from the memory is much faster than reading data from the hard disk, improving the query efficiency. the data buffer storage area will be mentioned later.

2:If no execution plan exists in the SQL plan cache, the server first performsSyntax VerificationIn the event of a syntax error, the server will end the query operation and return the corresponding error message to the application that calls it.

Note:At this time, the returned error message only contains basic syntax error information, such as the SELECT statement as selec. If the error message contains a column that is not in the list, at this time, the server will not check the syntax, because it is just a syntax verification, whether the semantics is correct in the next step.

3:After the syntax is met, verify itsIs the semantics correct?For example, whether the table name, column name, stored procedure, and other database objects actually exist. If any nonexistent objects exist, an error is reported to the application and the query is terminated.

4:NextGet the object resolution lockWhen querying a table, the server First locks the object to ensure data uniformity. If no lock is applied, data is inserted at this time, but there is no lock, the query has read this record, and some inserts will roll back because of transaction failure, which will lead to dirty reads.

Note:This summary was prompted by Zhou Qiang, yuanyou, and deep blue. I went to the bookstore last weekend and checked <SQL 2005 Technology insider>. When will the server obtain the object resolution lock, there is no obvious explanation. I only know that shared locks are usually applied to access objects during query operations. If you give an authoritative explanation in this regardArticle, You can advise.

5:NextDatabase User permission VerificationThe SQL statement syntax and syntax are correct. The query results may not be obtained at this time. If the database user does not have the corresponding access permission, the server reports an error due to insufficient permissions to the application, in a slightly larger project, a project usually contains several database connection strings. These database users have different permissions, including read-only permissions, write-only permissions, and read-write permissions, select different users for execution based on different operations. If you do not pay attention to them, no matter how well your SQL statements are written, they will be useless if they are perfect.

Note:This conclusion is the same as the note below 4th. I hope you will give me some advice.

6:The last step of parsing isDetermine 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 select different queries.AlgorithmReturns to the application in the most efficient form. For example, when performing table join queries, the server will decide whether to use Hash join, merge join, or loop join Based on the overhead cost. Which index will be more efficient, however, its automatic optimization is limited. To write efficient query SQL statements, you must optimize your own SQL query statements.
After the execution plan is determined, the execution plan will be saved to the SQL plan cache. The next time you have the same execution request, it will be directly retrieved from the plan cache, avoid re-compiling the execution plan.

 

A description of Zhou Qiang's point of view:

----------------- Reference -------------------------

5. If the "algebrizer" component can correctly complete step 1, a query processor tree will be generated for query optimization. After the optimization is completed, the final execution plan is generated.
6. the above process is called "Compilation" and "optimization ".

----------------------------------------------

 

Through the above description, we can clearly see that optimization is taken as a step after the execution plan. In fact, in <SQL 2005 Technology insider>, optimization is taken as the last part of the Execution Plan, optimization is part of the Execution Plan, rather than after the execution plan. Other points of view are basically agreed.

Step 3: Execute the statement.

After the server completes parsing the SQL statement, the server will know what the statement actually means. Then, the server will actually execute the SQL statement.
There are two situations:

1): if the data row contained in the query statement has been readData Buffer Storage ZoneThe server will directly read data from the data buffer area and return it to the application, avoiding reading from physical files and improving the query speed.

2): if the data row is not in the data buffer area, it will read the record from the physical file and return it to the application. At the same time, it will write the data row into the data buffer area, for the next use.

Note: The SQL cache is divided into several types. If you are interested, you can search for it. Sometimes, because of the existence of the cache, it is difficult for us to see the optimization results immediately, because the second execution will be extremely fast because of the existence of a cache, the cache is usually eliminated first, and then the performance before and after optimization is compared. Here are several common methods:

DBCC dropcleanbuffers

Delete all cleared buffers from the buffer pool.

DBCC freeproccache

Deletes all elements from the process cache.

DBCC freesystemcache

Release all unused cache entries from all caches. SQL Server 2005 database engine clears unused cache entries in the background in advance so that the memory can be used for the current entry. However, you can use this command to manually delete unused entries from all caches.

 

This can only basically eliminate the impact of SQL cache. At present, it seems that there is no solution to completely eliminate the cache. If you have any, please advise.

Conclusion:Only by knowing the SQL operation process submitted by the Service to execute the application can we debug our application well.
1: Make sure that the SQL syntax is correct;
2: Make sure the SQL semantics is correct, that is, whether the object exists;
3: whether the database user has the corresponding access permission.

Note:

Reference:

Http://database.ctocio.com.cn/tips/210/7791210.shtml

http://tech.it168.com/a2008/0805/199/000000199573.shtml