MYSQL in 与exist区别

exist 用法示例：
select A.IsNullable attr from UM_M_TableColumn A where EXISTS (select id from
UM_METADATA where TENANT_CODE = ‘META2021’) group by A.IsNullable; –510ms

in用法示例：
select A.IsNullable attr from UM_M_TableColumn A where meta_data_id IN (select id from
UM_METADATA where TENANT_CODE = ‘META2021’) group by A.IsNullable; –3.3s

关联用法：
select attr from (select A.IsNullable attr from UM_M_TableColumn A,
UM_METADATA B WHERE A.META_DATA_ID = B.ID
AND B.TENANT_CODE = ‘META2021’) A group by attr; –3.3s

UM_METADATA 140w UM_M_TableColumn 133w
区别 exists是扫描外部表，然后去匹配内查询的表
而in则是先查询子查询，然后再当作条件查询外表
因此，当外部表小，内部表大时更适合exists 反之in
而in与外部关联几乎性能一致，非常差，即便关联一个空表，mysql的性能也会有较大下滑.

最后编辑于 ： 2022.04.11 10:33:15 © 著作权归作者所有,转载或内容合作请联系作者