Optimize SQL statements to improve overall efficiency

Optimize specific applications with high resource consumption

What is the first thing to do when a performance problem occurs? I have asked many colleagues about this question. Some people say that they should optimize problematic SQL statements based on experience. For example, we generally say that we should reasonably use indexes and try not to use the Like statement with the "*" sign in front, do not compare the operator's front side for calculation or use of functions. These paths are correct, but experience sometimes may not solve the problem. When a problem occurs, the first thing to do is to determine what the problem is. Only after the problem is correctly identified can the problem be solved. The following is a brief introduction of how to determine the problem.

1. First, understand the functions of the Service, understand the real intention of the user, what the user really cares about, what the data he wants, and whether there are convenient and concise methods to meet user requirements. Instead of using complex SQL queries. In fact, some modifications can achieve the same purpose, but the SQL statements used have been greatly simplified. This is a top priority for solving performance problems.

2. when optimizing a fixed SQL statement, you must pay attention to the data volume related to the query and the size of the data volume. In some cases, you must make a query. If the query conditions are not properly processed, the returned record set is too large, which is of little significance to users. The key is that this will inevitably lead to a large number of disk IO, and performance problems are inevitable. Unless the user really needs so much data, but it turns out that most of them are not, so the focus is on how to limit the size of the returned record set or the size of the temporary intermediate data set used in the query. In this way, your optimization will be effective.

The following describes several common SQL methods for checking the problem.

Of course, there are some tips, such:

Paste the problematic SQL statement in the query analyzer and set statistics io to on. You can also right-click in the blank space and choose <query Options>,

Select <advanced>

Select Set Statistics Io.

In addition to the result set, you can also obtain the IO information related to this query, for example:

We generally focus on the number of logical reads. When multiple tables are jointly queried, the IO information of each table is displayed here. When the number of logical reads of a table is large, you need to focus on and analyze this table. Is it because the number of records in this table is too large during the query? Is the Index not properly used, can other filtering conditions be added to reduce related record sets. The following is a brief description:

Output description

The name of the Table.

Number of indexes or table scans performed by Scan count.

The number of pages that logical reads from the data cache.

The number of pages physical reads from the disk.

The number of pages cached by read-ahead reads for query.

The number of pages of the lob logical reads type (varchar (max), nvarchar (max), and varbinary (max) that are read from the data cache.

The number of pages of the text, ntext, image, or plain type read from the disk by lob physical reads.

Lob read-ahead reads refers to the number of cached text, ntext, image, or plain pages for query.

Disk I/O information is introduced here first. Another reference data is the number of milliseconds required to use the set statistics time on reference to display analysis, compilation, and execution statements. The specific usage method is basically the same as set statistics io on, but it only displays the time information of the analysis compilation and execution used for this query. You must understand it at first glance. I will not go into details here.

After the query is executed, in addition to the execution result set, the SQL statement execution configuration information is also displayed in the form of a record tree, corresponding to each step in the execution plan, for example, the index type used in a step, the number of evaluated rows, IO information, and time information. This information can be used for reference to determine where the SQL statement of this segment is located.

Analyze the data retrieval method selected by the SQL Server Query Optimizer when executing the current statement by referring to the estimated execution plan of the current statement or the actual execution plan.

The actual execution plan shows the execution plan used for this execution. The figure should look from the right to the left, from the bottom to the top. If you are querying multiple table connections, multiple execution steps will also be displayed here, you can check the operation information related to each step, such as IO overhead, CPU overhead, estimated number of rows, whether the Index is used, and the Index used. Pay attention to the fact that there are too many rows. The Indexl type used is also one of the information that needs attention.

The following is a brief description of some concepts in the execution plan:

Tooltip description

Physical operators used by Physical Operation, such as Hash Join or Nested Loops. The red physical operator indicates that the query optimizer has issued a warning, for example, missing column statistics or missing join predicates. This may cause the query optimizer to select a query plan that is less efficient than expected. For more information about column statistics, see use statistics to improve query performance.

When we recommend that you create statistics, update statistics, or create indexes for a graph execution plan, you can use the shortcut menu in the object Resource Manager of SQL Server Management Studio to immediately create or Update Missing column statistics and indexes. For more information, see the topic of the index operation guide.

Logical operators that match Logical Operation and physical operators, such as Inner Join operators. Logical operators are listed behind physical operators, both of which are at the top of the tooltip.

The Estimated Size of the Row generated by the Estimated Row Size operator ).

Estimated I/O Cost is used to estimate the overhead of all I/O activities that perform operations. This value should be as low as possible.

Estimated overhead of all CPU activities that Estimated CPU Cost uses to perform operations.

The overhead of the query optimizer that Estimated Operator Cost uses to perform this operation. The overhead of this operation is displayed in parentheses as a percentage of the total overhead of the query. Because the query engine selects the most efficient operation to execute the query or execution statement, this value should be as low as possible.

Estimated Subtree Cost query optimizer performs this operation and the total overhead of all operations prior to this operation in the same Subtree.

Estimated Number of The Number of Rows generated by the Rows operator.

Based on the above several reference information methods, you can generally determine the problem of the problem SQL, and then take the right medicine, the rest is to make targeted changes, here is just a reference, if you are smart, you must have a solution.

Edit recommendations]

1. Tips for improving MySQL database query efficiency 3)
2. How to obtain the record row number in the MySQL query result set
3. How to configure master-slave replication for MySQL Databases