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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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 timestamp1737000000; 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却是NULLtype是ALL意味着它最终选择了全表扫描然后在内存里做where过滤和filesort排序。“明明有索引为什么不用”这句话我在各种群里看过无数次真到自己排查时才知道浅层原因是隐式类型转换深层原因则跟数据格式、统计信息和成本评估都有关系。下面一步步拆。2. 第一层根因varchar时间字段与隐式类型转换2.1 隐式类型转换的触发条件和MySQL转换规则第二层根因从代码里找到。对账服务的Mapper接口里方法参数是java.util.Date类型XML里的SQL这样写select idlistForReconcile resultTypePayOrder 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列套了一层CASTCAST(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;得到的结果是typerangekeyidx_create_timerows只有一万多。再用显式CAST成DATETIME的方式模拟MyBatis的绑定EXPLAIN SELECT * FROM pay_order WHERE create_time CAST(2024-01-15 00:00:00 AS DATETIME);结果直接变成typeALLrows3567880。两组执行计划的差异非常明显问题基本锁定就是参数类型不匹配触发的隐式转换。两个执行计划的对比查询写法typekeyrowsExtra字符串常量直接比较rangeidx_create_time约1.2万Using index condition; Using filesortCAST成DATETIME后比较ALLNULL356万Using where; Using filesort2.3 修复写法后的执行计划对比修复方式其实很简单让绑定参数变成字符串。在MyBatis里可以给方法参数加Param注解后在XML中手动指定字符串类型更省事的做法是在Java代码里直接用DateTimeFormatter把Date格式化成“yyyy-MM-dd HH:mm:ss”字符串再传入。我当时的改法是这样select idlistForReconcile resultTypePayOrder SELECT id, order_no, pay_amount FROM pay_order WHERE create_time gt; #{beginTime, jdbcTypeVARCHAR} AND create_time lt; #{endTime, jdbcTypeVARCHAR} ORDER BY user_id LIMIT 100 /select发布后再看执行计划type从ALL变成了rangekey为idx_create_timerows从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_timetyperangerows从全表降到了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”放在一起比较create_time 存储值字典序比较结果实际时间顺序2024-01-05 09:05:03前第5位是0后2024-1-5 9:5:3后第5位是1前“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字符集下索引键字节数远大于datetime19字符最多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_traceenabledon; -- 这里执行你的慢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 三种方案对比与我的最终选择建议方案根治程度改造成本索引/排序效果适用场景改datetime根治中高涉及应用层改造最优时间和日期函数都能用索引系统处于可发布窗口数据量可控MySQL 8.0函数索引缓解低仅where等值/范围可用排序分组仍受限版本已升级、表结构暂时不能动的存量系统冗余datetime列根治中双写逻辑复杂最优但需保证双列一致下游多、无法一次性改类型的核心表如果让我对遇到类似问题的读者给一个优先级建议能选方案一就选方案一varchar改datetime才是从底层消除这次故障根源的做法。方案二适合作为过渡手段方案三适合企业级核心表的大规模改造。我在这个项目里最终选了方案一因为pay_order虽然重要但下游消费方不多应用层在老代码里也集中两周内就能完成改造和灰度。改完后同样的对账SQL执行计划变成了typerangekeyidx_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有没有typeALL、Extra有没有Using temporary或Using filesort。这些特征是潜在的性能隐患数据量翻倍后就会变成线上故障。我自己的习惯是维护一个核心SQL清单每次大版本变更或统计信息更新后批量跑一遍EXPLAIN把type从range退化到ALL的SQL捞出来提前处理。这个习惯救过我好几次。6.3 同类坑的举一反三varchar字段上的隐式转换不止时间最后说一个从这次复盘延伸出来的经验。varchar导致隐式转换的坑不只是时间字段。常见的高危场景还有手机号、身份证号等字段用varchar存储查询时忘了加引号写成phone13800138000MySQL会把phone列转成数值再比较索引直接失效两个字符集不一致的varchar列做join比如一个表是utf8mb4另一个表是utf8mb3MySQL需要对列做字符集转换关联条件上的索引无法直接用字符串列和数值列比较不管初始写的是参数还是常量只要类型不一致就会触发列上的CAST。排查这些问题的思路是完全一致的先看EXPLAIN的possible_keys和key为什么不一致再看SQL写法里有没有类型转换不要一上来就加索引或改参数。大多数时候优化器不蠢它只是在按你给它的类型、数据和统计信息做最合理的估算。这次把varchar时间字段的问题彻底处理后我把那个对账SQL的执行计划截图放进了团队的故障复盘文档里作为“类型即语义”的典型教材。MySQL的索引优化很多时候不是在调优而是在纠正早期数据结构设计埋下的债。如果你的表里也有varchar时间字段不用等慢查询日志来提醒现在就去看看数据格式和核心SQL的执行计划多半会有惊奇的发现。 
长河君 · 2026/9/10 7:13:16 · 阅读 1.2k 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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 timestamp1737000000; 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却是NULLtype是ALL意味着它最终选择了全表扫描然后在内存里做where过滤和filesort排序。“明明有索引为什么不用”这句话我在各种群里看过无数次真到自己排查时才知道浅层原因是隐式类型转换深层原因则跟数据格式、统计信息和成本评估都有关系。下面一步步拆。2. 第一层根因varchar时间字段与隐式类型转换2.1 隐式类型转换的触发条件和MySQL转换规则第二层根因从代码里找到。对账服务的Mapper接口里方法参数是java.util.Date类型XML里的SQL这样写select idlistForReconcile resultTypePayOrder 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列套了一层CASTCAST(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;得到的结果是typerangekeyidx_create_timerows只有一万多。再用显式CAST成DATETIME的方式模拟MyBatis的绑定EXPLAIN SELECT * FROM pay_order WHERE create_time CAST(2024-01-15 00:00:00 AS DATETIME);结果直接变成typeALLrows3567880。两组执行计划的差异非常明显问题基本锁定就是参数类型不匹配触发的隐式转换。两个执行计划的对比查询写法typekeyrowsExtra字符串常量直接比较rangeidx_create_time约1.2万Using index condition; Using filesortCAST成DATETIME后比较ALLNULL356万Using where; Using filesort2.3 修复写法后的执行计划对比修复方式其实很简单让绑定参数变成字符串。在MyBatis里可以给方法参数加Param注解后在XML中手动指定字符串类型更省事的做法是在Java代码里直接用DateTimeFormatter把Date格式化成“yyyy-MM-dd HH:mm:ss”字符串再传入。我当时的改法是这样select idlistForReconcile resultTypePayOrder SELECT id, order_no, pay_amount FROM pay_order WHERE create_time gt; #{beginTime, jdbcTypeVARCHAR} AND create_time lt; #{endTime, jdbcTypeVARCHAR} ORDER BY user_id LIMIT 100 /select发布后再看执行计划type从ALL变成了rangekey为idx_create_timerows从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_timetyperangerows从全表降到了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”放在一起比较create_time 存储值字典序比较结果实际时间顺序2024-01-05 09:05:03前第5位是0后2024-1-5 9:5:3后第5位是1前“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字符集下索引键字节数远大于datetime19字符最多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_traceenabledon; -- 这里执行你的慢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 三种方案对比与我的最终选择建议方案根治程度改造成本索引/排序效果适用场景改datetime根治中高涉及应用层改造最优时间和日期函数都能用索引系统处于可发布窗口数据量可控MySQL 8.0函数索引缓解低仅where等值/范围可用排序分组仍受限版本已升级、表结构暂时不能动的存量系统冗余datetime列根治中双写逻辑复杂最优但需保证双列一致下游多、无法一次性改类型的核心表如果让我对遇到类似问题的读者给一个优先级建议能选方案一就选方案一varchar改datetime才是从底层消除这次故障根源的做法。方案二适合作为过渡手段方案三适合企业级核心表的大规模改造。我在这个项目里最终选了方案一因为pay_order虽然重要但下游消费方不多应用层在老代码里也集中两周内就能完成改造和灰度。改完后同样的对账SQL执行计划变成了typerangekeyidx_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有没有typeALL、Extra有没有Using temporary或Using filesort。这些特征是潜在的性能隐患数据量翻倍后就会变成线上故障。我自己的习惯是维护一个核心SQL清单每次大版本变更或统计信息更新后批量跑一遍EXPLAIN把type从range退化到ALL的SQL捞出来提前处理。这个习惯救过我好几次。6.3 同类坑的举一反三varchar字段上的隐式转换不止时间最后说一个从这次复盘延伸出来的经验。varchar导致隐式转换的坑不只是时间字段。常见的高危场景还有手机号、身份证号等字段用varchar存储查询时忘了加引号写成phone13800138000MySQL会把phone列转成数值再比较索引直接失效两个字符集不一致的varchar列做join比如一个表是utf8mb4另一个表是utf8mb3MySQL需要对列做字符集转换关联条件上的索引无法直接用字符串列和数值列比较不管初始写的是参数还是常量只要类型不一致就会触发列上的CAST。排查这些问题的思路是完全一致的先看EXPLAIN的possible_keys和key为什么不一致再看SQL写法里有没有类型转换不要一上来就加索引或改参数。大多数时候优化器不蠢它只是在按你给它的类型、数据和统计信息做最合理的估算。这次把varchar时间字段的问题彻底处理后我把那个对账SQL的执行计划截图放进了团队的故障复盘文档里作为“类型即语义”的典型教材。MySQL的索引优化很多时候不是在调优而是在纠正早期数据结构设计埋下的债。如果你的表里也有varchar时间字段不用等慢查询日志来提醒现在就去看看数据格式和核心SQL的执行计划多半会有惊奇的发现。