Sky's blog

我和我追逐的梦

常用链接

统计

其他链接

友情链接

最新评论

编码最佳实践(6)--那些年,我们一起建的索引

    前几次的编码最佳实践系列,我们都着眼于Java代码,今天我们换个话题,看看另外一个领域,和Java代码大相径庭的SQL。 

    这次作为素材出场的,是来自项目中的一段SQL,用于BlackWhiteList Adapter,在每次请求时检查一下,看当前用户是否在黑白名单中。 

    先介绍一下数据库结构,很简单的三张表:BWLIST记录黑白名单信息,BWLISTXADDRESS记录每个黑白名单下的地址列表,BWLISTXAPPLICATION 记录每个application关联到的黑白名单。查询时有三个输入:PARTNER_ID和APP_ID是当前application的唯一标识,通过这两个参数就可以在BWLISTXAPPLICATION中找到对应的黑白名单的id(可能有多个),然后通过黑白名单的id就可以在BWLISTXADDRESS找到对应的所有地址,结合第三个参数ADDRESS就可以得知到当前地址是否在黑背名单中。 

    还是直接看SQL吧,典型的3表联合查询: 

String sql = "select A2.ID, A2.ADDRESS, A2.ADDRESS_TYPE, A2.DESCRIPTION, A1.type BWLIST_TYPE, A3.LIST_LEVEL, A3.SC_ID, A3.PARTNER_ID, A3.APP_ID 
from BWLIST A1, BWLISTXADDRESS A2, BWLISTXAPPLICATION A3 
where A1.id = A2.BWLIST_ID  and A2.BWLIST_ID = A3.BWLIST_ID  
        and A3.PARTNER_ID = ?  and A3.APP_ID = ? 
        and (INSTR(?, A2.ADDRESS ) > 0)"; 

    直接看where 后面的内容,"A1.id = A2.BWLIST_ID  and A2.BWLIST_ID = A3.BWLIST_ID "好理解, BWLISTXADDRESS和BWLISTXAPPLICATION两个表的BWLIST_ID字段是外键,对应到表BWLIST的主键ID,用来做关联。后面的"A3.PARTNER_ID = ?  and A3.APP_ID = ? "是用来唯一标识当前application,为了加速查询,建有一个PARTNER_ID+APP_ID的索引:UNIQUE INDEX SYS_C0098362 (APP_ID(150), SP_PK, BWLIST_ID)。 

    关键在最后一个where条件:(INSTR(?, A2.ADDRESS ) > 0)"。这里使用INSTR()而不是简单的=,是考虑到地址可能有多种格式,比如"13900000000","tel:13900000000", "tel:+8613900000000",实际都是一个号码。因此考虑在数据库将A2.ADDRESS保存为"13900000000",这样无论当前输入的地址格式是"13900000000","tel:13900000000", 还是"tel:+8613900000000",都可以被正确处理。 

    同样为了加速查询,开发的同事为A2.ADDRESS这个字段增加了索引:UNIQUE INDEX SYS_C0098354 (ADDRESS(250), BWLIST_ID)。 

    这样在上述几个索引的支持下,前面的sql语句的where条件,按说基本都被索引优化到了 ————— 但是,相信一些有经验的同事可能第一时间就已经反映过来,最后的这个基于ADDRESS字段的索引,有问题! 

    问题出在(INSTR(?, A2.ADDRESS ) > 0),INSTR()是一个SQL函数,作为一个基本常识,大家都知道的:如果索引列是SQL函数的参数,那么索引在查询时是用不上的。 

    很遗憾,当时编写这个SQL的同学可能不知道或者一时没有反应过来,结果上述的SQL被写入到产品。随后更糟糕的是,在压力测试中,居然没有被发现,原因是测试时使用的数据规模太小,只为被测试的黑白名单准备了几十个地址,所以虽然索引无法被利用,但是对于区区几十条记录,不走索引反而能更快一些...... 就这样逃过测试,发布并部署运行于客户线上。 

    然后,后面的事情就可以想象了,客户实际跑的时候,BWLISTXADDRESS中记录的条数远不是几十,而是几十万,几百万......而黑白名单过滤功能一旦开启,是每个请求都要检查一次,上面的SQL每次都要执行一次。于是数据库理所当然的顶不住,整个系统的速度都被拖累,客户就抱怨说黑白名单开启之后性能出现大幅下降。而痛苦的是我们自己测试时不能重现问题,嗯,用那个只有几十个地址的名单当然重现不出来...... 

    总之这个小bug引来了后面一堆的事情,我们不继续吐糟,回头来看看,问题是如何一步一步的产生: 

1. 开发人员犯错,常识性的小错误:索引对SQL函数无效 
2. 对于性能敏感的SQL,没有做慎重的处理:如果当时有用查询分析器看一下执行计划,就可以避免出现类似的索引失效而不自知的情况;如果有其他有经验的同事review这个SQL,也可以在早期发现问题 
3. 性能测试时数据建模失误,没有模拟到真实线上数据的规模,以至于最后一道关卡被突破,让性能问题逃过了性能测试 

    第3条是另外一个话题,我们这里重点来看1和2: 

    1的问题本质上是一个老生常谈的问题:如何避免在同一个坑中跌倒多次?这里所说的同一个坑,针对不同的对象有不同的含义:对于个人,上次犯下的错误下次会不会还继续?对于一个团队,A同学出错的地方B同学是否能避免?对于整个公司,A产品线遇到的问题B产品线能不能有所借鉴? 

    可以说,我们现在的这个编码最佳实践的系列,就是为了解决类似的问题:将我们不同的产品线犯下的一些典型问题总结下来,分享给其他人,避免同一个坑不停的有人踩的尴尬和无奈。 

    而2的问题在于我们的WOW(Way Of Working)还不够完善,对于性能敏感的关键代码,应该保持足够的谨慎和细致,类似的每次查询都要执行一次的SQL,怎么都要看看执行计划才能放心写入产品。我们也应该有完善的code review机制来保证当有疏漏的时候应该能及时补救。 

    最后我们再回到原始问题,关于这个SQL,我们现在知道INSTR()函数用不上索引,通常的解决方案是使用函数索引,但是对于"INSTR(?, A2.ADDRESS ) > 0",函数索引也无能为力。因此只好修改业务处理方式,不再在SQL查询这个层次处理地址格式的问题,将格式问题抛给Java代码:在数据库中保存标准格式如"tel:13900000000",业务处理流程中对输入的地址格式做标准化,将地址匹配简化为简单的"="操作,这样可以极大的节约数据库查询开销。 

    最终修订版本的SQL如下: 

String sql = "select A2.ID, A2.ADDRESS, A2.ADDRESS_TYPE, A2.DESCRIPTION, A1.type BWLIST_TYPE, A3.LIST_LEVEL, A3.SC_ID, A3.PARTNER_ID, A3.APP_ID 
from BWLIST A1, BWLISTXADDRESS A2, BWLISTXAPPLICATION A3 
where A1.id = A2.BWLIST_ID  and A2.BWLIST_ID = A3.BWLIST_ID 
        and A3.PARTNER_ID = ?  and A3.APP_ID = ? 
        and A2.ADDRESS=?"; 

    这也是性能优化的常见方式:将复杂的业务逻辑尽量留给Java代码,尽可能的保持数据库操作的简单和高效。 

posted on 2013-01-04 12:08 sky ao 阅读(1972) 评论(1)  编辑  收藏 所属分类: java

评论

# re: 编码最佳实践(6)--那些年,我们一起建的索引 2013-01-04 17:38 牌具

文章不错,文字不要弄背景就更好了  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航: