隐式转换导致JOIN索引失效的铁证是EXPLAIN中type=ALL且key=NULL;根治需统一字段物理类型、字符集与COLLATE,并校验脏数据及应用层传参。

直接改字段类型,别在ON里加CAST或CONVERT——否则索引照样失效。
怎么看是不是隐式转换惹的祸
执行 EXPLAIN 后重点关注两列:type 和 key。如果某张表的 type=ALL 且 key=NULL,基本可以断定是字段类型不一致触发了隐式转换。MySQL不会报错,只会静默放弃索引;PostgreSQL则大概率直接报 operator does not exist: integer = text。另外,用 SHOW FULL COLUMNS FROM table_name LIKE 'field_name' 对比两边的 Type、Collation、Character Set,三者必须完全一致。
为什么ON里写CAST(user_id AS SIGNED)还是慢
- CAST作用于整列,优化器无法下推索引查找,执行计划里会显示
Filter: CAST(o.user_id AS SIGNED) = u.id,说明是在逐行过滤阶段才转换,不是索引定位阶段 - MySQL中
CAST('123 ' AS SIGNED)会截掉尾部空格,但CAST('abc' AS SIGNED)返回0,导致错关联 - PostgreSQL里
o.user_id::BIGINT同样无法走索引,因为函数施加在索引列上,B+树结构就失效了 - SQL Server 的
CAST(customers.id AS INT)也会让customers.id上的索引失效
真正该做的三件事
根治不是拼SQL,而是从物理层对齐:
- 统一类型:MySQL用
ALTER TABLE orders MODIFY user_id BIGINT;PostgreSQL用ALTER TABLE orders ALTER COLUMN user_id TYPE BIGINT USING user_id::BIGINT(注意遇非法值会中断) - 先扫脏数据:MySQL跑
SELECT COUNT(*) FROM orders WHERE user_id REGEXP '[^0-9]';PostgreSQL用SELECT COUNT(*) FROM orders WHERE user_id !~ '^[0-9]+$' - 校验应用层传参:Java用
SqlParameter("@id", SqlDbType.BigInt),Python用cursor.execute("SELECT * FROM t WHERE id = %s", (123,)),绝不能传字符串"123"给INT字段
字符集和COLLATE不一致也得一起修
哪怕两边都是 VARCHAR(32),只要 Collation 不同(比如 utf8mb4_0900_as_cs vs utf8mb4_unicode_ci),MySQL仍会悄悄做 CONVERT(USING utf8mb4),导致索引跳过。修复必须同时指定字符集和排序规则:ALTER TABLE t2 MODIFY user_id VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs。只改 COLLATE 不改 CHARACTER SET,或者反过来,都没用。

















