SQLReview 到底在Review什么

FROM：MQ‘s email

 国际站现行流程，所有项目和小需求如果涉及到SQL的变动，一定要提交给DBA review。有时候DBA没有及时给出review结果，开发测试不得不等待，不禁要问，为什么这么慢？能不能快点？在回答这个问题之前，让我们先来看看DBA SQL REVIEW到底在REVIEW什么？

 

对网站来说，最宝贵最重要的是数据，这些数据有会员、公司、产品、商机等信息。网站应用作为跟客户交流的媒介产生了这些数据，加以处理并展现给客户，数据不能丢，所以我们存放在数据库里。最早选择数据存放在Oracle数据库里，因为Oracle是一流的数据库服务提供商，能为我们网站应用存取数据提供安全可靠便利的途径。但是，是不是只要把数据存在ORACLE里就一定很安全很可靠很便利呢？不一定！

举个例子，杭州市号称中国幸福指数最高的城市，风景优美，免费旅游。那是不是大家都跑到杭州去都永远能感受到幸福呢？

以交通出行为例，路上跑的有公交车，私家车，外地客车，大大小小的货车，还有各式各样的自行车、电动车，其余的选择步行或者就宅在家里。为了让交通流畅，调控的手段有开辟人行道自行车道，机动车道，后来增加公交专用车道，单行线等。在一定时间内大家觉得去西湖溜达溜达蛮方便的，很幸福。随着越来越多的人涌入杭州，越来越多的交通工具上路，慢慢的出现交通拥堵情况。大家的幸福指数有下降趋势，于是又有了新的调控手段，如单双号限行，某繁忙路段节假日单向行驶，某些线路×××车牌禁行等，开辟快速公交线路。这些在一定时间内又提升了幸福指数，于是又有更多的人更多的车涌入。幸福指数又要下滑了，这时没有什么可调控手段了，于是道路扩容，没地方扩了？修地铁，一号线，二号线，三号线…

我们的数据库就类似上面这种情形。Oracle数据库是最安全最可靠最便利的数据库，但我们把数据存放到里面是不是安全是不是可靠是不是存取很便利？ 从上面的例子可以看出，没有一序列的交通调控手段，杭州就是一个人堆。每个人都想行动快点，但是都快不起来，还会时不时有车毁人亡的事情。同样，在数据库里我们也需要一序列调控手段去确保数据放在数据库里是安全的可靠的存取便利的。

 

数据库存储数据，但数据库并不知道数据的来源走向以及数据的存取方式，因此数据库不能自动调整也不能给出有效的建议，所以目前需要DBA来判断。那么DBA是怎么去判断的呢？

做过多次SQLREVIEW的开发同学肯定会发现DBA老是问一些类似的问题，如这个表有多少数据啊，这个SQL并发有多少啊，这个更新多少笔提交一次啊，这个定时器什么时间跑啊。为什么DBA要不厌其烦的确认这些问题？因为DBA想更准确的把握数据。

一要把握数据量。了解数据量的增长情况，判断是否可能因为量变而引起某些方面的质变（如上面那个例子）。另外，你要返回多少数据量，这个也可能对现有系统有影响。数据是应用程序产生的，开发同学作为应用的负责人应该对新发应用带来哪些数据量的变化有一定的把握。所以DBA需要了解这个信息。

二要把握数据存取方式。你所取的数据是来源于一个表还是多个表？如果是一个表，一般有两种方式去取。如在字典里查几个字的意思，如果你字典很古老，拼音目录部分都没了，你就只能一页一页的翻，看每个字是不是你想要的；如果拼音目录还在，你可以从目录里找到这几个字对应的页码，然后直接翻到那几页去查。显然我们都喜欢有目录的字典，因为觉得它查起来快。前一种方法在数据库中叫“全表扫描”，后一种叫“索引范围扫描”，目录就是索引。通常我们都觉得选择索引存取肯定快，大部分情景下这是对的，有时候却恰恰相反。

