kingbase索引使用实践——btree

KingbaseES提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。 每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。
B-tree可以在可排序数据上的处理等值和范围查询。特别地，KingbaseES的查询规划 器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引:
< 、<= 、= 、>= 、>
将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。和oracle有所不同的是,kingbase的btree索引可以存储null值,因此 在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用,例如:

kingbase=# create table t1(id int);
CREATE TABLE
kingbase=# create index idx_t1 on t1 using btree(id);
CREATE INDEX
kingbase=# explain select * from t1 where id is null;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.26..14.92 rows=13 width=4)
   Recheck Cond: (id IS NULL)
   ->  Bitmap Index Scan on idx_t1  (cost=0.00..4.25 rows=13 width=0)
         Index Cond: (id IS NULL)
(4 行记录)

优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式 是一个常量且被固定在字符串的开头—例如:col LIKE ‘foo%’或者col ~ ‘^foo’, 但在col LIKE ‘%bar’上则不会。但是，如果我们的数据库没有使用C区域设置，我们需要创建一 个具有特殊操作符类的索引来支持模式匹配查询。同样可以将B- tree索引用于ILIKE和~*，但仅当模式以非字母字符开始，即不受大小写转换影响的字符。
当使用类型默认的index ops class时，仅适合于collate=”C”的查询（当数据库默认的lc_collate<>C时，索引和查询都需要明确指定collate “C”）。
索引、查询条件的collate必须一致才能使用索引。

kingbase=# create table test(id int, info text); 
CREATE TABLE
kingbase=# insert into test select generate_series(1,1000000),md5(random()::text);    
INSERT 0 1000000
kingbase=# create index idx on test(info collate "C");  
CREATE INDEX
kingbase=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like 'abcd%';
                        QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on PUBLIC.test  (cost=199.68..7809.06 rows=5000 width=36) (actual time=0.096..0.200 rows=19 loops=1)
   Output: id, info
   Filter: (test.info ~~ 'abcd%'::TEXT)
   Heap Blocks: exact=19
   Buffers: shared hit=19 read=3
   ->  Bitmap Index Scan on idx  (cost=0.00..198.43 rows=5000 width=0) (actual time=0.072..0.073 rows=19 loops=1)
         Index Cond: ((test.info >= 'abcd'::TEXT) AND (test.info < 'abce'::TEXT))
         Buffers: shared read=3
 Planning time: 0.318 ms
 Execution time: 0.247 ms
(10 行记录)

当数据库默认的lc_collate<>C时，还有一种方法让b-tree索引支持模糊查询。使用对应类型的pattern ops，使用pattern ops将使用字符的查询方式而非binary的搜索方式。

kingbase=# drop index idx;                                                                           
DROP INDEX
kingbase=# create index idx on test(info text_pattern_ops);   
CREATE INDEX
kingbase=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like 'abcd%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on PUBLIC.test  (cost=199.68..7809.06 rows=5000 width=36) (actual time=0.090..0.195 rows=19 loops=1)
   Output: id, info
   Filter: (test.info ~~ 'abcd%'::TEXT)
   Heap Blocks: exact=19
   Buffers: shared hit=19 read=3
   ->  Bitmap Index Scan on idx  (cost=0.00..198.43 rows=5000 width=0) (actual time=0.067..0.067 rows=19 loops=1)
         Index Cond: ((test.info ~>=~ 'abcd'::TEXT) AND (test.info ~<~ 'abce'::TEXT))
         Buffers: shared read=3
 Planning time: 0.482 ms
 Execution time: 0.241 ms
(10 行记录)

总结:
kingbase的btree索引适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。且支持存储null值,并且利用btree索引也能够实现前模糊、后模糊查询的功能