Java覆盖索引案例

Java 覆盖索引案例分析

覆盖索引（Covering Index）是数据库性能优化的重要手段，当一个索引包含所有需要查询的字段时，数据库可以直接从索引中获取数据，无需回表查询，从而大幅提升查询性能。

基础概念

覆盖索引：索引中包含查询所需的所有列，使查询可以完全通过索引完成，不需要访问数据表。

-- 普通索引
CREATE INDEX idx_name_age ON users(name, age);
-- 覆盖索引示例查询
SELECT name, age FROM users WHERE name = '张三';
-- 这个查询可以直接从索引idx_name_age中获取数据，无需回表

实际案例演示

1 建表及数据准备

-- 创建用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_username_age_city (username, age, city)
);
-- 插入测试数据
INSERT INTO users (username, email, age, city) VALUES
('zhangsan', 'zhangsan@example.com', 25, 'Beijing'),
('lisi', 'lisi@example.com', 30, 'Shanghai'),
('wangwu', 'wangwu@example.com', 28, 'Guangzhou'),
('zhaoliu', 'zhaoliu@example.com', 32, 'Shenzhen');

2 Java 代码示例

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 使用覆盖索引查询（推荐）
     * 查询只涉及username、age、city字段，可以使用覆盖索引
     */
    public List<UserBasicInfo> findUsersByUsername(String username) {
        String sql = "SELECT username, age, city FROM users WHERE username = ?";
        return jdbcTemplate.query(sql, new Object[]{username}, (rs, rowNum) -> {
            UserBasicInfo info = new UserBasicInfo();
            info.setUsername(rs.getString("username"));
            info.setAge(rs.getInt("age"));
            info.setCity(rs.getString("city"));
            return info;
        });
    }
    /**
     * 无法使用覆盖索引的查询（不推荐）
     * 查询涉及email字段，该字段不在索引中，需要回表
     */
    public User getFullUserByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{username}, (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
            user.setEmail(rs.getString("email"));
            user.setAge(rs.getInt("age"));
            user.setCity(rs.getString("city"));
            return user;
        });
    }
    /**
     * 覆盖索引配合分页查询
     */
    public PageResult<UserBasicInfo> findUsersByCityWithPagination(String city, int page, int size) {
        // 使用覆盖索引进行分页查询
        String countSql = "SELECT COUNT(*) FROM users WHERE city = ?";
        String querySql = "SELECT username, age, city FROM users WHERE city = ? LIMIT ?, ?";
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, city);
        List<UserBasicInfo> list = jdbcTemplate.query(querySql, 
            new Object[]{city, page * size, size},
            (rs, rowNum) -> {
                UserBasicInfo info = new UserBasicInfo();
                info.setUsername(rs.getString("username"));
                info.setAge(rs.getInt("age"));
                info.setCity(rs.getString("city"));
                return info;
            });
        return new PageResult<>(total, list);
    }
}
// 用户基础信息DTO（只包含索引中的字段）
class UserBasicInfo {
    private String username;
    private Integer age;
    private String city;
    // getters and setters 省略
}
// 完整用户实体
class User {
    private Long id;
    private String username;
    private String email;
    private Integer age;
    private String city;
    // getters and setters 省略
}

使用 MyBatis 的覆盖索引案例

@Mapper
public interface UserMapper {
    /**
     * 覆盖索引查询：只查询索引中的字段
     */
    @Select("SELECT username, age, city FROM users WHERE username = #{username}")
    List<UserBasicInfo> selectUserBasicInfo(String username);
    /**
     * 覆盖索引配合排序
     */
    @Select("SELECT username, age FROM users WHERE age > #{age} ORDER BY age DESC")
    List<UserBasicInfo> selectUsersByAgeSorted(@Param("age") int age);
    /**
     * 覆盖索引配合聚合函数
     */
    @Select("SELECT city, COUNT(*) as count FROM users GROUP BY city")
    List<CityCount> selectCityStats();
}
// 城市统计DTO
class CityCount {
    private String city;
    private Long count;
    // getters and setters
}

执行计划分析

-- 检查查询是否使用覆盖索引
EXPLAIN SELECT username, age, city FROM users WHERE username = 'zhangsan';
-- Extra列显示：Using index (表示使用了覆盖索引)
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
-- Extra列显示：Using index condition (可能需要回表)

性能对比测试

@Component
public class PerformanceTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void comparePerformance() {
        // 测试覆盖索引查询
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            jdbcTemplate.query(
                "SELECT username, age, city FROM users WHERE username = ?",
                new Object[]{"user" + i},
                rs -> { /* 处理结果 */ }
            );
        }
        long coverIndexTime = System.currentTimeMillis() - start;
        // 测试非覆盖索引查询
        start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            jdbcTemplate.query(
                "SELECT * FROM users WHERE username = ?",
                new Object[]{"user" + i},
                rs -> { /* 处理结果 */ }
            );
        }
        long nonCoverIndexTime = System.currentTimeMillis() - start;
        System.out.println("覆盖索引查询耗时: " + coverIndexTime + "ms");
        System.out.println("非覆盖索引查询耗时: " + nonCoverIndexTime + "ms");
        System.out.println("性能提升: " + (nonCoverIndexTime - coverIndexTime) + "ms");
    }
}

最佳实践总结

@Service
public class BestPractices {
    /**
     * 1. 创建合适的复合索引
     */
    public void createCompositeIndex() {
        String sql = "CREATE INDEX idx_username_age_city ON users(username, age, city)";
        jdbcTemplate.execute(sql);
    }
    /**
     * 2. 只查询需要的字段
     */
    public UserBasicInfo findUserProfile(String username) {
        // 只查询需要的字段，避免SELECT *
        String sql = "SELECT username, age, city FROM users WHERE username = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{username},
            (rs, rowNum) -> {
                UserBasicInfo info = new UserBasicInfo();
                info.setUsername(rs.getString("username"));
                info.setAge(rs.getInt("age"));
                info.setCity(rs.getString("city"));
                return info;
            }
        );
    }
    /**
     * 3. 注意索引顺序（最左前缀原则）
     */
    public List<UserBasicInfo> searchUsers(String username, Integer age, String city) {
        // 使用索引的最左前缀：username
        String sql = "SELECT username, age, city FROM users WHERE username = ?";
        if (age != null) {
            sql += " AND age = ?";
        }
        if (city != null) {
            sql += " AND city = ?";
        }
        // 动态构建参数
        List<Object> params = new ArrayList<>();
        params.add(username);
        if (age != null) params.add(age);
        if (city != null) params.add(city);
        return jdbcTemplate.query(sql, params.toArray(),
            (rs, rowNum) -> {
                UserBasicInfo info = new UserBasicInfo();
                info.setUsername(rs.getString("username"));
                info.setAge(rs.getInt("age"));
                info.setCity(rs.getString("city"));
                return info;
            }
        );
    }
}

注意事项

覆盖索引的优缺点对比：

最佳实践建议：

• 只为高频查询创建覆盖索引
• 避免索引包含过多字段
• 注意索引字段顺序,遵循最左前缀原则
• 定期分析索引使用情况,及时优化

覆盖索引是数据库性能优化的利器,合理使用能够显著提升查询性能，但同时也需要权衡存储和写入成本。