sql查询语句实例|SQL查询优化对比实例

【www.bbyears.com--php与数据库】

某银行做校园卡缴费的测试过程中，发现成功缴费时间很长，大约需要75秒左右，原因分析：在做校园卡缴费的时候，首先是从数据库中查询到需要缴费的费项，然后再对该费项进行缴费，缴费成功后修改相应的状态，交易完成后，查看日志，发现下面的查询语句执行时间很长，在数据库中执行时间大约33.782 secs.秒

低效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" ---www.111cn.net
   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语句进行优化；

本文来源：http://www.bbyears.com/jiaocheng/49611.html