前几次的编码最佳实践系列,我们都着眼于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代码,尽可能的保持数据库操作的简单和高效。
分享到:
相关推荐
不仅从语言角度系统而详细地阐述java安全编码的要素、标准、规范和最佳实践,而且从架构设计的角度分析了java api存在的设计缺陷和可能存在的安全风险,以及应对的策略和措施。可以将本书作为java安全方面的工具书,...
内容全部由java编码的最佳实践组成,从语法、程序设计和架构、工具和框架、编码风格和编程思想等五大方面,对java程序员遇到的各种棘手的疑难问题给出了经验性的解决方案,为java程序员如何编写高质量的java代码提出...
本书适合专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言,以切合实际的方案来解决复杂的实际问题。...
项目是基于ASP.NET的Web应用程序开发,旨在构建一个高性能、可扩展和可靠的在线平台。我们将使用ASP.NET MVC框架和C#编程...我们将遵循最佳的软件开发实践,进行系统测试和性能测试,以确保应用程序的质量和稳定性。
我们很高兴向您介绍《阿里巴巴Java编码指南》,该指南整合了阿里巴巴集团技术团队多年来的最佳编程实践。随着我们鼓励重用和更好地理解彼此的程序,大量的Java编程团队对项目之间的代码质量提出了苛刻的要求。过去...
6 第 2 部分 - 设计表和字段 6 检查各种变化 6 采用有意义的字段名 6 采用前缀命名 6 标准化和数据驱动 6 标准化不能过头 7 Microsoft Visual FoxPro 报表技巧 7 不活跃或者不采用的指示符 7 使用角色实体定义属于某...
遵循PEP 8编码规范,使代码更易读和遵循Python社区的最佳实践。 使用内置函数和迭代工具,如map(), filter(), 和 reduce()。 字符串与输出: 明确区别str()和repr()函数:str()用于生成人类可读
P3C前言我们很高兴向您介绍《阿里巴巴Java编码指南》,该指南整合了阿里巴巴集团技术团队多年来的最佳编程实践。 大量的Java编程团队对跨项目的代码质量提出了苛刻的要求,因为我们鼓励重用和更好地理解彼此的程序。...
翻译MSDN文章 —— 泛型FAQ:最佳实践 Visual C# 3.0 新特性概览 C# 2.0会给我们带来什么 泛型技巧系列:如何提供类型参数之间的转换 C#2.0 - Object Pool 简单实现 Attributes in C# 手痒痒,也来个c# 2.0 object ...
编码指南,它整合了阿里巴巴集团技术团队的最佳编程实践。 由于我们鼓励重用和更好地理解彼此的程序,因此大量 Java 编程团队对跨项目的代码质量提出了苛刻的要求。 我们过去见过很多编程问题。 例如,有缺陷的...
8.3 应用开发最佳实践 204 8.3.1 解析消息创建时间 204 8.3.2 换行符的使用 205 8.3.3 网页超链接的使用 206 8.3.4 隐藏浏览器工具栏 206 8.3.5 表情飘落效果 207 8.4 识别微信浏览器 208 ...
20.8 指导原则和最佳实践 20.8.1 善用finally块 20.8.2 不要什么都捕捉 20.8.3 得体地从异常中恢复 20.8.4 发生不可恢复的异常时回滚部分完成的操作——维持状态 20.8.5 隐藏实现细节来维系契约 20.9 未处理的...
在前两版的内容架构基础上,第3版增加了对Rails 2中新特性和最佳实践的内容介绍。相比第2版中的内容,Rails 2增加了REST、资源、轻量级web service等新特性。本书涵盖了这些全新的内容,因此能更好地体现出Rails框架...
但是,我们也指出了递归树的最佳用途,即利用它来产生猜测,再利用替代方法对猜测进行验证。 ·快速排序(第7.1节)中用到的划分方法与期望线性时间顺序统计算法(expected linear-time order-statistic algorithm,...
19.1.8 同步设计最佳实践 572 19.1.9 更多的同步类型 573 19.1.10 线程本地存储 580 19.2 计时器 583 19.3 小结 584 第20章 平台互操作性和不安全的代码 585 20.1 在C#中使用WinRT库 586 ...
数据科学最佳测试实践 数据科学家的简短教程,内容涉及如何为您的代码和数据编写测试。 在学习本教程之前,请通读此README文件,因为该文件包含许多有用的信息,可帮助您最好地准备本教程。 如何使用这个仓库 教程...
但是,我们也指出了递归树的最佳用途,即利用它来产生猜测,再利用替代方法对猜测进行验证。 ·快速排序(第7.1节)中用到的划分方法与期望线性时间顺序统计算法(expected linear-time order-statistic algorithm,...
1.5 关于C++上机实践 习题 第2章 数据类型与表达式 2.1 C++的数据类型 2.2 常量 2.2.1 什么是常量 2.2.2 数值常量 2.2.3 字符常量 2.2.4 符号常量 2.3 变量 2.3.1 什么是变量 2.3.2 变量名规则 2.3.3 定义变量 ...
1.5 关于C++上机实践 习题 第2章 数据类型与表达式 2.1 C++的数据类型 2.2 常量 2.2.1 什么是常量 2.2.2 数值常量 2.2.3 字符常量 2.2.4 符号常量 2.3 变量 2.3.1 什么是变量 2.3.2 变量名规则 2.3.3 定义变量 ...