mysql函数在where条件中如何使用_mysql查询优化技巧

where中直接调用mysql函数会导致索引失效，引发全表扫描；应避免date()、upper()等函数，改用范围查询如create_time >= '2024-01-01 00:00:00' and create_time

WHERE 中直接调用 MySQL 函数会拖慢查询速度

MySQL 在 WHERE 子句里对字段使用函数（比如 DATE(created_at)、UPPER(name)、YEAR(order_date)）时，通常会导致该字段无法走索引，哪怕它上面建了索引。这是因为函数改变了原始值的存储形态，优化器无法用 B+ 树索引做快速定位。

常见现象：执行 EXPLAIN 后看到 type=ALL 或 key=NULL，说明全表扫描了。

• 避免写 WHERE DATE(create_time) = '2024-01-01'，改用范围查询：WHERE create_time >= '2024-01-01 00:00:00' AND create_time
• 避免 WHERE UPPER(email) = 'ADMIN@EXAMPLE.COM'，改在写入时统一转小写并建索引，查询用 WHERE email = 'admin@example.com'
• 日期分组场景下，别在 WHERE 里用 WEEK() 或 MONTH()，优先考虑生成计算列或冗余字段（如 year_month CHAR(7)）并索引它

函数索引（MySQL 8.0.13+）能解决部分问题

MySQL 8.0.13 起支持函数索引（Functional Key），允许对表达式建索引，比如 UPPER(name) 或 DATE(updated_at)。但要注意它不是“万能加速器”，而是有明确限制的补救手段。

使用前确认：SELECT VERSION(); 返回 ≥ 8.0.13；且表引擎是 InnoDB。

腾讯混元

腾讯混元大由腾讯研发的大语言模型，具备强大的中文创作能力、逻辑推理能力，以及可靠的任务执行能力。

下载

• 建索引示例：CREATE INDEX idx_upper_name ON users ((UPPER(name))); —— 注意双括号语法
• 查询必须完全匹配该表达式才能命中：WHERE UPPER(name) = 'JOHN' 可走索引；WHERE name LIKE 'j%' 不行
• 函数索引不支持全文索引、空间索引，也不适用于 JSON_EXTRACT() 等返回非标量结果的函数

WHERE 里用函数 ≠ 一定不能优化，关键看是否可下推

有些函数其实是“索引友好型”的，比如 CAST()、CONVERT() 在类型兼容时可能被优化器下推，而 SUBSTRING()、REPLACE() 基本无法下推。是否能走索引，最终取决于优化器能否将条件重写为索引可比较的形式。

• WHERE CAST(id AS CHAR) = '123'：如果 id 是整型主键，优化器大概率会转成 WHERE id = 123，仍可用索引
• WHERE SUBSTRING(phone, 1, 3) = '138'：无法利用 phone 字段上的普通索引，除非建前缀索引（如 INDEX idx_phone_prefix (phone(3))）
• 用 EXPLAIN FORMAT=TREE（MySQL 8.0+）可看清优化器是否做了“Condition pushdown”

替代方案比硬扛函数更可靠

比起在 WHERE 里硬套函数，更可持续的做法是调整数据模型或查询逻辑。函数只是临时绕开设计缺陷的胶带，不是长期解法。

• 把常用计算结果存为生成列（GENERATED COLUMN），并为其加索引。例如：date_only DATE AS (DATE(created_at)) STORED，再 CREATE INDEX idx_date_only ON t1(date_only)
• 业务上需要频繁按小时统计？考虑在写入时就拆出 hour_key CHAR(13)（如 '2024-01-01-14'），避免每次查都算 HOUR(created_at)
• 模糊前缀匹配（如 LIKE 'abc%'）可以走索引；但 LIKE '%abc' 或 LIKE '%abc%' 不行，此时应评估是否引入全文索引或外部搜索引擎

真正卡住性能的，往往不是函数本身，而是函数掩盖了索引设计缺失或数据分布不合理。先看 EXPLAIN，再想函数，最后才考虑加索引——顺序错了，优化就容易白忙。