PolarDB 中的索引失效场景全解析：从数据类型不匹配到函数使用的 10 个案例

在数据库的使用过程中，索引就像是一本书的目录，能够帮助我们快速定位到想要的数据。然而，在 PolarDB 中，有时候索引会失效，这就好比书的目录突然不管用了，会大大影响数据查询的效率。接下来，我们就来全面解析 PolarDB 中索引失效的 10 个场景。

一、数据类型不匹配

在 PolarDB 里，当查询条件的数据类型和索引列的数据类型不匹配时，索引就可能会失效。咱们举个例子，假如有一个表 users，其中有一个 user_id 列，它的数据类型是 int，并且创建了索引。

-- 创建 users 表
CREATE TABLE users (
    user_id int,
    user_name varchar(50),
    PRIMARY KEY (user_id)
);

-- 创建 user_id 列的索引
CREATE INDEX idx_user_id ON users (user_id);

-- 错误的查询，将字符串类型作为查询条件
SELECT * FROM users WHERE user_id = '1';

在这个例子中，user_id 是 int 类型，但查询条件却用了字符串 '1'。PolarDB 在执行查询时，会对 user_id 进行隐式类型转换，这就导致索引无法正常使用。正确的做法应该是：

-- 正确的查询，使用 int 类型作为查询条件
SELECT * FROM users WHERE user_id = 1;

应用场景：在实际开发中，可能会因为前端传递过来的数据类型和数据库中索引列的数据类型不一致，导致这种索引失效的情况。 技术优缺点：优点是 PolarDB 会尝试进行隐式类型转换，保证查询能正常执行；缺点就是会导致索引失效，影响查询性能。 注意事项：在编写查询语句时，一定要确保查询条件的数据类型和索引列的数据类型一致。

二、使用函数操作索引列

当对索引列使用函数时，索引也容易失效。还是以 users 表为例，假设我们要查询 user_name 以大写字母开头的用户。

-- 查询 user_name 以大写字母开头的用户，使用函数操作索引列
SELECT * FROM users WHERE UPPER(user_name) LIKE 'A%';

在这个查询中，对 user_name 列使用了 UPPER 函数，这会导致索引失效。因为索引是基于原始数据建立的，使用函数后，索引无法直接匹配。正确的做法可以是在查询前对数据进行预处理。

-- 创建一个新的列，存储大写的 user_name
ALTER TABLE users ADD COLUMN upper_user_name varchar(50);
UPDATE users SET upper_user_name = UPPER(user_name);
-- 在新列上创建索引
CREATE INDEX idx_upper_user_name ON users (upper_user_name);
-- 使用新列进行查询
SELECT * FROM users WHERE upper_user_name LIKE 'A%';

应用场景：当需要对索引列进行复杂的计算或者转换时，容易出现这种情况。 技术优缺点：优点是函数操作可以灵活处理数据；缺点是会使索引失效，降低查询效率。 注意事项：尽量避免在查询条件中对索引列使用函数，如果必须使用，可以考虑添加辅助列并在其上创建索引。

三、使用 OR 连接条件

在查询中使用 OR 连接多个条件时，如果其中部分条件不使用索引，可能会导致整个索引失效。例如：

-- 查询 user_id 为 1 或者 user_name 为 'John' 的用户
SELECT * FROM users WHERE user_id = 1 OR user_name = 'John';

假设 user_id 有索引，而 user_name 没有索引，那么在执行这个查询时，索引可能无法有效使用。可以将查询拆分成两个查询，然后使用 UNION 合并结果。

-- 拆分查询
SELECT * FROM users WHERE user_id = 1
UNION
SELECT * FROM users WHERE user_name = 'John';

应用场景：当需要查询满足多个条件之一的数据时，可能会使用 OR 连接条件。 技术优缺点：优点是 OR 可以方便地组合多个查询条件；缺点是可能会导致索引失效。 注意事项：如果 OR 连接的条件中部分列没有索引，考虑使用 UNION 来替代。

四、LIKE 以通配符开头

在使用 LIKE 进行模糊查询时，如果以通配符 % 开头，索引会失效。比如：

-- 查询 user_name 包含 'ohn' 的用户，以通配符开头
SELECT * FROM users WHERE user_name LIKE '%ohn';

由于无法确定以 % 开头的字符串的起始位置，索引无法发挥作用。如果是从字符串开头进行匹配，索引可以正常使用。

-- 查询 user_name 以 'Joh' 开头的用户
SELECT * FROM users WHERE user_name LIKE 'Joh%';

应用场景：在需要进行模糊搜索时，经常会使用 LIKE 操作符。 技术优缺点：优点是 LIKE 可以实现灵活的模糊查询；缺点是以通配符开头会使索引失效。 注意事项：尽量避免在 LIKE 查询中以通配符开头，如果必须进行这种查询，可以考虑全文索引。

五、索引列参与计算

当索引列参与计算时，索引也会失效。例如：

