Postgresql与Oracle数据关于列索引的几个对比实验

朱清伟的学习笔记 2021-12-23

434

今天做个有趣的实验。

/*********************postgresql 数据库测试开始*********************/

--本次测试以pg 数据库 10.11版本
postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 10.11, compiled by Visual C++ build 1800, 64-bit
(1 行记录)

--创建一张测试表
create table t_index_null(id varchar(50),name text);

--造测试数据
insert into t_index_null(id,name)
select t.id,'test'||t.id::varchar
  from (select generate_series as id
          from generate_series(1,100000) ) as t;

--添加一条 id为null的数据  
insert into t_index_null(name) values ('zqw01');

--添加一条 id为空字符的数据  
insert into t_index_null(id,name) values ('','zqw02');

--对 id列 创建索引
create index idx_t_index_null_id on t_index_null(id);

--检锁id=1 的数据,看执行计划是否会走索引，执行计划走索引过滤
explain analyze 
select id,name
  from t_index_null 
 where id = '1';
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_index_null_id on t_index_null  (cost=0.29..8.31 rows=1 width=14) (actual time=0.061..0.062 rows=1 loops=1)
   Index Cond: ((id)::text = '1'::text)
 Planning time: 0.289 ms
 Execution time: 0.086 ms
(4 行记录)

--检锁id='' (空字符) 的数据,看执行计划是否会走索引，执行计划走索引过滤
explain analyze 
select id,name
  from t_index_null 
 where id = '';
                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_index_null_id on t_index_null  (cost=0.29..8.31 rows=1 width=14) (actual time=0.050..0.052 rows=1 loops=1)
   Index Cond: ((id)::text = ''::text)
 Planning time: 0.114 ms
 Execution time: 0.078 ms
(4 行记录)

--检锁id is null (空字符) 的数据,看执行计划是否会走索引,执行计划走索引过滤
explain analyze 
select id,name
  from t_index_null 
 where id is null;
                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_index_null_id on t_index_null  (cost=0.29..10.97 rows=3 width=14) (actual time=0.105..0.106 rows=1 loops=1)
   Index Cond: (id IS NULL)
 Planning time: 0.115 ms
 Execution time: 0.133 ms
(4 行记录)

/*********************postgresql 数据库测试结束*********************/

/*********************oracle 数据库测试开始*************************/

--本次测试以oracle 数据库 11.0版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE  11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

--创建一张测试表
create table scott.t_index_null(id varchar2(50),name varchar2(4000));

--造测试数据
insert into scott.t_index_null(id,name)
select level,'test'||level
  from dual 
 connect by level <= 100000;

--添加一条 id为null的数据  
insert into scott.t_index_null(name) values ('zqw01');

--添加一条 id为空字符的数据  
insert into scott.t_index_null(id,name) values ('','zqw02');

--对 id列 创建索引
create index idx_t_index_null_id on scott.t_index_null(id);

--检锁id=1 的数据,看执行计划是否会走索引，执行计划走索引过滤
set auto off


explain plan for
select id,name
  from scott.t_index_null 
 where id = '1';
 
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 42603800
---------------------------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  1 |  2029 |  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_NULL    |  1 |  2029 |  2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | IDX_T_INDEX_NULL_ID |  1 |    |  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"='1')
Note
-----
   - dynamic sampling used for this statement (level=2)

--检锁id='' (空字符) 的数据,看执行计划是否会走索引，从下面的执行计划来看, id = ''过滤,并没有走索引扫描，而是走全表扫表
set auto off


explain plan for
select id,name
  from scott.t_index_null 
 where id = '';
 
select * from table(dbms_xplan.display);  


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1102671017
-----------------------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1 |  2029 |  0   (0)|    |
|*  1 |  FILTER      |      |    |    |         |    |
|   2 |   TABLE ACCESS FULL| T_INDEX_NULL |   118K|   229M|   104   (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

--检锁id is null (空字符) 的数据,看执行计划是否会走索引,从下面的执行计划来看,id is null 也是没有走索引扫描,而是走全表扫表
set auto off


explain plan for
select id,name
  from scott.t_index_null 
 where id is null;
                                                            
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1935092368
----------------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     4 |  8116 |   104   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_INDEX_NULL |     4 |  8116 |   104   (2)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

/*********************oracle 数据库测试结束*************************/

总结:

postgresql 数据库某一列如果存储空字符'', 或者存在null值,然后在对这一列加索引,如果SQL用到了 = '' 或者 is null 过滤,执行计划有可能会走索引;

Oracle 数据库某一列如果存储空字符'', 或者存在null值,然后对这一列加索引,如果SQL用到了 = '' 或者 is null 过滤,SQL执行计划是一定不会走索引扫描;

oracle postgresql

文章转载自朱清伟的学习笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Postgresql与Oracle数据关于列索引的几个对比实验

评论