编码最佳实践(6)--那些年，我们一起建的索引
程序员文章站
2022-06-04 13:46:30
...
前几次的编码最佳实践系列，我们都着眼于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代码，尽可能的保持数据库操作的简单和高效。
这次作为素材出场的，是来自项目中的一段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代码，尽可能的保持数据库操作的简单和高效。