This is the 11th day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021

Writing in the front

When my new colleague came in and asked me what was interesting about where 1=1, it didn’t make sense. I laughed. I’m going to explain it today.

where 1=1

Let’s start with a piece of code

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(id) from t_book t where 1=1
<if test="title ! =null and title ! = "">
 AND title = #{title} 
</if> 
<if test="author ! =null and author ! = "">
 AND author = #{author}
</if> 
</select>
Copy the code

The code above is familiar, which is to query the total number of matches. In Mybatis, the if tag is often used to judge the condition after the WHERE clause, in order to prevent the SQL error caused by the null first field. Select * from where 1=1 where 1=1 where 1=1

There are many people on the Internet who say that this will cause performance problems, may make the index invalid, so we will test today, will not walk the index

The measured

The title field has been indexed. Let’s look at it through EXPLAIN

EXPLAIN SELECT * FROM t_book WHERE title = ‘ ‘;

EXPLAIN SELECT * FROM t_book WHERE 1=1 AND title = ‘ ‘; EXPLAIN SELECT * FROM t_book WHERE 1=1 AND title = ‘ ‘;

Comparing the two above, possible_keys (possible index) and key (actual index) are both searched using indexes.

conclusion

SQL instructions written by mysql will be parsed and optimized into its own processing instructions. In this process, meaningless conditions such as 1=1 will be optimized. Check with explain EXTENDED SQL and discover that conditions such as where1=1 are indeed optimized by mysql’s optimizer.

Mysql optimizer takes time after all. Although the mysql optimizer takes time, it will still have an impact when there is a large amount of data. Therefore, we suggest to write the code like this:

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_book t
<where>
<if test="title ! =null and title ! = "">
 title = #{title} 
</if>
<if test="author ! =null and author ! = ""> 
 AND author = #{author}
</if>
</where> 
</select>
Copy the code

We use the WHERE tag instead.

overtones

Thank you for reading, and if you feel like you’ve learned something, you can like it and follow it. Also welcome to have a question we comment below exchange

Come on! See you next time!

To share with you a few I wrote in front of a few SAO operation

Copy object, this operation is a little SAO!

Dry! SpringBoot uses listening events to implement asynchronous operations