一个隐式类型转换，让 2000 万行的表全表扫了 8 秒：慢查询优化的 4 个真凶

title: 一个隐式类型转换让 2000 万行的表全表扫了 8 秒慢查询优化的 4 个真凶date: 2026-09-09tags: [慢查询, MySQL, 索引, 覆盖索引, 执行计划]一、引子列表页偶发 8 秒卡顿用户反馈「订单列表页偶尔转圈 8 秒」。慢查询日志long_query_time1s抓到一条 SQLSELECT * FROM order WHERE phone 13800138000 LIMIT 20;order表 2000 万行phone字段上明明建了索引但这条却走了全表扫描EXPLAIN 里 typeALL单次 8 秒压测时直接把 DB CPU 打满。真凶只有一句phone 字段是varchar但 SQL 里传的是数字13800138000没加引号。MySQL 为了比较会对phone字段做隐式类型转换CAST(phone AS UNSIGNED)函数套在字段上索引立刻失效。改成一个引号——WHERE phone 13800138000——走索引后 12ms快了 660 倍。二、真凶一隐式类型转换字段被函数包裹MyBatis 里这个坑特别常见参数类型是Long生成的 SQL 里数字就没引号。// 错误示范参数用 Long生成的 SQL 里 phone 是数字触发隐式转换 Select(SELECT * FROM order WHERE phone #{phone} LIMIT 20) ListOrder listByPhoneWrong(Param(phone) Long phone); // 第 1 行Long 类型 // 正确示范参数用 StringSQL 里带引号索引生效 Select(SELECT * FROM order WHERE phone #{phone} LIMIT 20) ListOrder listByPhoneRight(Param(phone) String phone); // 第 2 行String 类型生成 13800138000逐行解释第 1 行用Long类型接 phoneMyBatis 拼出来的 SQL 是phone 13800138000MySQL 对 varchar 字段做CAST后索引失效第 2 行用String拼出来是phone 13800138000走phone索引的 ref 访问。这一处类型错配是生产环境慢查询最高频的来源没有之一。三、真凶二不看 EXPLAIN 就瞎加索引加索引不是越多越好得看执行计划。我习惯先用 EXPLAIN 看type和key// 用 MyBatis 跑 EXPLAIN注意EXPLAIN 是只读分析不真正执行查询 Select(EXPLAIN SELECT * FROM order WHERE phone #{phone} LIMIT 20) ListMapString, Object explainPhone(Param(phone) String phone); // 第 1 行返回结果里重点看 typeALL全表扫ref/range用了索引、key实际用到的索引、rows扫描行数逐行解释第 1 行执行 EXPLAIN返回的执行计划里typeALL且keynull就意味着全表扫描rows接近全表行数2000 万改成带引号的查询后typeref、keyidx_phone、rows1。我见过有人不看 EXPLAIN对着慢 SQL 一通加联合索引结果字段顺序不对、或还是隐式转换索引照样没用上白加了 3 个索引把写入拖慢。四、真凶三回表把「用上索引」的好处分吃掉即使走了索引如果SELECT *要的字段不在索引里MySQL 还要拿主键回表查聚簇索引取完整行数据量大时回表本身也很贵。// 用覆盖索引把查询需要的列都放进联合索引避免回表 // 建索引ALTER TABLE order ADD INDEX idx_phone_status_ctime (phone, status, create_time); Select(SELECT phone, status, create_time FROM order // 第 1 行只查索引里有的列 WHERE phone #{phone} AND status #{status} // 第 2 行命中联合索引前导列 ORDER BY create_time DESC LIMIT 20) // 第 3 行排序也走索引免 filesort ListOrderBrief listByPhoneCover(Param(phone) String phone, Param(status) int status);逐行解释第 1 行只查phone, status, create_time这三个列正好组成联合索引(phone, status, create_time)MySQL 在索引树里就能拿到全部所需数据不用回表EXPLAIN 的 Extra 出现Using index第 2 行命中联合索引前导列 phone第 3 行ORDER BY create_time因为 create_time 也在索引里且顺序一致排序直接走索引省掉filesort。我们那个列表页从SELECT *改成覆盖索引后即便在 2000 万行上P99 也从 8 秒降到 40ms。五、真凶四深分页 LIMIT 1000000 的代价另一个隐藏杀手是深分页。运营后台翻到第 5 万页时SELECT * FROM order WHERE user_id 123 ORDER BY id LIMIT 1000000, 20;MySQL 要先把前 100 万行读出来再丢掉只取最后 20 行越翻越慢。优化成「游标分页」// 游标分页用上一页最后一条的 id 做起点避免 OFFSET 扫描 Select(SELECT * FROM order WHERE user_id #{userId} AND id #{lastId} // 第 1 行id 取上一页最大值 ORDER BY id DESC LIMIT 20) // 第 2 行永远只扫 20 行 ListOrder listByCursor(Param(userId) long userId, Param(lastId) long lastId);逐行解释第 1 行用id #{lastId}替代OFFSET 1000000MySQL 直接从索引定位到 lastId 之前的位置第 2 行 LIMIT 20 永远只扫 20 行不管翻到第几页都稳定。运营后台改成游标分页后深翻页从 3 秒降到 30ms。六、四个真凶对照表真凶现象修复效果2000 万行表隐式类型转换typeALL参数改 String 加引号8s → 12ms不看 EXPLAIN乱加索引无效先看 type/key/rows避免无效索引回表Extra 无 Using index建覆盖索引P99 8s → 40ms深分页LIMIT 1000000,20游标分页3s → 30ms七、数字复盘这套优化上线后order表相关慢查询从日均 1.2 万条降到 0DB CPU 峰值从 98% 降到 35%列表页 P99 从 8 秒降到 40ms压测 QPS 从 120 提到 4000。最关键的不是「加了多少索引」而是「先 EXPLAIN 找到真凶再对症下手」。八、个人观点我不建议对着慢 SQL 凭直觉加索引——我见过太多「加了 5 个索引查询还是慢」的案例根因是隐式转换或回表。我更建议把 EXPLAIN 当成慢查询排查的第一步而不是最后一步先看type是不是 ALL、key是不是 null、rows是不是巨大这三列基本能定位 80% 的问题。覆盖索引是好东西但别为了覆盖把索引建得又宽又多写入和存储都有代价按真实查询列来。八、慢查询告警怎么配才不误报光优化还不够得让慢查询「自己跳出来」。我们用 MySQL 8.0.32 的performance_schema开了events_statements_summary_by_digest配合 Prometheus 的mysqld_exporter把slow_queries计数器拉出来配了一条increase(mysql_global_status_slow_queries[5m]) 10的告警。但这条太粗哪个 SQL 慢还得靠slow_query_log。我们最终的做法是两层第一层用long_query_time 1开慢查询日志只记超过 1 秒的避免日志爆炸第二层在应用侧用 MyBatis 拦截器统计每条 SQL 的执行时间超过 500ms 就打 WARN 并带上traceId这样慢 SQL 一出现就能从链路追踪里反查来源。两层的阈值不一样慢查询日志兜底「库级别真实慢」应用拦截器定位「是谁、在哪个请求里触发的。另一个误报来源是「偶发慢」被当成常态。我们给告警加了「5 分钟内出现 5 次以上才告警」的持续时间条件单次慢查询不吵人持续慢才升级。上线这套后慢查询从「用户投诉才发现」变成「告警 5 分钟内触达」那次隐式转换的 8 秒查询在上线当天就被告警抓到比下次大促提前两周修掉。九、一个联合索引顺序错的反例和影子库压测隐式转换不是索引失效的唯一原因联合索引的「列顺序」错配同样致命。我们另一个慢查询是WHERE status 1 AND user_id 123 ORDER BY create_time索引建成了(user_id, create_time, status)。理论上 user_id 是前导列、能命中但实际EXPLAIN显示typeref但rows仍有 80 万——因为 status 不在前导、无法用索引过滤只能先按 user_id 定位再回表筛 status等于白建了后半截。正确建法是把区分度高的、且常出现在等值条件的列放前导(status, user_id, create_time)。改完rows从 80 万降到 200typeref且 Extra 出现Using indexP99 从 1.2 秒降到 25ms。这条经验我们沉淀成规范联合索引前导列放「等值条件 高区分度」的列范围条件和排序列放后面。上线前我们用影子库压测验证索引效果。做法是把线上 1% 的流量复制到影子库结构与线上一致、带同样索引在影子库上跑同样的查询观察EXPLAIN和执行耗时。那次隐式转换的修复就是先在影子库验证phone加引号后走索引、P99 12ms才推到生产。影子库压测的好处是不动生产数据就能拿到真实执行计划避免「我觉得加了索引就快」的盲目自信。我的一点补充索引不是越多越好。我们早期给order表加了 7 个索引写入时每个索引都要维护大促写入 QPS 一高索引维护反而成了瓶颈写入 RT 从 8ms 涨到 40ms。后来砍到 3 个高频查询索引写入 RT 回到 9ms查询也没变慢。索引是「用空间和时间换查询快」但维护成本要算进总账。十、思考题如果phone字段上建了索引但查询是WHERE SUBSTR(phone, 1, 3) 138索引还会生效吗这种「按前缀模糊查」的需求除了改 SQL还能从索引设计上怎么解决