Why MySQL does not recommend using subqueries and joins

To do a paginated query:

1. For MySQL, it is not recommended to use subqueries and joins because the efficiency of joins is a flaw. Once the amount of data is large, the efficiency is difficult to guarantee. It is strongly recommended to retrieve data from a single table based on the index, and then join and merge the data in the program.
2. Don't use subqueries, the efficiency is too low. When executing subqueries, MYSQL needs to create temporary tables and delete these temporary tables after the query is completed. Therefore, the speed of subqueries will be affected to a certain extent. There is an additional process of creating and destroying temporary tables.
3. If it is JOIN, it uses nested query. The small table drives the large table and is associated through index fields. If the table has fewer records, it is OK. If it is large, it can be controlled and processed in the business logic.
4. The database is at the bottom layer, and the bottleneck is often the database. It is recommended that the database be used only as a data store tool without adding any business to it.

1. Advantages of Application Layer Association

Make cache more efficient. Many applications can easily cache the result objects corresponding to single-table queries. If a table in the association changes, the query cache cannot be used. After the split, if a table rarely changes, the query based on the table can reuse the query cache results.

Breaking down the query into individual parts can reduce lock contention.

By making associations at the application layer, it is easier to split the database and achieve high performance and scalability.

The efficiency of the query itself may also be improved. When querying an id set, using IN() instead of a join query allows MySQL to query in order of IDs, which may be more efficient than a random join.

This can reduce the number of redundant record queries. Doing an associative query at the application layer means that the application only needs to query a certain record once, while doing an associative query in the database may require

To access a portion of data repeatedly. From this point of view, such a reconstruction may also reduce network and memory consumption.

Furthermore, this is equivalent to implementing a hash join in the application, rather than using MySQL's nested loop join. In some scenarios, hash association is much more efficient.

2. Application layer association usage scenarios

When the application can conveniently cache the results of a single query

When you can distribute data across different MySQL servers

When IN() can be used instead of a join query

There are many concurrent scenarios and frequent DB queries, so sharding is required

3. Reasons why join is not recommended

1. DB is under great business pressure and would like to reduce the burden as much as possible. When the table has more than one million pages, joins cause performance degradation.
2. Distributed sub-libraries and sub-tables. Cross-database join is not recommended at this time. Currently, MySQL's distributed middleware has poor cross-database join performance.
3. Modify the table schema. It is easier to modify a single table query, but it is not easy to find and the cost is relatively high if the SQL statement written for join needs to be modified. When the system is large, it is difficult to maintain.

4. Solution without join

At the business layer, after a single table is queried for data, it is used as a condition for the next single table query. That is, a subquery. I am worried that there will be too many result sets from the subquery. MySQL has no limit on the number of INs, but MySQL limits the size of the entire SQL statement. By adjusting the parameter max_allowed_packet, you can modify the maximum value of a SQL statement. It is recommended to properly handle the business and limit the result set of a single query to be acceptable.

5. Advantages of join query

The advantage of associated query is that it can be paginated and the fields of the secondary table can be used as query conditions. When querying, the fields matched by the secondary table are used as the result set and the primary table is used to in it. But the problem is, if the amount of matched data is too large, it will not work, and the returned paging records will be different from the actual ones. The solution can be handed over to the front end, a one-time query, and let the front end display them in batches. The premise of this solution is that the amount of data is not too large, because the length of SQL itself is limited.

This concludes this article on why MySQL does not recommend the use of subqueries and joins. For more relevant MySQL subqueries and joins, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!