Shulou(Shulou.com)06/02 Report--

This article mainly introduces "what are the methods of SQL optimization". In the daily operation, I believe that many people have doubts about the methods of SQL optimization. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the questions of "what are the methods of SQL optimization?" Next, please follow the editor to study!

In the initial stage of the development of the application system, due to the lack of database data, we can not realize the advantages and disadvantages of various grammars of SQL statements for querying SQL statements and writing complex views, but with the rise of Internet big data and the increase of data in the database, the response speed of the system has become one of the most important problems that the system needs to solve at present.

A very important aspect of system optimization is the optimization of SQL statements. For massive data, the speed difference between low-quality SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that for a system can not simply achieve the function, but to write high-quality SQL statements to improve the availability of the system.

In most cases, Oracle uses indexes to traverse tables faster, and the optimizer improves performance mainly based on defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, it will cause the optimizer to delete the index and use the full table scan. Generally speaking, this kind of SQL statement is the so-called inferior SQL statement. When writing the SQL statement, we should know what the optimizer is based on to delete the index, which is helpful to write high-performance SQL statements. We need to be able to write not only SQL but also SQL with good performance.

Common optimization rules:

Table connection

-the more tables you join, the worse the performance

-if possible, split the connection into several processes one by one

-give priority to connections that significantly reduce the amount of data, which can reduce complexity or easily perform as expected

-if multiple table joins are inevitable, it is likely to be a design flaw

-the effect of the external join is poor, because the external join must scan the left and right tables

-use inner join queries whenever possible

2. Use temporary tables

If it is inevitable, consider using temporary tables or table variables to store intermediate results

Third, use less subqueries

Fourth, view nesting

Do not be too deep, the general view nesting should not exceed 2.

Considerations for SQL authoring:

1. There is no point in using an index for null columns, and any column that contains null values will not be included in the index, so the statement optimizer of is null or is not null in the where statement does not allow the use of indexes.

2. Concat or | if you perform this function operation on a column, the use of the index will also be ignored

3. Like, the wildcard appears in the first place, and the index cannot be used, otherwise you can

4. Do not use non-indexed columns or nested expressions in the order by clause

5. Not or! = cannot use index, use instead

6. Where and having. If the filtering behavior in having can be done in wehre, where should be given priority.

7. Exists replaces in,not in 4 as the least efficient because the table of the subquery is scanned all over the table. Consider using external connections or not exists

8. Index, do not add indexes to unnecessary fields, SQL statements as uppercase as possible, do not use functions on indexes

9, >, > =, it is recommended to use > =

10. Use union instead of or

11. Union and union all,union have de-duplication operation, which increases the computing time. Union all does not need to be de-duplicated, but will contain the same record. Under the same function, union all operation is preferred.

At this point, the study of "what are the methods of SQL optimization" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.