Complex SQL optimization steps and tips

Data management system, due to user requirements or system design requirements, there will be a large number of tables to join, but also to carry out a large number of statistical data query display, and even data permission restrictions and so on operations.

The result is that SQL is unusually complex, and as the amount of data increases, or if it is applied to a production environment (formal environment), the system slows down and experiences very poor behavior, which

Complex SQL for optimization. Inexperienced programmers will feel the impossible, so complex SQL statements look at a headache, how to do?

According to individual work experience to provide the optimization steps:

The first thing to do is to format the SQL statements so that the SQL is clear, and even add comments in steps, figuring out what each step is for;

In the second step, the first formatted SQL is compared with the final requirement, and if there is no table, the join is not done, and the unused fields do not return.

In the third step, the sub-bar checks to find out which table query is slow, or which condition causes the query to be slow.

The fourth step, with the SQL execution plan, this because they are not good at looking at execution plan, can only try to avoid full table scan, early limit the scope of SQL data filtering.

The fifth step is to check if an inappropriate in query is used, and the excess in will result in a sudden decrease in efficiency.

The sixth step, check whether there are too many or queries, or will cause a full table scan, can avoid as far as possible.

The seventh step is to check if the subquery is too complex, or if the SQL processing is too complex, if you can put the complex processing in the program to be processed.

The eighth step, can be replaced with exists place, with exists to replace.

The Nineth step, you can build a view, SQL simplification, as to whether the view can improve query efficiency, I do not know, need to find time to study.

The above steps are accurate, it is possible to take the method, in addition to the first few, the other has no sequencing.

These tips are the boss of the Guide to the SQL optimization experience, the record can not only summarize their own harvest, but also hope to collide with everyone's views, mutual progress!

Complex SQL optimization steps and tips