IRIS/Caché SQL优化经验分享 - Bitmap(位图)索引

正确的使用Bitmap Index (位图索引)来代替普通索引，可以成百上千倍的提高SQL查询性能。

先来看看Bitmap索引和普通索引的区别。我来在Patient表的Sex字段上创建两个索引

• idxSex: 普通索引
• bidxSex: bitmap索引

然后创建10个病人数据，查看索引的内容：

# 普通索引
^User.PatientI("idxSex"," F",1) =   ""
^User.PatientI("idxSex"," F",6) =   ""
^User.PatientI("idxSex"," F",8) =   ""
^User.PatientI("idxSex"," M",2) =   ""
^User.PatientI("idxSex"," M",3) =   ""
^User.PatientI("idxSex"," M",4) =   ""
^User.PatientI("idxSex"," M",5) =   ""
^User.PatientI("idxSex"," M",7) =   ""
^User.PatientI("idxSex"," M",9) =   ""
^User.PatientI("idxSex"," M",10)    =   ""

# bitmap索引
^User.PatientI("bidxSex"," F",1)    =   $zwc(407,2,1,6,8)/*$bit(2,7,9)*/
^User.PatientI("bidxSex"," M",1)    =   $zwc(413,2,0,1,6,8)/*$bit(3..6,8,10,11)*/

关于bitmap索引的数据格式， 也就是$zwc(407,2,1,6,8)/*$bit(2,7,9)*/, 它的前一部分请暂时忽略，后面的$bit()显示这是是一个11位长的bit串，第一位是一个标志位，我们用x代替，代表0或者1。 后面的10位对应10个病人，从ID=1到ID=10, 那么上面的值可以翻译成

^User.PatientI("bidxSex"," F",1)    =   [x,1,0,0,0,0,1,0,1,0,0]
^User.PatientI("bidxSex"," M",1)    =   [x,0,0,1,1,1,1,0,1,0,1,1]

这样，我们就了解了普通索引和位图索引的区别

• 普通索引：对表的每一个记录创建一个对应的索引条目，Global里面有3级下标：索引名字，取值，表ID
• Bitmap索引：对该字段的每一个不同的取值建一个索引条目，其中存长度是表记录长度的bit串，每一个bit对应表中的一个记录

知道了这些， 我们就基本清楚了Bitmap索引的特点和适用：

• Bitmap索引非常小

  上面的示例中Patient表有10条数据。普通索引有10条记录(这么说不准确，可以理解成一个节点下有10个子节点），而Bitmap索引只有2条。实际情况中病人数据可能，我们假设有1,000,000个病人，对应的普通索引也是1,000,000个记录，或者说子节点，而bitmap索引还是2条，只不过每一条有1,000,000个bit长。由于数据块的长度限制，这1,000,000个长的bit串会被切成64Kbit就是8KB大小的连续的块。

• Bitmap操作可以非常快

  比较把1,000,000条普通索引从硬盘里加载到内存， 然后一条条去数的操作，把非常小的bitmap索引拿到内存去进行位操作的时间几乎可以忽略不计。 在实践中， 通过用bitmap索引代替普通索引，曾经有过把一个复杂查询从几十秒减小到零点几秒的情况。

• Bitmap索引适用于数据选择性高的字段。

  文档上的说法是, 如果可能的取值大于10,000到20,000, 最好不要用Bitmap索引。理论上这和表的记录数，字段长度等等都有关系，没有个固定的门限值。而且，实践中很罕见您需要动脑子想某个字段要不要使用Bitmap索引。通常一个字段要不就是高度集中的，比如病人性别，就诊类型，科室，要不就是很分散的值。

  唯一需要斟酌的是日期字段。 如果只看分散程度，1年有365天，10年才365？天，似乎是可以使用bitmap索引。

  我们仔细算算帐。假设这个表10年有1,000,000记录，也就是每天300多条。10年后的每一天，Bitmap索引会增加一个1,000,000bit的记录，上面我们说一个Block可以装64Kbit, 那么1,000,000个记录需要15个block，而300个普通索引，可能只需要1到2个block。

  那么我们就得到一个结论：理论上日期字段不适合使用Bitmap索引；但如果不考虑长远，就为了短时间的查询性能提高，也不是不能用。

然后说说不能用和不合适用Bitmap索引的情况：

• IDKEY不是正整数

  最常见的不是正整数的IDKEY的场景是父子关系表中的子表。普通的非正整数IDKEY的表，还可以通过添加一个额外的正整数的KEY的方法来回避这个问题。而父子关系表，到目前为止，还是不能用bitmap索引。

  (如果您对数据模型的SQL性能有要求，坚决不要再使用父子关系表)

• 数据频繁插入删除的表

  举例说，如果表里只有一条记录，插入删除100次后, Bitmap索引需要100个bit来存储。又比如Ensemble中的消息表，通常要保留一个固定时间长度的消息，每天凌晨执行计划任务删除最陈旧的数据。结果就和上面的插入删除100次一样，其中的Bitmap索引会越来越长，性能日趋下降。

  如果您真的要在这样的表上做Bitmap索引，您需要创建任务，定时的清理其中的Bitmap索引。 细节请参考在线文档中的Maintaining Bitmap Indexes。