KingbaseES 索引unusable的使用
前言
KingbaseES中，索引不可用原则介绍
oracle数据库中，如果索引不可用(unusable)，在进行DML操作时，会触发报错:索引不可用。
在KES中如果设置索引不可用，插入数据不会报错，因为当索引被置为不可用状态后，如果要重新启动索引需要rebuild索引，此时会在新索引中重新组织表tuple数据。
insert into t1 select generate_series(1,10000) ;
create index iex on t1(id);
test=# explain analyze select * from t1 where id=50;
QUERY PLAN
Index Only Scan using iex on t1 (cost=0.29..8.30 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: (id = 50)
Heap Fetches: 1
Planning Time: 0.177 ms
Execution Time: 0.035 ms
(5 rows)
查看dba_indexes视图，索引状态是valid。
test=# select * from dba_indexes where index_name='IEX';
-[ RECORD 1 ]-----------+----------------------------
owner | SYSTEM
index_name | IEX
index_type | BTREE
table_owner | SYSTEM
table_name | T1
table_type | TABLE
uniqueness | NONUNIQUE
compression | DISABLED
prefix_length | 0
tablespace_name | database default tablespace
ini_trans |
max_trans |
initial_extent |
next_extent |
min_extents |
max_extents |
pct_increase |
pct_threshold |
include_column |
freelists |
freelist_groups |
pct_free |
logging | YES
blevel |
leaf_blocks |
distinct_keys |
avg_leaf_blocks_per_key |
avg_data_blocks_per_key |
clustering_factor |
status | VALID
num_rows |
alter index iex unusable;
此时索引状态变为UNUSABLE。
test=# select * from dba_indexes where index_name='IEX';
-[ RECORD 1 ]-----------+----------------------------
owner | SYSTEM
index_name | IEX
index_type | BTREE
table_owner | SYSTEM
table_name | T1
table_type | TABLE
uniqueness | NONUNIQUE
compression | DISABLED
prefix_length | 0
tablespace_name | database default tablespace
ini_trans |
max_trans |
initial_extent |
next_extent |
min_extents |
max_extents |
pct_increase |
pct_threshold |
include_column |
freelists |
freelist_groups |
pct_free |
logging | YES
blevel |
leaf_blocks |
distinct_keys |
avg_leaf_blocks_per_key |
avg_data_blocks_per_key |
clustering_factor |
status | UNUSABLE
执行计划已经不再使用索引。
test=# explain analyze select * from t1 where id=50;
QUERY PLAN
Seq Scan on t1 (cost=0.00..180.10 rows=1 width=4) (actual time=0.013..0.521 rows=1 loops=1)
Filter: (id = 50)
Rows Removed by Filter: 10007
Planning Time: 0.042 ms
Execution Time: 0.535 ms
(5 rows)
但是插入数据仍然成功
test=# insert into t1 values(434);
INSERT 0 1
alter index iex REBUILD;
再次查看dba_indexes，索引恢复可用状态
在实际生产系统中应该注意对大表的rebuild操作耗时过长，会影响业务进行。
