1. For mysql, it is not recommended to use subquery and join because the efficiency of join itself is hard. Once there is a large amount of data, it is difficult to ensure the efficiency.
MYSQL > create temporary table (s); create temporary table (s); delete temporary table (s);
3. If it is a JOIN, it is a nested query. Small tables drive large tables and are associated by index fields. This is OK if the table records are small. Large words in business logic can control processing.
4. Database is the lowest level, and the bottleneck is often database. It is recommended that the database be used only as a tool for the data store, not to add business to it.
1. Advantages of application layer association
Make caching more efficient. Many applications can easily cache the corresponding result object of a single table query. If a table in the association changes, the query cache cannot be used, whereas after splitting, if a table rarely changes, queries based on that table can reuse the query cache results.
Breaking down the query and executing a single query reduces lock contention.
Association at the application layer makes it easier to split the database and achieve high performance and scalability.
The query itself may also be more efficient. When querying id sets, use IN () instead of associative query to make MySQL query by ID order, which may be more efficient than random association.
Can reduce redundant record queries. Associated query at the application layer means that a record application needs to be queried only once, whereas associated query in the database may need to be queried
Part of the data is accessed repeatedly. From this point of view, such refactoring may also reduce network and memory corruption.
Further, this is equivalent to implementing hash association in the application, rather than using MySQL’s nested loop association. Some scenarios hash associations much more efficiently.
2. Application layer association usage scenarios
When the application can easily cache the results of a single query
When data can be distributed to different MySQL servers
When you can use IN () instead of associative query
There are many concurrent scenarios and frequent DB queries, which require separate databases and tables
3. Reasons why join is not recommended
1.DB bears great business pressure, so the burden will be reduced if it can be reduced. When the table is in the million level, join degrades performance;
2. Distributed sub-database sub-table. Cross-library joins are not recommended in this case. At present, the distributed middleware of mysql has poor performance of cross-library join.
3. Modify the schema of the table. It is easy to modify the single table query, but it is not easy to discover the SQL statement written by JOIN.
4. Solution without JOIN
In the business layer, a single table queries data, as a condition for the next single table query. Subqueries. Worry about the result set of the subquery. Mysql does not limit the number of ins, but mysql limits the size of an entire SQL statement. You can modify the maximum value of an SQL packet by adjusting the max_allowed_packet parameter. You are advised to limit the number of result sets that can be queried at one time. **
5. Advantages of JOIN query
The advantage of associated query is that it can be paginated. You can use the fields of the secondary table to make the query conditions. During the query, the fields matched by the secondary table are taken as the result set and the primary table is used to enter it. The problem is that if the amount of data matched is too large, it will not work, and it will result in the return of paging records that are not the same as the actual data. The solution can be handed over to the front end, which can query the data once and let the front end display it in batches. The prerequisite of this solution is that the amount of data is not too large, because the SQL itself is limited in length.