KingbaseES的索引使用技巧

最新推荐文章于 2024-01-02 16:37:37 发布

suixxx

最新推荐文章于 2024-01-02 16:37:37 发布

阅读量358

点赞数

文章标签： java 数据库服务器

本文链接：https://blog.csdn.net/Leo_Sui/article/details/133466994

版权

使用表达式索引

KingbaseES支持创建基于标量表达式的索引。

最常见的就是创建基于函数的索引。比如在做大小写无关的比较时，经常使用lower(name) 或者upper(name) 函数。但是因为用了函数，无法利用字段name上的普通索引，所以此时需要一个函数索引：

create table t1(name text);
create index idx_t1 on t1(upper(name));
explain select * from t1 where upper(name) = 'ada';

QUERY PLAN
-------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.21..14.37 rows=7 width=32)
    Recheck Cond: (upper(name) = 'ada'::text)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=7 width=0)
        Index Cond: (upper(name) = 'ada'::text)
(4 行记录)

也可以创建基于复杂表达式的函数索引（表达式需要用括号括起来）：

create table t1(id int, first_name text, last_name text);
create index idx_t1 on t1((first_name || ' ' || last_name));
explain select * from t1 where (first_name || ' ' || last_name) = 'Ada B';

QUERY PLAN
---------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.18..12.66 rows=4 width=68)
    Recheck Cond: (((first_name || ' '::text) || last_name) = 'Ada B'::text)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.18 rows=4 width=0)
        Index Cond: (((first_name || ' '::text) || last_name) = 'Ada B'::text)
(4 行记录)

表达式索引里面存储的值是表达式的值，所以它并不是在进行索引查找时去计算表达式的，而是在插入数据行或者更新数据行时进行计算的。因此在插入或者更新数据时，函数索引会慢一些。

使用局部索引

局部索引（partial index）是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的（叫做局部索引的谓词）。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。又因为局部索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。

比如，t1里面有100万行记录，SQL应用进程查询的是 id < 500的那批记录，则可以为它建立局部索引：

create table t1(id int);
create index idx_t1 on t1(id) where id < 500;
explain select * from t1 where id < 400;

QUERY PLAN
-------------------------------------------
Bitmap Heap Scan on t1 (cost=4.35..24.97 rows=850 width=4)
    Recheck Cond: (id < 400)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.13 rows=850 width=0)
        Index Cond: (id < 400)
(4 行记录)



explain select * from t1 where id > 400;

QUERY PLAN
-----------------------------------------------------
Seq Scan on t1 (cost=0.00..41.88 rows=850 width=4)
    Filter: (id > 400)
(2 行记录)

可以看出，符合索引条件范围的查询，能够使用创建的局部索引，不符合条件的则不能。

KingbaseES支持带任意谓词的局部索引，只要涉及被索引的表的字段就可以。不过谓词必须和那些希望从该索引中获益的查询中的where条件相匹配。准确的说只有在系统识别出该查询的where条件简单地包含了该索引的谓词时，此局部索引才能用于该查询。KingbaseES还不可以完全识别形式不同但数学上相等的谓词，但可以识别简单的不相等的包含，谓词条件必须准确匹配查询的where条件，不然系统将无法识别该索引是否可用。

由于条件匹配发生在执行计划的规划期间而不是运行期间，因此带绑定变量的条件不能使用局部索引。

使用联合索引¶

联合索引是在建立在某个关系表上多列的索引，也叫复合索引。创建联合索引时，应该将最常被访问的列放在索引列表前面。当where子句中引用了联合索引中的所有列，或者前导列，联合索引可以加快检索速度。

create table student(id int, name text, school text);
create index idx_student on student(id, name, school);

当查询条件为：1）id；2）id和name；3）id和school；4）id、name和school时，都可以使用idx_student联合索引。

当查询条件不包括id时，则无法使用联合索引。

使用索引提升Like模式匹配性能¶

KingbaseES产品支持基于Like表达式的通配符模式匹配，通过合理的应用索引，可以提高查询性能。

前匹配或者精确匹配使用Btree索引¶

普通的btree索引能够在2种情况下支持like操作符使用索引：

精确匹配型like

create table t1(id int, name text);
create index idx_t1 on t1(name);
explain select * from t1 where name like 'abc';

QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.67..61.54 rows=50 width=36)
    Filter: (name ~~ 'abc'::text)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.66 rows=50 width=0)
        Index Cond: (name = 'abc'::text)
(4 行记录)

精确匹配下，优化器能够将 name like 'abc' 转换为 name = 'abc'的索引条件。

索引字段为“C”collate

create table t1(id int, name text collate "C");
create index idx_t1 on t1(name);
explain select * from t1 where name like 'abc%';

QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.21..13.68 rows=6 width=36)
    Filter: (name ~~ 'abc%'::text)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=6 width=0)
        Index Cond: ((name >= 'abc'::text) AND (name < 'abd'::text))
(4 行记录)

Collate 为 "C"的字段做like操作时，也被转换为">= AND <" 的一对索引条件。

其他情况则需要使用text_pattern_ops，bpchar_pattern_ops指定的btree索引：

like const表达式：

create table t1(id int, name text);
create index idx_t1 on t1(name text_pattern_ops);
explain select * from t1 where name like 'abc%';
QUERY PLAN
---------------------------------------------------------
Bitmap Heap Scan on t1 (cost=4.21..13.68 rows=6 width=36)
    Filter: (name ~~ 'abc%'::text)
    -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=6 width=0)
        Index Cond: ((name ~>=~ 'abc'::text) AND (name ~<~ 'abd'::text))
(4 行记录)

like var 表达式：

create table t1(id int, name text);
create table t2(id int, name text);
create index idx_t2 on t2(name text_pattern_ops);
explain select t1.name, (select count(*) from t2 where t2.name like t1.name || '%') as cnt from t1;
KingbaseES产品也支持对a like b，其中b为一个变量表达式的情况，使用btree索引。

限制：

Btree索引应用于like模糊查询时，只能做前匹配（a like‘abc%’）或者精确匹配（a like ‘abc’），不能做后匹配（a like ‘%abc’）和中间匹配（a like ‘%abc%’），这是由btree索引只能做大于、大于等于、等于、小于、小于等于等操作决定的。

5.1.2.4.2. 后匹配reverse()表达式¶

如果只有后匹配查询需求，比如字符串like ‘%abc’，使用collate “C”的reverse()表达式的Btree索引。字符串like ‘%abc’等价于reverse(字符串) like ‘cba%’，将其转换成类似前匹配的方式。

explain select * from t1 where reverse(t1.name) like '2%';

QUERY PLAN
--------------------------------------------------------------------
Seq Scan on t1 (cost=10000000000.00..10000000017.00 rows=5 width=18)
    Filter: (reverse((name)::text) ~~ '2%'::text)
(2 rows)


create index on t1(reverse(name) collate "C");
analyze t1;
explain select * from t1 where reverse(t1.name) like '2%';

QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=6.20..11.20 rows=200 width=18)
    Filter: (reverse((name)::text) ~~ '2%'::text)
    -> Bitmap Index Scan on t1_reverse_idx (cost=0.00..6.15 rows=200 width=0)
        Index Cond: ((reverse((name)::text) >= '2'::text) AND (reverse((name)::text) < '3'::text))
(4 rows)

中间匹配使用TRGM索引¶

sys_trgm是一个扩展模块，它提供函数和操作符测定字母数字文本基于三元模型匹配的相似性，还有支持快速搜索相似字符串的索引操作符类。

三元模型是一组从一个字符串中获得的三个连续的字符。我们可以通过计数两个字符串共享的三元模型的数量来测量它们的相似性。

sys_trgm从一个字符串提取三元模型时忽略非文字字符（非字母）。当确定包含在字符串中的三元模型集合时，每个单词被认为有两个空格前缀和一个空格后缀。例如，字符串"cat"中的三元模型的集合是 "c"，"ca"，"cat"和"at"。字符串"foo|bar"中的三元模型的集合是 "f"，"fo"，"foo"，"oo"，"b"，"ba"，"bar"和"ar"。

sys_trgm模块提供GiST和GIN索引操作符类，并且额外支持基于三元模型的索引搜索：LIKE，ILIKE，~ 和 ~*查询。

sys_trgm支持like模糊匹配示例：

