1. 问题
有时候查询中会带有多个字段的查询条件,但是其中任何单个字段的选择率都不高,但是多个字段组合起来却有比较好的选择率。这种场景是bitmap索引大显身手的地方,但是bitmap索引对更新性能的影响相当大,不适合OLTP场景。PG不支持bitmap索引,但是有一个临时的内存中的类似bitmap索引的东西,叫“Bitmap Index Scan”。
除了Bitmap Index Scan,多字段索引也是一种选择,但是两种方法的性能有没有差异呢?下面作个测试看看。
2. 测试环境
测试环境在一个PC的虚拟机上
宿主机
CPU:AMD Athlon II X4 640 3.0GHz
MEM:6G
OS:Win7 64bit
虚拟机所在存储:Apacer A S510S 128GB
虚拟机
CPU:4 core
MEM: 2G
OS:CentOS release 6.5 (Final)
PostgreSQL:9.3.4(shared_buffers = 128MB,其它都是默认值)
3. 测试数据
postgres= # create table tb1( c1 int, c2 int) ;
CREATE TABLE
postgres= # insert into tb1 select round ( random( ) * 100) , round ( random( ) * 1000) from generate_series( 1, 10000000) ;
INSERT 0 10000000
postgres= # select pg_size_pretty( pg_table_size( 'tb1' ) ) ;
pg_size_pretty
----------------
346 MB
( 1 row )
4. 性能对比测试
4.1 全表扫描
postgres= # \ timing
Timing is on .
postgres= # select count ( * ) from tb1 where c1= 99 and c2= 999;
count
-------
92
( 1 row )
Time: 1376. 393 ms
4.2 c1单索引扫描
postgres= # create index tb1_idx1 on tb1( c1) ;
CREATE INDEX
Time: 65308. 131 ms
postgres= # select pg_size_pretty( pg_relation_size( 'tb1_idx1' ) ) ;
pg_size_pretty
----------------
214 MB
( 1 row )
Time: 2. 659 ms
postgres= # explain ( analyze, buffers) select count ( * ) from tb1 where c1= 99 and c2= 999;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate ( cost= 47675. 20. . 47675. 21 rows = 1 width= 0) ( actual time= 1267. 921. . 1267. 921 rows = 1 loops= 1)
Buffers: shared read= 39978
- > Bitmap Heap Scan on tb1 ( cost= 1891. 96. . 47674. 95 rows = 99 width= 0) ( actual time= 40. 726. . 1267. 631 rows = 92 loops= 1)
Recheck Cond: ( c1 = 99)
Rows Removed by Index Recheck: 7363157
Filter: ( c2 = 999)
Rows Removed by Filter: 99794
Buffers: shared read= 39978
- > Bitmap Index Scan on tb1_idx1 ( cost= 0. 00. . 1891. 93 rows = 102333 width= 0) ( actual time= 30. 829. . 30. 829 rows = 99886 loops= 1)
Index Cond: ( c1 = 99)
Buffers: shared read= 276
Total runtime: 1267. 961 ms
( 12 rows )
Time: 1268. 916 ms
c1单索引的选择率只有1/100,对查询性能提升毫无作用。
4.3 c2单索引扫描
postgres= # drop index tb1_idx1 ;
DROP INDEX
Time: 41. 062 ms
postgres= # create index tb1_idx2 on tb1( c2) ;
CREATE INDEX
Time: 50313. 915 ms
postgres= # select pg_size_pretty( pg_relation_size( 'tb1_idx2' ) ) ;
pg_size_pretty
----------------
214 MB
( 1 row )
Time: 0. 915 ms
postgres= # explain ( analyze, buffers) select count ( * ) from tb1 where c1= 99 and c2= 999;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate ( cost= 23558. 69. . 23558. 70 rows = 1 width= 0) ( actual time= 74. 215. . 74. 215 rows = 1 loops= 1)
Buffers: shared hit= 1948 read= 6954
- > Bitmap Heap Scan on tb1 ( cost= 180. 85. . 23558. 45 rows = 99 width= 0) ( actual time= 5. 462. . 73. 883 rows = 92 loops= 1)
Recheck Cond: ( c2 = 999)
Filter: ( c1 = 99)
Rows Removed by Filter: 9800
Buffers: shared hit= 1948 read= 6954
- > Bitmap Index Scan on tb1_idx2 ( cost= 0. 00. . 180. 82 rows = 9652 width= 0) ( actual time= 2. 414. . 2. 414 rows = 9892 loops= 1)
Index Cond: ( c2 = 999)
Buffers: shared read= 30
Total runtime: 74. 298 ms
( 11 rows )
Time: 76. 732 ms
c1单索引的选择率有1/1000,性能提升很明显。
4.4 c1+c2索引bitmap扫描
postgres= # create index tb1_idx1 on tb1( c1) ;
CREATE INDEX
Time: 56792. 281 ms
postgres= # explain ( analyze, buffers) select count ( * ) from tb1 where c1= 99 and c2= 999;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
------
Aggregate ( cost= 2456. 74. . 2456. 75 rows = 1 width= 0) ( actual time= 50. 347. . 50. 348 rows = 1 loops= 1)
Buffers: shared hit= 6587 read= 343
- > Bitmap Heap Scan on tb1 ( cost= 2073. 06. . 2456. 49 rows = 99 width= 0) ( actual time= 39. 969. . 50. 312 rows = 92 loops= 1)
Recheck Cond: ( ( c2 = 999) AND ( c1 = 99) )
Rows Removed by Index Recheck: 7291
Buffers: shared hit= 6587 read= 343
- > BitmapAnd ( cost= 2073. 06. . 2073. 06 rows = 99 width= 0) ( actual time= 37. 864. . 37. 864 rows = 0 loops= 1)
Buffers: shared hit= 30 read= 276
- > Bitmap Index Scan on tb1_idx2 ( cost= 0. 00. . 180. 82 rows = 9652 width= 0) ( actual time= 3. 091. . 3. 091 rows = 9892 loops= 1)
Index Cond: ( c2 = 999)
Buffers: shared hit= 30
- > Bitmap Index Scan on tb1_idx1 ( cost= 0. 00. . 1891. 93 rows = 102333 width= 0) ( actual time= 33. 030. . 33. 030 rows = 99886 loo
ps= 1)
Index Cond: ( c1 = 99)
Buffers: shared read= 276
Total runtime: 50. 484 ms
( 15 rows )
Time: 52. 287 ms
c1+c2索引bitmap扫描性能进一步提升。(这个性能提升也可能仅仅是因为buffers的hit命中率提升)
4.5 多字段索引扫描
postgres= # create index tb1_idx3 on tb1( c1, c2) ;
CREATE INDEX
Time: 67824. 333 ms
postgres= # select pg_size_pretty( pg_relation_size( 'tb1_idx3' ) ) ;
pg_size_pretty
----------------
214 MB
( 1 row )
Time: 0. 835 ms
postgres= # explain ( analyze, buffers) select count ( * ) from tb1 where c1= 99 and c2= 999;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate ( cost= 389. 13. . 389. 14 rows = 1 width= 0) ( actual time= 0. 580. . 0. 580 rows = 1 loops= 1)
Buffers: shared hit= 92 read= 3
- > Bitmap Heap Scan on tb1 ( cost= 5. 45. . 388. 89 rows = 99 width= 0) ( actual time= 0. 298. . 0. 566 rows = 92 loops= 1)
Recheck Cond: ( ( c1 = 99) AND ( c2 = 999) )
Buffers: shared hit= 92 read= 3
- > Bitmap Index Scan on tb1_idx3 ( cost= 0. 00. . 5. 42 rows = 99 width= 0) ( actual time= 0. 279. . 0. 279 rows = 92 loops= 1)
Index Cond: ( ( c1 = 99) AND ( c2 = 999) )
Buffers: shared read= 3
Total runtime: 0. 651 ms
( 9 rows )
Time: 3. 912 ms
由此可见,本例中,多字段索引的效率相当高。
5. 其它
5.1 多字段索引的适用条件
http://postgres.cn/docs/9.3/indexes-multicolumn.html
------------------------------------------------------------------
一个多字段的 B-tree 索引可以用在包含索引字段子集的查询条件里,不过, 如果在前导字段(最左边)上有约束条件,那么效率最高。准确的规则是前导字段上的等于约束, 加上第一个没有等于约束的非等于约束字段,将用于限制所扫描的索引范围。 将检查这两个字段右边字段上的索引以减少对表的访问,但是并不减少需要扫描的索引。比如, 假如我们有一个在(a, b, c)上的索引,查询条件是WHERE a = 5 AND b >= 42 AND c < 77, 那么索引就需要先扫描所有a= 5 且b= 42 ,直到所有a = 5 的记录扫描完毕。那些c >= 77的索引条目将被忽略,但是他们仍然会被扫描。 这个索引原则上仍然会被用于那些在b和/或c上有约束, 但是在a上没有约束的查询,但是就必须扫描整个索引了。因此,在大多数这种情况下, 优化器会选择顺序扫描表,而不使用索引。
------------------------------------------------------------------
5.2 多字段索引的原理
b-tree多字段索引的原理很简单,就是把多个字段,按定义索引时的先后顺序排序,所以越靠前的字段越重要。
下面是b-tree多字段索引条目内容的一个例子。
postgres= # \ d tb1
Table "public.tb1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"tb1_idx2" btree ( id, name)
postgres= # insert into tb1 values ( 1, 'aaaa' ) ;
INSERT 0 1
postgres= # create extension pageinspect;
CREATE EXTENSION
postgres= # SELECT * FROM bt_page_items( 'tb1_idx2' , 1) ;
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------------------------------
1 | ( 0, 5) | 24 | f | t | 01 00 00 00 0b 61 61 61 61 00 00 00 00 00 00 00
( 1 row )
postgres= # select 'a' : : bytea;
bytea
-------
\ x61
( 1 row )
运维网声明
1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网 享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com