避免WHERE子句中使用函数的索引优化策略

当索引失效时发生了什么？

在日常数据库性能调优中，开发者常遇到这样的场景：明明已经建立了索引，但查询性能却未达预期。通过EXPLAIN命令分析执行计划时，会看到"Using where; Using filesort"的提示，这意味着数据库引擎未能有效利用索引。这种现象往往与WHERE子句中函数的使用密切相关。

典型案例分析

假设存在用户表user_info：

CREATE TABLE `user_info` (
  `id` INT PRIMARY KEY,
  `mobile` VARCHAR(11) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created(created_at)
);

当执行以下查询时：

SELECT * FROM user_info 
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2023-06';

虽然created_at字段已建立索引idx_created，但数据库优化器却无法使用该索引。这是因为DATE_FORMAT()函数对字段进行了包装，导致索引树中的值无法与查询条件直接匹配，这种现象称为索引列计算失效。

函数包装的代价

1. 索引结构失效原理

B+树索引存储的是字段原始值，当使用YEAR(created_at)、SUBSTRING(mobile,1,7)等函数时：

• 需要为每条记录实时计算函数值
• 无法利用索引的有序性特征
• 导致全表扫描（type=ALL）

2. 性能损耗量化

通过对比测试发现：

函数的使用使查询效率下降约80倍，这种损耗在大数据量场景下会呈指数级增长。

三大优化策略

策略一：表达式改写

将函数计算转移到查询条件右侧：

-- 原始写法
WHERE YEAR(created_at) = 2023 

-- 优化后
WHERE created_at >= '2023-01-01' 
  AND created_at < '2024-01-01'

通过调整时间范围查询，既保持语义一致性，又让created_at索引能够被有效利用。这种改写方式适用于时间函数（DATE_FORMAT/YEAR/MONTH）、字符串截取（SUBSTRING）等场景。

策略二：冗余字段

对于需要频繁计算的列，可增加预处理字段：

ALTER TABLE user_info 
ADD COLUMN created_month CHAR(7) 
GENERATED ALWAYS AS (DATE_FORMAT(created_at, '%Y-%m')) STORED;

CREATE INDEX idx_created_month ON user_info(created_month);

通过存储生成的created_month字段，查询可直接使用：

SELECT * FROM user_info 
WHERE created_month = '2023-06';

策略三：虚拟列技术

在MySQL 5.7+和Oracle 11g+中支持函数索引：

-- MySQL语法
ALTER TABLE user_info 
ADD INDEX idx_created_year ((YEAR(created_at)));

-- PostgreSQL语法
CREATE INDEX idx_created_year 
ON user_info (EXTRACT(YEAR FROM created_at));

这种方法直接建立函数表达式的索引，但需要注意：

• 增加存储空间消耗
• 不同数据库语法存在差异
• 需定期维护函数索引

跨数据库的函数索引实现差异

MySQL的函数索引

-- 8.0+支持函数索引
ALTER TABLE orders 
ADD INDEX idx_order_year ((YEAR(order_date)));

-- 虚拟列+索引组合方案
ALTER TABLE employees 
ADD COLUMN name_initials VARCHAR(3) 
GENERATED ALWAYS AS (CONCAT(LEFT(first_name,1), LEFT(last_name,1))) VIRTUAL,
ADD INDEX idx_initials(name_initials);

实现特点：

• 必须使用括号包裹函数表达式
• 支持JSON字段的函数索引
• 虚拟列可选择STORED/VIRTUAL存储方式

Oracle的函数索引

CREATE INDEX idx_emp_upper 
ON employees(UPPER(last_name));

-- 支持基于函数的域索引
CREATE INDEX idx_product_search 
ON products(SUBSTR(description,1,50)) 
INDEXTYPE IS CTXSYS.CONTEXT;

特殊能力：

• 支持用户自定义函数的索引
• 可创建基于函数的位图索引
• 提供函数索引的监控视图

PostgreSQL表达式索引

CREATE INDEX idx_orders_total 
ON orders ((unit_price * quantity));

-- 支持复杂表达式
CREATE INDEX idx_geo_distance 
ON locations 
((point(lat, lon) <-> point(40.7128, -74.0060)));

优势对比：

• 表达式索引不依赖虚拟列
• 支持空间数据类型运算
• 可索引自定义函数返回值

虚拟列的最佳实践

存储方式选择原则

性能优化方案

1. 复合索引优化：将虚拟列与常用查询字段组合

   CREATE INDEX idx_emp_contact 
   ON employees (email_prefix, department_id);
   

2. 覆盖索引策略：包含所有查询字段避免回表

   CREATE INDEX idx_order_summary 
   ON orders (order_year) INCLUDE (total_amount, customer_id);
   

3. 统计信息维护：定期更新直方图数据

   ANALYZE TABLE user_info 
   UPDATE HISTOGRAM ON created_month;
   

自动化优化工具链

SQL重写引擎

# 使用SQLRewrite工具示例
from sql_rewriter import optimize_query

original_sql = """
SELECT * FROM logs 
WHERE HOUR(event_time) BETWEEN 9 AND 18
"""

optimized_sql = optimize_query(original_sql)
# 输出: WHERE event_time >= CURDATE() + INTERVAL 9 HOUR 
#    AND event_time < CURDATE() + INTERVAL 18 HOUR

支持规则：

• 时间函数范围转换
• 字符串操作反向改写
• 数学表达式预计算

慢查询分析工具

使用pt-query-digest分析日志：

pt-query-digest /var/lib/mysql/slow.log 
--filter '$event->{arg} =~ m/WHERE.*\(/'
--limit=10

输出报告包含：

1. 函数使用频次统计
2. 索引失效TOP语句
3. 自动生成的优化建议

互动思考环节

思考题

1. 当对WHERE LOWER(username) = 'admin'建立(LOWER(username))函数索引后，查询WHERE username = 'Admin'能否命中索引？为什么？
2. 如何为WHERE CAST(id AS CHAR) = '1001'这类类型转换查询设计优化方案？

案例讨论

某电商平台订单查询出现性能问题：

SELECT * FROM orders 
WHERE FROM_UNIXTIME(create_time, '%Y-%m') = '2023-12' 
ORDER BY amount DESC 
LIMIT 100;

表结构：

CREATE TABLE `orders` (
  `order_id` BIGINT PRIMARY KEY,
  `create_time` BIGINT COMMENT '秒级时间戳',
  `amount` DECIMAL(10,2),
  INDEX idx_ctime(create_time)
);

请根据本文知识设计至少两种优化方案，并说明选择依据。

🌟 让技术经验流动起来

▌▍▎▏ 你的每个互动都在为技术社区蓄能 ▏▎▍▌
✅ 点赞 → 让优质经验被更多人看见
📥 收藏 → 构建你的专属知识库
🔄 转发 → 与技术伙伴共享避坑指南

点赞 ➕ 收藏 ➕ 转发，助力更多小伙伴一起成长！💪

💌 深度连接：
点击 「头像」→「+关注」
每周解锁：
🔥 一线架构实录 ｜ 💡 故障排查手册 ｜ 🚀 效能提升秘籍