JOIN条件中字段类型不一致，导致索引失效的案例一则

最新推荐文章于 2023-12-20 10:00:00 发布

原创最新推荐文章于 2023-12-20 10:00:00 发布 · 2.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #table

MS SQL Server 专栏收录该内容

20 篇文章

订阅专栏

本文探讨了一种T-SQL查询中出现的奇怪现象：当使用TOP 5进行查询时，性能显著下降。通过分析执行计划，发现数据类型不匹配导致了性能瓶颈，并给出了相应的解决方案。

T-SQL如下：

select T1.sno from [MAY_BLACKFISH].dbo.MVS_PARTS_LOG T1 join (
select top 5 sno from Z_result order by sno
) T2 on T1.sno=T2.sno
where T1.TypeName='LCM'

其中MVS_PARTS_LOG为KW级TABLE， Z_result 只有几百笔记录；相关索引都已经建立。

调试的时候发现一点很奇怪，当把top 5 改成top 4，则一切OK， 1秒内完成查询；恢复成top 5时则几十秒还没出结果。

比对两者执行计划，一致的。

细看执行计划，发现有一个convert(char(12),...的转换，原来这里JOIN条件中T1.sno是char(12), 而T2.sno是nvarchar(12)， MSSQL强制加了一个convert转换，导致on条件不能满足SARG的形式。

将Z_result的sno也改成char(12)后，问题解决。

关于索引，以及SARG的形式可以参考http://blog.youkuaiyun.com/lewisshi/archive/2009/01/13/3765153.aspx

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

cdwolfling

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

Spark-SQL 之 join 类型

zhixingheyi_tian的博客

05-31

951

Shuffle Hash Join 启用 Shuffle Hash Join 必须满足以下几个条件：仅支持等值 Join，不要求参与 Join 的 Keys 可排序； spark.sql.join.preferSortMergeJoin 参数必须设置为 false，参数是从 Spark 2.0.0 版本引入的，默认值为 true，也就是默认情况下选择 Sort Merge Join；小表的大小（plan.stats.sizeInBytes）必须小于 spark.sql.autoBroadcastJoi

mysql left join关联字段的类型不一致导致索引失效

CaptainCats的博客

09-29

2361

t1和t2通过order_id关联，order_id在t2建有索引order_detail_index_order_id，原因是order_id字段在t1中是int类型，在t2中是varchar类型，导致用不到索引。我们用另外一种办法，在查询中通过函数将它们转换为一样的类型和字符集。不过修改字段类型有时候会可能遇到一些阻碍。一种办法是修改字段类型让它们保持一致，看下这条sql的explain。但是这里却没有用到。

参与评论您还未登录，请先登录后发表或查看评论

Join时数据类型不一致导致的倾斜

小道的博客

07-02

2791

本篇以hive sql解析器来讨论问题，spark sql 的处理方法类似，大家可自行测试。在进行join操作时，有mapjoin和hashjoin两个大类。mapjoin需要的是一个大表和一个小表进行join，小表存于内存中，对大表进行遍历，不会产生数据倾斜。如果是大表join大表，在内存中放不下，便会对两张表join的字段求hash值，然后将hash值相同的数据放入同一个reduce或者同一个spark分区进行处理，这样join条件相同的内容就能放在一起处理了。若得到的某个hash值比例

MySQL-关联字段类型不一致导致隐式转换

Super菜的专栏

06-26

512

JOIN 关联字段类型一定要相同。

hive sql join 时字段类型不一致问题

weifengs 的博客

06-13

7639

在用hive sql 查询数据时会遇到两个表通过字段join，假如两个字段类型不一样，有可能出现莫名其妙的结果。解决方法：将2个字段类型转成一样。比如两个表通过id关联，一个是 string 一个是 int，可以将 string 转成int eg: (cast id as int) as id

【152】MySQL 执行 inner join 语句时，关联字段类型不一致结果错误的解决方法。

zhangchao19890805的专栏

12-30

2510

首先，我们创建三张表 CREATE TABLE `t_comment` ( `c_id` varchar(32) NOT NULL COMMENT '主键', `c_text` varchar(255) NOT NULL COMMENT '帖子内容', `c_creator_id` varchar(32) NOT NULL COMMENT '发帖人ID', PRIMARY KEY (`c_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMEN

MYSQL跨表批量更新及JOIN连表字段编码不一致问题

zh的博客

04-20

407

A表员工部门中间表 B表员工考勤表背景：员工考勤表部门id字段缺失，批量修复B表数据，填充部门id sql UPDATE Bb INNER JOIN A aON a.staff_id = CONVERT ( b.staff_id USING utf8 ) COLLATE utf8_unicode_ci SET a.dept_id = b.dept_id WHERE ...

mysql 两个不同类型的字段做join on，以及发生的两个不同编码的字段比较问题

FREEDOM

03-26

5369

mysql 中有两个表： tableA表：id字段类型为varchar tableB表：id字段为int 执行以下语句： select a.*,b.* from tableA a left tableBb on a.id=b.id 会发现查出来的数据错乱，没有按照a.id=b.id 进行拼接。解决办法有如下三种，即对字段类型进行转换后做on： select a.*,b...

MySQL关联left/right+join+on条件与where不同

精彩人生

10-13

1154

以下的文章主要讲述的是MySQL关联left join 条件on与where 条件的不同之处，我们现在有两个表，即商品表(products)与sales_detail(销售记录表)。我们主要是通过这两个表来对MySQL关联left join 条件on与where 条件的不同之处进

MYSQL JOIN ON 字段类型不同数据混乱解决方法

Ai卿的博客

04-02

2470

role.id字段是bigint类型 1.CAST(xxx AS 类型) 例子：role.id= CAST(bu.role_id AS DECIMAL(64)) 2.CONVERT(xxx,类型) 例子：role.id= CONVERT(bu.role_id,DECIMAL(64)) 可用的类型：　二进制,同带binary前缀的效果 : BIN...

sql不走索引的情况合集

qq_37706457的博客

11-06

719

1.两个表join查询，却不走连接条件的索引。当索引无问题，sql也无问题时，就可以去看看这两个表连接字段的字符集是否一致. 2.where条件,where a=b,请保持ab类型一致,虽然数据库可以自动数字和字符的转换,但是会隐式的去调用tochar函数,使用函数后会导致全表扫描,其他不一致类型同理.

企业实战总结：SQL Join执行的常见问题及解决方案

涤生大数据

12-20

1015

需要注意的是mapjoin括号中的表是小表侧，而且是不能当做可能会补null的一边，也就是left join的左表，right join的右表，full join的两端，否则就算是写了hint也也不起作用。数据倾斜也是join中的一类常见问题，例如要对在购物车的商品以商品id做关联，总是免不了出现部分热门商品在购物车的次数远远超出平均值的情况。这时会出现严重的长尾。这里tbl3的join key为k1，tbl2的join key是k1, k2，tbl3的join key更短，可以放在前面。

ORACLE MYSQL中join 字段类型不同索引失效的情况

cri5768的博客

12-29

397

关于JOIN使用不同类型的字段类型，数据库可能进行隐士转换，MYSQL ORACLE都是如此，下面使用一个列子来看看，脚本如下： mysql: drop table testjoin1; drop tabl...

修改达梦数据库表字段类型的注意事项

weixin_46423077的博客

02-27

6702

达梦数据库的表字段支持多媒体类型；达梦的多媒体类型包括TEXT、LONGVARCHAR、BLOB、CLOB、IMAGE、BFILE。在协助用户做数据迁移时，用户提出按照标准模板要求修改表的多媒体类型列的字段类型，在使用alter table modify column语句修改时报错"数据类型的变更无效"，在备份了表数据并truncate table清除表数据后，修改多媒体类型列依旧报错。请详...

join用法

doveqian的专栏

04-27

749

ON | CROSS JOIN | 说明: ON 指定连接条件 CROSS JOIN 交叉连接参与连接的表连接类型，格式如下： [INNER | {LEFT | RIGHT | FULL}[OUTER][]JOIN] INNER 内连接，系统默认，可以省略

mysql两表关联但是关联字段类型不一致

最新发布

09-17

<think> 嗯，用户现在关注的是索引字段参与排序是否会导致索引失效的问题。从之前的对话来看，用户已经对索引失效的几种常见场景有所了解（比如字段运算、隐式类型转换、字符集不一致等），现在需要深入排序场景的具体分析。用户引用的资料[^3]提到order by使用驱动表索引字段时效率高，但用非驱动表字段会导致临时表排序。这其实点出了两个关键点：一是排序字段本身有无索引，二是字段所在表在查询中的位置。需要帮用户理清这两层关系。结合MySQL的B+树索引特性，排序本质上和索引的物理存储顺序强相关。如果排序方向和索引方向不一致（比如索引是ASC但要求DESC排序），或者排序字段与索引字段顺序不匹配（如索引是(a,b)但按b排序），都会引发问题。另外，多表关联时驱动表的选择也会间接影响排序是否走索引——这点在用户提供的案例[^4]中也有印证。用户可能实际遇到过慢查询问题，想确认是不是排序导致的。需要强调EXPLAIN结果中“Using filesort”这个关键信号，并举例说明哪些SQL写法会触发它。比如select * from table order by indexed_column这个简单案例，如果where条件过滤后数据量小，可能依然走索引排序；但若数据量大或包含text/blob字段，就可能退化为文件排序。考虑到用户是开发或DBA角色，可以补充一个性能对比：索引排序（Using index）耗时约0.01秒 vs 文件排序（Using filesort）耗时可能超过1秒。这样用户能直观理解优化意义。最后要关联到用户最初的索引失效主题，说明排序场景本质是“部分失效”——虽然where条件可能用到了索引，但排序环节没利用索引就会拉低整体性能。这和之前讨论的字符集问题[^4]不同，后者是彻底无法使用索引。 </think> 索引字段参与排序**不一定导致索引失效**，但需要满足特定条件才能利用索引加速排序。以下是关键分析： --- ### 一、索引排序生效的核心条件 1. **排序字段与索引字段顺序一致** 若索引为 `(a, b, c)`，则以下排序可走索引： ```sql ORDER BY a -- ✅ 生效 ORDER BY a, b -- ✅ 生效 ORDER BY a, b, c -- ✅ 生效 ``` 以下情况**失效**： ```sql ORDER BY b -- ❌ 违背最左前缀原则 [^1] ORDER BY a, c -- ❌ 跳过了索引字段b [^1] ``` 2. **排序方向需一致** 索引默认按 `ASC` 排序，混合方向会导致失效： ```sql ORDER BY a ASC, b DESC -- ❌ 方向不一致（需索引支持混合排序） ``` 统一方向则生效： ```sql ORDER BY a ASC, b ASC -- ✅ 生效 ``` 3. **非驱动表字段排序失效**（多表关联场景）若 `ORDER BY` 使用**非驱动表**的字段，MySQL无法利用索引排序，需创建临时表排序 [^3]： ```sql SELECT * FROM A LEFT JOIN B ON A.id = B.a_id ORDER BY B.create_time; -- ❌ 非驱动表字段导致临时表排序 ``` --- ### 二、索引排序生效的典型场景（附原理） ```mermaid graph LR A[查询语句] --> B{ORDER BY字段} B -->|匹配索引最左前缀| C[索引有序遍历] B -->|方向一致| C C --> D[直接返回结果] B -->|不匹配索引| E[全表扫描+临时表排序] ``` #### 案例解析 | **场景** | 是否生效 | 原因 | |---------------------------|----------|----------------------------------------------------------------------| | `INDEX(a,b)` + `ORDER BY a` | ✅ | 命中索引最左前缀 | | `INDEX(a,b)` + `ORDER BY b` | ❌ | 违背最左前缀原则 [^1] | | `INDEX(a)` + `ORDER BY a DESC` | ✅ | 索引可反向扫描 | | 多表关联时按驱动表索引排序 | ✅ | 如驱动表为A且按A.id排序 [^3] | | 关联字段字符集不一致 | ❌ | 如 `utf8mb4_bin` vs `utf8_general_ci` 导致排序失效 [^4] | --- ### 三、强制索引排序失效的陷阱 1. **索引列参与计算** ```sql ORDER BY YEAR(create_time) -- ❌ 对索引列运算导致失效 [^1] ``` 2. **隐式类型转换** ```sql -- 索引字段为varchar, 但按数值排序 ORDER BY numeric_column + 0 -- ❌ 隐式转换失效 [^2] ``` 3. **包含非索引字段** ```sql SELECT * FROM table ORDER BY a, d -- ❌ 若d不在索引中，需回表排序 ``` --- ### 四、优化建议 1. **设计索引时预排序** 按高频排序需求创建匹配的索引： ```sql CREATE INDEX idx_sort ON table (a ASC, b ASC); ``` 2. **避免混合排序方向** 统一使用 `ASC` 或确保索引支持混合排序（MySQL 8.0+支持降序索引）。 3. **多表关联时优先驱动表排序** 调整查询顺序或使用 `STRAIGHT_JOIN` 指定驱动表 [^3]。 4. **字符集/排序规则一致性** 确保关联字段的字符集和排序规则相同 [^4]： ```sql ALTER TABLE B CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ``` --- ### 结论索引字段排序失效的根本原因是**排序逻辑与索引物理存储顺序不匹配**。通过保证排序字段满足最左前缀、方向一致、避免计算和类型转换，并注意多表关联时的驱动表选择，可高效利用索引排序[^1][^3][^4]。

JOIN条件中字段类型不一致， 导致索引失效的案例一则

JOIN条件中字段类型不一致，导致索引失效的案例一则