New post

查找

Article
· 36 min ago 3m read

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)
Discussion (0)1
Log in or sign up to continue
Discussion (0)1
Log in or sign up to continue
Article
· 1 hr ago 3m read

IRIS/Caché SQL优化经验分享 - SQL优化器使用的统计数据

上个帖子写了TuneTable的执行, 提到了SQL优化器使用的那些统计数据, 这里逐一的介绍一下这些统计项。了解它们看懂和分析SQL执行计划的基础。 如果您不需要做单个查询的优化工作,可以调过这部分内容。 

表的统计项

  • Extent Size: 表的大小,也就是记录数。在执行多表关联(JOIN)的查询时,SQL优化器会根据Extent Size值,从数据量最小的表来开始执行查询。

您还需要了解:表创建的时候Extent Size会获得一个初始值,而之后的插入修改数据并不自动修改这个值。而只有执行TuneTable才会修改这个。 这也就是为什么没有执行过TuneTable的数据库SQL性能好不了的原因。下图中的Patient表,可以看出有1,000,000记录

 

 

 

字段的统计项

请看下面的图

 

  • 选择性(Selectivity)

选择性取值可以是1或者一个百分数。取值为1说明这是个unique的字段,比如上图的ID, PatientNumber。 %表示的值,取值越高说明唯一性越低。比如上图中的Name的选择性是1.2987%,说明不是唯一值,有重复的姓名,但比例不高。 相反,Sex的选择性是50%, 说明只有两个取值。 

 

  • 离散值选择性Outlier Selectivity),

始用于Caché2014.1

离散值又称为异常值(outlier)是指一组测定值中与平均值的偏差超过两倍标准差的测定值,与平均值的偏差超过三倍标准差的测定值,称为高度异常的离散值。

因为Selectivity和数据分布没有关系, 所以需要Outlier Selectivity来弥补它的不足。。比如上图中字段Sex, 因为取值有' M‘,' F‘两个值, selectivity就是50%。而图中“离散值选择性”是99.9667%,后栏中”离散值“是‘M’。 说明在1,000,000病人中, 绝大多数都是男性。 这个数据是我造的,真实环境中很难发生。 但其他的情况很常见,比如一个河南的医院数据中, 注册病人中可能99%都是河南的地址,那么当查询中包括“where address='河南' 和另一个查询 “where address=‘海南’'',SQL优化器会根据”离散值选择性“给出不同的执行计划。

 

  • 平均字段大小(Average Field Size)

始于Cache2015.2 最后加入的统计指标,SQL引擎根据这个指标可以更好的计算查询计划中生成的临时文件的大小。

Map/索引的统计项

Map的意思是SQL表和硬盘数据之间的映射。Map有两种类型,Data/Master和索引,这里的统计项就是Block Count(块计数)

  • Block Count(块计数)

始用于Caché 2013.1。 简单说, 就是表中的数据或者索引占多大的硬盘空间。

比如上图显示的IDKEY的行,它的类型是Data/Master,也就是表数据的大小是13627个Block。加上其他的索引。 每个BlockCount块的大小是2KB(和数据库的块大小无关),那么这个表数据占用硬盘就是大约27MB,每个索引占用的大小依次类推。

如果一个查询有多个合用的索引,SQL引擎会初步根据不同索引的Block Count, 选择小的那个。

Block Count还对SQL引擎对涉及父子表的查询的处理非常有用。

Discussion (0)1
Log in or sign up to continue
Discussion (0)1
Log in or sign up to continue
Article
· 3 hr ago 2m read

IRIS/Caché SQL优化经验分享

IRIS/Caché查询慢,主要原因有以下几个:

  • 应用是一个事务型的数据库, 数据模型的设计不适合某些复杂的分析查询

        这是慢的原因,不是慢的离谱的原因。数据模型是产品设计的范畴, 这里不讨论, 本文只讨论优化。

  • 历史原因,有些表的索引不够优化

        虽然还是设计问题,但可以在实施中或者维护中给出优化方案。

  • 产品运行中的问题造成的查询效率下降

IRIS/Caché数据平台的一个特点是允许跳过SQL约束,对底层数据的直接修改。坏的代码或者应用可能破坏表数据和表索引的约束,造成SQL性能的下降。维护人员应该知道怎么避免,和处理这样的问题。 

  • 维护工作缺乏造成

 比如Tune Table(调整表), 这是必须做的工作,但可惜很有些项目没有执行过。

还有些其他暂时没想到的原因。我会在以下链接的帖子里和各位分享我的参与的一些知识和经验。这些经验是从一些SQL优化的工作中学到的,包括Caché 2010, 2016, IRIS, HealthConnect/Ensemble的项目。比如在最近的一个IRIS项目中, 我和另一个合作伙伴的工程师将IRIS 2021上的HIS数据库的100个SQL查询的平均查询时间从几十秒降低到几秒, 最慢的查询从50分钟降低到10几秒钟。 

这其中的经验有些适合用户的维护人员, 有些适合项目实施,DBA或者数据模型的修改和设计者。但无论您是什么角色,即便您没有机会执行其中的一些操作,但了解其中的基本原理总是好事。我尽量写的易读,涉及您无关的内容, 跳过去就好。 

我会一点点的更新内容, 多谢关注。

Discussion (0)1
Log in or sign up to continue