MySQL 索引失效实测：VARCHAR 列用数字比较全扫 50 万行，JOIN 两表字符集不一致从 3ms 变成 1.9 秒
在 MySQL 8.0.46 测试库（users 50 万行、orders 100 万行自造数据）上复现三类常见索引失效：VARCHAR 手机号用数字比较、JOIN 关联列一边 utf8mb4 一边 utf8mb3、DATE() 包住索引列。用 EXPLAIN ANALYZE 记录耗时和执行计划，用 SHOW WARNINGS 看 Warning 1739，并给出改字符集（INPLACE 会报 ERROR 1846）、改写条件和函数索引三种修法的实测。附只读 information_schema / performance_schema 排查 SQL 和可复跑脚本。
MySQL 8.0.46 实测（测试库、自造数据）：phone 是 VARCHAR，WHERE phone = 13800123456 不加引号，执行计划变成全索引扫描 50 万行，78–95ms，SHOW WARNINGS 给出 Warning 1739；加上引号是 0.02ms。users（utf8mb4）和老表 orders（utf8mb3）按 user_code 关联，orders 一侧的索引用不上，优化器只好先扫 orders 100 万行，1.8–2.0 秒；把 orders.user_code 改成 utf8mb4（只能 COPY，100 万行 4.8 秒）后同一条 JOIN 2–3ms。DATE(created_at) = 某天 69ms，改成半开区间 0.42–0.45ms。
适用对象
- VARCHAR 列和数字比较时，MySQL 把两边都转成浮点数比较，索引只能整棵扫：本次 50 万行 78–95ms，加引号 0.02ms。反过来 INT 列和字符串比较不受影响。
- JOIN 关联列字符集不一致时，utf8mb3 一侧会被转换成 utf8mb4，这一侧的索引不能用于 ref 访问；本次优化器因此改成先扫 orders 100 万行，1.8–2.0 秒，改成同一字符集后 2–3ms。
- 改列字符集在 8.0.46 上 ALGORITHM=INPLACE 直接报 ERROR 1846，只能 COPY 重建表（100 万行 4.8 秒），生产上要按锁表变更处理。
- 排查时 EXPLAIN 后紧跟 SHOW WARNINGS，看到 Warning 1739 就是类型或排序规则转换导致索引用不上；performance_schema 里这类语句的 SUM_NO_INDEX_USED 仍是 0，别只按它筛。
VARCHAR 手机号不加引号：为什么索引还在用，却慢了 4000 多倍？
EXPLAIN 显示 type=index、key=idx_phone、rows=498600、Extra 是 Using where; Using index。乍一看「用了索引」，其实是把 idx_phone 从头到尾扫一遍（Covering index scan），再逐行做 phone = 13800123456 的过滤。EXPLAIN ANALYZE 里那一层 actual rows=500000，最后过滤剩 1 行。
MySQL 文档里的规则是：字符串和数字比较时，两边都按浮点数比较。索引里存的是字符串的排序，换成数字之后顺序就对不上了（'2' 在 '13800…' 后面，数字上却小得多），所以只能整棵扫，不能按值定位。
我还顺手测了这条规则的另一面：SELECT '13800123456abc' = 13800123456 返回 1，同时给 Warning 1292 Truncated incorrect DOUBLE value；' 13800123456'（前面带空格）= 13800123456 也返回 1。也就是说不加引号不只是慢，还可能匹配到你没想到的值。
本节判断
- 反过来不受影响：id 是 INT，WHERE id = '123456' 仍是主键常量查找，因为被转换的是右边的常量，不是列。
- 在代码里，这类问题多半来自 ORM 或拼 SQL 时把手机号、订单号当成整数传参；数据库这边只能看到 Warning 1739，排查要从 SQL 原文入手。
| 写法 | 执行计划 | 读取行数 | 第 1 轮 | 第 2 轮 | 脚本复跑 |
|---|---|---|---|---|---|
| phone = 13800123456 | Covering index scan + Filter | 500000 | 95.2 ms | 78.2 ms | 81.3 ms |
| phone = '13800123456' | Covering index lookup | 1 | 0.023 ms | 0.016 ms | 0.018 ms |
| id = '123456'（INT 列用字符串） | 主键常量（Rows fetched before execution） | 1 | 0.0003 ms | 0.0001 ms | — |
JOIN 关联列一边 utf8mb4 一边 utf8mb3：同一条 SQL 从 3ms 变成 1.9 秒
查询是 SELECT COUNT(*) FROM users u JOIN orders o ON o.user_code = u.user_code WHERE u.id BETWEEN 1000 AND 1999。正常的走法是：先按主键取 users 1000 行，再用每个 user_code 去 orders.idx_user_code 里查。两边字符集一致时就是这样，2–3ms。
字符集不一致时，比较要把 orders.user_code（utf8mb3）转换成 utf8mb4 再比，orders 这一侧的索引不能用于按值查找（SHOW WARNINGS：Warning 1739 Cannot use ref access on index 'idx_user_code' … on field 'user_code'）。优化器的选择是把顺序倒过来：先把 orders 的 idx_user_code 整棵扫一遍（100 万行），每一行再去 users.idx_user_code 里查，再用 u.id BETWEEN 过滤。EXPLAIN ANALYZE 里内层 loops=1e+6，两轮 1979ms / 1839ms，脚本复跑 1807ms。
反过来写成从 orders 出发（WHERE o.id BETWEEN 1000 AND 1999），同样字符集不一致，却只要 3.0ms / 3.09ms：被转换的是 orders.user_code 这个值，拿转换后的值去 users（utf8mb4）索引里查完全没问题。所以同一对表，有的查询慢、有的不慢，很容易让人以为是数据量或缓存问题。
本节判断
- 只读排查 SQL 第 1 段会列出同名列在不同表里的字符集/排序规则差异；本次输出：user_code 有 2 种，orders:utf8mb3/utf8mb3_general_ci | users:utf8mb4/utf8mb4_0900_ai_ci。
- 关联列名字不一样时第 1 段查不到，需要按你们的 JOIN 关系人工核对；第 2 段同理找出同名列一边字符串一边数字的情况（本次找到 sms_log.phone 是 bigint、users.phone 是 varchar(20)）。
| 写法 / 状态 | 执行计划 | 第 1 轮 | 第 2 轮 | 脚本复跑 |
|---|---|---|---|---|
| 按 u.id 过滤，字符集不一致 | Covering index scan on o（100 万行）→ 每行查 u | 1979 ms | 1839 ms | 1807 ms |
| 按 o.id 过滤，字符集不一致 | Index range scan on o（1000 行）→ 查 u | 3.0 ms | 3.09 ms | — |
| 按 u.id 过滤，orders.user_code 改成 utf8mb4 后 | Index range scan on u（1000 行）→ 查 o | 2.92 ms | 2.19 ms | 3.2 ms |
修字符集时我踩的坑：INPLACE 报 ERROR 1846，只能 COPY 重建
我第一次修的时候想走在线变更，写的是 ALTER TABLE orders MODIFY user_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, ALGORITHM=INPLACE, LOCK=NONE，结果直接报 ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY。
去掉 ALGORITHM 子句重跑，走的是 COPY：100 万行的 orders 重建用了约 4.8 秒（脚本复跑 4.75 秒）。COPY 期间表不允许写入，本机 4.8 秒，生产表大几十倍就是分钟到小时级，要么放维护窗口，要么用 gh-ost / pt-online-schema-change 这类工具（经验做法，本次未实测）。
改完 ANALYZE TABLE 后，同一条 JOIN 的计划回到「先 users 1000 行、再查 orders」，2.92ms / 2.19ms，Warning 1739 消失。
本节判断
- 改之前先用第 1 段 SQL 把所有不一致的列列出来，一次改完，不要改了一个又在另一个 JOIN 上踩到。
- 只改应用里的 SQL（例如 ON o.user_code = CONVERT(u.user_code USING utf8mb3)）在本例能让 orders 的索引可用，但 utf8mb4 里 4 字节字符转不过去会出问题，这是经验判断、本次未实测，我更倾向把表改一致。
DATE(created_at) = '2026-03-01'：改成半开区间 0.42ms，函数索引 3.5ms
WHERE DATE(created_at) = '2026-03-01'，执行计划里条件变成 cast(users.created_at as date) = '2026-03-01'，idx_created 整棵扫 50 万行，三次 68.4 / 69.4 / 70.7ms，命中 1786 行。
改写成 created_at >= '2026-03-01' AND created_at < '2026-03-02'，变成索引范围扫描，只读 1786 行，0.449 / 0.419 / 0.47ms。
MySQL 8.0.13 起支持函数索引，我也试了 ALTER TABLE users ADD INDEX idx_created_date ((DATE(created_at)))，50 万行建索引 645ms。建好后原 SQL 不改就能走 Index lookup on users using idx_created_date，3.69 / 3.48ms。比全扫快很多，但比半开区间慢约 8 倍：半开区间走的是覆盖索引（计划里写着 Covering），函数索引这条计划里没有 Covering，我的理解是要多读一层数据，这一点本次没有单独验证。
本节判断
- 能改 SQL 就改成半开区间（>= 当天 0 点、< 次日 0 点），不要用 BETWEEN '… 00:00:00' AND '… 23:59:59'，后者在有小数秒时会漏数据（经验判断）。
- SQL 由第三方系统生成、改不了时，函数索引是退路，但每个写入都要多维护一个索引。
| 写法 | 执行计划 | 耗时 |
|---|---|---|
| DATE(created_at) = '2026-03-01' | Covering index scan（50 万行）+ Filter | 68.4 / 69.4 / 70.7 ms |
| created_at >= '2026-03-01' AND < '2026-03-02' | Covering index range scan | 0.449 / 0.419 / 0.47 ms |
| 原写法 + 函数索引 ((DATE(created_at))) | Index lookup（非覆盖） | 3.69 / 3.48 ms |
怎么在自己的库里找出这类问题：先查结构，再看语句摘要
只读 SQL 一共 4 段，只查 information_schema 和 performance_schema。前 3 段查结构：同名列字符集不一致、同名列一边字符串一边数字、表排序规则和库默认不一致。第 4 段按语句摘要找「扫描行数远大于返回行数」的 SELECT。
第 4 段这里我踩了第二个坑。一开始我想按 SUM_NO_INDEX_USED > 0 筛，结果这几条慢语句的 no_index_calls 全是 0：隐式转换后走的是全索引扫描，MySQL 认为「用了索引」。改成按 avg_rows_examined / avg_rows_sent 排序后，测试库里排在前两位的正是 JOIN（平均扫描 200 万行、返回 1 行、1441ms）和 phone 不加引号（扫描 50 万行、返回 1 行、57ms）。
拿到可疑语句后，在测试环境或从库上 EXPLAIN，紧跟 SHOW WARNINGS；出现 1739 就能确定是本文这类问题。
我处理这类慢 SQL 的四步
- 01
1
跑只读 SQL 第 4 段
找扫描行数 / 返回行数比值特别大的 SELECT，别只看是否用了索引。
- 02
2
EXPLAIN + SHOW WARNINGS
type=index 且 rows 接近全表、Warning 1739，就是类型或排序规则转换。
- 03
3
对照结构差异
用第 1、2 段确认是哪两列类型或字符集不一致，列出所有受影响的 JOIN。
- 04
4
选修法
改 SQL（加引号、半开区间）最便宜；改表字符集要 COPY 重建，排维护窗口；改不了 SQL 再考虑函数索引。
觉得有用？转给正在做这件事的同事或朋友
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
MySQL 字段是 varchar，查询条件不加引号为什么索引失效？
字符串和数字比较时 MySQL 按浮点数比较，索引里的字符串顺序用不上，只能整棵扫。本次 MySQL 8.0.46、50 万行测试表上不加引号 78–95ms，加引号 0.02ms。
EXPLAIN 里 key 有值、type 是 index，是不是就算走了索引？
type=index 是把整棵索引扫一遍，rows 接近全表时和全表扫描差不多。本次 phone 不加引号就是 type=index、rows=498600。要看 type 是否为 ref / range / const。
两张表 JOIN 字符集不一致一定会慢吗？
不一定，看从哪张表出发。本次从 utf8mb4 的 users 出发要查 utf8mb3 的 orders 时 1.8–2.0 秒；从 orders 出发查 users 只要 3ms。
把 utf8mb3 的列改成 utf8mb4 能在线做吗？
在 MySQL 8.0.46 上改列字符集用 ALGORITHM=INPLACE 会报 ERROR 1846，只能 COPY 重建表，本次 100 万行约 4.8 秒，期间不能写入。大表要排维护窗口或用在线变更工具。
这些数字能直接套到我们的生产库吗？
不能。这是本机测试库、自造数据、数据全在内存里，只说明量级和相对关系。可以用文中的只读 SQL 和演示脚本在你们的测试环境复测。
下一步
推荐动作
你提供这份只读 SQL 的输出（可脱敏）和可疑语句的 EXPLAIN + SHOW WARNINGS，我们判断是类型、字符集还是函数包列导致索引失效，给出改 SQL / 改表的顺序和变更窗口建议；生产变更由你方授权执行。