ҳ-Career-HOME-Сﲿ

SQL查询优化实例(一)

表px_bjhmc:41380 条数据; 表ldlsc_xtcsb:2349 条数据; 表px_xyhmc:1623823 条数据

低效SQL(耗时:33.782 secs.):

SELECT DISTINCT hmc.bjbh,
                hmc.pxjgid,
                hmc.bjmc,
                csb.tjxbt,
                hmc.pxlx,
                hmc.bjrs,
                hmc.bjsfjs,
                csb.xzqhdm,
                hmc.shyj,
                hmc.kbrq
  FROM px_bjhmc hmc, ldlsc_xtcsb csb, px_xyhmc c
 WHERE hmc.pxjgid = csb.dwbh
   AND hmc.pxjgid = c.pxjgid

   AND hmc.shyj = '1'
   AND hmc.bjsfjs = '1'
   AND c.pxsfjs = '1'
   AND hmc.jyrq >= '20130505000000'
   AND hmc.jyrq <= '20140102235959'
   AND hmc.pxjgid = '00000000000020'
优化方案一(耗时:6.485 secs.):

SELECT DISTINCT hmc.bjbh,
                hmc.pxjgid,
                hmc.bjmc,
                csb.tjxbt,
                hmc.pxlx,
                hmc.bjrs,
                hmc.bjsfjs,
                csb.xzqhdm,
                hmc.shyj,
                hmc.kbrq
  FROM px_bjhmc hmc, ldlsc_xtcsb csb, px_xyhmc c
 WHERE hmc.pxjgid = csb.dwbh
   AND hmc.pxjgid = c.pxjgid
   AND csb.dwbh = c.pxjgid
   AND hmc.shyj = '1'
   AND hmc.bjsfjs = '1'
   AND c.pxsfjs = '1'
   AND hmc.jyrq >= '20130505000000'
   AND hmc.jyrq <= '20140102235959'
   AND hmc.pxjgid = '00000000000020'

低效SQL(耗时:33.782 secs.):

SELECT DISTINCT hmc.bjbh,
                hmc.pxjgid,
                hmc.bjmc,
                csb.tjxbt,
                hmc.pxlx,
                hmc.bjrs,
                hmc.bjsfjs,
                csb.xzqhdm,
                hmc.shyj,
                hmc.kbrq
  FROM px_bjhmc hmc, ldlsc_xtcsb csb, px_xyhmc c
 WHERE hmc.pxjgid = csb.dwbh
   AND hmc.pxjgid = c.pxjgid


AND hmc.shyj = '1' AND hmc.bjsfjs = '1' AND c.pxsfjs = '1' AND hmc.jyrq >= '20130505000000' AND hmc.jyrq <= '20140102235959' AND hmc.pxjgid = '00000000000020'
优化方案二(耗时:0.031 secs.):

SELECT DISTINCT hmc.bjbh,
                hmc.pxjgid,
                hmc.bjmc,
                csb.tjxbt,
                hmc.pxlx,
                hmc.bjrs,
                hmc.bjsfjs,
                csb.xzqhdm,
                hmc.shyj,
                hmc.kbrq
  FROM px_bjhmc hmc, ldlsc_xtcsb csb
 WHERE hmc.pxjgid = csb.dwbh
   AND EXISTS (SELECT 1
          FROM px_xyhmc c
         WHERE hmc.pxjgid = c.pxjgid
           AND c.pxsfjs = '1')
   AND hmc.shyj = '1'
   AND hmc.bjsfjs = '1'

   AND hmc.jyrq >= '20130505000000'
   AND hmc.jyrq <= '20140102235959'
   AND hmc.pxjgid = '00000000000020'

降龙十八掌:

    • 多表同时关联:a.id = b.id, a.id = c.id, b.id = c.id;
    • 多表联接查询时,联少不联多,避免笛卡尔积操作;
    • 当一个表仅作为检索条件时,避免联接查询,多数情况下用Exists语句进行优化;
关于作者
  1. 不是很懂,好吧我不是程序员,只自学过一点php和c,另外,博客的模版很好看。