MySQL 函数索引不生效时怎么检查表达式一致性

MySQL 函数索引看起来已经建好，但查询仍然走全表扫描时，先别急着加更多索引。最常见的排查顺序是：把索引定义里的函数表达式与 WHERE 谓词逐字对照，再检查复合索引的前导列、查询需要的列和回表成本，最后用 SHOW INDEX、EXPLAIN 或 EXPLAIN ANALYZE确认优化器实际选择。

先确认索引里的表达式和查询谓词是不是同一件事

函数索引索引的是“表达式的值”，不是一个可以随意改写的函数名字。比如按天查询订单，可以把日期表达式放进索引：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL,
  KEY idx_order_day ((DATE(created_at)), tenant_id, status)
);

对应的查询先保持同样的表达式形状：

SELECT id, tenant_id, status
FROM orders
WHERE DATE(created_at) = '2026-09-07'
  AND tenant_id = 17;

这里要核对四件事：函数是不是同一个、参数顺序是否相同、是否额外包了一层转换、常量类型是否和表达式结果相容。MySQL 官方文档用 SUBSTRING() 举例说明，索引定义和查询中的参数需要一致；所以把查询改成不同长度的截取、不同的转换链，不能只看“都在处理日期或字符串”就认为能命中。

一个实用的对照表如下：

再看复合索引的列顺序和回表成本

表达式一致，只能说明“有机会使用”。上面的索引实际由函数列、tenant_id 和 status组成，查询能否有效缩小范围，还取决于索引列顺序。生产排查时把它拆成三个问题：

1. 查询是否提供了前导 key part，还是只过滤了后面的列？
2. 函数结果的选择性是否足够，命中行太多时全表扫描可能更便宜？
3. SELECT返回的列是否都在索引可获得的范围内，还是仍要回表读取整行？

以 idx_order_day ((DATE(created_at)), tenant_id, status)为例，日期与租户条件一起出现时，优化器更容易把它当成一个有边界的查找；如果只写租户条件，函数列在最前面就可能让这个索引不适合当前查询。反过来，如果业务最常按租户再按日期查，也可以评估 (tenant_id, (DATE(created_at)), status)的顺序，但要用真实查询集合比较，而不是凭列名排序。

覆盖索引也不要理解成“只要建了函数索引就不会回表”。InnoDB 二级索引会携带主键值；但查询如果还需要索引之外的列，仍然可能回到聚簇索引读取整行。图中把 PRIMARY(id)、SELECT 列、覆盖索引和回表放在同一访问关系里，便于定位这部分成本。

用 SHOW INDEX 和 EXPLAIN 把猜测变成证据

先看索引当前到底长什么样，再看优化器对具体语句的判断：

-- 先核对索引名、顺序和基数等元数据
SHOW INDEX FROM orders;

-- 再看当前语句选择了哪个 key，以及预计扫描多少行
EXPLAIN FORMAT=JSON
SELECT id, tenant_id, status
FROM orders
WHERE DATE(created_at) = '2026-09-07'
  AND tenant_id = 17;

重点不是只看 possible_keys，而是看实际的 key、访问类型、估算行数以及是否出现覆盖索引相关信息。需要核对实际执行与估算偏差时，再在可接受的测试或灰度环境使用 EXPLAIN ANALYZE；它会执行语句，因此不要把它当成无副作用的计划预览。

如果索引定义和谓词都对，但计划仍不理想，可以先确认表统计信息是否过旧，再评估选择性与查询返回列。ANALYZE TABLE orders;会更新优化器使用的统计信息，但它不是“强制使用索引”的按钮；更新后仍应重新查看计划。

常见误区：重写函数、盲目加列和忽略统计信息

• 把“看起来等价”当成“优化器一定等价”：先统一函数和参数，再讨论是否需要改写查询。
• 只增加索引列：额外列可能扩大索引和写入成本，先确认它是否服务于过滤、排序或覆盖。
• 看到全表扫描就强制索引：表很小、匹配范围很大或索引选择性低时，全表扫描可能本来就是更便宜的方案。

可以把排查顺序固定为“表达式一致性 → 列顺序 → 返回列与回表 → 统计信息 → 新索引设计”。这样每一步都有可观察证据，也能避免把一个谓词写法问题误判成索引数量问题。

相关问题

函数索引和生成列索引该怎么选？

函数索引适合表达式稳定、查询写法明确的场景；如果需要让表达式结果被显式查询、复用或单独维护，可以评估生成列，但要按同一组查询和写入成本比较。

为什么 EXPLAIN 的 possible_keys 有索引，key 却是 NULL？

possible_keys只是候选集合，key才是该计划实际选择的索引。继续看选择性、估算行数、返回列和表规模，不能只凭候选列表下结论。

什么时候应该先跑 ANALYZE TABLE？

当数据分布发生明显变化、索引基数估算异常且索引定义和谓词已经对齐时，可以先更新统计信息，再重新比较计划；不要用它替代表达式和列顺序检查。