索引范围扫描有个特点就是它得循规蹈矩的先从索引条目中取出一个记录，然后再去表中取对应的数据块。取到后再回到索引取下一笔记录，然后再去表中取对应的数据库。这里的每个‘取’的操作都是要成本。这里可以看出每取一笔数据实际上发生两次‘取’操作。如果要从100笔记录里查出50笔，则可能需要‘取’100次（注：这个结果只是说明问题，实际算法比这个复杂，不一定是这个结果）。而如果用全表扫描的方法，则可能只需要一次或两三次就能取出想要的。全表扫描的特点就在于它每次取的数目很多。所以走索引快不快，要结合数据总量以及返回结果的数据量占总量的比重一起分析。一般经验返回结果数量占总量的5%以内的适合用索引，数据总量很大的时候可能是1%甚至更少。

当数据存取时要从多个表去取的时候，这个算法又复杂一点，但也有规律可循。无论多少个表关联，Oracle一次只能用两个表进行联接，就像每个人只有两只手，要找出两个大小不一样的篮子彼此质量相同的鸡蛋。一种方法就是两手分别从两个篮子里抓一个进行比对，相等就记下，不等右手继续抓下一个跟左手比较。右手都抓完了，左手才换下一个鸡蛋，然后右手继续从头开始抓。这种存取方式就类似于程序中一个for 循环里面再嵌套一个for循环，最里面的进行比较。遗憾的是Oracle只支持两层循环。这种存取方式的成本也是可以计算的。假设外层循环的次数为m, 里面循环的次数为n，则总比对次数为m+m*n 。显然，当m远小于n的时候，总比对次数最小，这种存取数据的成本也最少。所以一般表连接的时候我们主张过滤后返回结果集小的表在前，过滤后返回结果集大的表在后，用小结果集去驱动大结果集查询。这种表连接方式叫“嵌套循环”（NEST LOOP）。当SQL实际执行的时候么有按这种方式去连接的时候，DBA有时候会在SQL里加“提示”（HINT）告诉数据库按嵌套循环方式做表连接。提示格式是： /*+ use_nl(表a,表b) ordered */ 。用过的人一定很熟悉，没用过的请注意这不是注释！

表连接方式还有一种常用的叫哈希连接（HASH JOIN），采用的是hash算法，这里就不介绍了。提示格式是: /*+ use_hash(表a,表b) ordered */

三要把握数据存取的并发数。每种存取方式都有其特点和适用的场景，该使用哪种存取方式除了要考虑数据量外还要这种存取方式的并发数目。数据库里在存取数据过程中为保证数据的一致性等需要申请一些不同的资源。这些资源的特点就是很稀有，因此占用时间必须很短。就像吃饭打卡一样，打卡机就是这种稀有资源。吃饭的人多了，打卡机就是瓶颈，后面的人就要排队等待了。对数据库而言，某种存取方式的并发如果很高的话，这种存取方式单次消耗时间就越短越好，否则就会有很多任务在排队等待。如果这种存取方式是全表扫描且有耗时很久，很多会话将会等待，应用对应页面将会超时。同时因为会话持有的数据库连接没有及时释放，其他模块可能就取不到数据库连接。所以对于全表扫描所支持的并发数很低，如果索引范围扫描，支持的并发数会高一些。具体的数目跟实际的存储的I/O能力有关。开发人员和DBA一般可以根据已有经验进行判断多少可以承受。

四要把握数据存取时间。这点主要是针对standalone应用来说。Standalone应用多是跑一些操作数据量比较大的SQL，这些SQL对数据的存取方式很有可能对其他应用有影响，但并不表示就不可以上线，可以将上线时间选在数据库低峰期。另外就是定时器的间隔，这个间隔阀值如何设定也要看具体的逻辑对数据库的影响。

 

