Comparison of efficiency between single-table query and multi-table join query in MySql database

During this period of time, while working on a project, I encountered a module where the connections between the data were very complex. I was very confused when creating the table. How should I deal with these complex data? Should I query a single table and then handle the relationship between the data at the business layer, or should I directly handle the data relationship through multi-table connection query?

After consulting materials and reading blogs, there are two answers:

1. Answers in "High Performance MySQL"

Many high-performance applications decompose associated queries. Simply, you can do a single table query on each table and then join the results in your application. For example, the following query:

select * from tag
join tag_post on tag_post.tag_id=tag.id
join post on tag_post.post_id=post.id
where tag.tag='mysql';

Can be broken down into the following queries instead:

Select * from tag where tag='mysql';
Select * from tag_post where tag_id=1234;
Select * from post where id in(123,456,567,9989,8909);

Why on earth do this?

At first glance, there is no benefit in doing this. The original query has become multiple queries here, and the returned results are exactly the same.

In fact, reconstructing queries by decomposing associated queries has the following advantages: (In high-concurrency, high-performance applications, it is generally recommended to use single-table queries)
1. Make cache more efficient. Many applications can easily cache the result objects corresponding to single-table queries. In addition, for MySQL query cache, if a table in the association changes, the query cache cannot be used. After splitting, if a table rarely changes, the query based on the table can reuse the query cache results.

2. After breaking down the query, executing a single query can reduce lock contention.

3. Making associations at the application layer makes it easier to split the database and achieve high performance and scalability.

4. The efficiency of the query itself may also be improved.

5. It can reduce the query of redundant records.

6. Furthermore, this is equivalent to implementing hash joins in the application, rather than using MySQL's nested ring joins. In some scenarios, hash joins are much more efficient.

7. Single-table query is beneficial for later splitting of databases and tables when the amount of data is large. If joint query is used, once the database is split, the original SQL needs to be modified.

8. Last time I saw a CTO sharing his technology, the company stipulated that join queries were prohibited at the bottom level. It is indeed slow when the data is large.

9. Join queries may indeed be fast, but MySQL resources are usually much more scarce than program code resources.

2. Some other answers

Hypothetical scenario: Suppose the website has a company library section, and I want to search for all companies in a certain city.

Data tables: tbl_company (t1), tbl_city (t2).

Example 1:

The t1 table stores cityid and performs table join query based on id

select * from t1 inner join t2 on t1.cityid=t2.cityid;

Example 2:

The table t1 stores cityName. When the user clicks Shanghai on the front end, the id of Shanghai is passed to the back end (without considering passing cityName).

Find cityName based on id select cityName from t2 where cityid= #{cityid};

Then select * from t1 where cityName = #{cityName};

The difference between the two: Example 1 only performs one table association query, while Example 2 performs two single table queries.

Considering the large amount of data, multi-table join queries will affect query efficiency, so they are all optimized to single-table queries. TP: The above is without using indexes

Which one will be more efficient?

Answer: SQL optimization is also related to the business. Will the query of this statement be frequent? Should the overhead caused by two connections be considered? If these do not need to be considered and there is no index, the difference will not be big. 2 should be slightly better than 1.

If the data is not particularly large, cascade query is still faster.

For traditional databases, reduce the number of database queries as much as possible.

BUT, 1. MySQL is very fast at handling connections/disconnections and replying to small and simple queries; 2. Today's networks are very fast. So multiple small queries may be faster for MySQL.

Finally, the master has no conclusion on which one is better. Haha, in fact, the whole book clearly expresses one meaning, test! Benchmark! For your own data environment, test both methods. Let the data speak for itself.

Conclusion

Personally, I suggest using a single table query! It would be better to associate data at the application layer!

The above is the details of the MySql single-table query and multi-table connection query efficiency issue. For more information about the MySql single-table and multi-table connection query efficiency, please pay attention to other related articles on 123WORDPRESS.COM!