SQL Server query optimization in layman's view

At present, the application of network database has become one of the most widely used, and the security of database, performance is the most important thing that enterprises care about. The database is becoming the lifeblood of the enterprise, and optimizing the query solves every performance issue with regard to database applications, and here's what Microsoft SQL Server has done for us.

Optimization query We understand that it seems very abstract, the scope is very large, about the database here, the optimization query is actually very simple, do a simple example. A packet passes through an infinite number of routers to reach its destination address, and if the router will point to the nearest path when it passes through each router, then he is at the fastest speed. In the database is also the same, query optimization is to establish a signpost, is to this thick book, write a simple and easy to use the directory, so that the computer can quickly read the data we query. From here, it can be thought that the optimization query is the establishment of the directory, which we call the index.

The library is usually compared to a database, and the book catalogue corresponds to our index.

Classify books, and books of the same kind are placed in the same bookshelf.

To the book number, the number is about the location of the bookshelf, the location of the bookshelf to decide.

The numbering is counted.

In this way, we have indexed the library, the establishment of the index, the database query efficiency is not only 2.3 times times, sometimes improve the astonishing.

Knowing the index, we naturally think about the design of the index, the different query index will be different query statements to optimize the query. So what index do we want to get by simply clicking on the mouse? Microsoft SQL Server has addressed this issue. Let's take a look at how it is done. This simple operation is once again exaggerated to what extent!

Open the table that needs to be indexed in SQL Server, right-click Index items, we can see the displayed window here, if we say that our application is as simple as the library, then we can click here to complete the operation of the mouse, if we are more complex query method? Or is there a more efficient way to improve the efficiency of the space? That's what I'm going to say.

Often we need not so neat index model, such as we do the application of the site, the database is in what way to call the data, according to such a way, we build the corresponding index, our needs will be achieved. It should be cumbersome to associate with this operation, which requires a deep understanding of SQL queries, and here Microsoft's SQL Server has prepared the appropriate strategy for us, and we will explain it in a simple case.

Start by typing the statement we need to query and look at his operator overhead, but the smaller the overhead, the greater the increase.

We query all the information about this phone number, the operator overhead is 0.134137, here is not, you can see the mouse in the index lookup. Select our query statement and click Analyze Query.

Click Start Analysis in the upper left corner to get the analysis results.

After copying to the Clipboard, we create a new query, copy the statements in, and then execute. The new index is automatically generated, and the index is much more efficient than the simple index we created manually.

In our view, the query rate has increased by more than 40 times, a very exaggerated number. This creates an index that will be effective for all similar queries, greatly improving the efficiency of database queries, and implementing optimized queries.

SQL Server query optimization in layman's view