Java案例如何避免索引失效？

wen java案例 2026-06-07 60

本文目录导读：

Java案例如何避免索引失效？

避免在索引列上使用函数或计算
避免隐式类型转换
避免使用 LIKE 以通配符开头
避免 OR 导致全表扫描
避免或 <> 以及 NOT IN
使用 PreparedStatement 防止 SQL 注入，避免号
联合索引的最左前缀原则
控制 JOIN 查询的驱动表和索引
监听慢查询，提前代码审查
总结：Java 开发者的行动清单

在 Java 开发中，索引失效通常发生在数据库查询（如 MySQL、PostgreSQL）或搜索引擎（如 Elasticsearch）中，虽然索引失效的根本原因在于 SQL 语句的写法或数据库设计，但作为 Java 开发者，我们可以在 代码层面 进行预防和控制。

以下是针对 关系型数据库（如 MySQL） 常见的 Java 案例及避免索引失效的最佳实践：

避免在索引列上使用函数或计算

失效场景： WHERE DATE(create_time) = '2024-01-01' 或 WHERE salary + 1000 > 5000。

Java 代码案例（错误的写法）：

// 假设 create_time 有索引
String sql = "SELECT * FROM orders WHERE DATE(create_time) = ?";
preparedStatement.setString(1, "2024-01-01");

避免方案：

不要对索引列做任何操作，将函数操作转移到参数一侧。

正确写法：

// 使用范围查询，而不是函数
String sql = "SELECT * FROM orders WHERE create_time >= ? AND create_time < ?";
preparedStatement.setObject(1, LocalDate.of(2024, 1, 1).atStartOfDay());
preparedStatement.setObject(2, LocalDate.of(2024, 1, 2).atStartOfDay());

避免隐式类型转换

失效场景： 列是 varchar 类型，但 Java 传入了 int 或 long。

Java 代码案例（错误的写法）：

// 假设 order_no 是 VARCHAR，且有索引
String sql = "SELECT * FROM orders WHERE order_no = ?";
// 错误：传入了数值类型
preparedStatement.setLong(1, 10086L);

MySQL 会做隐式类型转换 CAST(order_no AS SIGNED)，导致索引失效。

避免方案：

确保参数类型与数据库列类型完全匹配。

正确写法：

preparedStatement.setString(1, "10086");

避免使用 `LIKE` 以通配符开头

失效场景： WHERE name LIKE '%张'。

Java 代码案例（错误的写法）：

String keyword = request.getParam("name");
// 用户输入了“张”，但代码拼接成了 %张%
String sql = "SELECT * FROM user WHERE name LIKE '%" + keyword + "%'";

避免方案：

如果是前缀匹配（张%），索引有效；如果是后缀（%张）或中间（%张%），索引失效。
技术方案： 对于全文搜索需求，使用 Elasticsearch 或 MySQL 的 全文索引。
业务妥协： 如果必须模糊查询，限制只有前缀匹配才走索引；或使用 INSTR / LOCATE 函数但需清楚索引失效。

避免 `OR` 导致全表扫描

失效场景： WHERE age = 18 OR name = '张三'，age 有索引而 name 没有。

Java 代码案例（错误的写法）：

String sql = "SELECT * FROM users WHERE age = ? OR name = ?";

避免方案：

思路： 将 OR 改为 UNION，或确保所有列都有索引。

正确写法：

// 合并两个查询结果
String sql = "SELECT * FROM users WHERE age = ? " +
           "UNION " +
           "SELECT * FROM users WHERE name = ?";

或者：在 name 上也建立索引。

避免或 `<>` 以及 `NOT IN`

失效场景： WHERE status != 1。

MySQL 优化器 通常认为这种查询会返回大部分数据，不选择索引。

Java 代码案例（错误的写法）：

String sql = "SELECT * FROM orders WHERE status != 1";

避免方案：

改为正向逻辑：例如查询 status = 0 代替 status != 1（前提是业务允许）。
或者：使用 NOT EXISTS 或 LEFT JOIN ... IS NULL 替代 NOT IN。

使用 `PreparedStatement` 防止 SQL 注入，避免号

这虽然不直接导致索引失效，但错误的 SQL 拼接容易引发隐式转换。

错误写法：

String sql = "SELECT * FROM user WHERE id = " + userId; // 极差

正确写法（使用参数化查询）：

PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM user WHERE id = ?");
ps.setInt(1, userId);
ResultSet rs = ps.executeQuery();

联合索引的最左前缀原则

失效场景： 联合索引 (city, age, name)，但查询条件只包含 name 或 age。

Java 代码案例：

// 索引失效：没有使用最左边的 city
String sql = "SELECT * FROM user WHERE age = 20 AND name = '张三'";

避免方案：

建立合理的联合索引顺序。
在 Java 代码层：根据查询频率，将最常用的筛选条件放在最左边。WHERE city = ? 必须是第一个条件。

控制 `JOIN` 查询的驱动表和索引

失效场景： JOIN 子句中关联列的数据类型不一致（如 int vs varchar）或被函数包裹。

错误案例：

// user.id 是 int，order.user_id 是 varchar
String sql = "SELECT * FROM orders JOIN users ON orders.user_id = users.id";

避免方案：

确保关联列的类型一致，字符集一致（如 utf8mb4）。
为关联列建立索引。

监听慢查询，提前代码审查

在 Java 代码中集成 慢查询日志 或使用 数据库连接池的监控（如 Druid、HikariCP）来发现潜在问题。

监控方案：

// 使用 Druid 监控慢查询
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="connectionProperties" value="druid.stat.slowSqlMillis=100"/>
</bean>

Java 开发者的行动清单

维度	核心原则	代码层面怎么做
类型安全	参数类型与数据库列类型严格一致	使用 `setString()` / `setInt()` 等方法，避免拼接
函数操作	索引列上不能有函数	将函数计算放在参数侧
模糊查询	禁止以开头	使用 `前缀匹配` 或 ES 搜索引擎
OR/IN	避免无法索引的 `OR`	拆分为 `UNION`
联合索引	遵循最左前缀	手动调整 `WHERE` 条件顺序（或依赖 MySQL 8.0 索引跳跃扫描）
工具与监控	及时发现	开启慢查询 + Java 日志记录 SQL 并分析执行计划