MySQL联合查询和简单查询究竟如何选择？

Question

最近看高性能ＭySQl，里面是推荐把联合查询分解为多个简单的查询，既然是这样 那么还要联合查询干嘛？究竟是如何选择才是效率更高的选择呢？

Answer 1

Simple joint queries generally do not need to be decomposed. What we are talking about here should be more complex joint queries, such as joint queries of 3 or 4 tables. If the query is more complex and involves grouping, sorting, etc., it cannot be effectively used at runtime. Indexed. It is even possible to create temporary tables. Then the efficiency will be relatively poor. And it is not conducive to query caching. After decomposition, for each simple query, the database has a query caching mechanism, which will be more efficient. Try to use explain to analyze the joint query statements. What are the problems? Make targeted corrections. When it’s time to decompose, you still have to decompose.

Answer 2

select * from tb1
left join tb2 on tb1.id=tb2.tid
where tb1.stat=1 and tb2.stat=1 and tb1.type=1

select * from (
select * from tb1 where stat=1 and type=1
) as tb1
left join tb2 on tb1.id=tb2.tid
where tb2.stat=1

The above is a simple case. In fact, I don’t quite understand what you mean by decomposing the joint query into multiple simple queries. The above can only be some of my personal optimization (decomposition) plans (although it is not like this every time) Use).

If the decomposition you are talking about is to split the joint table into many statements, and then call them one after another in the code, I believe the efficiency is even lower than the joint table. The IO overhead of each connection to the database is definitely more than that of one joint table. .

Optimizing the efficiency of joint table query can be said in one sentence Use the least amount of correct data for association

The minimum data can be passed. Before the table is associated, the correct data can be extracted in advance to avoid association when there is a lot of data that is obviously known to be wrong.

No matter what join method, this method can be applied.

There are many more efficiency-improving solutions
such as where order, field types, indexes, etc. The scope is huge.

Answer 3

As long as you use the index well, there is no problem with joint query