Like what:
SELECT * FROM table1 where name= ' Zhangsan ' and TID > 10000
and implementation:
SELECT * FROM table1 where TID > 10000 and name= ' Zhangsan '
Some people do not know the execution efficiency of the above two statements is the same, because if it is simple to read from the statement, the two statements are indeed different, if the TID is an aggregation index, then the following sentence only from the table after 10,000 records to find the line , and the previous sentence to look up from the whole table to see a few name= ' ' Zhangsan ', and then according to the restrictions on conditions tid>10000 to provide the results of the query.
In fact, such fears are unnecessary. There is a query analysis optimizer in SQL Server that calculates the search criteria in the WHERE clause and determines which index reduces the search space for the table scan, that is, it enables automatic optimization.
Although the query optimizer can automatically query optimization based on the WHERE clause, it is still necessary to understand how the query optimizer works, and if not, sometimes the query optimizer does not follow your intent to make a quick query.
During the query analysis phase, the query optimizer looks at each stage of the query and determines whether it is useful to limit the amount of data that needs to be scanned. If a phase can be used as a scan parameter (SARG), it is called an optimization and can be used to quickly obtain the required data.
Sarg definition: An operation that restricts the search because it usually refers to a specific match, a match that is worth the range, or a connection of more than two conditions. The form is as follows:
Column name operator < constant or variable >
Or
< constant or variable > operator column name
The column name can appear on one side of the operator, and the constant or variable appears on the other side of the operator. Such as:
Name= ' John '
Price >5000
5000< Price
Name= ' John ' and Price >5000
If an expression does not satisfy the Sarg form, it cannot limit the scope of the search, that is, SQL Server must determine for each row whether it satisfies all the conditions in the WHERE clause. So an index is useless for expressions that do not satisfy the Sarg form.
After introducing the SARG, let's summarize the experience of using SARG and the different conclusions encountered in practice and some of the data:
1. Whether the like statement belongs to Sarg depends on the type of wildcard character used
such as: Name like ' Zhang% ', this belongs to Sarg
And: Name like '% Zhang ', does not belong to Sarg.
The reason is that the wildcard% is not available for indexing when the string is opened.
2, or will cause full table scan
Name= ' John ' and price >5000 symbol Sarg, and: Name= ' John ' or price >5000 does not conform to SARG. Using or can cause a full table scan.
3, non-operator, function caused by not satisfied with the Sarg form of statements
Statements that do not satisfy the Sarg form are typically those that include non-operator statements, such as not,!=, <>,!<,!>, not EXISTS, no in, not, and so on, as well as functions. Here are a few examples that do not satisfy the Sarg form:
ABS (Price) <5000
Name like '% three '
Some expressions, such as:
WHERE Price *2>5000
SQL Server also thinks that Sarg,sql server converts this into:
WHERE Price >2500/2
This is not recommended, however, because sometimes SQL Server does not guarantee that the conversion is completely equivalent to the original expression.
4, in the effect is quite with or
Statement:
Select * FROM table1 where tid in (2,3)
And
Select * FROM table1 where tid=2 or tid=3
Is the same, it will cause a full table scan, and its index will be invalidated if there is an index on the TID.
Current 1/3 page
123 Next read the full text
