企业官网建设流程全解析

本文摘要：线上接口突然变慢，慢查询日志里出现 DATE() 包裹索引列的写法，EXPLAIN 显示全表扫描。用 EXPLAIN ANALYZE 实测范围改写与函数索引两种修复的代价差异，并给出版本对照。

一、问题与结论

一个按日期筛选订单的接口，EXPLAIN输出type=ALL、key=NULL——created_at列上明明建了KEY idx_created_at (created_at)，索引却被完全绕开。SQL 原文如下：

SELECTCOUNT(*)FROMordersWHEREDATE(created_at)='2024-06-15';

结论先行：B-tree 索引存储的是列的原始值，不是DATE()等函数的运算结果。优化器拿到DATE(created_at) = '2024-06-15'时，无法将等式右边的值直接与索引中的DATETIME原始值做范围匹配，只能回退到全表扫描。这是基于成本的优化器决策，不是 bug——所有支持 B-tree 索引的 MySQL 版本都存在此行为。

二、排查与选择依据

慢查询日志与 EXPLAIN 定位

排查分三步。第一步开启慢查询日志，将阈值降到 1 秒捕获可疑查询：

SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;

日志里出现的 SQL 原文直接暴露了DATE(created_at)这个函数调用。拿到 SQL 后，执行EXPLAIN即可确认type=ALL和key=NULL。

第二步用EXPLAIN ANALYZE（需要 8.0.18+）获取实际执行代价。普通EXPLAIN只展示优化器的预估计划，EXPLAIN ANALYZE会真实执行并输出每一步的实际行数和耗时，两者可以交叉验证。

第三步对比修复后的执行计划。如果范围改写后type变为range、key变为idx_created_at，说明问题定位正确。

如果表规模只有几千行，全表扫描可能只有几毫秒，不会被慢查询日志捕获。此时建议在业务低峰期手动对线上表执行EXPLAIN ANALYZE，直接对比两种写法的计划差异。

替代方案与取舍

修复思路有四条，各有明确的适用边界：

什么时候不该用上述方案：如果查询频率极低（如每小时执行一次的报表任务），全表扫描的实际耗时在可接受范围内，强行加索引反而增加写入时的维护开销。生成列方案还有一个容易忽略的代价——查询若仍写DATE(created_at)，MySQL 不会自动命中生成列，必须显式查询该列，对存量代码的改动量较大。

三、关键原理

MySQL 官方文档在 “Index Use and Function Calls” 一节中明确说明：当 WHERE 条件对索引列调用函数时，条件无法与索引中存储的值进行直接比较，优化器因此放弃该索引。这条行为属于文档描述的已知限制，不是 bug。

其根本原因在于 B-tree 的查找机制。B-tree 索引按列值的字典序组织，支持等值匹配和范围扫描。但当条件变成DATE(created_at) = '2024-06-15'时，MySQL 需要对索引中每一行都执行一次DATE()函数才能判断是否匹配——这本质上等价于全表扫描，索引的有序性完全无法发挥作用。

MySQL 8.0.13 引入了functional index（函数索引），将函数表达式的计算结果物化并存入 B-tree：

ALTERTABLEordersADDINDEXidx_date_expr((DATE(created_at)));

函数索引要求函数是确定性的。RAND()、NOW()、CURRENT_TIMESTAMP等非确定性函数不允许建索引，因为同一行每次读取可能产生不同结果，索引值不稳定。

四、可运行示例

以下示例在 MySQL 8.0.13+ 环境下可直接执行，完整覆盖"复现问题 → 对比修复"的验证流程。

CREATETABLEorders(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,user_idINTUNSIGNEDNOTNULL,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULL,KEYidx_created_at(created_at))ENGINE=InnoDB;INSERTINTOorders(user_id,amount,created_at)SELECTFLOOR(1+RAND()*100000),ROUND(RAND()*500,2),DATE_ADD('2024-01-01',INTERVALFLOOR(RAND()*600)DAY)+INTERVALFLOOR(RAND()*86400)SECONDFROM(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)a,(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)b,(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)c,(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)d,(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)e,(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)f;ANALYZETABLEorders;

