varchar存时间导致索引失效？从慢查询到datetime改造实战

1. 事故现场：一条对账SQL如何从毫秒级变成全表扫描

1.1 业务背景与表结构

前阵子线上对账服务突然报慢查询告警，单条SQL的执行时间从几十毫秒一路涨到47秒。DBA把慢查询日志甩到我这边时，第一反应是数据量涨了，或者某个索引被误删了。等拿到执行计划一看，明明索引就在那儿，优化器却选择了全表扫描，而罪魁祸首不是什么高深的配置，而是一个被很多人忽视的建表习惯：用varchar存时间字段。

先交代一下背景。pay_order是一张支付订单表，核心字段大概是这样的：

CREATE TABLE `pay_order` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(64) NOT NULL, `user_id` bigint unsigned NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `pay_amount` decimal(10,2) NOT NULL DEFAULT '0.00', `create_time` varchar(19) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_create_time` (`create_time`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

create_time是varchar(19)，存的是形如“2024-01-15 10:23:45”的字符串。表里当时有大约350万行数据，不算特别大，但已经足够让一条错误的执行计划把接口拖垮。这张表是好几年前的老项目留下的，当时开发图省事，接口入参就是字符串，直接拼了进去。小数据量时怎么跑都行，等到数据量上了百万，隐患就藏不住了。

1.2 慢查询日志里的第一现场

接到告警后，我第一时间从慢查询日志里捞出了那条SQL：

# Query_time: 47.123456 Lock_time: 0.000321 Rows_sent: 100 Rows_examined: 3567880 SET timestamp=1737000000; SELECT id, order_no, pay_amount FROM pay_order WHERE create_time >= '2024-01-15 00:00:00' AND create_time < '2024-01-16 00:00:00' ORDER BY user_id LIMIT 100;

注意几个关键数字：Query_time 47秒，Rows_examined 356万，几乎等于全表行数，最后Rows_sent却只有100行。这是典型的“为了取100行翻了全表”。这个接口本身是给财务对账用的，每15分钟跑一次，取最近一天的数据按user_id排序后分批拉取。以前一两百毫秒就能完成，现在要40多秒，直接导致了上游任务队列积压。

拿到SQL后，我做了两件事：一是用相同参数手动执行，确认稳定复现；二是跑了一次EXPLAIN，把优化器的选择看清楚。

1.3 基础EXPLAIN解读：possible_keys里有索引，key却是空的

EXPLAIN的输出当时长这样：

+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+ | 1 | SIMPLE | pay_order | NULL | ALL | idx_create_time| NULL | NULL | NULL | 3567880 | 11.11 | Using where; Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+

最蹊跷的地方就在这里：possible_keys一列明明写着idx_create_time，说明优化器知道这个索引存在，也认为它有可能被使用；但最终的key却是NULL，type是ALL，意味着它最终选择了全表扫描，然后在内存里做where过滤和filesort排序。

“明明有索引为什么不用”这句话我在各种群里看过无数次，真到自己排查时才知道，浅层原因是隐式类型转换，深层原因则跟数据格式、统计信息和成本评估都有关系。下面一步步拆。

2. 第一层根因：varchar时间字段与隐式类型转换

2.1 隐式类型转换的触发条件和MySQL转换规则

第二层根因从代码里找到。对账服务的Mapper接口里，方法参数是java.util.Date类型，XML里的SQL这样写：

<select id="listForReconcile" resultType="PayOrder"> SELECT id, order_no, pay_amount FROM pay_order WHERE create_time &gt;= #{beginTime} AND create_time &lt; #{endTime} ORDER BY user_id LIMIT 100 </select>

问题就出在参数绑定上。MyBatis对java.util.Date默认使用TimestampTypeHandler，最终通过PreparedStatement.setTimestamp()绑定参数。也就是说，MySQL收到的比对值不是字符串，而是一个DATETIME/TIMESTAMP类型。

当varchar列和一个DATETIME值比较时，MySQL的隐式类型转换规则是这样的：字符串和日期时间比较，会把字符串转换为日期时间再比较，而不是把日期时间转成字符串。于是SQL实际执行时，等价于对create_time列套了一层CAST：

CAST(create_time AS DATETIME) >= '2024-01-15 00:00:00'

只要索引列被包在函数或CAST里，B+树的顺序就被破坏了，优化器无法再基于索引列本身的有序性做范围定位，只能放弃索引，退化为全表扫描。

2.2 MyBatis/JDBC参数类型绑定带来的坑

这个坑最隐蔽的地方在于：SQL文本里看起来一模一样，都是create_time >= '2024-01-15 00:00:00'，但底层绑定的是字符串还是时间类型，执行计划可能完全不同。

我在测试环境做了一个对照实验。同样的表和数据，用字符串常量查询：

EXPLAIN SELECT * FROM pay_order WHERE create_time >= '2024-01-15 00:00:00';

得到的结果是type=range，key=idx_create_time，rows只有一万多。

再用显式CAST成DATETIME的方式模拟MyBatis的绑定：

EXPLAIN SELECT * FROM pay_order WHERE create_time >= CAST('2024-01-15 00:00:00' AS DATETIME);

结果直接变成type=ALL，rows=3567880。两组执行计划的差异非常明显，问题基本锁定就是参数类型不匹配触发的隐式转换。

两个执行计划的对比：

2.3 修复写法后的执行计划对比

修复方式其实很简单：让绑定参数变成字符串。在MyBatis里可以给方法参数加@Param注解后，在XML中手动指定字符串类型，更省事的做法是在Java代码里直接用DateTimeFormatter把Date格式化成“yyyy-MM-dd HH:mm:ss”字符串再传入。

我当时的改法是这样：

<select id="listForReconcile" resultType="PayOrder"> SELECT id, order_no, pay_amount FROM pay_order WHERE create_time &gt;= #{beginTime, jdbcType=VARCHAR} AND create_time &lt; #{endTime, jdbcType=VARCHAR} ORDER BY user_id LIMIT 100 </select>

发布后再看执行计划，type从ALL变成了range，key为idx_create_time，rows从356万降到1.2万左右，接口耗时从47秒回到200毫秒以内。到这里，第一层问题解决。

不过，如果故事到这里就结束，这篇博客没有必要写。真正让人头疼的是，这个修复上线后的第三天，另一个按天汇总的对账脚本又开始报慢查询。这次的SQL写法上明明都用了字符串比较，索引却没有按预期工作，原因就藏在varchar时间字段的数据格式里。

3. 别以为改成字符串就没事：脏数据与表达式让索引再次失效

3.1 数据格式不统一，字典序和时间序悄然错位

第二次中招的慢SQL长这样：

SELECT DATE_FORMAT(STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') AS d, COUNT(*), SUM(pay_amount) FROM pay_order WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-02-01 00:00:00' GROUP BY d;

这个SQL在where条件里确实用的是字符串比较，按理说能走idx_create_time。EXPLAIN显示key也确实变成了idx_create_time，type=range，rows从全表降到了30多万行。但查询仍然耗费十几秒，问题出在两方面：select和group by里的函数，加上历史数据的脏格式。

先看数据格式。我随机抽查了表里create_time的值，发现除了规范的“2024-01-15 10:23:45”，还有不少“2024-1-5 9:5:3”这种没补零的写法，长度从13位到19位不等。这种数据是早期代码用字符串拼接日期时间产生的，没有做格式化统一。

varchar列上的B+树索引，本质是按字符串的字典序排列的。只有字符串格式完全统一、且显式补零到定长，字典序才恰好等于时间序。一旦出现不补零的数据，比如“2024-02-01”和“2024-1-5”放在一起比较：

“2024-01-05”的第五个字符是'0'，“2024-1-5”的第五个字符是'1'，按字典序前者排在后者前面，但按真实时间排序就乱了。再拿“2024-02-01”和“2024-1-5”比较，第五位'0'小于'1'，于是二月一日排到了一月五日前面，时间顺序直接反了。

这意味着优化器在基于varchar列做范围扫描时，无法准确判断哪些字符串值落在业务想要的时间区间内。为了不丢数据，它只能扩大扫描范围，或者直接放弃索引选择更保守的全表过滤。数据越脏，这种不稳定性越严重，执行计划的波动就越难预测。

3.2 表达式包裹列，覆盖索引和索引下推全部落空

第二个问题在于查询里的SELECT和GROUP BY都对create_time使用了STR_TO_DATE和DATE_FORMAT。以MySQL 8.0为例，虽然支持索引下推（ICP），可以过滤掉一部分不满足条件的行再回表，但无法做到“不回表”：

• 索引idx_create_time里只存了create_time的原始字符串，而查询要的是经过STR_TO_DATE转换后的日期，无法直接从索引叶子节点取到结果；
• GROUP BY的列是表达式DATE_FORMAT(...)，索引里同样没有排好序的值，必须构建临时表做分组；
• 这30多万行数据要先回表取create_time、pay_amount等字段，再对每行做函数计算，最后分组统计，整个过程在CPU和随机IO上的开销都很大。

这解释了为什么key看起来是对的、rows也在能接受的范围内，查询却还是慢。索引能帮上忙的只有where那一层，后续的处理它一个都帮不上。这也是varchar存时间字段一个很容易被低估的坏处：所有时间函数、日期运算、分组维度提取，都不能直接在索引上完成。

3.3 为什么“格式统一”只是看上去美好

这里插一段观点。有些人会说，只要保证所有数据都严格按“YYYY-MM-DD HH:mm:ss”补零，varchar存时间不也能用索引吗？从B+树原理上讲，格式统一且定长语义不变时，字典序确实等于时间序，范围扫描也能正常工作。我在这个项目里也一度这么想，差点就让业务侧做一个一次性数据清洗然后继续用varchar。

最终没有这么做，有三个原因：

• 格式规约只能靠“人遵守”，一旦某个老接口或者第三方回调没有按格式拼接，脏数据又会冒出来，问题会反复。
• varchar(19)在utf8mb4字符集下索引键字节数远大于datetime（19字符最多76字节，相对datetime的8字节），索引页能存放的键值数量少很多，范围扫描时读更多索引页，成本天然偏高。
• 日期函数在varchar上无法直接使用，后续每写一个统计SQL都要记得转换，维护成本很高。

所以，单纯清洗数据是治标不治本。varchar时间字段的根子问题，在于类型语义错了：时间就该用时间类型存储，字符串只能描述它，不能替代它。

4. 优化器为什么“选错”索引：成本估算与统计信息

4.1 执行计划里的rows、filtered、Extra到底该怎么读

在排查第二次慢查询的过程中，我还发现一个很多人容易忽略的点：EXPLAIN输出里的rows是一个估算值，不是实际扫描行数，更不代表最终要返回的行数。它来自优化器对索引统计信息和数据分布的推测，可能被高估或低估，直接影响到选不选这个索引。

以这条SQL为例：

SELECT id, order_no, pay_amount FROM pay_order WHERE create_time >= '2024-01-15 00:00:00' AND create_time < '2024-01-16 00:00:00' ORDER BY user_id LIMIT 100;

EXPLAIN里filtered如果是11.11%，rows估算为356万，意味着优化器认为经过where条件过滤后大概会剩下39万行，然后再对这39万行做sort排序，最后取100行。优化器在比较走idx_create_time和直接全表扫描两条路径的成本时，会把这39万行的回表开销和filesort开销都算进去。当它认为回表随机IO代价太高时，它就会放弃索引，即使实际只有1.2万行符合条件——统计信息不准确时，这种误判会更严重。

4.2 统计信息失效与Cardinality的坑

InnoDB的统计信息由innodb_stats_persistent控制，默认持久化到磁盘，定期自动更新。但自动更新的触发依赖表数据变化量达到一定比例，在数据量快速变化或者varchar字段值分布严重不均时，Cardinality可能长期停留在旧值。

排查时可以这样验证：

SHOW INDEX FROM pay_order;

重点看idx_create_time这一行的Cardinality值，它表示索引中不同值的估算数量。如果这个值明显小于表中实际的不同值数量，统计信息可能已经失真。此时执行：

ANALYZE TABLE pay_order;

强制更新统计信息，再看EXPLAIN是否变化。我在这个项目里执行后，rows估算从356万降到了30万级别，部分SQL的执行计划变得合理很多。

4.3 用optimizer_trace和EXPLAIN ANALYZE还原优化器的决策过程

有时候ANALYZE TABLE还不够，特别是当你想知道优化器在几个索引之间到底怎么锱铢必较的时候。MySQL 8.0提供了两个非常好用的工具。

一个是optimizer_trace，可以完整记录优化器在接收到SQL后做过的所有成本计算：

SET optimizer_trace='enabled=on'; -- 这里执行你的慢SQL SELECT * FROM information_schema.OPTIMIZER_TRACE;

结果里会给出table_scan的成本、potential_range_indexes有哪些、每个索引的rows_estimation、最终选择哪个索引以及原因。你可以清楚看到优化器对idx_create_time的range扫描成本估算，和对全表扫描成本估算的差值。

另一个是EXPLAIN ANALYZE，直接输出实际执行耗时和真实扫描行数：

EXPLAIN ANALYZE SELECT id, order_no, pay_amount FROM pay_order WHERE create_time >= '2024-01-15 00:00:00' AND create_time < '2024-01-16 00:00:00' ORDER BY user_id LIMIT 100;

它会显示actual time和actual rows，方便和EXPLAIN的估算值做对比。如果发现估算值和实际值偏差很大，优先考虑ANALYZE TABLE；如果偏差不大但查询还是慢，那说明问题不在选索引，而在执行计划本身要做大量回表或排序。

4.4 FORCE INDEX只能用来验证假设，不能当长期方案

排查和临时止血时，很多人会直接FORCE INDEX。我自己在第一次处理时也试了：

SELECT id, order_no, pay_amount FROM pay_order FORCE INDEX(idx_create_time) WHERE create_time >= '2024-01-15 00:00:00' AND create_time < '2024-01-16 00:00:00' ORDER BY user_id LIMIT 100;

结果有意思：这条SQL在某些参数下确实变快了，但换成另一个时间范围（比如跨三个月）反而比全表扫描还慢。原因是FORCE INDEX会强制优化器先走索引，当范围很大、回表次数很多时，随机IO成本远超全表顺序扫描。所以FORCE INDEX适合用来验证“优化器是不是选错了”，不适合作为长期配置。真正要做的，是让数据模型本身支持更高效的执行路径，也就是下一章要聊的改造方案。

5. 彻底修掉这个隐患：三种改造方案与选择

5.1 方案一：直接改成datetime类型

既然varchar存时间这么多坑，最本质的办法就是改表结构，把create_time改成datetime。这是根治方案，也是我最终选择的方向。

改造步骤要注意顺序，不能上来直接ALTER，因为字符串格式的脏数据没法被MySQL自动转换成合法的datetime值。我按下面的流程操作：

-- 1. 新加一个datetime列 ALTER TABLE pay_order ADD COLUMN create_time_dt datetime NULL AFTER create_time; -- 2. 分批回填数据，先看有多少脏数据 SELECT COUNT(*) FROM pay_order WHERE STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s') IS NULL; -- 3. 脏数据处理好之后，统一回填 UPDATE pay_order SET create_time_dt = STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s') WHERE create_time_dt IS NULL; -- 4. 确认无误后，切换列并重建索引 ALTER TABLE pay_order DROP COLUMN create_time, CHANGE COLUMN create_time_dt create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX idx_create_time (create_time);

有几个细节需要重点提醒。

第一，千万级别以上的表不要用一条UPDATE全量回填，会长时间锁住大量行，影响线上写入。要分批次执行，比如每次只处理1万行，循环跑。更稳妥的做法是使用pt-osc或gh-ost这类在线改表工具，它们会在迁移过程中同步增量数据，把对业务的影响降到最低。

第二，脏数据必须提前暴露。第2步查出来STR_TO_DATE返回NULL的记录，要交给业务确认来源，该修的修、该补的补。如果直接忽略，转换后这些行会变成NULL，线上查询结果直接丢失。

第三，应用层的INSERT代码也需要跟着改，不能再传字符串了。MyBatis里把create_time的类型映射改为Date或LocalDateTime，很多老代码的入参是String，改动面会比想象中大。

5.2 方案二：MySQL 8.0函数索引

如果表结构短期不能动，但MySQL已经升级到了8.0，可以用函数索引来缓解。比如针对create_time字符串，创建一个对STR_TO_DATE结果建索引的表达式索引：

ALTER TABLE pay_order ADD INDEX idx_create_time_func ((STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s')));

查询时，where条件里必须写一模一样的表达式，优化器才能命中这个索引：

SELECT id, order_no, pay_amount FROM pay_order WHERE STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s') >= '2024-01-15 00:00:00' AND STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s') < '2024-01-16 00:00:00';

函数索引的坑有两个。一是表达式必须逐字符完全一致，哪怕格式化字符串从'%Y-%m-%d %H:%i:%s'改成'%Y-%m-%d %H:%i'，都会导致索引不可用。二是它解决不了排序和分组上的索引利用问题，GROUP BY表达式日期时还是要临时表。它更像是给存量系统穿的保护衣，只能挡一部分查询压力。

5.3 方案三：冗余标准datetime列，双写过渡

有些系统下游消费方太多，直接改类型风险很大。这时可以走冗余列方案：

• 保留create_time varchar列，继续兼容老接口和第三方；
• 新增create_time_std datetime列，代码里写入时同时维护两列；
• 历史数据用一次性脚本回填；
• 新开发的所有查询都走create_time_std，老查询逐个迁移，迁移完成后择机下线varchar列。

这个方案的优点是对老业务基本无感，可以分阶段推进；缺点是冗余带来的存储成本和双写逻辑，以及两列可能在某些代码路径下不一致的风险。需要加一个定时校验任务，定期抽查两列值是否一致。

5.4 三种方案对比与我的最终选择建议

如果让我对遇到类似问题的读者给一个优先级建议：能选方案一就选方案一，varchar改datetime才是从底层消除这次故障根源的做法。方案二适合作为过渡手段，方案三适合企业级核心表的大规模改造。我在这个项目里最终选了方案一，因为pay_order虽然重要，但下游消费方不多，应用层在老代码里也集中，两周内就能完成改造和灰度。

改完后，同样的对账SQL执行计划变成了type=range，key=idx_create_time，回表行数1.2万，接口耗时稳定在150毫秒以内，排序、分组查询也都可以直接在时间索引上做，彻底消除了隐式转换和字符串格式带来的不确定性。

6. 复盘后的预防清单与日常排查建议

6.1 建表规范和Code Review红线

这次事故对我团队最大的产出，是一条写进开发规范的红线：业务表中的时间字段，只允许使用datetime或timestamp，禁止使用varchar、char存时间；如果确实需要存原始时间字符串，必须同时具备规范化处理逻辑，并且不能作为查询条件。

这条红线不仅是为了索引，更是为了数据正确性。datetime类型自带范围校验，非法值会被数据库拒绝，而varchar可以塞进“20240230”这种根本不存在的时间，脏数据源头就堵不住了。

Code Review阶段，我会重点关注三点：

• WHERE和JOIN条件涉及时间字段时，检查参数绑定类型是否为字符串，警惕隐式类型转换；
• 时间字段上出现函数包裹，比如WHERE DATE(create_time) = ...，提醒改写为范围查询或函数索引；
• 新表设计出现varchar类型的时间字段，直接打回。

6.2 慢查询监控与执行计划定期巡检

线上慢查询日志建议把long_query_time设置为1秒甚至是0.5秒，并接入监控告警。重点不是看哪个SQL慢，而是看Rows_examined与Rows_sent的比值。像这篇案例里，查100行翻了356万行，比值超过三万倍，属于典型的扫描量严重超标。

此外，每年或者每半年可以对核心SQL做一次执行计划巡检。哪怕有些SQL当前不慢，也要看它的EXPLAIN有没有type=ALL、Extra有没有Using temporary或Using filesort。这些特征是潜在的性能隐患，数据量翻倍后就会变成线上故障。

我自己的习惯是维护一个核心SQL清单，每次大版本变更或统计信息更新后，批量跑一遍EXPLAIN，把type从range退化到ALL的SQL捞出来提前处理。这个习惯救过我好几次。

6.3 同类坑的举一反三：varchar字段上的隐式转换不止时间

最后说一个从这次复盘延伸出来的经验。varchar导致隐式转换的坑，不只是时间字段。常见的高危场景还有：

• 手机号、身份证号等字段用varchar存储，查询时忘了加引号，写成phone=13800138000，MySQL会把phone列转成数值再比较，索引直接失效；
• 两个字符集不一致的varchar列做join，比如一个表是utf8mb4，另一个表是utf8mb3，MySQL需要对列做字符集转换，关联条件上的索引无法直接用；
• 字符串列和数值列比较，不管初始写的是参数还是常量，只要类型不一致，就会触发列上的CAST。

排查这些问题的思路是完全一致的：先看EXPLAIN的possible_keys和key为什么不一致，再看SQL写法里有没有类型转换，不要一上来就加索引或改参数。大多数时候，优化器不蠢，它只是在按你给它的类型、数据和统计信息做最合理的估算。

这次把varchar时间字段的问题彻底处理后，我把那个对账SQL的执行计划截图放进了团队的故障复盘文档里，作为“类型即语义”的典型教材。MySQL的索引优化，很多时候不是在调优，而是在纠正早期数据结构设计埋下的债。如果你的表里也有varchar时间字段，不用等慢查询日志来提醒，现在就去看看数据格式和核心SQL的执行计划，多半会有惊奇的发现。