编码最佳实践(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代码，尽可能的保持数据库操作的简单和高效。

我认为作为一个开发人员应该掌握
1、命名
如UNIQUE INDEX SYS_C0098354 (ADDRESS(250), BWLIST_ID)   对于这种自动生成的名字我认为应该禁止，查看执行计划不方便。应该建立一套命名规则，比如索引/唯一索引/主键等
2、什么时候走索引/什么时候不走
3、会看简单的执行计划
4、知道去哪查自己使用的数据库产品的慢查询

我认为楼上说的挺好的。。。

限制索引使用的4个sql写法
1）使用不等于操作符
2）使用is null 或者is not null
3）使用函数
4）比较不匹配的数据类型

最近一直发愁的是，一个视图效率很差，因为涉及到查询权限控制，所以查询用的sql拼接，这样等于先查询视图所有数据 再过滤，不走索引，速度很慢，你们有什么借鉴的经验么

一个比较简单的算法是能不能在树上进行二分查找。

1、改进视图让其走索引
2、不行的话，拆成多条走索引的sql
3、封装存储过程/通过Java逻辑实现
4、有没有办法通过如触发器进行汇总，方便查询等等

这东西跟团队的文化和管理也很有关系。
很多的软件技术人员现在的工作状态就是：接受任务，完成任务。这些对于技术人员来说无可厚非，他们面对的检验任务完成情况的人再疏漏掉这个过程，最终的结果就是LZ说的情况一样。
分析一下现有团队的管理现状：
很多情况下，对于任务的获取与任务的拆解过程，都会集中在所谓的业务或需求人员手中，这些人往往是有很多的业务积累，这些人多数而言都不是技术能力很强的人，而所谓的技术大拿，很多人并不乐于将精力投入到这些事务当中，结果是业务和需求人员无法在过程中发现开发中隐含的技术风险，技术人员我行我素只管自己管的部分。任务的拆解过程，就因为这种团队结构的层层过滤而疏漏而遗忘在团队合作中。去要求一个低级的代码人员考虑SQL的性能，本身就是不现实的。
总体而言，在软件开发行业内的刚入行的人，程序方面一般都过关，而数据库和界面方面，基本都不合格。这就是这个行业的周边培训机制不健全或者说过时所导致的必然结果。
这种性能问题背后都是一个管理成本和生产成本的问题，作为一个纯技术人员，知道这些如何处理并能教会别人就足以了。在一个浮躁的环境下，产生技术积累人力积累以及文化积累本身就不现实，也不是几个人就能改变的事情。

另外一提，LZ说的这个问题，其实也可以不动java代码，直接用oracle的Regexp_replace也可以解决，即把计算公式放在参数边，数据库项目使用原始值匹配。

很有道理！！

你理解有问题，其实不是先查询视图所有数据，视图其实也是sql只是预编译了，它可以走里面表的索引，你可以看下执行计划，估计是你的过滤条件不包含索引，或者你的过滤条件可选性很低走全表扫描