-- 查询 user_id 加 1 等于 2 的用户
SELECT * FROM users WHERE user_id + 1 = 2;

在这个查询中，user_id 参与了计算，索引无法直接匹配。可以将计算移到等式另一边。

-- 将计算移到等式另一边
SELECT * FROM users WHERE user_id = 2 - 1;

应用场景：在进行一些复杂的查询时，可能会对索引列进行计算。 技术优缺点：优点是可以进行复杂的计算查询；缺点是会导致索引失效。 注意事项：尽量避免在查询条件中对索引列进行计算，如果需要计算，可以将计算结果提前处理好。

六、使用 NOT IN 或 <>

在查询中使用 NOT IN 或 <> 时，索引可能会失效。比如：

-- 查询 user_id 不在 1, 2 范围内的用户
SELECT * FROM users WHERE user_id NOT IN (1, 2);

或者

-- 查询 user_id 不等于 1 的用户
SELECT * FROM users WHERE user_id <> 1;

这些查询会导致索引无法有效利用。可以考虑使用 NOT EXISTS 或者 LEFT JOIN 来替代。

-- 使用 NOT EXISTS 替代 NOT IN
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM (SELECT 1 AS id UNION ALL SELECT 2) t
    WHERE u.user_id = t.id
);

应用场景：当需要排除某些特定值时，会使用 NOT IN 或 <>。 技术优缺点：优点是可以方便地排除特定值；缺点是会使索引失效。 注意事项：尽量避免使用 NOT IN 或 <>，可以尝试使用其他替代方法。

七、复合索引列顺序问题

在使用复合索引时，如果查询条件没有按照索引列的顺序进行使用，可能会导致索引部分失效。假设我们创建了一个复合索引 idx_user_id_name 包含 user_id 和 user_name 两列。

-- 创建复合索引
CREATE INDEX idx_user_id_name ON users (user_id, user_name);

-- 查询 user_name 为 'John' 的用户，没有按照索引列顺序使用
SELECT * FROM users WHERE user_name = 'John';

在这个查询中，由于没有先使用 user_id 列，idx_user_id_name 索引无法完全发挥作用。如果先使用 user_id 列，索引就可以正常使用。

-- 按照索引列顺序使用
SELECT * FROM users WHERE user_id = 1 AND user_name = 'John';

应用场景：在创建复合索引后，编写查询语句时可能没有注意索引列的顺序。 技术优缺点：优点是复合索引可以提高多列查询的效率；缺点是如果使用不当，会导致部分索引失效。 注意事项：在使用复合索引时，要按照索引列的顺序编写查询条件。

八、数据分布不均匀

如果索引列的数据分布不均匀，也可能导致索引失效。比如，user_status 列只有两个值 'active' 和 'inactive'，并且大部分数据都是 'active'。

-- 创建 user_status 列的索引
CREATE INDEX idx_user_status ON users (user_status);

-- 查询 user_status 为 'active' 的用户
SELECT * FROM users WHERE user_status = 'active';

由于数据分布不均匀，PolarDB 可能会认为全表扫描比使用索引更高效，从而导致索引失效。可以考虑使用分区表或者其他优化方法。 应用场景：在某些业务场景下，数据的分布可能会出现不均匀的情况。 技术优缺点：优点是索引在数据分布均匀时可以提高查询效率；缺点是数据分布不均匀时可能会失效。 注意事项：当发现数据分布不均匀时，可以考虑对表进行优化，如分区表。

九、统计信息不准确

PolarDB 会根据统计信息来选择最优的查询执行计划。如果统计信息不准确，可能会导致索引失效。可以使用 ANALYZE 语句来更新统计信息。

-- 更新 users 表的统计信息
ANALYZE users;

应用场景：在数据发生了大量插入、更新或删除操作后，统计信息可能会不准确。 技术优缺点：优点是统计信息可以帮助 PolarDB 选择最优的执行计划；缺点是不准确的统计信息会导致索引失效。 注意事项：定期更新统计信息，特别是在数据发生大规模变化后。

十、索引被禁用或删除

如果索引被禁用或者删除，自然就无法使用了。可以使用以下语句来查看和管理索引。

-- 查看 users 表的索引
SHOW INDEX FROM users;

-- 删除 idx_user_id 索引
DROP INDEX idx_user_id ON users;

应用场景：在进行数据库维护或者优化时，可能会不小心禁用或删除索引。 技术优缺点：优点是可以灵活管理索引；缺点是如果误操作，会导致索引失效。 注意事项：在进行索引管理操作时，要谨慎操作，避免误删或禁用重要索引。

文章总结

在 PolarDB 中，索引失效的场景有很多种，包括数据类型不匹配、使用函数操作索引列、使用 OR 连接条件等。我们在实际开发和数据库维护过程中，要注意这些场景，尽量避免索引失效，提高查询性能。同时，要根据具体的业务场景和数据特点，合理创建和使用索引，定期更新统计信息，确保数据库的高效运行。