The first answer comes from the answer in High Performance Mysql

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:

Post_id =tag.id Join POST on tag_post.post_id=post.id WHERE tag.tag= 'mysql'; Select * from tag where tag= 'mysql'; Select * from tag where tag= 'mysql'; Select * from tag_post where tag_id=1234; Select * from post WHERE id in(123,456,567,999,8909);Copy the code

In fact, refactoring 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 object of a single table query. In addition, for MySQL’s query cache, if a table in the association changes, then the query cache cannot be used, and if a table rarely changes, then the query based on that table can reuse the query cache results.

  1. Breaking down the query and executing a single query reduces lock contention.

  2. Association at the application layer makes it easier to split the database and achieve high performance and scalability.

  3. The query itself may also be more efficient.

  4. Can reduce redundant record queries.

  5. Furthermore, this is equivalent to implementing hash association in the application rather than using MySQL’s nested ring association, which is much more efficient in some scenarios.

  6. Single table query is beneficial to the late data volume of the sub-database sub-table, if the joint query, once the sub-database, the original SQL needs to be changed.

  7. The last time I looked at a CTO technology share, the company had a policy that the bottom line forbade join federated queries. Big data is slow.

  8. Federated queries may indeed be fast, but mysql resources are often much more constrained than program code resources.

Two. Single table multiple query and multi-table joint query effect comparison

Under the same conditions, take users, roles, and user role association tables as examples.

select * from sys_user where user_name='admin'; select * from sys_user_role where user_id ='1'; select * from sys_role where role_id ='1'; select * from sys_user su LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id LEFT JOIN sys_role sr ON sur.role_id =  sr.role_id where user_name='admin'; >>>>>>>>> [SQL]select * from sys_user where user_name='admin'; [SQL] select * from sys_user_role where user_id ='1'; [SQL] select * from sys_role where role_id ='1'; Affected row: 0 Time: 0.004s [SQL] SELECT * from sys_user su LEFT JOIN sys_user_role sur ON su. User_id = sur.user_id LEFT JOIN sys_role sr ON sur.role_id = sr.role_id where user_name='admin'; Affected rows: 0 Time: 0.001sCopy the code

Querying the Packing Unit

select unit_name from erp_unit where unit_id=1; select unit_name from erp_unit where unit_id=2; select unit_name from erp_unit where unit_id=3; SELECT eus.unit_name AS smallName, eum.unit_name AS middleName, eub.unit_name AS bigName FROM erp_goods_detail egd LEFT JOIN erp_unit eus ON egd.small_unit_id = eus.unit_id LEFT JOIN erp_unit eum ON egd.middle_unit_id = eum.unit_id LEFT JOIN erp_unit eub ON egd.big_unit_id = eub.unit_id WHERE egd.goods_id = '1357597885043163138'; >>>>>>>>>>>>>>>>>>>>> [SQL]select unit_name from erp_unit where unit_id=1; [SQL] select unit_name from erp_unit where unit_id=2; [SQL] select unit_name from erp_unit where unit_id=3; Affected row: 0 Time: [SQL] SELECT eus. Unit_name AS middleName, eum. Unit_name AS middleName eub.unit_name AS bigName FROM erp_goods_detail egd LEFT JOIN erp_unit eus ON egd.small_unit_id = eus.unit_id LEFT JOIN erp_unit eum ON egd.middle_unit_id = eum.unit_id LEFT JOIN erp_unit eub ON egd.big_unit_id = eub.unit_id WHERE egd.goods_id = '1357597885043163138'; Affected row: 0 Time: 0.002sCopy the code

Take a look at the runtime in the code

@Service public class AServiceImpl implements AService { @Autowired private UnitMapper unitMapper; @Override public void testA() { singleSearch(); allSearch(); } private void singleSearch() { Long start = System.currentTimeMillis(); for (int i = 0; i < 10; i++) { unitMapper.selectNameByUnitId((long) 1); unitMapper.selectNameByUnitId((long) 2); unitMapper.selectNameByUnitId((long) 3); } Long end = System.currentTimeMillis(); System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start)); } private void allSearch() { Long start = System.currentTimeMillis(); for (int i = 0; i < 10; i++) { unitMapper.searchAll(); } Long end = System.currentTimeMillis(); System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start)); }}Copy the code

The total time of singleSearch method was 4533, and that of allSearch method was 1378

Time comparison of each query method in traversal singleSearch(unit ms):[4, 4, 3, 4, 4, 3, 5, 3, 3, 4, 3, 4, 3, 3, 3, 3, 3, 3, 7, 4, 5, 19, 4, 4, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 3, 220]

AllSearch (ms):[5, 4, 4, 4, 5, 4, 4, 4, 5, 9]

Again, let’s adjust the number of iterations to 1000

@Service public class AServiceImpl implements AService { @Autowired private UnitMapper unitMapper; @Override public Map<String, Long> testA() { Long single = singleSearch(); Long all = allSearch(); Map map = new HashMap(); map.put("single", single); map.put("all", all); return map; } private Long singleSearch() { Long start = System.currentTimeMillis(); for (int i = 0; i < 1000; i++) { unitMapper.selectNameByUnitId((long) 1); unitMapper.selectNameByUnitId((long) 2); unitMapper.selectNameByUnitId((long) 3); } Long end = System.currentTimeMillis(); System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start)); return end - start; } private Long allSearch() { Long start = System.currentTimeMillis(); for (int i = 0; i < 1000; i++) { unitMapper.searchAll(); } Long end = System.currentTimeMillis(); System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start)); return end - start; }}Copy the code

The output

{" code ": 200," success ": true," data ": {" single" : 362416, "all" : 7388}, "MSG" : "successful"}Copy the code