Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is the difference between single table query and multi-table join query in MySql database", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "MySql database single-table query and multi-table join query what is the difference?"

I. the answer in "High performance mysql"

Many high-performance applications decompose associative queries. Simply, you can make a single table query for each table, and then associate the results in the application. For example, the following query:

Select * from tagjoin tag_post on tag_post.tag_id=tag.idjoin post on tag_post.post_id=post.idwhere 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 (123Person456, 567, 9989, 8909)

Why on earth are you doing this?

At first glance, this does not do any good, the original query, but here has become multiple queries, the return result is exactly the same.

In fact, refactoring queries by decomposing associated queries has the following advantages: (in high concurrency and high performance applications, it is generally recommended to use single table queries)

1. Make caching more efficient. Many applications can easily cache the corresponding result objects in the form table. In addition, for MySQL's query cache, if a table in the association changes, the query cache cannot be used, and after split, if a table rarely changes, the query based on that table can reuse the query cache results.

two。 After decomposing the query, executing a single query can reduce lock competition.

3. By associating in the application layer, it is easier to split the database, and it is easier to achieve high performance and scalability.

4. The query itself may also be more efficient.

5. Queries with redundant records can be reduced.

6. Further, this is equivalent to implementing a hash association in the application, rather than using MySQL's nested ring association, which is much more efficient in some scenarios.

7. Single-table query is conducive to the later large amount of data sub-database sub-table, if the joint query, once the sub-database, the original sql needs to be changed.

8. The last time I saw some CTO technology sharing, the company banned the use of join federation query at the bottom. It's really slow when the data is big.

9. Union queries may indeed be fast, but the resources of mysql are usually much more strained than those of program code.

Second, some other answers

Scenario hypothesis: suppose the site has a company library section, I want to search all the companies in a city.

Data sheet: tbl_company (T1), tbl_city (T2).

Example 1:

T1 table stores cityid to do table join query according to id

Select * from T1 inner join T2 on t1.cityid=t2.cityid

Example 2:

T1 save cityName users in the foreground and click on Shanghai, then send the id of Shanghai to the background (do not consider passing cityName)

Find out cityName select cityName from T2 where cityid= # {cityid} according to id

Then select * from T1 where cityName = # {cityName}

The difference between the two: in example 1, there is only one table association query, and in example 2, there are two single table queries.

Considering the large amount of data, multi-table join query will affect the query efficiency, so it is optimized to single-table query. TP: the above is without using the index

Which kind of efficiency will be more efficient?

A: sql optimization is also related to business. Whether the query in this statement will be frequent or not, and whether to consider the cost of two connections, if you do not have to consider these, if there is no index, there will be little difference. 2 should be slightly better than 1.

The data is not very large or cascading query is fast.

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

BUT, 1. Mysql are very quick to deal with connecting / disconnecting, replying to small and simple queries; 2. The network is very fast now. So multiple small queries may be faster for mysql.

In the end, the Great God did not come to the conclusion that which was better. Ha ha, in fact, the whole book clearly expresses a meaning, testing! Be a benchmark! For your own data environment, test both ways. Speak with data.

At this point, I believe that everyone on the "MySql database single-table query and multi-table join query what are the differences" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.