Analysis of the advantages of MySQL using multiple queries instead of a complex join query

See: http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt344

Multi-performance applications will decompose the associated query. Simply, you can make a single-table query for each table, and then associate the results in your application. For example, the following query:

Can be decomposed into the following queries instead:

Why on earth do you do this? At first glance, this does not do any good, originally a query, here has become a number of queries, the return of the results are identical. In fact, refactoring the query in a way that decomposes the associated query has the following advantages:

? make the cache more efficient. Many applications can easily query the corresponding result object in a table of CDs. For example, the tag in the query above is already cached, so the application can skip the first query. For example, content with IDs 123, 567, 9098 is already cached in the app, so there are fewer IDs in the third query in (). In addition, the query cache for MySQL Note 6, if a table in the association changes, then the query cache can not be used, and after splitting, if a table is rarely changed, then the query based on the table can reuse the query cache results.

? After the query is decomposed, executing a single query can reduce the competition for locks.

The application layer makes it easier to split the database, making it easier to perform high-performance and scalable.

The efficiency of the query itself may also be improved. In this example, using in () instead of an associative query allows MySQL to query in the order of IDs, which may be more efficient than a random association. This will be covered in detail in our follow-up.

? You can reduce the query for redundant records. Making an association query at the application level means that for a record application to be queried only once, and in the database for an associated query, you may need to access a subset of the data repeatedly. From this point of view, this refactoring can also reduce the network and memory of the extinction.

? Further, this is equivalent to implementing a hash association in the application instead of using the nested Loop Association of MySQL. Some scenario hash associations are much more efficient.

In many scenarios, it is much more efficient to put associations into applications by refactoring queries, such as when the application can easily cache the results of a single query, when data can be distributed to different MySQL servers, when it is possible to use in () instead of the associated query, When using the same data table in a query.

Analysis of the advantages of MySQL using multiple queries instead of a complex join query