java - 有关mybatis里面对同一张表的操作的疑惑

Question

比如：
查询会员列表的话，where后面带很多个if test条件是不是会影响效率，还是重写个sql，但是如果每个条件查询都重写sql，sql又太多了，显得很乱。该怎么选择？

Answer 1

The efficiency will not be affected much, after all, they are some memory operations, Mybatis动态SQL一个最大的问题就是IF...TEST太多的话,后期维护会很累,因为一个SQL调用方很很多,到时候找组合会很麻烦,SQL调优会很吃力,所以尽量每个查询都写一个(分页除外),如果实在需要IF...TEST, please don’t recommend too many

Answer 2

It won’t be too slow to write the if judgment in a set way, but if it doesn’t work, use the view

Answer 3

There must be a cache, it should not affect the efficiency too much.
In addition, if there are N conditions, then there are 2^N corresponding condition combinations. How many different SQLs do you need to write?

Answer 4

1. It is very common in the query function to have many if test conditions after where, and it is also common to have more than ten or twenty query conditions;
2. Since MyBatis provides the if test tag function, it must have been optimized. Compared with writing multiple SQLs and then judging the offload through Java code, it is definitely much more efficient;
3. A large part of the impact on execution speed is the hardware configuration of the database server and the database platform. As an ordinary programmer, you don’t need to consider that much. If you are really worried about this problem, you can also put your own code in the test environment for stress testing;
4. Compared with changes in query conditions, changes in query result columns affect efficiency more, because you need to manually turn off the cache remapResults

<select id="verificationCode_select" parameterClass="String" resultClass="HashMap" remapResults="true">$sql$</select>

In summary, no matter whether it works or not, let’s write the code and SQL and test it first!