上面四种就是DBA SQL REVIEW所要REVIEW的，简单的说就是要把握数据量的变化以及这些数据如何去存取才不会给数据库的性能带来危害。数据是应用程序产生的，数据的流向和存取方式是由应用程序控制的，开发同学作为应用程序的OWNER对这两点应该比任何其他人都清楚。DBA 要求做SQLREVIEW，要求开发提供sqlmaps文件，这些都只是手段，真正的目的是想了解这个数据。而sqlmaps所反映的信息其实很有限，里面的动态条件更是让DBA无可奈何，DBA不得不要将每个sqlmaps里的sql去掉ibatis语法然后拿出来分析，并且要跟开发同学确认相关信息。这个操作其实是个效率很低的操作，但是却是最重要的不得不做的事情。选择看sqlmaps文件其实是目前没有更好选择的选择。如果你理解了上面的四点，把那四点信息准确的告诉了DBA，DBA甚至可以不看sqlmaps就可以确认sqlreview结果。如果你对你所负责的那块业务的所有数据的量、存取方式、并发等都有较好的把握，对变动的SQL带来的数据量的变化以及数据存取方式的可能变化都有些有预见和应对方法的话，你就可以质疑DBA的review结果甚至可以不用review就通过。为了提高sqlreview的效率， DBA又在业余开发了一个工具（10.20.36.20:8080/SQLAutoParse/），这个工具能直接将sqlmaps文件的sql全部解析成不带ibatis语法的SQL，并连接到开发库上生成执行计划，给出初步建议。由于DBA资源紧张，欢迎有兴趣的同学参与进来开发，让这个工具早日上线给开发使用。有兴趣的请联系国际站DBA叶正盛。

工具只是辅助我们去实现自己心里的想法。相信把握数据这种事情对一个有数据库应用开发经验的同学是可以做到的，加上工具的辅助，未来SQLREVIEW就可以变成类似于UNIT TEST的过程。

 

继续扯。

当上面四点都能把握了，也并不表示数据库的安全可靠便利就永远存在了。数据库里的数据是一直在变化的，数据量的变化随时可能导致未预料的质变。如数据量的增加很有可能导致数据存取方式的变化（SQL执行计划）。在数据库高峰期（尤其是发布时），这种瞬间的变化很有可能就将数据库置于高风险中，严重的会导致数据库宕机或者应用挂掉。所以每个新上的项目新发的小需求，即使有些SQL是没有变化，似乎是不用review的，但是如果这个小需求发上去后会带来数据量的剧增，也还是有风险的。所以把握数据这个思想随时都要有，当你有疑虑的时候就可以找DBA一起分析。

还有一种变化可能是缓慢的，随着数据量或者并发数的增加，某些数据的存取方式已经不能满足要求或者带来风险，这时候就要寻求其他的解决方案。如数据库硬件升级。不过这个方案成本高不会首先考虑。常用的方法是对于数据量不大但是访问并发数很高的，存取数据不再直接走数据库，而改用udas,cache或者bdb等。对于某些数据量大并且访问频率非常高的，改用走搜索引擎。还有就是如果某些数据不适合用Oracle数据库存取可以改用mysql，甚至用NoSQL类的数据库，具体根据数据的量和存取特点。

把握数据并不只是用在sqlreview阶段。在提交数据订正的时候，也要考虑你要订正的数据带来的影响。比如说av认证表增加一个订单号，要订正老数据，要写SQL了，最直接的想法就是一句update av_info set crm_order_number = xxx where crm_order_number is null；等。这SQL本身没有错。但是没有考虑到av数据的量以及av数据相关的业务是如何去使用这些数据的。订正的时候肯定还有其他业务并发访问av表，直接用这个SQL订正可能会阻塞其他相关业务，很有可能导致故障。正确的做法是写PLSQL块，改用批量提交。多少笔提交一次适合取决于这个数据的存取并发数。同样还有线上数据库查询。有时候开发同学会跟DBA确认这个查询SQL是否有风险，DBA其实还是从上面几方面考虑的。先看你的SQL对数据的存取方式以及数据量。大数据量的全表扫描会占用数据库主机的I/O资源，同时如果那个表正在被相关业务并发访问，很有可能导致额外的等待事件。所以DBA建议就是查询能根据主键查就根据主键查，能用到索引且返回结果集小的话也可以查。对自己负责的业务数据应当有一定的了解，然后再自己判断这个查询有什么影响。

 

开发产生数据并控制数据的流向和存取方式，测试验证数据的正确性，DBA确保数据安全。数据，是我们共同的东西，把握数据，规避风险，我们一起努力！

 

 

附：需要了解数据库应用的开发知识的，推荐看Thomas Kyte著的《Oracle 9i&10g编程艺术》。在此基础上，对数据库应用架构和性能调优有更深兴趣的，推荐看旺旺翻译的《Oracle性能诊断艺术》