PolarDB 索引失效：数据类型与函数使用案例

1. 引言：索引为什么突然不工作了？

作为一名数据库管理员，最让人头疼的事情之一就是：明明建立了索引，查询却依然慢如蜗牛。最近在PolarDB上就遇到了这样一个典型案例：一个原本运行良好的查询突然变得异常缓慢，经过排查发现是索引失效导致的。而罪魁祸首，竟然是数据类型转换和函数使用不当！

PolarDB作为阿里云自主研发的云原生数据库，虽然对索引有着很好的优化，但如果我们使用不当，依然会踩到索引失效的坑。今天，我们就来深入探讨PolarDB中因数据类型和函数使用导致的索引失效问题，并通过实际案例展示如何避免这些陷阱。

2. 数据类型不匹配导致的索引失效

2.1 隐式类型转换的代价

在PolarDB中，当我们比较不同数据类型的列或值时，数据库会自动进行隐式类型转换。这种转换虽然方便，但却可能导致索引失效。

-- 案例1：字符串与数字比较导致索引失效
-- 表结构
CREATE TABLE user_orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(20) NOT NULL,  -- 订单号实际上是数字字符串，如'100001'
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME NOT NULL,
    INDEX idx_order_no (order_no)  -- 为order_no建立了普通索引
);

-- 问题查询：使用数字与VARCHAR列比较
EXPLAIN SELECT * FROM user_orders WHERE order_no = 100001;
/*
执行计划显示：
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | user_orders| ALL  | idx_order_no  | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
可以看到type=ALL，表示全表扫描，索引idx_order_no没有被使用
*/

在这个案例中，虽然order_no列上有索引，但因为查询条件中使用了数字100001与VARCHAR类型的order_no比较，PolarDB需要进行隐式类型转换，导致索引失效。

2.2 解决方案

-- 正确写法：保持类型一致
EXPLAIN SELECT * FROM user_orders WHERE order_no = '100001';  -- 使用字符串比较
/*
执行计划显示：
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user_orders| ref  | idx_order_no  | idx_order_no | 82      | const |    1 | Using where |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+-------------+
现在使用了索引，rows=1表示效率很高
*/

3. 函数使用不当导致的索引失效

3.1 常见导致索引失效的函数操作

在PolarDB中，对索引列使用函数会导致索引失效，因为数据库无法预先计算函数的结果来使用索引。下面看几个典型案例：

-- 案例2：日期函数导致索引失效
-- 表结构
CREATE TABLE user_login_log (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    login_time DATETIME NOT NULL,
    ip VARCHAR(50) NOT NULL,
    INDEX idx_login_time (login_time)  -- 登录时间索引
);

-- 问题查询：对索引列使用DATE函数
EXPLAIN SELECT * FROM user_login_log WHERE DATE(login_time) = '2023-01-01';
/*
执行计划显示：
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | user_login_log| ALL  | NULL          | NULL | NULL    | NULL | 500000 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
type=ALL表示全表扫描，索引未被使用
*/

3.2 解决方案

-- 正确写法：使用范围查询替代函数
EXPLAIN SELECT * FROM user_login_log 
WHERE login_time >= '2023-01-01 00:00:00' 
AND login_time < '2023-01-02 00:00:00';
/*
执行计划显示：
+----+-------------+---------------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | user_login_log| range | idx_login_time| idx_login_time | 5       | NULL | 1200 | Using where |
+----+-------------+---------------+-------+---------------+----------------+---------+------+------+-------------+
现在使用了索引，rows=1200表示只扫描了少量数据
*/

4. 更复杂的案例：组合索引与函数

4.1 组合索引的部分失效

-- 案例3：组合索引部分失效
-- 表结构
CREATE TABLE product_sales (
    id BIGINT PRIMARY KEY,
    product_id BIGINT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(50) NOT NULL,
    INDEX idx_product_date (product_id, sale_date)  -- 组合索引
);

-- 问题查询：对组合索引的第二列使用函数
EXPLAIN SELECT * FROM product_sales 
WHERE product_id = 1001 
AND YEAR(sale_date) = 2023;
/*
执行计划显示：
+----+-------------+--------------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | product_sales| ref  | idx_product_date | idx_product_date | 8      | const | 500 | Using where |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------------+
虽然使用了索引，但只使用了product_id部分，sale_date部分因为函数而失效
*/

4.2 解决方案

-- 正确写法：使用范围查询
EXPLAIN SELECT * FROM product_sales 
WHERE product_id = 1001 
AND sale_date >= '2023-01-01' 
AND sale_date < '2024-01-01';
/*
执行计划显示：
+----+-------------+--------------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | product_sales| range | idx_product_date | idx_product_date | 12      | NULL |  120 | Using where |
+----+-------------+--------------+-------+------------------+------------------+---------+------+------+-------------+
现在完全使用了组合索引
*/

5. 函数索引：PolarDB的解决方案

