KingbaseES不同字符类型比较转换规则

Postgresql 常用的字符数据类型的有char、varchar和text，其中 char 固定长度类型， varchar 和 text 是可变长度类型。这三种类型在进行比较时，会进行隐含的类型转换。这种转换会导致索引可能无法使用，影响SQL的执行计划。以下以例子的形式展示Postgresql 不同字符数据类型间的转换规则。

一、创建测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table test_char(id char(9),desc_info text);
create table test_varchar(id varchar(9),desc_info text);
create table test_text(id text,desc_info text);
​
insert into test_char select generate_series(100001,200000),repeat('a',100);
insert into test_varchar select generate_series(100001,200000),repeat('a',100);
insert into test_text select generate_series(100001,200000),repeat('a',100);
​
create index ind_test_char on test_char(id);
create index ind_test_varchar on test_varchar(id);
create index ind_test_text on test_text(id);
​
analyze test_char;
analyze test_varchar;
analyze test_text;

二、创建SQL游标

1
2
3
4
5
6
7
8
prepare test_char_bind_varchar(varchar) as select * from test_char where id=$1;
prepare test_char_bind_text(text) as select * from test_char where id=$1;
​
prepare test_varchar_bind_char(char) as select * from test_varchar where id=$1;
prepare test_text_bind_char(char) as select * from test_text where id=$1;
​
prepare test_varchar_bind_text(text) as select * from test_varchar where id=$1;
prepare test_text_bind_varchar(varchar) as select * from test_text where id=$1;

三、Postgresql字符类型的隐含转换规则

1、对于 varchar 与 char 比较，默认是 varchar 转成 char。

例子2，由于等式左边发生了类型转换，无法使用索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
例子1：
testdb=# explain execute test_char_bind_varchar('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_char on test_char  (cost=0.42..8.44 rows=1 width=111)
   Index Cond: (id = '123456'::bpchar)
(2 rows)
​
例子2：等式左边发生类型转换，无法使用索引
testdb=# explain execute test_varchar_bind_char('123456');
                           QUERY PLAN                           
-----------------------------------------------------------------
 Seq Scan on test_varchar  (cost=0.00..2975.00 rows=1 width=108)
   Filter: ((id)::bpchar = '123456'::bpchar)
(2 rows)

2、对于 text 与 char 比较，默认是 char 转成 text 。

例子3，由于等式左边发生了类型转换，无法使用索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
例子3：等式左边发生类型转换，无法使用索引。
testdb=# explain execute test_char_bind_text('123456');
                           QUERY PLAN                          
----------------------------------------------------------------
 Seq Scan on test_char  (cost=0.00..3225.00 rows=500 width=111)
   Filter: ((id)::text = '123456'::text)
(2 rows)
​
例子4：
testdb=# explain execute test_text_bind_char('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_text on test_text  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: (id = '123456'::text)
(2 rows)

3、对于 varchar 与 text 比较，默认是 varchar 转成 text ，但二者的转换不影响索引的使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
testdb=# explain execute test_varchar_bind_text('123456');
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Index Scan using ind_test_varchar on test_varchar  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: ((id)::text = '123456'::text)
(2 rows)
​
testdb=# explain execute test_text_bind_varchar('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_text on test_text  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: (id = '123456'::text)
(2 rows)

PG 字符类型数据转换规则：varchar -> char -> text

四、KingbaseES 类型转换及优化

用过Oracle的人都知道，char与varchar 之间的比较不会因为类型不同而无法使用索引，Kingbase在特性上向Oracle靠拢，为用户从Oracle向KingbaseES迁移提供便利。KingbaseES 继承Postgresql 的特性，同时通过代码的优化，避免了char与varchar和text之间比较导致的转换而无法使用索引的情况。以下的例子在KingbaseES V8R6 版本进行过实际验证。

1、对于 varchar 与 char 比较，同样是 varchar 转成 char。

kingbase 针对这个问题，进行了特殊的优化处理，即使等式左边的varchar发生了类型转换，也不影响索引的使用，如：例子6。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
例子5：
testdb=# explain execute test_char_bind_varchar('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_char on test_char  (cost=0.42..8.44 rows=1 width=111)
   Index Cond: (id = '123456'::bpchar)
(2 rows)
​
例子6：不会因为等式左边发生类型转换而无法使用索引。
testdb=# explain execute test_varchar_bind_char('123456');
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Index Scan using ind_test_varchar on test_varchar  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: ((id)::text = '123456'::text)
(2 rows)

2、对于 text 与 char 比较，kingbase 进行了特殊的优化处理，使得转换发生在等式的右边，不影响索引的使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
例子7：
testdb=# explain execute test_char_bind_text('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_char on test_char  (cost=0.42..8.44 rows=1 width=111)
   Index Cond: (id = '123456'::bpchar)
(2 rows)
​
例子8：
testdb=# explain execute test_text_bind_char('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_text on test_text  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: (id = '123456'::text)
(2 rows)

3、对于 varchar 与 text 比较，默认是 varchar 转成 text 。与PG一样，二者的转换不影响索引的使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
test=# explain execute test_varchar_bind_text('123456');
                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 Index Scan using ind_test_varchar on test_varchar  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: ((id)::text = '123456'::text)
(2 rows)
​
test=# explain execute test_text_bind_varchar('123456');
                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
 Index Scan using ind_test_text on test_text  (cost=0.29..8.31 rows=1 width=108)
   Index Cond: (id = '123456'::text)
(2 rows)

 

Tips：以上例子是基于Postgresql 12.3 和 KingbaseES V8R6版本测试的结果。

posted @ 2021-10-28 16:11  数据库集中营  阅读(544)  评论(0)  编辑  收藏  举报