1. Why does Ali prohibit JOIN of more than three tables?

This specification is aimed at MySQL database. It is not difficult to simulate some scenarios to create more data to query and compare them. It is easy to find that query performance will significantly decline when joining 1 table is added.

Consider the following scenario (100W data for each of 3 tables, PC test) :

It takes more than 3S to JOIN three tables, and more than 5S to JOIN a dictionary table. The performance impact of joining more than one table is relatively large.

2. How to avoid multiple table joins?

The diboot framework solves this problem well. In addition to simplifying the SQL of associated scenarios, the diboot kernel also achieves high performance for single-table queries by dismantling joins. Let people write less at the same time, but also make the system performance to achieve better.

Let’s compare the test data for the above scenario to see how much of a performance boost the diboot associative binding brings. Scene: N-N association scene of “residents” and “real estate”, with 100W data in each of the 3 tables, index in the associated fields of the middle table, and personal PC test. Requirement: The query returns a page of “resident” main table data and associates its “property” data. VO is shown as follows:

Public class CitizenVo {private class CitizenVo {private class CitizenVo {private class CitizenVo {private class CitizenVo; GENDER private String genderLabel; }

If you are used to Mybatis writing SQL, you may be wondering how to write this SQL. However, using diboot, you only need to add two lines of annotations to tell diboot the relationship between them.

The performance comparison test results show that, compared with the 4S + time of handwritten SQL, Diboot only takes about 0.4s to complete the query binding, improving the performance by nearly 10 times. When the amount of data is larger and larger, the handwritten SQL will be slower and slower, while the diboot can still be stable at <1s, and the performance will be easily improved by more than 10 times.

This comparison test also verified the theoretical basis of diboot associative binding: the correctness of the optimization suggestion of “refactoring query mode” in the book “High Performance MySQL”, and the larger the data amount is, the more complex the associative scenario is, and the more obvious the performance advantage of using diboot associative binding.

So: stop associating SQL by hand, diboot does less writing and performs better!

Resources: See the article to learn how to achieve high performance diboot?

See the video to see how diboot can improve query performance

Diboot is a simple and efficient low code development framework