作者：
韓鋒
從數據庫架構上看，考慮到分布式與集中式的差異較大，本次將重點放在集中式數據庫上。從之前接觸用戶到第三方調查機構的報告來看，數據庫的集中式架構仍然是主流架構，占據近八成左右的市場份額。因此選擇以集中式數據庫為評測對象。
隨著國產數據庫應用步入深水區，用戶開始在更核心、更多元的場景使用國產庫。在使用過程之中，用戶非常關心的一個問題，就是國產數據庫的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 |
--------------------------------------------------------------------------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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+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)
* 直接使用索引掃描，實現了跳躍掃描功能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)
* 直接使用索引掃描，實現了跳躍掃描功能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)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+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;責任編輯：武曉燕
來源：
韓鋒頻道
