SQL 语句优化

SQL 语句质量直接影响数据库性能。本文梳理 MyBatis 开发中的 SQL 优化核心技巧。

避免全表扫描

全表扫描是性能杀手，尤其在大数据量表中。

问题场景

<!-- 反例：无 WHERE 条件，全表扫描 -->
<select id="selectAllUsers" resultType="User">
    SELECT * FROM user
</select>

<!-- 反例：WHERE 条件未使用索引 -->
<select id="findByEmail" resultType="User">
    SELECT * FROM user WHERE email LIKE '%@example.com'
</select>

注意：LIKE '%...' 左模糊查询无法使用索引，必然导致全表扫描。

优化方案

<!-- 正例：WHERE 条件命中索引字段 -->
<select id="findByUsername" resultType="User">
    SELECT * FROM user WHERE username = #{username}
</select>

<!-- 正例：右模糊查询可走索引 -->
<select id="findByEmailSuffix" resultType="User">
    SELECT * FROM user WHERE email LIKE 'admin@%'
</select>

索引覆盖

索引覆盖是指查询所需的所有列都在索引中，无需回表查询。

覆盖索引示例

假设表上有联合索引 (username, email)：

<!-- 覆盖索引查询，只需扫描索引树 -->
<select id="findUsernameAndEmail" resultType="UserVO">
    SELECT username, email FROM user WHERE username = #{username}
</select>

<!-- 反例：SELECT * 导致回表 -->
<select id="findUserById" resultType="User">
    SELECT * FROM user WHERE username = #{username}
</select>

注意：SELECT * 查询超出索引范围的列时，需要回表查询数据行，增加 IO 开销。

只查需要的列

<!-- 正例：明确指定所需列 -->
<select id="findUserNameById" resultType="String">
    SELECT username FROM user WHERE id = #{id}
</select>

执行计划分析

执行计划是分析 SQL 性能的核心工具。

使用 EXPLAIN 分析

在数据库中直接执行：

EXPLAIN SELECT * FROM user WHERE username = 'admin';

关键字段解读：

type 性能从高到低：

MyBatis 中验证执行计划

开发阶段可用以下方式验证 SQL 执行计划：

@Test
public void testExplain() {
    try (SqlSession session = sqlSessionFactory.openSession()) {
        Connection conn = session.getConnection();
        try (PreparedStatement ps = conn.prepareStatement(
                "EXPLAIN SELECT * FROM user WHERE username = ?")) {
            ps.setString(1, "admin");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("type: " + rs.getString("type"));
                    System.out.println("key: " + rs.getString("key"));
                    System.out.println("rows: " + rs.getInt("rows"));
                }
            }
        }
    }
}

常见 SQL 反模式

1. SELECT *

<!-- 反例 -->
<select id="findAll" resultType="User">
    SELECT * FROM user
</select>

<!-- 正例 -->
<select id="findActiveUsers" resultType="User">
    SELECT id, username, email FROM user WHERE status = 1
</select>

2. 在 WHERE 中使用函数

<!-- 反例：对索引列使用函数，索引失效 -->
<select id="findByYear" resultType="User">
    SELECT * FROM user WHERE YEAR(create_time) = #{year}
</select>

<!-- 正例：使用范围查询 -->
<select id="findByYear" resultType="User">
    SELECT * FROM user
    WHERE create_time >= #{start} AND create_time &lt; #{end}
</select>

3. 隐式类型转换

<!-- 反例：phone 是 VARCHAR 类型，传入数字导致隐式转换和索引失效 -->
<select id="findByPhone" resultType="User">
    SELECT * FROM user WHERE phone = #{phone}
</select>

注意：Java 代码中确保参数类型与数据库字段类型一致，避免隐式类型转换导致索引失效。

要点总结

• 避免全表扫描，WHERE 条件必须命中索引
• 左模糊查询 LIKE '%...' 无法使用索引
• 使用覆盖索引减少回表查询，避免 SELECT *
• 用 EXPLAIN 分析执行计划，type 至少达到 range 级别
• 避免在 WHERE 中对索引列使用函数
• 确保参数类型与数据库字段类型一致，避免隐式转换