场景 A：函数包裹——索引失效

EXPLAINANALYZESELECTCOUNT(*)FROMordersWHEREDATE(created_at)='2024-06-15';

预期输出（关键字段）：

-> Aggregate: count(*) (cost=165051 rows=1) -> Filter: (date(orders.created_at) = '2024-06-15') (cost=163875 rows=1667) -> Table scan on orders (cost=163875 rows=1000000)

Table scan on orders确认走了全表扫描，idx_created_at未被使用。

实际输出：因硬件和数据分布而异，验证重点是计划中出现Table scan而非Index range scan。上述成本数值为按数据量估算的预期值，请以实际执行结果为准。

场景 B：范围改写——索引命中

EXPLAINANALYZESELECTCOUNT(*)FROMordersWHEREcreated_at>='2024-06-15 00:00:00'ANDcreated_at<'2024-06-16 00:00:00';

预期输出（关键字段）：

-> Aggregate: count(*) (cost=72 rows=1667) -> Index range scan on orders using idx_created_at (cost=72 rows=1667)

Index range scan配合using idx_created_at，确认索引命中。对比两个场景的执行计划，从全表扫描到索引范围扫描的差异说明了索引失效的代价。

实际输出：验证重点是计划从Table scan变为Index range scan，且key指向idx_created_at。成本数值受硬件和数据分布影响，以实际执行结果为准。

场景 C：函数索引——保留函数写法

ALTERTABLEordersADDINDEXidx_date_expr((DATE(created_at)));EXPLAINANALYZESELECTCOUNT(*)FROMordersWHEREDATE(created_at)='2024-06-15';

预期输出（关键字段）：

-> Aggregate: count(*) (cost=72 rows=1667) -> Index range scan on orders using idx_date_expr (cost=72 rows=1667)

命中idx_date_expr，代价与范围改写方案相近。但注意，百万行表上执行ALTER TABLE会触发 online DDL，期间增加额外 I/O 开销，建议在低峰期操作。

实际输出：验证重点是key变为idx_date_expr，计划从Table scan变为Index range scan。若仍出现Table scan，检查索引定义的表达式是否与查询中完全一致。

常见失败：表达式不匹配导致函数索引失效

建了DATE(created_at)的函数索引后，如果查询改用UNIX_TIMESTAMP写法：

EXPLAINANALYZESELECTCOUNT(*)FROMordersWHEREUNIX_TIMESTAMP(created_at)>=UNIX_TIMESTAMP('2024-06-15 00:00:00')ANDUNIX_TIMESTAMP(created_at)<UNIX_TIMESTAMP('2024-06-16 00:00:00');

idx_date_expr不会被命中，回退全表扫描。函数索引只对与索引定义完全一致的表达式生效。修复方法：确保查询中的函数写法与索引定义逐字一致，或为UNIX_TIMESTAMP的写法单独建索引。

五、验证结果与边界

两个容易忽略的边界：

1. 表小不是问题的保护伞。千行级别的表，全表扫描和索引扫描的耗时差异不明显，优化器甚至可能"正确地"选择全表扫描。索引失效在百万行以上的大表上才显著暴露，排查时面对的往往已经是大表了。

2. 函数索引不降低写入开销。每次INSERT、UPDATE都需要重新计算函数值并维护额外的 B-tree 条目。高频写入表加函数索引前，应先评估 DML 性能影响。如果查询频率远低于写入频率，范围改写通常是更均衡的选择。

参考资料

MySQL 8.0 Reference Manual - Index Use and Function Calls（函数条件导致索引失效的文档依据）

MySQL 8.0 Reference Manual - CREATE INDEX Statement（函数索引语法与确定性要求）

MySQL 8.0 Reference Manual - Optimizer Switches（优化器开关对计划选择的影响）

MySQL 8.0 Reference Manual - Online DDL Operations（DDL 期间的锁与 I/O 影响）