特别说明哦：我的日志都是自己写的，完全是自己写的，完全没有转载的，可别以为我是转载的哦
这里的文章除特别说明的以外，全部使用《知识共享 署名-非商业性使用-相同方式共享 2.5 中国大陆许可协议》进行许可，不可以盗用哦

来来，位图索引测试报告

大 | 中 | 小
[夜晚 2008年2月12日 23:25 | by gs ]

这个测试主要是对比DM和MS的性能，顺便看看DM给我们提供的位图索引有什么好处（据说OR也提供了位图索引）
好吧，话不多说，简单介绍一下位图索引，然后测试数据
位图索引据说一般是用于数据仓库的，也就是插入啊、删除啊、更新啊（也就是DML操作）等操作不多的场合，主要用于保存和检索大量数据。并且，字段中有很多的值是重复的，这样才适合使用位图索引。
DML操作频繁会导致索引变得非常巨大，字段中的重复值很少则位图索引的效率就很低。
因为上传不了xls文件，偶又懒得改程序，所以需要xls文件的就发邮件给偶
下面贴结果

DM 位图索引 与 MS B树索引
说明每组测试循环进行12轮查询，每轮查询重复进行10次查询，共进行5组
若没有说明，则达梦使用的是位图索引
第7个测试是用来与第1个测试比较的，可以明显可看出位图索引的作用
1SELECT * FROM Dv_Message WHERE incept='greensea'
表约有19万记录，其中greensea的记录有196条
DM(DSN)MS(DSN)
1125125218.75171.875140.6251156.25171.875187.5125171.875
2125125156.25109.375109.3752109.375156.25140.625156.25140.625
3125125171.875109.3751253109.375140.625156.25156.25187.5
4109.375109.3751251251254140.625171.875187.5203.125171.875
5109.375109.375125156.25109.3755140.625156.25234.375187.5109.375
6140.625109.375125140.6251256156.25171.875218.75187.5125
7109.375125109.3751251257156.25265.625218.75171.875203.125
8125109.375109.3751251258156.25218.75218.75187.5156.25
9125187.5140.6251251259156.25218.75203.125187.5171.875
10109.375171.87512512512510156.25203.125187.5187.5140.625
11125109.375125140.625140.62511250156.25140.625203.125125
12109.375109.375125140.625156.2512187.5234.375218.75156.25140.625
AVG119.792 126.302 138.021 132.813 127.604 AVG156.250 188.802 192.708 175.781 153.646
TotalAVG128.906 AVEDVE14.713541667TotalAVG173.438 AVEDVE28.385416667
2SELECT * FROM Dv_Message WHERE incept='雪花'
表约有19万记录，其中雪花的记录有6条
DM(DSN)MS(DSN)
162.593.7593.7562.5109.375146.87531.2546.87531.2531.25
2109.37562.578.12593.75109.375278.12546.87546.87546.87531.25
378.12578.12578.12546.875125346.87531.2531.2546.87562.5
462.578.12578.12578.12593.75446.87531.2546.87546.87546.875
578.12578.12578.12578.12578.125546.87531.2562.531.2546.875
693.7562.5109.375171.87578.125646.87546.87562.546.87531.25
7156.2562.593.75171.87562.5762.531.2546.87531.2531.25
8156.2562.562.593.7562.5862.531.2531.2546.87531.25
9109.37578.12562.578.12578.125946.87531.2546.87531.2546.875
10140.62562.562.562.593.751062.546.87562.531.2546.875
11109.37562.593.7593.7562.51146.87531.2562.531.2546.875
1262.578.12578.12578.12578.1251246.87531.2562.531.2546.875
AVG101.563 71.615 80.729 92.448 85.938 AVG53.385 35.156 50.781 37.760 41.667
TotalAVG86.458 AVEDVE20.451388889TotalAVG43.750 AVEDVE9.5833333333
3SELECT * FROM Dv_Message WHERE incept='昔小樱'
表约有19万记录，其中昔小樱的记录有208条
DM(DSN)MS(DSN)
1125156.25218.75140.625140.6251187.5187.5187.5156.25187.5
2125109.375140.625187.5140.6252171.875156.25140.625140.625156.25
3109.375140.625109.375109.3751253218.75125140.625156.25171.875
4171.875171.875125125187.54234.375156.25218.75125156.25
5250140.625156.2593.751255234.375171.875171.875203.125187.5
6187.5125109.375109.375140.6256187.5203.125296.875187.5156.25
7187.5125125109.3751257140.625140.625156.25203.125234.375
8156.25125109.375109.3751258156.25140.625140.625218.75171.875
9156.2593.7512593.751259281.25140.625140.625203.125218.75
10140.625156.2593.75109.375156.2510203.125140.625140.625203.125359.375
11187.5125109.37593.75296.87511171.875140.625187.5171.875359.375
12140.62512512512512512171.875203.125140.625140.625250
AVG161.458 132.813 128.906 117.188 151.042 AVG196.615 158.854 171.875 175.781 217.448
TotalAVG138.281 AVEDVE26.432291667TotalAVG184.115 AVEDVE35.859375
4SELECT COUNT(*) FROM Dv_Message WHERE incept='greensea'
表约有19万记录，其中greensea的记录有196条
DM(DSN)MS(DSN)
193.7593.75140.62578.12593.75131.2531.2531.2531.2531.25
2203.125203.12578.12593.7593.75246.87562.531.2531.2546.875
3156.25171.87562.578.12562.5346.87546.87546.87546.87546.875
412512512562.578.125446.87546.87531.2546.87546.875
593.7593.7512593.7562.5546.87546.87546.87546.87546.875
6140.62578.125156.2512593.75646.87531.2562.546.87546.875
7187.578.125187.593.75125762.546.87562.531.2546.875
862.593.7562.562.5109.375846.87546.87562.546.87546.875
912512593.7578.12578.125978.12531.2546.87531.2546.875
10125109.37512593.7562.51046.87546.87562.531.2546.875
1193.7593.7562.5109.37578.1251146.87531.2546.87531.2562.5
1293.75109.37512593.7578.1251246.87546.87546.87531.2531.25
AVG125.000 114.583 111.979 88.542 84.635 AVG49.479 42.969 48.177 37.760 45.573
TotalAVG104.948 AVEDVE28.020833333TotalAVG44.792 AVEDVE7.6736111111
5SELECT * FROM Dv_Message WHERE incept='樱の雪梦'
表约有19万记录，其中雪花的记录有1条
DM(DSN)MS(DSN)
162.562.578.12578.12578.125162.531.2546.87531.2546.875
278.12593.7578.12593.7578.125231.2546.87531.2546.87531.25
378.12578.12593.7512578.125331.2531.2531.2546.87546.875
462.578.12578.12593.7578.125431.2546.87531.2546.87531.25
562.578.12562.578.12578.125531.2531.2546.87546.87546.875
662.562.593.75125109.375631.2531.2531.2546.87531.25
778.12593.75171.87593.75109.375746.87546.87531.2531.2546.875
862.5156.25156.2578.12578.125831.2546.87531.2546.87531.25
978.12578.12593.7593.75109.375931.2546.87531.2546.87531.25
1078.12512578.12562.593.751031.2546.87531.2531.2531.25
1162.578.12578.12578.125140.6251131.2531.2531.2546.87546.875
1262.562.5140.62562.5109.3751246.87531.2546.87562.546.875
AVG69.010 87.240 100.260 88.542 95.052 AVG36.458 39.063 35.156 44.271 39.063
TotalAVG88.021 AVEDVE19.305555556TotalAVG38.802 AVEDVE8.3072916667
6SELECT COUNT(*) FROM Dv_Message WHERE incept='樱の雪梦'
表约有19万记录，其中雪花的记录有1条
DM(DSN)MS(DSN)
162.562.5156.2562.562.5131.2546.87531.2531.2546.875
278.125109.375140.62593.7562.5231.2531.2515.62515.62531.25
362.578.12512593.7562.5331.2531.2546.87546.87546.875
478.12578.125125140.62562.5446.87531.2531.2531.2546.875
578.12562.5125187.578.125515.62531.2531.2531.2531.25
662.562.5140.625171.87562.5631.2546.87531.2531.2546.875
7109.37562.578.125109.37562.5731.2531.2531.2531.2531.25
862.593.7562.5156.25281.25846.87515.62531.2531.2531.25
962.578.12562.578.12578.125915.62531.2531.2531.2531.25
1078.12562.593.7578.12562.51046.87531.2515.62546.87531.25
1178.12578.12578.12562.578.1251131.2531.2531.2531.2531.25
1278.12578.12578.12562.578.1251246.87546.87546.87531.2531.25
AVG74.219 75.521 105.469 108.073 85.938 AVG33.854 33.854 31.250 32.552 36.458
TotalAVG89.844 AVEDVE27.864583333TotalAVG33.594 AVEDVE6.640625
7SELECT * FROM Dv_Message WHERE incept='greensea'
表约有19万记录，其中greensea的记录有196条
DM使用B+树索引
DM(DSN)MS(DSN)
1156.25140.625203.125156.25187.51187.5156.25171.875234.375187.5
2328.125109.375171.875125187.52218.75140.625156.25156.25140.625
3234.375109.375156.25125234.3753203.125140.625156.25234.375187.5
4203.125156.25171.875125234.3754218.75125265.625187.5234.375
5171.875171.875328.125140.625171.8755234.375187.5218.75187.5140.625
6125156.25156.25218.75140.6256140.625218.75171.875250203.125
7265.625140.625140.625218.75140.6257187.5171.875187.5218.75203.125
8171.875125156.25187.5140.6258171.875203.125218.75203.125140.625
9187.5171.875156.25140.625171.8759187.5234.375171.875156.25187.5
10156.25187.5171.875171.875218.7510156.25218.75187.5171.875156.25
11125109.375203.125171.875171.87511156.25140.625171.875171.875171.875
12218.75125203.125171.875109.37512140.625140.625171.875140.625187.5
AVG195.313 141.927 184.896 162.760 175.781 AVG183.594 173.177 187.500 192.708 178.385
TotalAVG172.135 AVEDVE32.647569444TotalAVG183.073 AVEDVE26.970486111
几个结论MS的全表扫描比DM快
MS的COUNT比DM快
其他的就不说了，大家可以慢慢看还能对比出什么信息
位图索引的作用也显而易见了，所以也不说了
当然，人均只有6条短信，而且DML操作频繁，可不能做位图索引，用B树索引才是正确的



请给这篇日志评个分吧！
  • Currently -0.5/9
  • 1
    -4
  • 2
    -3
  • 3
    -2
  • 4
    -1
  • 5
    0
  • 6
    1
  • 7
    2
  • 8
    3
  • 9
    4

现在的分数: -0.5 分，共2个评分

Tags: , , , ,
网页开发 | 评论(0) | 引用(0) | 阅读(708)