深入探究MySQL中使用where 1=1是否存在性能影响

目录

• 前言
• 聊聊 mybatis 中多条件拼接的两种常规写法
• where 1=1
• 使用 <where> 标签
• 性能影响
• where 1=1
• <where> 标签
• 总结

<select id="" parameterType = "">
    SELECT * FROM users 
    WHERE 1=1
        <if test="userName != null ">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null ">
            AND user_age = #{userAge }
        </if>
        <if test="userSex!= null ">
            AND user_sex = #{userSex}
        </if>
</select>

<select id="" parameterType = "">
    SELECT * FROM users 
    WHERE 1=1
        <if test="userName != null ">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null ">
            AND user_age = #{userAge }
        </if>
        <if test="userSex!= null ">
            AND user_sex = #{userSex}
        </if>
</select>

<select id="" parameterType = "">
    SELECT * FROM users 
    <where>
        <if test="userName != null ">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null ">
            AND user_age = #{userAge }
        </if>
        <if test="userSex!= null ">
            AND user_sex = #{userSex}
        </if>
    </where>
</select>

SELECT VERSION();

5.7.44

# 数据构造 SQL
CREATE TABLE IF NOT EXISTS t_user
(
  id INT not null auto_increment primary key comment '自增ID',
  name varchar(20) comment '姓名',
  age tinyint  comment '年龄'
)ENGINE = INNODB;


INSERT INTO t_user ( NAME, age ) VALUES ( '张三', 18 ),( '李四', 19 ),( '王五', 20 ),( '司总', 21 );

# 注：需要选中一起执行可以查看到优化后的 SQL
explain select * from t_user where 1=1 AND name = '张三';
show warnings;

# 优化后的 SQL
/* select#1 */ SELECT
`mydatabase`.`t_user`.`id` AS `id`,
`mydatabase`.`t_user`.`name` AS `name`,
`mydatabase`.`t_user`.`age` AS `age` 
FROM
    `mydatabase`.`t_user` 
WHERE
    (
    `mydatabase`.`t_user`.`name` = '张三')

# 性能对比
select * from t_user where 1=1 AND name = '张三'
> OK
> 查询时间: 0.046s

select * from t_user where 1=1
> OK
> 查询时间: 0.046s