文章 Hao Ma · 三月 22 阅读大约需 4 分钟 IRIS/Caché SQL优化经验分享 - Collation(排序规则) 这个帖子内容有点深。如果您读的有困难,请直接跳过这篇,对绝大多数IRIS/Caché使用者,它一点都不重要。 数据库表的Collation(排序规则)本来是一个非常简单的概念。说到它是因为曾经发现过由Collation引起的性能问题。 我试图用一句话来解释数据库的排序规则: 绝大多数数据库因为业务查询需要,保存的字符型数据是不分大小写的。当你执行一个 order by, group by, distinct,like等等条件查询时,因为这个不分大小写的collation,你得到的结果也不分大小写。例如,对名字做group by, James, james一定是在一组。 如果非要区分大小写,会在查询的时候使用一个函数 因为要操作非英语的字符集,以及可以被当作字符看待的数字类型,适应不同的排序规则,一个数据库可能有很多种Collation类型。 很简单,在表一级定义Collation的SQL语句是: #SQL #新手 #InterSystems IRIS 0 0 0 17
文章 Hao Ma · 三月 21 阅读大约需 1 分钟 IRIS/Caché SQL优化经验分享 - Bitmap Extent Bitmap索引是指对某个,或者某几个字段建立的bit map(位图映射)。如果是对整个表的记录,也就是表的%ID做位图映射,得到的特殊的bitmap索引在IRIS/Caché里被称为Bitmap Extent。 建立Bitmap Extent索引的目的就是加快COUNT(*)的执行。提高了多少呢? 下面两个显示的是最简单的全表查询花费的时间: 不使用Bitmap Extent : 1.3810s 使用Bitmap Extent: 0.0038 相差有几百倍。 #SQL #新手 #InterSystems IRIS 0 0 0 10
文章 Hao Ma · 三月 20 阅读大约需 2 分钟 IRIS/Caché SQL优化经验分享 - 复合索引的使用 复合索引(combined index)也被称为组合索引或者联合索引,顾名思义,就是一个索引建立在多个字段上。当用这些字段为条件查询时,相比对每个字段单独做索引,复合索引能给出很好的性能,还能减少索引的数量。 为什么能减少索引的数量? 通常来说,也就是在其他数据库,联合索引符合”最左匹配“的原则。在BING上搜索“复合索引,得到的第一个搜索结果的这篇文章就说的就很简单明了: 下面这个SQL语句在 列X,列Y,列Z 上建立了一个复合索引。 mysql CREATE INDEX 索引名 ON 表名(列名X, 列名Y, 列名Z); 其实这相当于建立了三个索引,分别是: #SQL #新手 #InterSystems IRIS 0 0 0 18
文章 Hao Ma · 三月 19 阅读大约需 4 分钟 IRIS/Caché SQL优化经验分享 - Bitmap(位图)索引 正确的使用Bitmap Index (位图索引)来代替普通索引,可以成百上千倍的提高SQL查询性能。 先来看看Bitmap索引和普通索引的区别。我来在Patient表的Sex字段上创建两个索引 idxSex: 普通索引 bidxSex: bitmap索引 然后创建10个病人数据,查看索引的内容: #SQL #新手 #InterSystems IRIS 0 0 0 16
文章 Hao Ma · 三月 19 阅读大约需 3 分钟 IRIS/Caché SQL优化经验分享 - 检查索引的完整性 Caché/IRIS的特点是运行Global的修改,而这个修改和SQL是无关的,因此非常容易出现数据库表数据完整性的问题,也就是表中的数据是不是符合定义的表约束。 这样的情况非常常见。有些是人为的对Global的错误修改, 有些是应用系统的事务性管理写的不对,造成事务回滚的时候破坏了索引的完整性。无论什么原因,只要使用Global操作,破坏SQL的完整性非常难以避免。结果就是SQL查询给出错误结果。 最简单的解决方法就是执行“索引检查(Validate Indices)" 我们来做个实验 - 先修改一个global: 如下图, 将Patient表的一个记录的SEX字段,从'M'改到‘F'. 运行索引检查, 结果会提示您问题在什么地方。 #SQL #新手 #InterSystems IRIS 1 0 0 6
文章 Hao Ma · 三月 19 阅读大约需 3 分钟 IRIS/Caché SQL优化经验分享 - SQL优化器使用的统计数据 上个帖子写了TuneTable的执行, 提到了SQL优化器使用的那些统计数据, 这里逐一的介绍一下这些统计项。了解它们看懂和分析SQL执行计划的基础。 如果您不需要做单个查询的优化工作,可以调过这部分内容。 表的统计项 Extent Size: 表的大小,也就是记录数。在执行多表关联(JOIN)的查询时,SQL优化器会根据Extent Size值,从数据量最小的表来开始执行查询。 您还需要了解:表创建的时候Extent Size会获得一个初始值,而之后的插入修改数据并不自动修改这个值。而只有执行TuneTable才会修改这个。 这也就是为什么没有执行过TuneTable的数据库SQL性能好不了的原因。下图中的Patient表,可以看出有1,000,000记录 #SQL #新手 #InterSystems IRIS 0 0 0 16
文章 Hao Ma · 三月 19 阅读大约需 2 分钟 IRIS/Caché SQL优化经验分享 IRIS/Caché查询慢,主要原因有以下几个: 应用是一个事务型的数据库, 数据模型的设计不适合某些复杂的分析查询 这是慢的原因,不是慢的离谱的原因。数据模型是产品设计的范畴, 这里不讨论, 本文只讨论优化。 历史原因,有些表的索引不够优化 虽然还是设计问题,但可以在实施中或者维护中给出优化方案。 产品运行中的问题造成的查询效率下降 IRIS/Caché数据平台的一个特点是允许跳过SQL约束,对底层数据的直接修改。坏的代码或者应用可能破坏表数据和表索引的约束,造成SQL性能的下降。维护人员应该知道怎么避免,和处理这样的问题。 维护工作缺乏造成 比如Tune Table(调整表), 这是必须做的工作,但可惜很有些项目没有执行过。 #SQL #新手 #InterSystems IRIS 0 0 0 17
文章 Hao Ma · 三月 18 阅读大约需 4 分钟 IRIS/Caché SQL优化经验分享 - Tune Table TuneTable(调整表)收集数据库中表的统计信息,用来为SQL引擎制定最优的执行计划。在其他数据库产品里,这个动作被称为“gather stats job"或者类似的名字,相比较TuneTable不是那么直白,但作用是一样的。 TuneTable是否要人工执行 一定要。 在IRIS 2023版本, 第一次加入了TuneTable的自动执行功能,在此之前的所有IRIS/Caché版本, 如果没有人工执行TuneTable, SQL引擎无法保证给出最好的查询计划。 即使是IRIS2023有了自动执行功能,也还需要人工执行TuneTable的操作,后面解释。 #SQL #新手 #InterSystems IRIS 1 0 0 20
文章 Hao Ma · 三月 6 阅读大约需 2 分钟 使用USEEXTENTSET减小Journal大小 [翻译文章:大型数据集的存储注意事项]这篇文章其实很有用, 但恐怕注意的人不多,特意写文章介绍一下。 USEEXTENTSET翻译过来就是"使用EXTENT集合“,不够直白,它的工作简单说就是:通过哈希类名,使用更短的Global名字。 #对象数据模型 #新手 #Ensemble #InterSystems IRIS 0 0 0 12