(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消耗比之前少了很多,原基表全表扫描,现变成了走索引,如下图所示:
推荐阅读