MySQL > alter table associative query; Many high-performance applications decompose associative queries. Simply, you can perform a single table query on each table and then associate the results in your application.

For example, the following query:

mysql> SELECT FROM tag
    ->    JOIN tag_post ON tag_post,tag_id=tagid
    ->    JOIN post ON tag_post post_id=post.id
    -> WHERE tag.tag='mysql';
Copy the code

Can be broken down into the following queries instead:

mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123.456.567.9098.8904);
Copy the code

Decompose the advantages of associated query

  • Make caching more efficient. Many applications can easily cache the corresponding result object of a single table query. For MYSQL’s query cache, if a table in an 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 can reduce 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.In this example, useIN()Instead of associative queries, MYSQL can perform queries in order of ID, 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 require repeated access to some data. From this point of view, such refactoring may also reduce network and memory consumption.
  • 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

This abstract is from high Performance MySQL (3rd edition)

Welcome to follow my wechat official account: The Coder of wind and waves