国产集中库SQL能力评测 – 访问路径
随着国产数据库应用步入深水区，用户开始在更核心、更多元的场景使用国产库。在使用过程之中，用户非常关心的一个问题，就是国产数据库的SQL支持情况怎么样？是不是能如 Oracle 那样，针对复杂多变的 SQL 也能生成相对优秀的执行计划，进而保证良好的执行效率。之前也曾听闻过用户吐槽，国产数据库的优化器存在诸多不足。这也促使笔者考虑针对国产数据库做些 SQL 能力的评测，方便用户有着更深入的了解。这将是一个系列，笔者看个人精力会逐步完成。受限于个人能力水平及时间精力等因素，测试过程及结果仅代表个人，不能完全反映厂商产品能力，欢迎批评指正。
1. 评测方案说明
1)评测对象架构：集中式
从数据库架构上看，考虑到分布式与集中式的差异较大，本次将重点放在集中式数据库上。从之前接触用户到第三方调查机构的报告来看，数据库的集中式架构仍然是主流架构，占据近八成左右的市场份额。因此选择以集中式数据库为评测对象。
2)评测功能标准：Oracle
长期以来，Oracle 数据库一直是数据库业内的标杆性产品，特别是在集中式数据库领域。因此，本次测试会以Oracle 的能力为标准与国内数据库进行对比。此外，考虑到国内大部分已有业务也都是基于 Oracle 去开发的，因此迁移到国产数据库采用与Oracle为参照物也具有很好的参考意义。
3)评测产品范围：主流+代表性
国内数据库厂商及产品非常多，选择哪些厂商及产品是个很头疼的事情。这里本着主流或有代表性的原则进行选择。从现有集中式数据库的市场占有率方面，选择头部的厂商达梦、电科金仓为代表。从生态方面选择 openGauss 生态的海量数据；MySQL生态上没有太好选择，故使用最新社区版本；PG 生态上由之前的电科金仓来代表。自研方面，则采用的崖山数据库，毕竟其主打也是Oracle的兼容能力。最后也选择 Oracle 在国内仍然大规模使用的版本作为参照对象。
4)评测环境&版本
测试环境：采用Docker镜像方式测试版本：采用官方镜像（可能非最新）见下文测试数据：自行构造测试配置：数据库默认配置，未优化
图片
2. Oracle 访问路径能力说明
这里主要谈 Oracle 数据的表及索引的访问路径问题。
1)表访问路径
❖ 全表扫描为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处。一个多块读操作可以使一次I/O能读取多块数据块，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。这也是最为常规的访问路径，下文将以此方式为主。
❖ ROWID扫描行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。为了通过ROWID存取表，Oracle首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。
❖ 采样扫描将从全部数据块中读取指定比例的数据之后，然后再通过过滤返回满足条件的行。在每次执行时，都会从全部的数据块中读取指定比例的数据块。所以每次读取的数据块都是不同的，当某个数据块被选定为读取对象时，块中所有行将被全部读取。此种访问路径常见于统计信息收集等场景之中。
2)索引访问路径
❖ 索引唯一扫描通过唯一索引查找一个数值经常返回单个ROWID。如果存在UNIQUE或PRIMARY KEY约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。在大部分情况下该扫描方式主要被使用在检索唯一ROWID的查询中，为了进行索引唯一扫描而必须基于主键来创建索引或者创建唯一索引，且在SQL语句中必须为索引列使用"="比较运算符。否则即使基于具有唯一值的列创建了索引，在执行时优化器也不能可能选择索引唯一扫描，而会选择范围扫描。
❖ 索引范围扫描索引最普遍的数据读取方式，优化器选择该扫描方式的情况有两种，即由开始值与结束值的情况和有一个以上的行但没有结束的情况。索引范围扫描在寻找开始位置的时候使用随机读取，但之后所执行的全部都是连续扫描。如果再精确描述，即在查找分支块时使用的是随机读取，在经过分支块查找到开始的叶块之后所执行的就是连续扫描。在扫描方向上，又可分为升序扫描和降序扫描。
❖ 索引全扫描索引全扫描不读取索引结构中的每个块，这与其名称表面上相悖。索引全扫描处理索引的所有叶块，但为了查找到第一个叶块需要处理足够多的分支块。一旦在索引中获得一个叶块，则其前和后的叶块将按顺序被链接起来。即，叶块不仅可以通过分支块导航；而且，一旦获得一个叶块，也可以随指针获得下一个叶块。事实上，使用这种双向链表可以在索引结构中前进或后退。索引全扫描使用单块IO按顺序读取索引，它从根开始，通过分支块到达第一个叶块。这些块都是每次读取一块。当获取第一个叶块时，可按顺序读取每个叶块，同样是一次一块。索引全扫描从索引中按顺序读取数据。因此，索引全扫描可以避免排序。
❖ 索引快速全扫描索引快速全扫描将索引等同于表的一个缩小版本。它一次读取索引多个数据块，处理叶块数据，并忽略分支块。它能够比索引全扫描更快地读取索引结构，因为它是使用了多块io。扫描索引中的所有的数据块，与索引全扫描很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。索引快速全扫描每次I/O读取的是多个数据块，这也是该方式与索引全扫描之间的主要区别。
❖ 索引跳跃扫描索引跳过扫描改进了非前缀列的索引扫描。通常，扫描索引块比扫描表数据块更快。跳过扫描允许将复合索引在逻辑上拆分为更小的子索引。在跳过扫描中，查询中未指定复合索引的初始列。换句话说，它被跳过了。逻辑子索引的数量由初始列中不同值的数量决定。如果复合索引的前导列中只有很少的不同值，而索引的非前导键中有很多不同值，则跳过扫描是有利的。
3)Oracle 测试示例
复制
-- 表扫描：全表扫描
SQL> explain plan for select * from emp;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 273K| 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 10000 | 273K| 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
-- 索引扫描：索引唯一扫描（index unique scan）
SQL> explain plan for select * from emp where emp_id=111;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
-- 索引扫描：索引范围扫描（index range scan）
SQL> explain plan for select * from emp where emp_id<100;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2772 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 99 | 2772 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_PK | 99 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
-- 索引扫描：索引快速全扫描（index fast full scan）
SQL> explain plan for select emp_name from emp;
SQL> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 80000 | 14 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_EMP_NAME | 10000 | 80000 | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
-- 索引扫描：索引全扫描（index full scan）
SQL> exec dbms_stats.set_table_stats(ownname=>TESTUSER,tabname=>EMP,numrows=>1000000,numblks=>5000);
SQL> select num_rows,blocks from user_tables where table_name=EMP;
NUM_ROWS BLOCKS
---------- ----------
1000000 5000
//通过伪造统计信息，放大表扫描的成本，让优化器选择使用索引全扫描
SQL> explain plan for select emp_name from emp order by 1;
SQL> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 7812K| 46 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_EMP_NAME | 1000K| 7812K| 46 (0)| 00:00:01 |
---------------------------------------------------------------------------------
-- 索引扫描：索引跳跃扫描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,t,cascade=>true);
SQL> explain plan for select * from t where object_name=TEST;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_T | 7 | 189 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.
3. 国产库访问路径能力评测
下文将对国产数据库（含MySQL）做测试对比。在之前先看下结论，国产数据库在访问路径方面能力都还可以，部分数据库还是稍有不足，具体可参考下面及之后的测试步骤。
图片
1)MySQL
复制
-- 表扫描：全表扫描
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
-- 索引扫描：索引单键扫描
mysql> explain select * from emp where emp_id=111;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
-- 索引扫描：索引范围扫描
mysql> explain select * from emp where emp_id<100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 99 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
-- 索引扫描：索引扫描
mysql> explain select emp_name from emp;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_name | 33 | NULL | 9796 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
-- 索引扫描：索引跳跃扫描
mysql> create table t as select * from information_schema.tables;
mysql> insert into t select * from t;
...
mysql> insert into t select * from t;
mysql> alter table t add id int;
mysql> create index idx_tmp on t(table_type,table_name);
mysql> analyze table t;
mysql> explain select table_type,table_name from t where table_name=COLLATIONS;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| 1 | SIMPLE | t | NULL | range | idx_tmp | idx_tmp | 195 | NULL | 18045 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
-- 表扫描：全表扫描
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
-- 索引扫描：索引单键扫描
mysql> explain select * from emp where emp_id=111;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
-- 索引扫描：索引范围扫描
mysql> explain select * from emp where emp_id<100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 99 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
-- 索引扫描：索引扫描
mysql> explain select emp_name from emp;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_name | 33 | NULL | 9796 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
-- 索引扫描：索引跳跃扫描
mysql> create table t as select * from information_schema.tables;
mysql> insert into t select * from t;
...
mysql> insert into t select * from t;
mysql> alter table t add id int;
mysql> create index idx_tmp on t(table_type,table_name);
mysql> analyze table t;
mysql> explain select table_type,table_name from t where table_name=COLLATIONS;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| 1 | SIMPLE | t | NULL | range | idx_tmp | idx_tmp | 195 | NULL | 18045 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.
2)DM
复制
-- 表扫描：全表扫描
SQL> explain select * from emp;
1 #NSET2: [1, 10000, 163]
2 #PRJT2: [1, 10000, 163]; exp_num(6), is_atom(FALSE)
3 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
-- 索引扫描：索引唯一扫描
SQL> explain select * from emp where emp_id=111;
1 #NSET2: [1, 1, 163]
2 #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3 #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)
4 #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(111),exp_cast(111)], is_global(0)
* 没有唯一扫描方式，DM都认为是范围扫描
-- 索引扫描：索引范围扫描
SQL> explain select * from emp where emp_id<100;
1 #NSET2: [1, 99, 163]
2 #PRJT2: [1, 99, 163]; exp_num(6), is_atom(FALSE)
3 #BLKUP2: [1, 99, 163]; INDEX33555485(EMP)
4 #SSEK2: [1, 99, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range(null2,exp_cast(100)), is_global(0)
* 被认为是从NULL到指定数值的范围扫描
-- 索引扫描：索引全扫描
SQL> explain select emp_name from emp;
1 #NSET2: [1, 10000, 60]
2 #PRJT2: [1, 10000, 60]; exp_num(2), is_atom(FALSE)
3 #SSCN: [1, 10000, 60]; IDX_EMP_NAME(EMP); btr_scan(1); is_global(0)
* 直接使用索引扫描，不用再回表查
-- 索引扫描：索引跳跃扫描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> CALL SP_TAB_INDEX_STAT_INIT (TESTUSER, T);
SQL> explain select * from t where object_name=TEST;
1 #NSET2: [1, 109, 64]
2 #PRJT2: [1, 109, 64]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [1, 109, 64]; T.OBJECT_NAME = TEST
4 #SSCN: [1, 109, 64]; IDX_T(T); btr_scan(1); is_global(0)
* 直接使用索引扫描，实现了跳跃扫描功能
-- 表扫描：全表扫描
SQL> explain select * from emp;
1 #NSET2: [1, 10000, 163]
2 #PRJT2: [1, 10000, 163]; exp_num(6), is_atom(FALSE)
3 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
-- 索引扫描：索引唯一扫描
SQL> explain select * from emp where emp_id=111;
1 #NSET2: [1, 1, 163]
2 #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3 #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)
4 #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(111),exp_cast(111)], is_global(0)
* 没有唯一扫描方式，DM都认为是范围扫描
-- 索引扫描：索引范围扫描
SQL> explain select * from emp where emp_id<100;
1 #NSET2: [1, 99, 163]
2 #PRJT2: [1, 99, 163]; exp_num(6), is_atom(FALSE)
3 #BLKUP2: [1, 99, 163]; INDEX33555485(EMP)
4 #SSEK2: [1, 99, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range(null2,exp_cast(100)), is_global(0)
* 被认为是从NULL到指定数值的范围扫描
-- 索引扫描：索引全扫描
SQL> explain select emp_name from emp;
1 #NSET2: [1, 10000, 60]
2 #PRJT2: [1, 10000, 60]; exp_num(2), is_atom(FALSE)
3 #SSCN: [1, 10000, 60]; IDX_EMP_NAME(EMP); btr_scan(1); is_global(0)
* 直接使用索引扫描，不用再回表查
-- 索引扫描：索引跳跃扫描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> CALL SP_TAB_INDEX_STAT_INIT (TESTUSER, T);
SQL> explain select * from t where object_name=TEST;
1 #NSET2: [1, 109, 64]
2 #PRJT2: [1, 109, 64]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [1, 109, 64]; T.OBJECT_NAME = TEST
4 #SSCN: [1, 109, 64]; IDX_T(T); btr_scan(1); is_global(0)
* 直接使用索引扫描，实现了跳跃扫描功能1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.
3)KingBase
复制
-- 表扫描：全表扫描
TEST=# explain select * from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..192.00 rows=10000 width=39)
* 表的顺序扫描
-- 索引扫描：索引唯一扫描
TEST=# explain select * from emp where emp_id=111;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using EMP_PK on emp (cost=0.29..8.30 rows=1 width=39)
Index Cond: (emp_id = 111::numeric)
* 标准索引扫描
-- 索引扫描：索引范围扫描
TEST=# explain select * from emp where emp_id<100;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using EMP_PK on emp (cost=0.29..93.77 rows=99 width=39)
Index Cond: (emp_id < 100::numeric)
* 标准索引扫描
-- 索引扫描：索引全扫描
TEST=# explain select emp_name from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..192.00 rows=10000 width=13)
* 默认走了全表扫描(即使增加到100万的记录也是如此)
TEST=# set enable_hint=on;
TEST=# explain analyze verbose select /*+IndexOnlyScan(emp idx_emp_name)*/ emp_name from emp;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_emp_name on public.emp (cost=0.42..69579.87 rows=1000000 width=15) (actual time=0.209..151.571 rows=1000000 loops=1)
Output: emp_name
Heap Fetches: 1100000
Planning Time: 0.123 ms
Execution Time: 172.407 ms
* 尝试强制走索引扫描，成本更高。
-- 索引扫描：索引跳跃扫描（index skip scan）
TEST=# create table t as select 1 id,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
TEST=# insert into t select 2 ,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
...
TEST=# create index idx_t on t(id,attname );
TEST=# analyze verbose t;
TEST=# explain select * from t where attname =TEST;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using idx_t on t (cost=0.29..889.09 rows=11 width=92)
Index Cond: (attname = TEST::name)
* 直接使用索引扫描，实现了跳跃扫描功能
-- 表扫描：全表扫描
TEST=# explain select * from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..192.00 rows=10000 width=39)
* 表的顺序扫描
-- 索引扫描：索引唯一扫描
TEST=# explain select * from emp where emp_id=111;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using EMP_PK on emp (cost=0.29..8.30 rows=1 width=39)
Index Cond: (emp_id = 111::numeric)
* 标准索引扫描
-- 索引扫描：索引范围扫描
TEST=# explain select * from emp where emp_id<100;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using EMP_PK on emp (cost=0.29..93.77 rows=99 width=39)
Index Cond: (emp_id < 100::numeric)
* 标准索引扫描
-- 索引扫描：索引全扫描
TEST=# explain select emp_name from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..192.00 rows=10000 width=13)
* 默认走了全表扫描(即使增加到100万的记录也是如此)
TEST=# set enable_hint=on;
TEST=# explain analyze verbose select /*+IndexOnlyScan(emp idx_emp_name)*/ emp_name from emp;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_emp_name on public.emp (cost=0.42..69579.87 rows=1000000 width=15) (actual time=0.209..151.571 rows=1000000 loops=1)
Output: emp_name
Heap Fetches: 1100000
Planning Time: 0.123 ms
Execution Time: 172.407 ms
* 尝试强制走索引扫描，成本更高。
-- 索引扫描：索引跳跃扫描（index skip scan）
TEST=# create table t as select 1 id,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
TEST=# insert into t select 2 ,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
...
TEST=# create index idx_t on t(id,attname );
TEST=# analyze verbose t;
TEST=# explain select * from t where attname =TEST;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using idx_t on t (cost=0.29..889.09 rows=11 width=92)
Index Cond: (attname = TEST::name)
* 直接使用索引扫描，实现了跳跃扫描功能1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.
4)YashanDB
复制
-- 表扫描：全表扫描
SQL> explain plan for select * from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 41( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引唯一扫描（index unique scan）
SQL> explain select * from emp where emp_id=111;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引范围扫描（index range scan）
SQL> explain select * from emp where emp_id<100;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 100| 1( 0)| |
|* 2 | INDEX RANGE SCAN | EMP_PK | TESTUSER | 100| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引快速全扫描（index fast full scan）
SQL> explain select emp_name from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | INDEX FAST FULL SCAN | IDX_EMP_NAME | TESTUSER | 10000| 29( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引全扫描（index full scan）
exec dbms_stats.set_table_stats(TESTUSER,EMP,null,1000000,5000,34);
//通过伪造统计信息，放大表扫描的成本，让优化器选择使用索引全扫描
SQL> explain select emp_name from emp order by 1;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | INDEX FULL SCAN | IDX_EMP_NAME | TESTUSER | 1000000| 29( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引跳跃扫描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
...
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,t,cascade=>true);
SQL> explain select * from t where object_name=TEST;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | INDEX SKIP SCAN | IDX_T | TESTUSER | 1| 3( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 表扫描：全表扫描
SQL> explain plan for select * from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 41( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引唯一扫描（index unique scan）
SQL> explain select * from emp where emp_id=111;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引范围扫描（index range scan）
SQL> explain select * from emp where emp_id<100;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 100| 1( 0)| |
|* 2 | INDEX RANGE SCAN | EMP_PK | TESTUSER | 100| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引快速全扫描（index fast full scan）
SQL> explain select emp_name from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | INDEX FAST FULL SCAN | IDX_EMP_NAME | TESTUSER | 10000| 29( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引全扫描（index full scan）
exec dbms_stats.set_table_stats(TESTUSER,EMP,null,1000000,5000,34);
//通过伪造统计信息，放大表扫描的成本，让优化器选择使用索引全扫描
SQL> explain select emp_name from emp order by 1;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | INDEX FULL SCAN | IDX_EMP_NAME | TESTUSER | 1000000| 29( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 索引扫描：索引跳跃扫描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
...
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,t,cascade=>true);
SQL> explain select * from t where object_name=TEST;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | INDEX SKIP SCAN | IDX_T | TESTUSER | 1| 3( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.
5)Vertbase
复制
-- 表扫描：全表扫描
vastbase=> explain select * from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..218.00 rows=10000 width=44)
* 表的顺序扫描
-- 索引扫描：索引唯一扫描
vastbase=> explain select * from emp where emp_id=111;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using emp_pk on emp (cost=0.00..8.27 rows=1 width=44)
Index Cond: (emp_id = 111::number)
* 标准索引扫描
-- 索引扫描：索引范围扫描
vastbase=> explain select * from emp where emp_id<100;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using emp_pk on emp (cost=0.00..9.13 rows=50 width=44)
Index Cond: (emp_id < 100::number)
* 标准索引扫描
-- 索引扫描：索引全扫描
vastbase=> explain select emp_name from emp;
-- 表扫描：全表扫描
vastbase=> explain select * from emp;
QUERY PLAN
----------------------------------------------------------
Seq Scan on emp (cost=0.00..218.00 rows=10000 width=44)
* 表的顺序扫描
-- 索引扫描：索引唯一扫描
vastbase=> explain select * from emp where emp_id=111;
QUERY PLAN
-------------------------------------------------------------------
Index Scan using emp_pk on emp (cost=0.00..8.27 rows=1 width=44)
Index Cond: (emp_id = 111::number)
* 标准索引扫描
-- 索引扫描：索引范围扫描
vastbase=> explain select * from emp where emp_id<100;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using emp_pk on emp (cost=0.00..9.13 rows=50 width=44)
Index Cond: (emp_id < 100::number)
* 标准索引扫描
-- 索引扫描：索引全扫描
vastbase=> explain select emp_name from emp;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.
THE END
