2. Index selection
For each optimisable clause, the optimizer looks at the database system table to determine whether there are relevant indexes that can be used to access the data. Only when the columns in the index
This index is considered useful only if 1 prefix of 1 matches the column in the query clause. Because the index is constructed in the order of columns, the required match is
Exact match. For clustered indexes, the original data is also sorted according to the index column order. Want to access data using indexed secondary columns, just like looking up in a phone book
Just like finding all entries with a last name, sorting is basically useless, because you still have to look at each row to determine whether it meets the criteria. If 1 clause
There are available indexes, and the optimizer determines the selectivity for it.
Therefore, during the design process, all queries should be carefully checked according to the query design criteria, and the index should be designed based on the optimization characteristics of the query.
(1) A relatively narrow index has relatively high efficiency. For narrower indexes, more index rows can be stored on each page, and the index levels are also small.
Therefore, more index pages can be placed in the cache, which also reduces I/O operations.
(2) SQL Server optimizer can analyze a large number of index and merge possibilities. So compared with fewer wide indexes, more narrow indexes can provide more to the optimizer.
choice. But don't keep unnecessary indexes as they will increase storage and maintenance expenses. For composite indexes, composite indexes, or multi-column indexes, SQL
The Server optimizer only retains the distribution statistics of the most important columns, so that the first column of the index should be very selective.
(3) Too many indexes on the table will affect the performance of UPDATE, INSERT and DELETE, because all indexes must be adjusted accordingly. In addition, all pagination
All operations are recorded in the log, which also increases I/O operations.
(4) Indexing a column that is frequently updated will seriously affect performance.
(5) Due to storage expenses and I/O operation, smaller self-indexes have better performance than larger indexes. But its disadvantage is that it needs to maintain self-organized
List.
(6) Try to analyze the frequency of use of each important query, so that the indexes that are used the most can be found, and then these indexes can be optimized appropriately first.
(7) Any column in the WHERE clause in the query is likely to be an index column, because the optimizer focuses on handling this clause.
(8) Indexing small tables with less than 1 range is not cost-effective, because table scanning is often faster and cheaper for small tables.
(9) Columns used with "ORDER BY" or "GROUP BY" are generally suitable for triaging indexing. If there is a clustering cable on the column used in the "ORDER BY" command
, then no more worksheet will be generated because the rows have been sorted. The "GROUP BY" command must generate 1 worksheet.
(10) Clustered indexes should not be constructed on frequently changing columns, as this will cause the entire row to move. Pay special attention to this when implementing large transaction processing systems.
One thing is that data in these systems tend to change frequently.
3. Merge selection
When the index selection ends and all clauses have a processing fee based on their access plan, the optimizer starts executing the merge selection. Merge selection
Select to find a valid order for merge clause access plans. To do this, the optimizer compares the different sorts of clauses and selects from Physical Magnetics
From the perspective of disk I/O, the merger plan with the lowest processing cost is viewed. Because the number of clause combinations will grow very rapidly with the complexity of the query, the SQL Server query optimizer enables
Use tree pruning techniques to minimize the expenses caused by these comparisons. When this merge selection phase ends, the SQL Server query optimizer has generated 1 based on
Fees query execution plan, this plan takes full advantage of available indexes and accesses original data with minimal system expenditure and good execution performance.
3.2 Efficient query selection
It is not difficult to see from the above three stages of query optimization that designing a solution with the least physical I/O and logical I/O and mastering the balance between processor time and I/O time is
The main objectives of efficient query design. In other words, I hope to design such a query: make full use of indexes, least disk read and write, and make the most efficient use of memory and CPU.
resource.
The following suggestions are summarized from the optimization strategy of the SQL Server optimizer, which is very helpful for designing efficient queries.
1. If there is a unique index, then the WHERE clause with the "=" operator performs the best, followed by the closed interval (range), and then the open area.
between.
2. From the perspective of database access, the WHERE clause containing discontinuous connections (OR and IN) generally does not perform well. Therefore, the optimizer may adopt
R policy, this strategy generates a worksheet with an identifier for each possible execution that matches. The optimizer regards these line identifiers (page number and line number) as
Points to the "dynamic index" of matching rows in 1 table. The optimizer only needs to scan the worksheet, take out each row identifier, and then get the corresponding row from the data table, so
The cost of R strategy is to generate worksheets.
3. The WHERE clause containing NOT, <>, or != is of no use for the index selection of the optimizer. Because such clauses are exclusionary, not
Inclusive, so the selectivity of the clause cannot be determined before scanning the entire original data table.
4. Limit data conversion and string operations. The optimizer generally does not generate index selections based on expressions and data conversion in the WHERE clause. For example:
paycheck * 12>36000 or substring(lastname,1,1)=“L”
If the table has an index for paycheck and lastname, it cannot be optimized using the index. The above conditional expression can be rewrite as:
paycheck<36000/12 or lastname like “L%”
Local variables in clauses are considered not known and considered by the optimizer, with the exception being variables defined as variables that are stored in the process input parameters.
6. If there is no index containing the merge clause, the optimizer constructs 1 worksheet to store the rows in the smallest table in the merge. Then construct 1 on this table
clustered indexes to complete an efficient merge. The cost of this approach is the generation of the worksheet and the subsequent generation of the triage index. This process is called
REFORMATTING. Therefore, you should pay attention to the size of the database tempdb in RAM or on disk (except the SELECT INTO statement). Also, if these types of
Operations are very common, so putting tempdb in RAM is very beneficial to improve performance.
4 Other considerations for performance optimization
The above lists some of the main factors affecting SQL Server, but in fact it is far more than these. The operating system has a great impact. Under Windows NT, the file system
The choice of unified selection, network protocol, enabled services, and SQL Server priority also affects the performance of SQL Server to varying degrees.
There are so many factors that affect performance, and the applications are different. It is unrealistic to find a general optimization solution. It must be done in the process of system development and maintenance.
It must be adjusted continuously according to the operation situation. In fact, most of the optimization and adjustment work is carried out on a server independent of the client, so it is also
Realistic and feasible.
Previous page12Read the full text