create extension sys_trgm;
create table t1(id int, name varchar(20));
create table t2(id int, name varchar(20));
create index idx_t2_trgm on t2 using gin(name gin_trgm_ops);
explain select t1.id, (select count(*) from t2 where t2.name like t1.name || '%') as cnt from t1;

QUERY PLAN
--------------------------------------------------------------
Seq Scan on t1 (cost=0.00..14894.01 rows=900 width=12)
    SubPlan 1
    Aggregate (cost=16.52..16.53 rows=1 width=8)
        -> Bitmap Heap Scan on t2 (cost=8.04..16.51 rows=4 width=0)
            Recheck Cond: ((name)::text ~~ ((t1.name)::text || '%'::text))
            -> Bitmap Index Scan on idx_t2_trgm (cost=0.00..8.04 rows=4 width=0)
                Index Cond: ((name)::text ~~ ((t1.name)::text || '%'::text))
(7 行记录)

不过对于like的部分匹配和全匹配情况，trgm索引的性能可能会弱于btree索引。trgm索引并不支持相等也不支持简单的比较操作符，所以你可能也需要一个普通的B-tree索引。

删除不必要的索引¶

通过查看系统表sys_stat_user_indexes，来观察哪些索引表从来没有被使用。对于长期不使用的索引可以删除它们，以便减少磁盘占用空间和维护索引的代价。

select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from sys_stat_user_indexes order by idx_scan, idx_tup_read, idx_tup_fetch;

relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
--------+--------------+----------+--------------+---------------
bar     | idx_btree_bar| 0        | 0            | 0
t1      | idx_t1_name  | 3        | 1            | 1

(2 rows)

定期vacuum和重建索引¶

KingbaseES数据库执行delete操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的update或insert操作中该部分的空间不能够被重用。可以利用数据库磁盘空间工具VACUUM，其作用是删除那些已经标示为删除的数据并释放空间。但vacuum工具不能够对相应的索引进行清理，需要手动去重建索引。

另外，对于Btree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。

可以在以下几个级别重建索引：

数据库： reindex database d1;
表：reindex table t1;
单个索引：reindex index idx1;

重置索引后，需要执行 ANALYZE命令重新分析一下表。

其他建议¶

索引的使用通常能够提高select，update以及delete语句的性能，但会降低insert语句的性能，因此索引并非是越多越好，使用索引应该遵循以下原则：

仅当要通过索引访问表中很少的一部分记录（1%~20%）
更新较少的表可以考虑使用覆盖索引

覆盖索引扫描，允许直接从索引得到元组，覆盖索引扫描要求查询中的某个表所需要的数据均可以从这个表上的同一个索引的索引页面中获得。

使用覆盖索引之前,需要先对表进行 VACUUM 才能使覆盖索引被优化器使用。对于更新频繁的表，需要多次 VACUUM。

不合适建索引的场合：

索引列上有函数（确定性的函数可以创建函数索引）
索引列选择率差，不如全表扫描
不要索引常用的小型表，其维护代价有时会高于其收益
不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间

使用索引建议工具¶

索引建议根据数据库服务器运行过程中where和join里的统计信息，根据SQL语句提示哪些关系表如果创建了相应的索引后，性能会有很大提升。

索引建议能够针对：select、update、delete、insert into…select、explain等语句，提供包括：btree、bitmap、hash、gin、gist、brin、spgist访问类型的索引建议。

索引建议列表里面包括的信息有：SQL语句、索引建议（create index DDL）、当前无索引时cost、创建索引后的cost、cost提升百分比等信息。

索引建议的使用，需要使用 KingbaseES V8R6的版本。其基本使用方法如下：

编辑kingbase.conf文件，添加以下内容后重启服务器：

shared_preload_libraries = ‘sys_stat_statements, sys_qualstats’;
sys_qualstats.enabled = true

创建相关扩展组件：

create extension sys_qualstats;
create extension sys_hypo;

执行SQL，查询索引建议：

select * from t1 where id = 100;
select * from index_recommendation_by_qual;
select * from index_recommendation_by_index;

suixxx

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
KingbaseES的索引使用技巧

对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。字符串"foo|bar"中的三元模型的集合是 "f"，"fo"，"foo"，"oo"，"b"，"ba"，"bar"和"ar"。由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。最常见的就是创建基于函数的索引。
复制链接

扫一扫