Improve system performance--Thinking of SQL statement optimization

The software is in the process of research and development to keep an eye on the scalability of the system. But at the same time, it is also concerned about the performance of the system, SQL statements as a part of system performance can not be ignored. Starting today with the experience of development, let me talk about my understanding and cognition of SQL statement optimization:

1, in the Union query statement to do small table driver large table:

Federated query is often used to a query method, left join, right connection, internal connection and so on from time to time to be applied in query statements, however, in this process is assumed to be able to ascertain the amount of data in each table, it is better just, in such a case should be followed by a small amount of data from the table. Why? Hehe, for example a table has 1000 data, B table has 20 data. Using a left connection for federated queries assuming a table Span style= "font-family: Microsoft Jas Black, Sans-serif" > driver b table Table B will be asked to be interviewed 1000 times, but assuming b table driver A table, a Just to be interviewed 20 times, its performance is conceivable.

2, do not use in+ subquery

Note that I do not say that you do not use in query, if the value in is given. Instead of making queries from other tables through Select, it is understandable to use Inkeyword, but if the data in is obtained from other tables by Select, it is strongly recommended that you change it to a federated query, in detail by reading the blog SQL statement optimization--in,not In,exists,not exists, left Join...on".

3, note wherekeyword The order of the query conditions, the query condition has not been given a value to the last side. Put the given non-fuzzy query condition to the front;

This blog continues to be updated in. Please look forward to it.

Improve system performance--Thinking of SQL statement optimization