IRIS/Caché SQL优化经验分享 - 优化关键字

SQL查询优化器一般情况下能给出最好的查询计划，但不是所有情况都这样，所以InterSystems SQL还提供了一个方式， 也就是在查询语句里加入optimize-option keyword（优化关键字）, 用来人工的修改查询计划。

比如下面的查询：

SELECT AVG(SaleAmt) FROM %PARALLEL User.AllSales GROUP BY Region

其中的%PARALLEL, 就是最常用的优化关键字， 它强制SQL优化器使用多进程并行处理这个SQL。

您可以这样理解： 如果查询优化器足够聪明，那么绝大多数情况下，根本就不需要优化关键字来人工干预。因此，您也一定不奇怪在不同的IRIS/Caché版本中， 关键字的表现可能不一样。越新的版本，应该是越少用到。比如上面的%PARALLEL， 在Caché的大多数版本中， 在查询中加上它一般都能提高查询速度，而在IRIS中，尤其是2023版本以后， 同样的SQL查询语句，很大的可能查询优化器已经自动使用多进程并行查询了，不再需要用户人工干预了。

因此，先总结有关优化关键字的要点：

1. 优化关键字主要是FROM语句中使用。 UPDATE, INSERT语句也有可以使用的关键字，比如%NOJOURAL等等， 这里我不介绍了，请各位自己查询文档。

   INSERT, UPDATE的关键字常用的有：%NOCHECK %NOINDEX %NOLOCK %NOTRIGGER 等等

2. 各个不同版本的文档中这部分内容有少许的不同。

3. 使用查询关键字要结合阅读查询计划，需要经验的积累。用的多了， 在当前版本什么样的查询需要添加关键字就比较有数了。

最新版本的联机文档在： Specify Optimization Hints in Queries | Configure SQL Performance Options

%PARALLEL

指定查询使用多个进程并行处理。在Query Plan中您可以得到证实。有关Query Plan的阅读请看前面的帖子。

%IGNOREINDEX

指定不用某一个或者某几个index。比如以下查询：

select min(ps_supplycost) 
                from %PARALLEL
                %IGNOREINDEX SQLUser.supplier.SUPPLIER_PK
                %IGNOREINDEX SQLUser.part.PART_PK
                %IGNOREINDEX SQLUser.nation.Nation_PK 
                %IGNOREINDEX SQLUser.region.REGION_PK
                        partsupp,
                        supplier,
                        nation,
                        region
                where p_partkey = ps_partkey
                        and s_suppkey = ps_suppkey
                        and s_nationkey = n_nationkey
                        and n_regionkey = r_regionkey
                        and r_name = 'AFRICA'
                        ...

为什么要强制不用某些索引？

一个是用在测试中，经常会比较不同索引的表现。比如你原来有个复合索引，它希望试试新创建的索引是不是更好， 那么很可能您需要告诉SQL引擎不要用以前的索引了。

还有就是您发现某个索引的使用没有让查询性能变好，强制不用它结果可以使用另一个索引，从而来得到更好的查询速度。

%ALLINDEX

用于测试所有可用的索引。

SQL引擎默认会在多个可用的索引中选中它判断最高效的，但这个判断不是总正确。加入%ALLINDEX会在生成查询计划前，测试所有可用的索引，以证实或者调整判断。 用到比较多的情况是有多个范围查询字句的情况。在Caché和早期IRIS版本中， 很多情况下， 使用%ALLINDEX会带来性能的提升， 尽管对所有可用索引做测试会有个额外开支.

比如以下的语句，

SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}

%NOINDEX

在最新版的IRIS文档中， 这个关键字已经去掉了。 我自己的测试中，在2022年后的IRIS中， 它其实已经不起作用了。 但在Caché中， 非常多的使用%NOINDEX的例子。

Caché在线文档中的这段是这么说的：当绝大多数数据被条件选中（或未被选中）时，这种方法最常用。在小于 (<) 或大于 (>) 条件语句下，使用 %NOINDEX 条件级提示通常是有益的。对于“等于”条件语句，使用 %NOINDEX 条件级提示没有任何好处。对于连接条件语句，不支持在 =* 和 *= WHERE 子句外部连接中使用 %NOINDEX；而在 ON 子句连接中使用 %NOINDEX。

这是文档上的例子: E.Age<65已经包含了绝大多数的表记录，那么使用相应的索引可能不经济，因为后面取“Name"还是要直接回表操作，这样的情况， 不用E表的Age的索引，查起来还快一些。

SELECT P.Name,P.Age,E.Name,E.Age
FROM %ALLINDEX Sample.Person AS P LEFT OUTER JOIN Sample.Employee AS E
     ON P.Name=E.Name
WHERE P.Age > 21 AND %NOINDEX E.Age < 65

幸好新版IRIS变的智能了。

%FIRSTTABLE, %STARTTABLE

这两个关键字都是强制查询计划中对JOIN的执行从那个表开始。如果SQL引擎没法给出正确的判断的话，人工指定是需要的。

其他的关键字包括： %FULL, %INORDER, %NOFLATTEN, %NOMERGE, %NOREDUCE, %NOSVSO, %NOTOPOPT, %NOUNIONOROPT, 等等。 不同的版本会有出入。如果您需要了解更多的关键字的使用，可以到community.intersystems.com里搜索相关的文章，比如这篇Force inner select to be executed, Query Plan Error or Correct Estimation