IRIS/Caché SQL优化经验分享 - 真实案例分享

最近有某国内三甲医院为满足评级和飞行检查要求，希望提升HIS和IRIS的SQL查询效率，客户和实施工程师整理了一个慢查询的SQL列表， 有一些查询比较慢， 查询时间在甚至大于60分钟。

在我们和厂商共同努力下，对整个库的SQL查询做了优化。 下表是记录了我们在进行了大部分优化工作后的结果，您可以看到大多查询从几十分钟减少到了几十秒甚至1秒以内。其中有几个慢到几分钟的查询，最后经过细调， 也把查询耗时减少到了一分钟以内。 优化的效果还是很明显的。

这里我分享一下操作的要点，以便给其他有同样问题的客户一个思路。

其实如果您看过我前面的帖子，应该已经有了基本的概念。我就把工作流程总结一下，其实就这么几个步骤：

步骤一：

检查硬件配置。 配置中和SQL性能相关的有这么几个： 1. 数据缓存大小，应该至少为物理内存的一半以上。 2. BBSIZE, 也就是单个进程最大的内存占用，对应不同的Caché/IRIS版本和不同的应用，这个配置有区别，但当然是越大越好，询问您的实施工程师配置是否正确。 3. 是否使用了大页内存，这个能从messages.log里看到。

步骤二

执行Tunetable。 在上面说的这个客户的系统上从来没人执行过Tunetable, 因此SQL引擎其实是没法正确工作的。执行后基本可以解决80%的慢SQL问题。时间短风险小见效快， 找个半夜业务小的时候直接在生产环境执行。

执行完之后，只剩下20-30个SQL还是太慢(超过5秒）， 之后的工作我们集中在这些Case上，

步骤三

校验索引。 在生产环境上数据和索引出错非常普遍，因为有人/业务代码直接去改Global. 有的是没改对， 有的是改的过程出了问题，比如有进程没做好错误管理，出问题了没有回滚等等。SQL表数据的完整性出问题，一个结果是查的数不对， 这是显见的，还有一个是查的慢， 这个大部分人想不到。

校验索引是发现这个问题的最有效的办法。 原则上说， 所有的索引都应该执行一下，但因为时间长，影响业务，我们的做法是把相关用到的索引校验了一遍。其中发现了很多数据被修改的问题，比如必须的字段里面是空的， 要求是数字的字段里面放的是字符串等等等等。

校验索引比重新build索引要慢的多， 但对业务的影响也小的多。

步骤四

使用bitmap和bitmap extent索引。本来bitmap extent是不用人工添加的。创建任何一个bitmap索引，系统会自动添加bitmap extent。然而客户用的是SQLStorage的存储格式，修改会非常麻烦。但结果是值得的，上面所处理的绝大多数SQL, 查询时间都降到了60秒以下， 和count()相关的查询， 更是可以从1000秒直接减少到5秒以内。

步骤五

对于极少数查询时间已经提高了很多，但还是不很如人意的SQL, 我们仔细的检查查询计划，用查询关键字来做最后的优化。在好几个查询里， %PARALLEL证明是起作用的， 也就是说，本来用并行多进程查询是可以提高效率的， 但SQL引擎没自动判断出来。

除此之外， 修改SQL语句有时候是可以改善查询速度的，尤其是含子查询, IN, TOP，ORDER BY的语句， 通过检查查询计划，比较不同查询计划的执行情况，可以做出一下成功的性能改善。

欢迎讨论指正，也欢迎感兴趣的客户前来咨询我们gcdpsales@intersystems.com获取更多信息。