计算列查询优化：含LOWER函数的索引有效性及性能提升方案

关于LOWER函数是否导致索引失效 #

是的，LOWER(email)这类函数调用会让你创建的tracker_id + email联合索引失效。因为索引存储的是email字段的原始值，数据库无法直接通过索引匹配经过LOWER()转换后的内容，只能先扫描符合tracker_id = :id和is_deleted = 0的记录，再逐一计算LOWER(email)并做对比，这就是查询速度慢的核心原因。

优化方案 #

1. 统一存储小写的邮箱/手机号 #

在数据插入或更新时，直接把email和phone字段转成小写存储（可以在业务层处理，或者用数据库触发器实现）。查询时去掉LOWER()函数，同时确保传入的:text参数也提前转成小写，SQL语句改成：

SELECT *
FROM customer
WHERE tracker_id = :id
  AND is_deleted = 0
  AND (email LIKE :text OR phone LIKE :text)
LIMIT :startingPosition, 11

这样就能直接命中你已有的tracker_id + email、tracker_id + phone联合索引，大幅提升查询效率。

2. 创建函数表达式索引 #

如果没法修改数据存储方式，就针对函数处理后的结果创建索引。以MySQL为例，创建包含LOWER(email)和LOWER(phone)的联合索引：

CREATE INDEX idx_tracker_isdeleted_lower_email ON customer(tracker_id, is_deleted, LOWER(email));
CREATE INDEX idx_tracker_isdeleted_lower_phone ON customer(tracker_id, is_deleted, LOWER(phone));

这样查询中的LOWER(email)和LOWER(phone)就能直接匹配索引，避免全量扫描。不同数据库语法略有差异，比如PostgreSQL用CREATE INDEX ... ON customer(tracker_id, is_deleted, lower(email));，原理一致。

3. 拆分OR查询为UNION ALL #

OR条件容易导致数据库无法高效利用索引，你可以把两个查询分支拆分后用UNION ALL合并，让每个分支单独使用对应的索引：

SELECT * FROM customer
WHERE tracker_id = :id
  AND is_deleted = 0
  AND email LIKE LOWER(:text)
UNION ALL
SELECT * FROM customer
WHERE tracker_id = :id
  AND is_deleted = 0
  AND phone LIKE LOWER(:text)
LIMIT :startingPosition, 11;

如果存在同一记录同时匹配email和phone条件的情况，用UNION代替UNION ALL来去重，但UNION ALL的性能更优。

4. 优化现有索引结构 #

把is_deleted字段加入到联合索引中，比如将原索引tracker_id + email改成tracker_id, is_deleted, email（如果用函数索引就改成tracker_id, is_deleted, LOWER(email)）。这样查询中的is_deleted = 0条件可以直接通过索引过滤，减少需要回表查询的记录数。

内容的提问来源于stack exchange，提问作者thomas113412