5.1 函数索引的使用

PolarDB支持函数索引，这为解决上述问题提供了另一种思路：

-- 案例4：创建函数索引解决查询需求
-- 为YEAR(sale_date)创建函数索引
ALTER TABLE product_sales ADD INDEX idx_year_sale_date ((YEAR(sale_date)));

-- 现在之前的查询可以使用函数索引了
EXPLAIN SELECT * FROM product_sales 
WHERE product_id = 1001 
AND YEAR(sale_date) = 2023;
/*
执行计划显示：
+----+-------------+--------------+------+----------------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys              | key                  | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+----------------------------+----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | product_sales| ref  | idx_product_date,idx_year_sale_date | idx_year_sale_date | 5       | const |  120 | Using where |
+----+-------------+--------------+------+----------------------------+----------------------+---------+-------+------+-------------+
现在使用了函数索引
*/

5.2 函数索引的优缺点

优点：

可以直接支持对列使用函数的查询
避免了重写SQL的需求
特别适合无法修改的遗留查询

缺点：

占用额外的存储空间
增加写入时的计算开销
不是所有函数都支持创建函数索引

6. 其他需要注意的场景

6.1 LIKE查询与索引

-- 案例5：LIKE查询导致索引失效
-- 表结构
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    create_time DATETIME NOT NULL,
    INDEX idx_title (title)
);

-- 问题查询：LIKE以通配符开头
EXPLAIN SELECT * FROM articles WHERE title LIKE '%数据库%';
/*
执行计划显示：
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
全表扫描，索引失效
*/

解决方案：

-- 正确写法：使用全文索引或避免前导通配符
-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_title_content (title, content);

-- 使用全文索引查询
EXPLAIN SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);
/*
执行计划显示使用了全文索引
*/

6.2 OR条件与索引

-- 案例6：OR条件导致索引失效
-- 表结构
CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    INDEX idx_department (department),
    INDEX idx_salary (salary)
);

-- 问题查询：OR条件
EXPLAIN SELECT * FROM employees 
WHERE department = '研发部' OR salary > 10000;
/*
执行计划显示：
+----+-------------+-----------+------+----------------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys        | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employees | ALL  | idx_department,idx_salary | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-----------+------+----------------------+------+---------+------+------+-------------+
全表扫描，索引失效
*/

解决方案：

-- 正确写法：使用UNION ALL替代OR
EXPLAIN 
SELECT * FROM employees WHERE department = '研发部'
UNION ALL
SELECT * FROM employees WHERE salary > 10000 AND department <> '研发部';
/*
执行计划显示两个查询分别使用了索引
*/

7. 应用场景分析

在实际应用中，数据类型和函数导致的索引失效问题常见于以下场景：

遗留系统迁移：将其他数据库迁移到PolarDB时，原有的SQL可能包含隐式类型转换
多数据源集成：从不同数据源获取的数据类型可能不一致
报表查询：复杂的报表查询经常包含各种日期处理和格式化函数
动态SQL生成：应用程序动态生成的SQL可能不注意类型一致性
ORM框架使用：某些ORM框架生成的SQL可能不够优化

8. 技术优缺点总结

PolarDB在索引优化方面的优势：

支持函数索引，为特定查询提供优化手段
优秀的查询优化器，能识别更多可以使用索引的场景
对隐式类型转换有较好的处理能力
支持多种索引类型（B-Tree、全文、空间等）

仍需注意的限制：

隐式类型转换仍可能导致索引失效
不是所有函数都能用于函数索引
组合索引有最左前缀限制
某些复杂表达式仍无法使用索引

9. 注意事项

开发阶段：
- 设计表结构时，确保关联字段的数据类型一致
- 避免在索引列上使用函数
- 为常用查询条件创建合适的索引
测试阶段：
- 使用EXPLAIN分析关键查询的执行计划
- 对大数据量表进行查询性能测试
- 检查慢查询日志中的潜在问题
运维阶段：
- 定期监控索引使用情况，删除无用索引
- 使用PolarDB的性能洞察功能分析查询性能
- 考虑使用SQL审计功能追踪问题查询

10. 文章总结

在PolarDB使用过程中，索引是提高查询性能的关键，但数据类型不匹配和函数使用不当会导致索引失效，严重影响查询性能。通过本文的案例分析，我们了解到：

保持查询条件与索引列的数据类型一致至关重要
避免在索引列上使用函数，必要时考虑使用函数索引
组合索引有最左前缀原则，使用时需要注意顺序
LIKE查询、OR条件等特殊语法也容易导致索引失效
PolarDB提供的函数索引、全文索引等特性可以帮助解决特定场景下的性能问题

作为开发者和管理员，我们应该养成使用EXPLAIN分析查询执行计划的习惯，在设计和优化SQL时充分考虑索引的使用情况。只有这样，才能充分发挥PolarDB的性能优势，构建高效的数据库应用。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。