【SQL】索引失效的几种情况

在数据库查询中，虽然索引可以显著提高查询性能，但在某些情况下，索引可能会失效，导致查询性能下降。以下是一些常见的索引失效情况及其原因：

1. 查询条件中使用了函数或表达式

当查询条件中的列使用了函数、表达式或进行计算时，索引可能失效。例如：

-- 索引失效，使用了函数
SELECT * FROM users WHERE UPPER(username) = 'ALICE';

2. 隐式类型转换

当查询条件中的列与值类型不匹配，数据库需要进行隐式类型转换时，索引可能失效。例如：

-- 索引失效，隐式类型转换
SELECT * FROM users WHERE phone_number = 1234567890;

如果 phone_number 列是字符串类型，而查询值是整数类型，索引会失效。

3. 使用不等运算符

使用 <> 或 != 这样的不等运算符时，索引可能失效。例如：

-- 索引失效，使用不等运算符
SELECT * FROM users WHERE age != 30;

4. 使用 IS NULL 或 IS NOT NULL

某些数据库在使用 IS NULL 或 IS NOT NULL 条件时，索引可能失效。例如：

-- 索引失效，使用 IS NULL
SELECT * FROM users WHERE age IS NULL;

5. 前缀匹配

对于复合索引，如果查询条件不包含索引的最左前缀列，索引会失效。例如，复合索引 (a, b) 在以下查询中会失效：

-- 索引失效，未使用最左前缀列
SELECT * FROM table WHERE b = 10;

6. LIKE 模式匹配

使用 LIKE 进行模糊匹配时，如果模式以 % 开头，索引会失效。例如：

-- 索引失效，LIKE 模式以 % 开头
SELECT * FROM users WHERE username LIKE '%alice';

7. OR 条件

使用 OR 连接的条件，如果每个条件列都没有索引，索引会失效。例如：

-- 索引失效，OR 条件中的列没有索引
SELECT * FROM users WHERE username = 'Alice' OR email = 'alice@example.com';

8. 数据分布不均

如果索引列的数据分布高度不均匀，即存在“热点”数据，数据库优化器可能会选择不使用索引。例如：

-- 假设 age 列中大部分值都是 30
SELECT * FROM users WHERE age = 30;

9. 更新频繁的列

对于频繁更新的列，即使创建了索引，索引的维护成本高，数据库优化器可能会选择不使用索引。

10. 小表

对于数据量较小的表，数据库优化器可能会选择全表扫描而不是使用索引，因为全表扫描的开销较小。

例子：防止索引失效

假设有一个用户表 users，包含以下数据：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    phone_number VARCHAR(15),
    age INT
);

-- 创建索引
CREATE INDEX idx_username ON users (username);
CREATE INDEX idx_phone_number ON users (phone_number);
CREATE INDEX idx_age ON users (age);

1. 避免函数或表达式

错误：

-- 索引失效
SELECT * FROM users WHERE UPPER(username) = 'ALICE';

正确：

-- 索引有效
SELECT * FROM users WHERE username = 'alice';

2. 避免隐式类型转换

错误：

-- 索引失效
SELECT * FROM users WHERE phone_number = 1234567890;

正确：

-- 索引有效
SELECT * FROM users WHERE phone_number = '1234567890';

3. 避免不等运算符

错误：

-- 索引失效
SELECT * FROM users WHERE age != 30;

正确：

-- 索引有效
SELECT * FROM users WHERE age < 30 OR age > 30;

4. 避免 IS NULL

错误：

-- 索引失效
SELECT * FROM users WHERE age IS NULL;

正确：

-- 索引有效（假设 age 列默认值为 -1）
SELECT * FROM users WHERE age = -1;

使用 Java 示例

在 Java 中避免索引失效的查询示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class IndexOptimizationExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            // 避免使用函数或表达式
            String query = "SELECT * FROM users WHERE username = ?";
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                stmt.setString(1, "alice");
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        System.out.println("User ID: " + rs.getInt("user_id") + ", Username: " + rs.getString("username"));
                    }
                }
            }

            // 避免隐式类型转换
            query = "SELECT * FROM users WHERE phone_number = ?";
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                stmt.setString(1, "1234567890");
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        System.out.println("User ID: " + rs.getInt("user_id") + ", Phone Number: " + rs.getString("phone_number"));
                    }
                }
            }

            // 避免不等运算符
            query = "SELECT * FROM users WHERE age < ? OR age > ?";
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                stmt.setInt(1, 30);
                stmt.setInt(2, 30);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        System.out.println("User ID: " + rs.getInt("user_id") + ", Age: " + rs.getInt("age"));
                    }
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

通过了解并避免这些索引失效的情况，可以确保索引在查询中的有效性，提高查询性能。