sql报表中对字段使用函数会导致索引失效，因索引基于原始值构建而函数改变数据形态；常见失效写法包括upper()、date()、左模糊like、concat()等；应改用校对规则、范围查询、右模糊、冗余字段或函数索引优化。
SQL报表中对字段使用函数（如 UPPER()、DATE()、SUBSTRING()、CONCAT() 等）进行条件过滤或连接时，数据库通常无法利用该字段上的索引，导致全表扫描，性能急剧下降。根本原因是：索引是基于原始列值构建的，而函数改变了数据形态，优化器无法将索引B+树中的有序值与函数计算结果直接匹配。
哪些常见函数写法会触发索引失效
以下写法在 WHERE 或 ON 子句中极易导致索引失效（假设 user_name 和 create_time 上有索引）：
-
WHERE UPPER(user_name) = 'ZHANGSAN'→ 索引失效（函数作用于索引列） -
WHERE DATE(create_time) = '2024-01-01'→ 索引失效（隐式类型转换 + 函数） -
WHERE user_name LIKE '%san'→ 索引失效（左模糊，无法用B+树前缀匹配） -
WHERE CONCAT('U_', user_name) = 'U_zhangsan'→ 索引失效（表达式计算列） -
WHERE user_name + '' = 'zhangsan'（MySQL中字符串隐式拼接）→ 同样失效
安全高效的函数改写策略
核心原则：**让索引列以原始、独立、可比较的形式出现在等值/范围条件左侧**。具体可按场景替换：
-
大小写比较：避免
UPPER()/LOWER()，改用校对规则（collation）。例如建索引时指定COLLATE utf8mb4_general_ci，查询直接写WHERE user_name = 'zhangsan'（不区分大小写） -
日期截断查询：避免
DATE(create_time) = '2024-01-01'，改用范围查询：WHERE create_time >= '2024-01-01' AND create_time -
前缀匹配：把
LIKE '%abc'（左模糊）转为LIKE 'abc%'（右模糊），或建立反向索引（如 MySQL 8.0+ 支持函数索引：CREATE INDEX idx_name_rev ON users ((REVERSE(user_name)))，再查REVERSE('cba') = REVERSE(user_name)） -
多字段拼接查询：避免在 WHERE 中拼接，改为业务层预计算或冗余字段。例如增加
full_code字段并建索引，查询走WHERE full_code = 'U_zhangsan'
函数索引（Functional Index）——MySQL 8.0+/PostgreSQL 的利器
当必须依赖函数逻辑且无法改写业务时，可显式创建函数索引，让优化器“知道”如何加速这类查询：
- MySQL 8.0+ 示例：
CREATE INDEX idx_upper_name ON users ((UPPER(user_name)));
之后WHERE UPPER(user_name) = 'ZHANGSAN'就能走索引 - PostgreSQL 示例：
CREATE INDEX idx_lower_email ON users (LOWER(email)); - 注意：函数索引要求表达式确定性（deterministic），且需确保查询中函数调用与索引定义完全一致（包括参数、括号、空格）
验证是否真正生效：别只看执行计划
执行 EXPLAIN 只是第一步。重点看三列：
- key：显示实际使用的索引名（非 NULL）
- rows：扫描行数应明显小于表总行数（如从百万级降到几百）
-
Extra：避免出现
Using filesort、Using temporary、Using where; Using index condition（后者虽用了索引但仍有回表过滤，不够理想） - 更进一步：用
EXPLAIN FORMAT=JSON查看used_columns和index_condition，确认索引列是否被完整下推
