性能优化技巧 - 多层排号键

排号键是SPL独特的数据类型,适合替代多层次、各层不连续的键值,比如身份证号、合同编号、产品编号、组织机构代码等。排号键定位速度快,常用于优化内存索引查询和外键关联计算。

内存索引查询

cardNormal.btx是集文件格式的身份证信息表,数据量一百万条,字段为:cardNo(身份证,主键),name(姓名),gender(性别),province(省份),email(电子邮件),mobile(移动电话),address(住址)。cardK.btxcardNormal.btx在结构和数据上完全相同,唯一的区别在于cardNo字段是排号键。

本案例cardNormal.btxcardK.btx分别执行百万次索引查询,并比较两者性能。

其中cardNo是简化后的身份证,格式如下:

位数

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

规则

行政区划

生日

流水

校验

细则

地区

流水

校验

范例

1

0

0

3

1

9

8

0

1

2

1

3

0

2

3

x

省、地区:各自取值范围为1-10

生日:取值为"1980-06-01""1981-01-01"

流水:取值为1-100

校验:根据前15位计算出的冗余校验位,取值为010,其中10x来表示。

将上述身份证转为排号键,可采取如下思路:

1.      省、地区:排号键的每一层只支持1-255的整数,因此将省和地区分别转为整数,作为第12层排号键。

2.      生日:排号键以1为起始,可以取得更好的性能,因此算出原生日和1980-06-01的间隔天数,作为第3层排号键。

3.      流水:转为整数,作为第4层的排号键。

4.      校验位:冗余数据,可删除。

具体转换脚本如下:

k(int(mid(cardNo,1,2)),

int(mid(cardNo,3,2)),

interval("1980-06-01",date(mid(cardNo,5,8),"yyyyMMdd")),

int(mid(cardNo,13,3))

)

下面对cardNormal.btxcardK.btx分别执行百万次索引查询。


A

B

C

1

=cardNormal=file("d:\\temp\\cardNormal.btx").import@b().keys(cardNo).index()

/cardNormal.btx读入内存

2

=paramList=cardNormal.(cardNo).sort(rand()).to(100000)

/随机取1万个身份证

3




4

=now()



5

for 100

=paramList.(cardNormal.find(~))

/查询百万次

6

=interval@ms(A4,now())


/字符串键性能:5537ms

7




8

=cardK=file("d:\\temp\\cardk.btx").import@b().keys(cardNo).index@s()


/cardk.btx读入内存

9

=paramListK=paramList.(k(int(mid(~,1,2)),   int(mid(~,3,2)), interval("1980-06-01",date(mid(~,5,8),"yyyyMMdd")),   int(mid(~,13,3)) ))


/将字符串参数转为排号键参数

10

=now()



11

for 100

=paramListK.(cardK.find(~))

/查询百万次

12

=interval@ms(A10,now())


/排号键性能:1977ms

A8:对排号键建立内存哈希索引,需使用函数选项@s

可以看到,对字符串键建立索引,查询需耗费5547毫秒,而排号键只需1977毫秒,后者明显快。

 外键关联查询

taxNormal.btx是集文件格式的报税信息表,数据量一千万条,字段为:serial(主键,流水号),cardNo(外键,身份证),tax(报税额),area(所属地区),declaretype(申报类型),unit(申报单位),declareTime(申报时间),network(办理网点)。taxK.btx taxNormal.btx在结构和数据上完全相同,唯一的区别在于cardNo字段是排号键。

本案例算法:

1.       taxNormal.btxcardNormal.btx进行外键关联计算,排除操作系统缓存和硬盘IO的影响,只取关联动作消耗的时间。

2.       taxK.btxcardK.btx进行外键关联计算,排除操作系统缓存和硬盘IO的影响,只取关联动作消耗的时间。

3.       比较12的差异。

具体算法如下:


A

B

C

1

=file("d:\\temp\\taxNormal.btx").cursor@b()

for A1,10000

/打开报税表,预遍历游标

2

=A1.reset()


/重置游标到起点

3

=file("d:\\temp\\cardNormal.btx").import@b().keys(cardNo).index()


/打开身份证表

4

=now()



5

for A1,10000


/正式遍历游标

6

=interval@ms(A4,now())

=A1.reset()

/遍历游标耗时3748ms

7

=A1.switch(cardNo,A3:cardNo)

for A1,10000

/建立关联,预遍历游标

8

=A1.reset()


/重置游标到起点

9

=now()



10

for A7,10000


/正式遍历关联游标

11

=interval@ms(A9,now())

=A11-A6

/ 建立关联耗时:7553ms

12

/上面:字符串键关联。下面:排号键关联



13

=file("d:\\temp\\taxK.btx").cursor@b()

for A13,10000

/打开报税表,预遍历游标

14

=A13.reset()


/重置游标到起点

15

=file("d:\\temp\\cardK.btx").import@b().keys(cardNo).index@s()


/打开身份证表

16

=now()



17

for A13,10000


/正式遍历游标

18

=interval@ms(A16,now())

=A13.reset()

/遍历游标耗时2884ms

19

=A13.switch(cardNo,A15:cardNo)

for A19,10000

/建立关联,预遍历游标

20

=A19.reset()


/重置游标到起点

21

=now()



22

for A19,10000


/正式遍历关联游标

23

=interval@ms(A21,now())

=A23-A18

/建立关联耗时:966ms

B2B7B13B19:预遍历游标,避免操作系统缓存的影响。遍历之后需用reset函数重置游标,以便后续再次遍历。

B11B23:用总时间减去遍历时间,获得关联动作消耗的时间。

可以看到,用普通键做外键关联计算,其关联动作耗时7553ms;排号键做外键关联计算时,只需耗时966ms,后者明显快。而且可以看出,使用排号键读出记录(fetch)的时间也少于使用字符串(其它字段和记录行数都相同),说明排号键对象的生成性能更好。