SQL索引失效的情况有哪些

什么是SQL索引失效

在大型数据库环境中，一个好的索引可以使查询速度提高好几倍，因此索引的设计和使用非常重要。然而，尽管建立了索引，有时候 SQL 查询并不能使用索引进行优化，导致索引失效，从而导致查询变得缓慢或不稳定。索引失效指的是对于一个具有索引的表，查询执行时实际并未使用到索引的情况。

下面我们将列举 SQL 索引失效的常见情况及其解决方法。

索引列进行了函数处理

当 SQL 查询语句中，针对索引列进行了函数处理时，索引就会失效。

-- 示例代码
-- 创建表
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
);
-- 建立索引
CREATE INDEX idx_age ON test(age);

-- 对age进行函数处理，从而导致索引失效
-- 下面的查询将不使用索引进行优化
SELECT COUNT(*) FROM test WHERE  age * 2 > 100;

解决方法：

需要提前对查询语句进行优化，避免在索引列上进行函数处理。如果确实需要函数操作，可以将函数操作放在查询语句中，而不是在索引列上进行函数操作。

模糊查询使用了LIKE

在 SQL 中，使用 LIKE 进行模糊查询时，由于查询语句需要将所有记录进行匹配，会导致索引失效，从而使查询变得缓慢。

-- 示例代码
-- 创建表
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
);
-- 建立索引
CREATE INDEX idx_name ON test(name);

-- 使用 LIKE 进行模糊查询，导致索引失效
-- 下面的查询将不使用 idx_name 索引进行优化
SELECT * FROM test WHERE name LIKE '%John%';

解决方法：

在需要进行模糊查询时，可以使用全文本索引（Full Text Index），其可在索引列上建立需要搜索的词汇库，结合关键词的方式进行查询，大大提升了查询效率。另外一个方法是在查询语句中使用左前缀匹配，例如：

-- 使用左前缀匹配，索引可用
SELECT * FROM test WHERE name LIKE 'John%';

OR 操作和 IN 操作门限过低

当 SQL 查询语句中使用 OR 操作或 IN 操作，如果门限过低，就会导致索引失效。

-- 示例代码
-- 创建表
CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
);
-- 建立索引
CREATE INDEX idx_name ON test(name);

-- 使用 OR 做逻辑判断，门限过低导致索引失效
-- 下面的查询将不使用 idx_name 索引进行优化
SELECT * FROM test WHERE name = 'John' OR name = 'Kate' OR name = 'Lucy';

-- 使用 IN 做逻辑判断，门限过低导致索引失效
-- 下面的查询将不使用 idx_name 索引进行优化
SELECT * FROM test WHERE name IN ('John', 'Kate', 'Lucy');

解决方法：

在 SQL 查询语句中，使用 OR 操作或 IN 操作设置的门限过低，会使优化器选择不使用索引，而是选择全表扫描。这时候需要分别考虑，根据实际占比进行调整门限值，从而避免索引失效。

总结

SQL 索引失效是 SQL 性能优化中非常重要的一环。为了防止索引失效，需要遵循下面的几个原则：

• 避免在索引列上进行函数处理
• 避免使用 LIKE 进行模糊查询
• 合理使用 OR 和 IN，避免门限过低