kingbase SQL 调优基础-05

SQL 优化手段
 SQL 调优实际操作中使用的一些调优手段：
 使用索引
 使用 HINT
 调整性能参数
 使用并行
 使用 Query Mapping
 物化视图
 逻辑优化规则

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

5.1.1.2 Hash 索引
Hash 索引查询效率比 Btree 高得多，相比 Btree 索引需要从根节点查询到叶子节点，且需要多次 IO 及 CPU 操
作，Hash 索引因其索引结构的特殊性，理想情况下仅需一次检索即可定位到数据。
与此同时，Hash 也存在一定的局限性，只适合做等值查询。由于 Hash 索引是通过比较哈希值来做定位，因此
当查询列上有较大比例的重复值时，会带来严重的哈希冲突，进而影响查询速度，这种情况下就不建议使用 Hash 索引。
create index i_hash on t2 using hash(id);

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

Bitmap 索引主要适用于当表具有很多属性且查询可能会涉及其中任意组合时的情况。
优化器为普通索引建立位图表，即 Bitmap heap table，主要包括 2 种扫描方式：
 Bitmap Index Scan：用来在内存中创建一个位图表，每一个 Bit 表示一个与过滤条件有关的页面。Bit 上有数
据为 1，不可能为 0。通过位图扫描，能够快速确定某个值在哪些记录上存在与否。
 Bitmap Heap Scan：Bitmap 上检索到的记录都对应其 rowid，用这个 rowid 到关系表上去查找完整的记录信息
并过滤。
create index idx_t on test using bitmap(a);

5.1.1.5 GIN 索引
GIN 是通用倒排序索引（Generalized Inverted Index），它是一个存储对（Key，postion list）的集合，其中
Key 是一个键值，而 postion list 是包含 Key 的位置值。比如（’Tom’，’10:25 14:3 29:5’）就表示关键字’Tom’ 在
这些位置（元组 TID）上存在
通用倒排序索引（GIN）主要适用于包含多个组合值的查询，如数组、全文检索等。
create table t3(id int, info text);
insert into t3 values(generate_series(1,10000), md5(random()::text));
create index i_t3_gin on t3 using gin(to_tsvector('english',info));
analyze;
explain analyze select * from t3 where to_tsvector('english', info) @@ plainto_tsquery( 'hello');

5.1.1.6 GiST 索引
GiST 是通用的搜索树 (Generalized Search Tree)。它是一种平衡树结构的访问方法，在系统中作为一个基本模
版，可以使用它实现任意索引模式。B-trees，R-trees 和许多其它的索引模式都可以用 GiST 实现。
GiST 索引适用于多维数据类型和集合数据类型，和 Btree 索引类似，同样适用于其他的数据类型。GiST 可以用
来做位置搜索，如包含、相交、左边、右边等。和 Btree 索引相比，GiST 多字段索引在查询条件中包含索引字段的
任何子集都会使用索引扫描，而 Btree 索引只有查询条件包含第一个索引字段才会使用索引扫描。

索引使用技巧
函数索引
create index idx_t1 on t1(upper(name));
也可以创建基于复杂表达式的函数索引（表达式需要用括号括起来）：
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';

使用局部索引
局部索引（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;

使用联合索引
联合索引是在建立在某个关系表上多列的索引，也叫复合索引。创建联合索引时，应该将最常被访问的列放在索
引列表前面。当 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 时，则无法使用联合索引。

5.1.2.4 使用索引提升 Like 模式匹配性能
前匹配或者精确匹配使用 Btree 索引
普通的 btree 索引能够在 2 种情况下支持 like 操作符使用索引：
1. 精确匹配型 like
create table t1(id int, name text);
create index idx_t1 on t1(name);
explain select * from t1 where name like 'abc';
精确匹配下，优化器能够将 name like ’abc’ 转换为 name = ’abc’ 的索引条件

2. 索引字段为“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%';
Collate 为”C” 的字段做 like 操作时，也被转换为”>= AND <” 的一对索引条件。
其他情况则需要使用 text_pattern_ops，bpchar_pattern_ops 指定的 btree 索引：

3. 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%';

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%';

5.1.2.5 删除不必要的索引
通过查看系统表 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;

5.1.2.6 定期 vacuum 和重建索引
利用数据库磁盘空间工具 VACUUM，其作用是删除那些已经标示为删除的数据并释放空间。
可以在以下几个级别重建索引：
 数据库： reindex database d1;
 表：reindex table t1;
 单个索引：reindex index idx1;
重置索引后，需要执行 ANALYZE 命令重新分析一下表。

5.1.3 使用索引建议工具
索引建议的使用，需要使用 kingbase V8R6 的版本。其基本使用方法如下：
1. 编辑 kingbase.conf 文件，添加以下内容后重启服务器：
shared_preload_libraries = ‘sys_stat_statements, sys_qualstats’;
sys_qualstats.enabled = true
2. 创建相关扩展组件：
create extension sys_qualstats;
create extension sys_hypo;
3. 执行 SQL，查询索引建议：
select * from t1 where id = 100;
select * from index_recommendation_by_qual;
select * from index_recommendation_by_index;