IRIS/Caché SQL优化经验分享 - 检查索引的完整性

Caché/IRIS的特点是运行Global的修改，而这个修改和SQL是无关的，因此非常容易出现数据库表数据完整性的问题，也就是表中的数据是不是符合定义的表约束。 

这样的情况非常常见。有些是人为的对Global的错误修改， 有些是应用系统的事务性管理写的不对，造成事务回滚的时候破坏了索引的完整性。无论什么原因，只要使用Global操作，破坏SQL的完整性非常难以避免。结果就是SQL查询给出错误结果。

最简单的解决方法就是执行“索引检查(Validate Indices)"

我们来做个实验

- 先修改一个global： 如下图， 将Patient表的一个记录的SEX字段，从'M'改到‘F'. 

运行索引检查, 结果会提示您问题在什么地方。 

SAMPLES>do ##class(Patient).%ValidateIndices()
​
​
Checking index integrity for class 'User.Patient'
Begin time:  03/19/2024 15:25:43
​
Verifying data from data map 'IDKEY' is indexed correctly...
Bitmap index 'bidxSex', entry $bit(^User.PatientI("bidxSex"," F",1),4)=0, row is missing from index, it should be 1.
Index 'idxSex', entry ^User.PatientI("idxSex"," F",3) missing.
Data Map evaluation complete, 1000000 rows checked, 2 errors found, elapsed time: 27.344792 seconds
​
Verifying data from index map "$Patient" is correct...
Index map "$Patient" evaluation complete, 0 errors, elapsed time: 12.026982 seconds
​
Verifying data from index map idxSex is correct...
Index 'idxSex', entry ^User.PatientI("idxSex"," M",3), data differs for field 'Sex' between data and index map.
Index map idxSex evaluation complete, 1 errors, elapsed time: 12.285592 seconds
​
Verifying data from index map bidxSex is correct...
Bitmap index 'bidxSex', entry  $bit(^User.PatientI("bidxSex"," M",1),4), for row with ID '3', bit is ON, but indexed field value(s) differ from data map value(s).
Index map bidxSex evaluation complete, 1 errors, elapsed time: 13.591468 seconds
​
Verifying data from index map idxPatientNumber is correct...
Index map idxPatientNumber evaluation complete, 0 errors, elapsed time: 13.646994 seconds
​
%ValidateIndices is complete, total elapsed time: 41.048687 seconds
​
SAMPLES>
​

实际上， 上述例子是危害最小的，它只是破坏您对不同性别的病人的查询的准确性。而且这样的问题可以通过索引重建解决。我见过更糟的情况， 比如说。

• 数据主键或者ID字段出错，造成count()给出错误值
• 某个字段的数据丢失，造成count(*)和count(1)得到不同的值。注意， count(*)会使用占用硬盘最小的唯一字段的索引。 
• 数据记录丢失
• 等等

总之， 这些错误少部分可能能通过索引重建解决， 而大部份需要执行索引检查来知道发生了什么。

总结

1. 检查索引%Storage.%ValidateIndices()开始用于Caché 2016. 它检查索引，数据的完整性。 

2. 当一个索引定义后没有build过， 它会build这个索引。而且是生产环境中最安全的build索引的方式，也是最慢的方式。

3. 使用索引检查必须要把index定义在类定义里。 某些老系统使用SQLStorage, 其中索引定义在Storage块的<SQLMAP>部份。对这样的索引，需要把索引名字加到类定义。 

4. %ValidateIndices() 有个自动更新设置，实际中尽量不要用，除非您根本不关心错误具体在那些数据，以及错误是怎么造成的。 

5.  如果一个索引执行BuildIndices()不成功。 这时候使用%ValidateIndices()来build索引，%ValidateIndices()会停止在错误数据的那个记录上。

最后，当处理索引时您可能需要暂时将某个索引上线或者下线，

暂时disable某些索引

# 将需要建立的索引先下线：
do $SYSTEM.SQL.SetMapSelectability(“HoleFoods.Product",“ProductName",0)
​
# 将索引上线
do $SYSTEM.SQL.SetMapSelectability(“HoleFoods.Product",“ProductName",1)