Let's talk about how to avoid using join queries to optimize database queries when there is a large amount of database data.

Boss said that we should try to avoid using join to query a large amount of data at ordinary times. We would rather query the data in a table at a time and then use the data for query. We should not use join to divide the data into multiple queries as much as possible, please let me talk about the SQL optimization boss. I usually want to query a large amount of data and try to avoid using join. I 'd rather query the data of a table at a time than using the data for query. join is divided into multiple queries as much as possible, let me talk about SQL optimization.

Reply content:

Boss said that we should try to avoid using join to query a large amount of data at ordinary times. We would rather query the data in a table at a time and then use the data for query. We should not use join to divide the data into multiple queries as much as possible, let me talk about SQL optimization.

If a relational database is used, it is natural to join tables unless there are some special cases:

1. The SQL statement is too complex or the statistical information is not accurate. As a result, the execution plan generated by the database is incorrect, resulting in low operation efficiency and the SQL statement cannot be rewritten in a short time.

2. The data volume is particularly large (at least million), and the database load becomes the performance bottleneck of the entire system. During the architecture design, the database table connection function is not required.

3. The ORM framework is used. By default, N + 1 queries are used for correlated objects. If the data volume is not large and the cache function is available, the efficiency is not low.

Therefore, it is more efficient to use the join function of the database than to fetch data multiple times for association. Otherwise, a big data table is transmitted to PHP, it takes a long time.

The most basic principle of SQL optimization is to let the database filter data as soon as possible and efficiently to avoid invalid operations. There are many specific methods and the implementation scheme should be determined based on different databases.

Please do it yourselfexplain.

It depends on whether your database link is a persistent link. If it is configured as a persistent link, it is better. However, to join, you can first use the where or other methods to reduce your primary table, don't pull it up, so the performance will be much better. But let's take a look at the specific problem. Once you run it, the result will be displayed. However, if it is not a long link, it is estimated that the table will be faster.

The reason for dividing it into multiple queries is that, whether it is join or subquery, the mysql optimizer will optimize the SQL statement (subquery will be smart to do bad things, and join will occasionally)
Therefore, you can write data one by one.

The join method is actually quite good, mainly because the index is not so handy and prone to problems.

Therefore, your boss directly said that join should not be used for big data.

The reason for separate query: for example, if I get the primary key id of the corresponding side and then query it in another table, the efficiency must be high.

One sentence: the simpler the query, the higher the efficiency.

I really don't understand that when the data volume is large, the join operation will lead to poor performance. If the data volume is large, it takes several hundred megabytes to query a table separately, and it takes a long time to load it into the program.

1. The index will speed up the query

2. Use cache (memchache, etc)