kingbase数据库 SQL优化——索引

使用索引¶

索引是一种有序的存储结构，也是一项极为重要的SQL优化手段，可以提高数据检索的速度。通过在表中的一个或多个列上创建索引，很多SQL语句的执行效率可以得到极大的提高。

如图所示，使用索引根据索引仅读取想要查找的块，从而减少代价。

• 索引类型

• 索引使用技巧

• 使用索引建议工具

1. 索引类型¶

1.1. Btree索引¶

Btree是索引是最常见的索引类型，也是KingbaseES的默认索引，采用B+树(N叉排序树)实现，由于树状结构每一层节点都有序列，因此非常适合用来做范围查询和优化排序操作。Btree索引支持的操作符有 >，<，>=，<=，=，IN，LIKE等 ，同时，优化器也会优先选择Btree来对ORDER BY、MIN、MAX、MERGE JOIN进行有序操作。

精确匹配为类似：id = 1005，name =‘abc’之类的条件。而范围匹配则为类似：id < 1005，id > 10 and id <= 100等条件。

如果SQL语句仅访问被索引的列，则整个查询只需要通过索引即可完成，无需查找表数据。如果该语句还需要访问索引列以外的其他列数据，则会使用索引指向的tid来查找表中的行数据。

create table t1 (id int, info text);
insert into t1 values(generate_series(1,100000), md5(random()::text));
analyze;
explain analyze select * from t1 where id < 10;  --无索引

QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on T1  (cost=0.00..2084.00 rows=10 width=37) (actual time=0.024..20.771 rows=9 loops=1)
    Filter: (ID < 10)
    Rows Removed by Filter: 99991
Planning time: 0.216 ms
Execution time: 20.804 ms
(5 rows)



create index i_btree on t1 using btree(id);
explain analyze select * from t1 where id < 10;  --有btree索引

QUERY PLAN
----------------------------------------------------------------------------
Index Scan using I_BTREE on T1  (cost=0.29..8.45 rows=9 width=37)(actual time=0.006..0.011 rows=9 loops=1)
    Index Cond: (ID < 10)
Planning time: 0.250 ms
Execution time: 0.036 ms
(4 rows)



explain analyze select min(id) from t1;  --BTREE索引优化聚集函数min

QUERY PLAN
----------------------------------------------------------------------------
Result  (cost=0.33..0.34 rows=1 width=4) (actual time=0.144..0.144 rows=1 loops=1)
    InitPlan 1 (returns $0)
    Limit (cost=0.29..0.33 rows=1 width=4) (actual time=0.132..0.135 rows=1 loops=1)
        -> Index Only Scan using I_BTREE on T1 (cost=0.29..3691.29 rows=100000 width=4) (actual time=0.129..0.130 rows=1 loops=1)
            Index Cond: (ID IS NOT NULL)
            Heap Fetches: 1
Planning time: 0.309 ms
Execution time: 0.221 ms
(8 rows)

1.2. Hash索引¶

Hash索引查询效率比Btree高得多，相比Btree索引需要从根节点查询到叶子节点，且需要多次IO及CPU操作，Hash索引因其索引结构的特殊性，理想情况下仅需一次检索即可定位到数据。

与此同时，Hash也存在一定的局限性，只适合做等值查询。由于Hash索引是通过比较哈希值来做定位，因此当查询列上有较大比例的重复值时，会带来严重的哈希冲突，进而影响查询速度，这种情况下就不建议使用Hash索引。

create table t2 (id int, info text);
insert into t2 values(generate_series(1,100000), md5(random()::text));
analyze;
create index i_hash on t2 using hash(id);
explain analyze select * from t2 where id = 10;  --HASH索引

QUERY PLAN
----------------------------------------------------------------------------
Index Scan using I_HASH on T2  (cost=0.00..8.02 rows=1 width=37) (actual   time=0.013..0.014 rows=1 loops=1)
    Index Cond: (ID = 10)
Planning time: 0.167 ms
Execution time: 0.033 ms
(4 rows)



explain analyze select * from t2 where id < 10;  --HASH索引不支持非等值操作

QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on T2  (cost=0.00..2084.00 rows=10 width=37)  (actual time=0.015..26.223 rows=9 loops=1)
    Filter: (ID < 10)
    Rows Removed by Filter: 99991
Planning time: 0.070 ms
Execution time: 26.245 ms
(5 rows)

1.3. Bitmap索引(执行节点)¶

Bitmap索引是用一个位图来存放记录索引信息的结构。它用一个bit位来表示某个属性对应的值是否存在，存在为1，不存在为0，比较适合那种值比较单一（比如：性别字段）的情况。Bitmap索引有很大的压缩比空间，比较适合OLAP应用。

Kingbase在为where x=33 or x=44 or x= 55类似的查询创建查询计划的时候，将where过滤分解成3个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

Bitmap索引主要适用于当表具有很多属性且查询可能会涉及其中任意组合时的情况。

优化器为普通索引建立位图表，即Bitmap heap table，主要包括2种扫描方式：

• Bitmap Index Scan：用来在内存中创建一个位图表，每一个Bit表示一个与过滤条件有关的页面。Bit上有数据为1，不可能为0。通过位图扫描，能够快速确定某个值在哪些记录上存在与否。

• Bitmap Heap Scan：Bitmap上检索到的记录都对应其rowid，用这个rowid到关系表上去查找完整的记录信息并过滤。

Bitmap index/heap scan示例：

create table t1(id int);
create index idx_t1 on t1(id);
explain select * from t1 where id = 200;

QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.26..14.95 rows=13 width=4)
    Recheck Cond: (id = 200)
    -> Bitmap Inde