Improving system performance-Thoughts on SQL statement Optimization

In the process of software development, the scalability of the system has been noticed from beginning to end, but at the same time, the system performance is also concerned. As a part of the system performance, SQL statements cannot be ignored, starting from today, I will talk about my understanding and cognition of SQL statement Optimization Based on my development experience:

1. Small table-driven big table in the joint query statement:

Joint query is a commonly used query method. Left join, right join, internal join, and so on are occasionally applied in the query statement, however, in this process, if we can determine the data volume of each table, it would be better. In this case, the from should be followed by a table with a small amount of data. Why? For example, if Table a has 1000 data records and table B has 20 data records, use a left join for joint query. If table a drives table B, table B will be accessed 1000 times, however, if Table B drives Table a, as long as Table a is accessed 20 times, its performance can be imagined.

2. Do not use in + subqueries

Please note that I didn't mean not to use in queries here. If the value in is given rather than obtained from other tables by using select, The in keyword is understandable, however, if the data in is obtained from other tables through the select statement, we strongly recommend that you change it to the Union query method. For details, refer to the blog 《SQL statement Optimization-in, not in, exists, not exists, left join... on".

3. Pay attention to the order of the query conditions following the where keyword, put the query conditions that have not been set to the end, and put the given non-fuzzy query conditions at the top;

This blog is continuously updated and will be available soon.