Shulou(Shulou.com)05/31 Report--

Editor to share with you about the database SQL SELECT query example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

As Web developers, although not professional DBA, but we always can not do without the database. The average developer will only apply four classic sentences of SQL: select,insert,delete,update. So much so that we have never studied how they work, so here we talk about how select works in the database.

The most classic topic in the Bax S architecture is nothing more than the three-tier architecture, which can be roughly divided into the data layer, the business logic layer and the presentation layer, while the role of the data layer is to interact with the database, such as querying records. We often write the query SQL, and then call the program to execute SQL. But what is the internal work flow? I think most of my friends are not as clear as I am about which step to do first and then which step to do.

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

When we execute the SQL statement in the data layer, the application connects to the corresponding database server and sends the SQL statement to the server for processing.

Step 2: the server parses the requested SQL statement

1.SQL plan cache, friends who often use query analyzer probably know the fact that it often takes a long time for a query statement to run for the first time, but if you run the same statement immediately or within a certain period of time, the query results will be returned in a very short time.

Reason:

After receiving the query request, the server does not immediately go to the database to query, but to find out whether there is a corresponding execution plan in the plan cache in the database, and if so, it directly calls the compiled execution plan, which saves the compilation time of the execution plan.

If the queried row already exists in the data buffer, there is no need to query the physical file, but the data is fetched from the cache, so that fetching data from memory is much faster than reading data from hard disk, and the query efficiency is improved. The data buffer store will be mentioned later.

two。 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.

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

4. 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.

5. The next step is the verification of database user rights. 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 insufficient permissions to the application. In larger projects, a project often contains several database connection strings. These database users have different permissions, some of which are read-only. Some are write-only, some are readable and writable, choose different users to execute according to different operations, and pay a little attention to it, no matter how perfect your SQL statements are.

6. 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 hashjoin,mergejoin or loopjoin according to the overhead cost, which index will be more efficient, and so on, but its automatic optimization is limited. If you want to write an efficient query SQL, you still need to optimize your own SQL query statement.

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.

Step 3: statement execution

After the server parses the SQL statement, the server will know what the statement represents, and then it will actually execute the SQL statement.

At this time, there are two situations:

If the data rows contained in the query statement have been read into the data buffer, the server will read the data directly from the data buffer and return it to the application, avoiding reading from the physical file and improving the query speed.

If the data row is not in the data buffer, the record is read from the physical file and returned to the application, while the data row is written to the data buffer for next use.

Note: there are several kinds of SQL cache. Friends who are interested can search it here. Sometimes because of the existence of cache, it is difficult for us to see the optimization result immediately. Because the second execution is very fast because of the existence of cache, it is generally necessary to eliminate the cache first, and then compare the performance before and after optimization. Here are several common methods:

DBCC DROPCLEANBUFFERS

Removes all purge buffers from the buffer pool.

DBCC FREEPROCCACHE

Removes all elements from the process cache.

DBCC FREESYSTEMCACHE

Releases all unused cache entries from all caches.

The SQLServer2005 database engine cleans up unused cache entries in the background in advance to make memory available 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 caching. At present, there seems to be no solution to completely eliminate caching. If you have one, please advise.

Conclusion: only by knowing the operation flow of the SQL submitted by the service execution application can we debug our application well.

Make sure the SQL syntax is correct

Ensure the semantic correctness of SQL, that is, whether the object exists

Whether the database user has the appropriate access rights.

The above is all the contents of the article "sample Analysis of Database SQL SELECT query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.