(FAQ)Sql where条件中采用了or关键字造成全表扫描，CPU、IO性能问题
金蝶云社区
1,008次浏览
编辑于2015年03月02日 00:00:00
【问题】: (FAQ)Sql where条件中采用了or关键字造成全表扫描，CPU、IO性能问题
版本: 7.0.1
模块: 系统性能
【答复】:
原因分析： 1. 当sql中的基表数据量过大，where条件中使用了or 关键字，即使条件中的字段已建索引，索引也是使用不上。因为用了or关键字，记录的查询没法通过某个确定的地址进行查询。从而走全表扫描，造成CPU、IO严重的性能问题,sql语句很长时间执行不出来。 解决方法： 1.将or两边关联的条件分别独立出来跟前面的select 内容进行组合，中间用union all进行数据的连接，如下所示： SELECT "TO"."FID" "ID","TO"."FMATERIALID" "T1.ID","T1"."FNUMBER" "T1.NUMBER","T0"."FCOMPANYID" "T2.ID","T2"."FNAME_L2" "T2.NAME","T2"."FNAME_L1" "T2.NAME_L1","T2"."FNAME_L2" "T2.NAME_L2","T2".FNAME_L3" "T2.NAME_L3","T2"."FNUMBER" "T2.NUMBER" FROM "T_BD_MATERIALCOMPANYINFO" "T0" LEFT OUTER JOIN "T_BD_MATERIAL" "T1" ON "T0"."FMATERIALID" = "T1"."FID" LEFT OUTER JOIN "T_ORG_COMPANY" "T2" ON "T0"."FCOMPANYID" = "T2"."FID" WHERE ((("T0"."FCOMPANYID" = :1 AND "T0"."FMATERIALID" = :2) AND ("T0"."FSTATUS" <> :3)) Union all SELECT "TO"."FID" "ID","TO"."FMATERIALID" "T1.ID","T1"."FNUMBER" "T1.NUMBER","T0"."FCOMPANYID" "T2.ID","T2"."FNAME_L2" "T2.NAME","T2"."FNAME_L1" "T2.NAME_L1","T2"."FNAME_L2" "T2.NAME_L2","T2".FNAME_L3" "T2.NAME_L3","T2"."FNUMBER" "T2.NUMBER" FROM "T_BD_MATERIALCOMPANYINFO" "T0" LEFT OUTER JOIN "T_BD_MATERIAL" "T1" ON "T0"."FMATERIALID" = "T1"."FID" LEFT OUTER JOIN "T_ORG_COMPANY" "T2" ON "T0"."FCOMPANYID" = "T2"."FID" WHERE (("T0"."FCOMPANYID" = :4 AND "T0"."FMATERIALID" = :5) AND ("T0"."FSTATUS" <> :6)) Sql改造后，查看执行计划，cost消耗比之前少了很多，原基表全表扫描，现变成了走索引，如下图所示:
版本: 7.0.1
模块: 系统性能
【答复】:
原因分析： 1. 当sql中的基表数据量过大，where条件中使用了or 关键字，即使条件中的字段已建索引，索引也是使用不上。因为用了or关键字，记录的查询没法通过某个确定的地址进行查询。从而走全表扫描，造成CPU、IO严重的性能问题,sql语句很长时间执行不出来。 解决方法： 1.将or两边关联的条件分别独立出来跟前面的select 内容进行组合，中间用union all进行数据的连接，如下所示： SELECT "TO"."FID" "ID","TO"."FMATERIALID" "T1.ID","T1"."FNUMBER" "T1.NUMBER","T0"."FCOMPANYID" "T2.ID","T2"."FNAME_L2" "T2.NAME","T2"."FNAME_L1" "T2.NAME_L1","T2"."FNAME_L2" "T2.NAME_L2","T2".FNAME_L3" "T2.NAME_L3","T2"."FNUMBER" "T2.NUMBER" FROM "T_BD_MATERIALCOMPANYINFO" "T0" LEFT OUTER JOIN "T_BD_MATERIAL" "T1" ON "T0"."FMATERIALID" = "T1"."FID" LEFT OUTER JOIN "T_ORG_COMPANY" "T2" ON "T0"."FCOMPANYID" = "T2"."FID" WHERE ((("T0"."FCOMPANYID" = :1 AND "T0"."FMATERIALID" = :2) AND ("T0"."FSTATUS" <> :3)) Union all SELECT "TO"."FID" "ID","TO"."FMATERIALID" "T1.ID","T1"."FNUMBER" "T1.NUMBER","T0"."FCOMPANYID" "T2.ID","T2"."FNAME_L2" "T2.NAME","T2"."FNAME_L1" "T2.NAME_L1","T2"."FNAME_L2" "T2.NAME_L2","T2".FNAME_L3" "T2.NAME_L3","T2"."FNUMBER" "T2.NUMBER" FROM "T_BD_MATERIALCOMPANYINFO" "T0" LEFT OUTER JOIN "T_BD_MATERIAL" "T1" ON "T0"."FMATERIALID" = "T1"."FID" LEFT OUTER JOIN "T_ORG_COMPANY" "T2" ON "T0"."FCOMPANYID" = "T2"."FID" WHERE (("T0"."FCOMPANYID" = :4 AND "T0"."FMATERIALID" = :5) AND ("T0"."FSTATUS" <> :6)) Sql改造后，查看执行计划，cost消耗比之前少了很多，原基表全表扫描，现变成了走索引，如下图所示:
推荐阅读