一条sql优化过程

昨天开发那边有说查询速度很慢，有一个sql现在效率很低，发过来的sql：

select lea.id lea_id,
       lea.leaid lea_leaid,
       lea.racctid lea_racctid,
       racctid_ref.acctid lea_racctid_ref,
       lea.leaname lea_leaname,
       lea.leatype lea_leatype,
       decode(lea.leatype,
              'leaguer_leaguertype_0',
              '公众会员',
              'leaguer_leaguertype_1',
              '商务会员',
              'leaguer_leaguertype_2',
              '特邀会员',
              '') lea_leatype_enum,
       lea.rvocationid lea_rvocationid,
       rvocationid_ref.vocationname lea_rvocationid_ref,
       lea.leatel lea_leatel,
       lea.ispart lea_ispart,
       lea.rownerid lea_rownerid,
       rownerid_ref.sname lea_rownerid_ref,
       lea.contname lea_contname,
       lea.conttel lea_conttel,
       lea.address lea_address
  from lea lea
  left join acct racctid_ref on racctid_ref.id = lea.racctid
  left join vocation rvocationid_ref on rvocationid_ref.id =
                                        lea.rvocationid
  left join org_employee rownerid_ref on rownerid_ref.id =
                                         lea.rownerid
 where 1 = 1
   and lea.istolea = 0

查询一次大约120S左右，acct表1000W，vocation 100左右，org_employee  3000左右，lea 8W左右的数据，acct id为主键，lea.racctid普通索引， lea.rvocationid 普通索引

后来发现执行计划使用了hash join链接，这的确很差的，后来修改了一次

select lea.id lea_id,
       lea.leaid lea_leaid,
       racctid_ref.acctid lea_racctid,
       lea.acctid lea_racctid_ref,
       lea.leaname lea_leaname,
       lea.leatype lea_leatype,
       decode(lea.leatype,
              'leaguer_leaguertype_0',
              '公众会员',
              'leaguer_leaguertype_1',
              '商务会员',
              'leaguer_leaguertype_2',
              '特邀会员',
              '') lea_leatype_enum,
       lea.rvocationid lea_rvocationid,
       rvocationid_ref.vocationname lea_rvocationid_ref,
       lea.leatel lea_leatel,
       lea.ispart lea_ispart,
       lea.rownerid lea_rownerid,
       rownerid_ref.sname lea_rownerid_ref,
       lea.contname lea_contname,
       lea.conttel lea_conttel,
       lea.address lea_address
  from lea lea
--  left join acct racctid_ref on racctid_ref.id = lea.racctid
  left join vocation rvocationid_ref on rvocationid_ref.id =
                                        lea.rvocationid
  left join org_employee rownerid_ref on rownerid_ref.id =
                                         lea.rownerid
 where 1 = 1
   and lea.istolea = 0
   and lea.racctid in
       (select id from acct where id = lea.racctid)

还是走hash join链接，后来在org_employee 上的id建立了一个索引，发现速度很快就上去了，0.7s左右就出来了，当然前边有个查询字段racctid_ref.acctid 如果改成lea.acctid  ，这时候，速度可以提高到0.015s，但是sql是自动生成的，如果修改的话整个系统都可能受到影响，硬编码绝对不是最好的方法，于是此时的速度已经可以的了，不错，速度终于恢复正常了，查找索引失效原因：

前阵子做过数据迁移，可能是迁移的过程中导致的索引失效，但是很多索引都是rebuild的，所以做迁移或move,rebuild的时候，千万要注意索引的问题